Blob field not savec to SQLite DB when using ParamByName.LoadFromStream

Data Abstract
1

Please consider the simplified Attached sample showing that a blob field is not correctly saved to an SQLite DB.
Specs: Delphi XE3 32-Bit, DA 7.0.67.1073, UniDac 4.6.12 with SQLite driver:

  1. Insert stream in table
  2. Apply updates
  3. Reload the DB
  4. Checking values added via table [FieldByName(‘value’).LoadFromStream] are identical
    –> OK
  5. Checking values added via server [aCustomCommand.ParamByName(‘value’).LoadFromStream(aStream)] are identical
    –> ERROR, table value’s size is 0 instead of 298600

See stream insertion code in fServerDataModule.pas: procedure TServerDataModule.ConnectionManagerConnectionCreated.

2

Hello,

Do you use the same database as in previous issue http://connect.remobjects.com/discussion/3289/field-data-size-is-truncated-after-being-restored-from-an-sqlite-db#latest , that has ‘Value’ field defined as NVARCHAR? If so, I’ ve run your testcase with this database and have seen that again right value is saved in the back-end database in both cases (through FieldByName and ParamByName), but after reloading data table both records are read with 0 length of data.

And again the same test but with field defined as BLOB in database, I’ve got right result. So seems like this is UniDAC issue too.

Best regards

3

Hello,
Database has changed: it now uses Blob field. The problem is when the server tries to add data in the DB using the following commands, not when the client uses a TDAMemDataTable:

procedure TServerDataModule.ConnectionManagerConnectionCreated(…)
begin
…
aCustomCommand := Connection.NewCommand(‘command2’, stSQL, ‘Custom’);
aCustomCommand.SQL := ‘INSERT INTO test VALUES (:id, :value)’;
aCustomCommand.RefreshParams;
aCustomCommand.ParamByName(‘id’).Value := ‘0’;
aCustomCommand.ParamByName(‘value’).BlobType := dabtBlob;
aCustomCommand.ParamByName(‘value’).LoadFromStream(aStream);
aCustomCommand.Execute;
…
end;

Please see the fServerDataModule.pas file in the sample I sent you.

Also the problem is in saving the DB not loading it: when I open the SQLite DB created using SQLiteSpy for example (located at c:\tmp\testdb.db3) and I do the following query, I get a 0 length value:

SELECT LENGTH(value) from test where id = 0;

Please advise.

4

Found the source of the problem, in the above sample code, this was missing:
aCustomCommand.ParamByName(‘value’).DataType := datBlob;