Board index » delphi » Working with CLOB's

Working with CLOB's

I'm using Delphi 4 SP2, BDE 5.1, and Oracle 8.0.5 on NT.

I'm having no success in using a CLOB field in my application .  So now
I am trying to setup a simple test application to work out the
problems.  I have a form, a database, a select query, an update query, a
richedit to display and edit the field, and an edit box to enter the
record id to retrieve.

When I enter the record id and exit the edit box, the onexit event does
this:
    procedure TForm1.edtSearchExit(Sender: TObject);
begin
  with qrySelect do begin
    { SELECT KEY_ID, DSCR, DOC FROM FIT_DBA.STANDARD
       WHERE KEY_ID = :KEY_ID  }
    Close;
    Params[0].AsString := edtSearch.Text;
    Open;
  end;
end;

qrySearch.RequestLive is False.   The AfterScroll event does this:

procedure TForm1.qrySelectAfterScroll(DataSet: TDataSet);
begin
  RichEdit1.Lines.Assign( TMemoField( DataSet.FieldByName( 'DOC' )) );
  edtSize.Text := IntToStr( TMemoField( DataSet.FieldByName( 'DOC'
)).BlobSize );
end;

The richedit component is stuffed with the blob field contents.  I edit,
and then press a button.  The button's click event does this:

procedure TForm1.btnSaveClick(Sender: TObject);
begin

  try
    with qryUpdate do begin
      { UPDATE FIT_DBA.STANDARD
        SET DOC=:DOC
        WHERE KEY_ID=:KEY_ID }

      Params[0].AsMemo := RichEdit1.Text;
      Params[1].AsInteger := qrySelect.FieldByName( 'KEY_ID' ).Value;
      ExecSQL;
    end;
  except
  end;
end;

When the ExecSQL is performed, I get the following error:  ORA-22275:
invalid LOB locator specified.'

In the initialization section of the unit,  I set the global variable,
Oracle8Blobs to True;

What do I need to do to get this simple setup to work?
--
Jim Poe
<j...@fulcrumit.com>
Fulcrum InteTech, Inc.

 

Re:Working with CLOB's


Just for grins, try performing the update inside of a database transaction.

V/R
Russell L. Smith

Quote
Jim Poe wrote in message <367AB7F8.9A118...@fulcrumit.com>...
>When the ExecSQL is performed, I get the following error:  ORA-22275:
>invalid LOB locator specified.'

Re:Working with CLOB's


Russell,

Thanks for the response.  I wrapped the update in a transaction and got the
same error.

Quote
Russell L. Smith wrote:
> Just for grins, try performing the update inside of a database transaction.

> V/R
> Russell L. Smith

> Jim Poe wrote in message <367AB7F8.9A118...@fulcrumit.com>...
> >When the ExecSQL is performed, I get the following error:  ORA-22275:
> >invalid LOB locator specified.'

--
Jim Poe
<j...@fulcrumit.com>
Fulcrum InteTech, Inc.

Re:Working with CLOB's


Russell,

I shouldn't have to resort to using the dbms_lob package to update a CLOB,
should I?

Quote
Russell L. Smith wrote:
> Just for grins, try performing the update inside of a database transaction.

> V/R
> Russell L. Smith

> Jim Poe wrote in message <367AB7F8.9A118...@fulcrumit.com>...
> >When the ExecSQL is performed, I get the following error:  ORA-22275:
> >invalid LOB locator specified.'

--
Jim Poe
<j...@fulcrumit.com>
Fulcrum InteTech, Inc.

Re:Working with CLOB's


I ran some tests using the following code which is quite similar to yours.
When Oracle8Blobs is False (the default), it works with both AsMemo and
AsString.  If Oracle8Blobs is True, AsMemo will cause ORA-22275.  I didn't
dig into the VCL to figure out why.  Let me know if this helps.

create table temp (
  key_id number not null primary key,
  desc_tx clob)

  try
    dbMain.StartTransaction;
    with TQuery.Create(nil) do
      try
        DatabaseName := 'dbMain';
        SQL.Text := 'update TEMP set DESC_TX = :DESC_TX where KEY_ID =
:KEY_ID';
        ParamByName('KEY_ID').AsInteger := 1;
        ParamByName('DESC_TX').AsString := RichEdit1.Text;
        ExecSQL;
        dbMain.Commit;
      finally
        Free;
      end;
  except
    dbMain.Rollback;
  end;

BTW, when I omit the transaction, I get the "lob locators can't span
transaction" error.  Perhaps my SQLPASSTHRU MODE is set differently from
yours.

V/R
Russell L. Smith

Quote
Jim Poe wrote in message <367AF24C.8CD3D...@fulcrumit.com>...
>I shouldn't have to resort to using the dbms_lob package to update a CLOB,
>should I?

Other Threads