Write blob field to database from client

[bzwirs 4]

Delphi 11.1

Datasnap

 

Having trouble writing a blob field from a client app to the database on server.  Client uses SQLite database and main database on server is Firebird.  Client app uses TMSFNCSignature Capture component to write the signature to the SQLite database blob field like this and the image is actually saved to the blob field:

 

procedure TMainForm.Save_Signature;
var
  ms   : tMemoryStream;
begin
  ms := TMemoryStream.Create;
  EMUSig.SaveToImageStream(ms);
  Open_InvSigQry(MarafillerDM.FDQuerySchedSALEDATE.Value,
                 MarafillerDM.FDQuerySchedMACHID.Value);
  MarafillerDM.InvSigQry.Insert;
  MarafillerDM.InvSigQrySALEDATE.Value := MarafillerDM.FDQuerySchedSALEDATE.Value;
  MarafillerDM.InvSigQryMACHID.Value := MarafillerDM.FDQuerySchedMACHID.Value;
  MarafillerDM.InvSigQrySIGNATURE.LoadFromStream(ms);
  MarafillerDM.InvSigQry.Post;
  MarafillerDM.InvSigQry.Close;
end;

 

Using SQLite DBBrowser I can confirm that the image is written to the database (.png image).

 

My problem is writing that image to a blob field on the server database (using datasnap for the connection).  The table I am writing to on the server has several fields including 1 blob field defined as - 'CUSTSIGNATURE   BLOB SUB_TYPE 0 SEGMENT SIZE 80' in the Firebird database.  I use the following code to write to the database.  The EMUInvQry gathers the necessary data to transfer from a couple of tables: 

 

procedure TMainForm.Transfer_EMUInvoice;
var
  ms    : tStream;
begin
  MarafillerDM.EMUInvClient.Insert;
  MarafillerDM.EMUInvClientINVNUM.AsString := MarafillerDM.EMUInvQryDOCKET.AsString;
  MarafillerDM.EMUInvClientINVDATE.Value :=
                  ModifiedJulianDateToDateTime(MarafillerDM.EMUInvQrySALEDATE.Value);
  MarafillerDM.EMUInvClientMACHID.Value := MarafillerDM.EMUInvQryMACHID.Value;
  MarafillerDM.EMUInvClientTOTPKTS.Value := MarafillerDM.EMUInvQryPKTSALES.Value;
  MarafillerDM.EMUInvClientTOTVALUE.Value := MarafillerDM.EMUInvQryMACHVALUE.Value;
  MarafillerDM.EMUInvClientSITECODE.Value := MarafillerDM.EMUInvQrySITECODE.Value;
  MarafillerDM.EMUInvClientTOTRETPKTS.Value := MarafillerDM.EMUInvQryRETURN_PKTS.Value;
  MarafillerDM.EMUInvClientTOTRETVALUE.Value := MarafillerDM.EMUInvQryRETURN_VALUE.Value;
  MarafillerDM.EMUInvClientADJTOTPKTS.Value := MarafillerDM.EMUInvQryADJ_PKTS.Value;
  MarafillerDM.EMUInvClientADJTOTVALUE.Value := MarafillerDM.EMUInvQryADJ_VALUE.Value;
  if (MarafillerDM.EMUInvQryPAYEMUCOMM.AsString = 'F') then
    MarafillerDM.EMUInvClientCOMMISSION.Value := MarafillerDM.EMUInvQryCOMMISSION.Value;
  MarafillerDM.EMUInvClientBALANCEDUE.Value := MarafillerDM.EMUInvQryBAL_DUE.Value;
  MarafillerDM.EMUInvClientFILLER.AsString :=  sFillerName;
  ms := TStream.Create;
  MarafillerDM.EMUInvQrySIGNATURE.SaveToStream(ms);
  try
    if Assigned(ms) then
      MarafillerDM.EMUInvClientCUSTSIGNATURE.LoadFromStream(ms);
  finally
    ms.Free;
  end;
  MarafillerDM.EMUInvClient.Post;
end;

 

When I check the Firebird database, all fields are transferred except the blob field which is set to null. 

 

Can someone please advise me how to correctly transfer the blob field data.  Have googled and tried a few different approaches but nothing works for me.  Obviously missing something but can't see the forest for the trees.

 

Any help would be appreciated.

 

Bill Zwirs

[PeterBelow 238]

For database BLOB fields you generally cannot use a generic TStream, you need to ask the BLOB field to create a TBlobStream descendant specific for the database engine in question. In your case you first create the blob stream for the local database, store the field content in it, the create a blob stream for the server database, copy the content of the first stream to it, then load the contents into the server db field.

[Vandrovnik 214]

Would it help, if you use ms.Position := 0; before .LoadFromStream(ms)?

[Fr0sT.Brutal 900]

1 hour ago, PeterBelow said:

For database BLOB fields you generally cannot use a generic TStream, you need to ask the BLOB field to create a TBlobStream descendant specific for the database engine in question. In your case you first create the blob stream for the local database, store the field content in it, the create a blob stream for the server database, copy the content of the first stream to it, then load the contents into the server db field.

It's OK as he uses TField.LoadFromStream

[Tom Chamberlain 47]

2 hours ago, PeterBelow said:

For database BLOB fields you generally cannot use a generic TStream, you need to ask the BLOB field to create a TBlobStream descendant specific for the database engine in question. In your case you first create the blob stream for the local database, store the field content in it, the create a blob stream for the server database, copy the content of the first stream to it, then load the contents into the server db field.

This is how it is suppose to work (for FireDac and ADO), you create a blob for the database engine TFDBlobStream, giving it the DB field with write access, then save into that blob stream. (yes free the blob before the post)  I'm using rich text here with embedded fonts and graphics so a PNG is not any different.

var
  BlobStream: TFDBlobStream;
....
    dbQueryObj.Append;

    ....

    BlobStream := TFDBlobStream.Create(dbQueryObj.FieldByName('Notes') as TBlobField, bmWrite);
    try
     rveEditor.SaveRTFToStream(BlobStream, False);
    finally
      BlobStream.Free;
    end;
    dbQueryObj.Post;

 

[Alexander Elagin 143]

A generic TStream has no idea how to store data, it is just an abstract class. Therefore any data written to it after this code snippet:

Quote

ms := TStream.Create;
  MarafillerDM.EMUInvQrySIGNATURE.SaveToStream(ms);

are simply discarded.

I'd replace the first line with this:

ms := TMemoryStream.Create;

The variable declaration can remain the same.

[bzwirs 4]

Thanks all for your replies.  In the end I still had some troubles with blob streams so simply created an image file and used loadfromfile procedure.  Seems to be working quite good now.

 

Bill