Recent

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

chrnobel

  • Sr. Member
  • ****
  • Posts: 283
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: October 11, 2018, 12:32:42 pm by chrnobel »

chrnobel

  • Sr. Member
  • ****
  • Posts: 283
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

chrnobel

  • Sr. Member
  • ****
  • Posts: 283
Re: Sqlite3, Zeoslib, string saved as blob - error in TDataset?
« Reply #2 on: October 11, 2018, 12:31:48 pm »
Ok, have done some further investigations.

By default SQlite saves all kinds of strings as text, meaning if one eg. uses Sqliteman or DB Browser for SQlite, text is allways created as ... tadaaah text.

However this is interpreted as a Memo in Zeos (or FPC, dunno), therefore new text, even if it originates from a sting is inserted as a Memo.

For Zeos/Lazarus to understand how to do it right, the field have to be a Varchar, which is not used internally by SQlite, as it always deals with text - however SQlite accepts (and saves) Varchar in the create statement, so when Zeos/FPC later on reads from the database, it checks the type with the definition.

The tricky part is then, if using one of the above tools, the possibility varchar does not exist, so what one have to do (if not doing it totally by hand) is to use the tool, but do not press accept, but instead copy the suggested SQL statement, and then paste it in to the execute SQL part of the tool, and change all the occurrences of TEXT with VARCHAR(xx).

It is important that there is a length definition af the Varchar, the "xx" part, which have to be below 255 - actually it can have any value, as SQlite internally do not care of the length.
My suggestion is setting it relatively low, eg. 20, otherwise the field will be very wide in DBGrid. 

 

TinyPortal © 2005-2018