IBX and BLOB update

[sydenis]

Ubuntu 20.04 + Lazarus 2.0.12
I use the IBSQL component from ibx4lazarus2. 4-0 to access the Firebird 3 database
I need to write and read a BLOB field from my database.

There are no problems with reading from db:

Code: Pascal  [Select][+][-]

1. IBSQL1.FieldByName('blob').AsBlob.SaveToFile(MyFile);

I get the desired file.

But writing to db does not work:

Code: Pascal  [Select][+][-]

1. IBSQL1.ParamByName('blob').AsBlob.LoadFromFile(MyFile);

error - External: SIGSEGV

What is the problem?
Looking the error it seem that ParamByName ('blob') does not exist?
How to do it correctly?

[devEric69]

On one line you used FieldByName to save, but on the other line you wrote ParamByName to load: is this normal?

[sydenis]

I guess yes. Here is code sample:
read from db:

Code: Pascal  [Select][+][-]

1.   with IBSLQ1 do
2.   begin
3.     SQL.Text:='select blob_field from tab1 where id=1;';
4.     Transaction.StartTransaction;
5.     ExecQuery;
6.     FieldByName('blob_field').AsBlob.SaveToFile('image.bin');
7.     .....

write to db:

Code: Pascal  [Select][+][-]

1.   with IBSQL1 do
2.   begin
3.     SQL.Text:='update tab1 set blob_field=:blob_field where id=1;';
4.     Transaction.StartTransaction;
5.     ParamByName('blob_field').AsBlob.LoadFromFile('image.bin');
6.     ExecQuery;  
7.    .....

What is wrong?

[tonyw]

Ubuntu 20.04 + Lazarus 2.0.12
I use the IBSQL component from ibx4lazarus2. 4-0 to access the Firebird 3 database
I need to write and read a BLOB field from my database.

There are no problems with reading from db:

Code: Pascal  [Select][+][-]

1. IBSQL1.FieldByName('blob').AsBlob.SaveToFile(MyFile);

I get the desired file.

But writing to db does not work:

Code: Pascal  [Select][+][-]

1. IBSQL1.ParamByName('blob').AsBlob.LoadFromFile(MyFile);

The problem is that you are using TIBSQL in the way that you would use TIBDataset. However, TIBSQL is working directly with the underlying Firebird interface and that requires a different approach.

The call to ParamByName('blob').AsBlob returns the interface IBlob. The input parameter has not yet been set and so this always returns nil - hence the exception.

To set the value of the blob, you have to create a TIBBlobstream, load it from a file and assign the IBlob interface returned by TIBBlobStream to the param e.g.

var b:TIBBlobStream;
begin
  b := TIBBlobStream.create;
 try
   b.Database := IBSQL.Database;
  b.Mode := bmWrite;
    b.LoadFromFile(...);
    IBSQL.ParamByName('blob').asBlob := b.blob;
  finally
    b.free;
  end;

[sydenis]

Tony, thank you very much! I used your advice and everything works fine.
My self I found a solution through asQuad:

Code: Pascal  [Select][+][-]

1.    b.LoadFromFile(...);
2.    b.Finalize;
3.    IBSQL1.ParamByName('blob').AsQuad  := b.BlobID;

thank you again

[tonyw]

Tony, thank you very much! I used your advice and everything works fine.
My self I found a solution through asQuad:

Code: Pascal  [Select][+][-]

1.    b.LoadFromFile(...);
2.    b.Finalize;
3.    IBSQL1.ParamByName('blob').AsQuad  := b.BlobID;

thank you again

Yes - that is an equivalent statement.