* * *

Author Topic: Sqlite3, Zeoslib, string saved as blob - error in TDataset?  (Read 456 times)

chrnobel

  • Full Member
  • ***
  • Posts: 221
I am having a strange problem, which really puzzles me.

I am trying to save a string to a textfield in Sqlite3, but it is consequently saved as a blob with the stringvalue plus null.

But upon creating a record it works ok.

First I create the record, and for this example I only deal with one record:

Code: Pascal  [Select]
  1.       SessionQuery.Active:=false;
  2.       SessionQuery.ReadOnly:=false;
  3.       SessionQuery.SQL.Text:='select * from session';
  4.       SessionQuery.Active:=true;
  5.       SessionDataSource.DataSet.Insert;
  6.       SessionDataSource.DataSet.FieldByName('cvalue').AsString:='abc';
  7.       SessionDataSource.DataSet.Post;    
  8.  


If I then check the record in an external Sqlite3 tool the cvalue is represented as text with the correct value 'abc' (hex: 61 62 63)

Then I try to modify the field:
Code: Pascal  [Select]
  1.       SessionQuery.Active:=false;
  2.       SessionQuery.ReadOnly:=false;
  3.       SessionQuery.SQL.Text:='select * from session';
  4.       SessionQuery.Active:=true;
  5.       SessionDataSource.DataSet.Edit;
  6.       SessionDataSource.DataSet.FieldByName('cvalue').AsString:='def';
  7.       SessionDataSource.DataSet.Post;    
  8.  

But now, when checking with my Sqlite3 tool cvalue is no more a text, but is converted to blob with the value 'def' plus an odd character, and the hexvalue is not 64 65 66 as expected, but 64 65 66 00.

So when editing the textfield a null is added to the string, which makes it impossible to make an exact search.

Is that a Lazarus/FPC problem, is it Zeos, or is it Sqlite, and how can it be fixed?

Lazarus 1.8.2 - 64bit debian
Zeos 7.2.4-stable
Sqlite3 3.11.0
« Last Edit: July 04, 2018, 01:46:16 pm by chrnobel »

chrnobel

  • Full Member
  • ***
  • Posts: 221
Re: Sqlite3, Zeoslib, string saved as blob?
« Reply #1 on: July 03, 2018, 10:11:38 am »
I have done some experimenting, using parameterised query instead:
Code: Pascal  [Select]
  1.           Sessionsentence.Clear;
  2.           Sessionsentence.Add('update session ');
  3.           Sessionsentence.Add('set cvalue=:cvalue ');
  4.           Sessionsentence.Add('where cvalue=:oldcvalue');
  5.           SessionQuery.SQL:=Sessionsentence;
  6.           SessionQuery.Params.ParamByName('cvalue').AsString:=cvalue;
  7.           SessionQuery.Params.ParamByName('oldcvalue').AsString:=oldcvalue;
  8.           SessionQuery.ExecSQL;
  9.  


This actually works as expected, so now the cvalue is not converted to a blob.

To me it seems like there is a problem with DataSet.FieldByName('cvalue').AsString.

I did not have the problem with 32bit Lazarus 1.2.6 and Zeoslib 7.1.3

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus