Recent

Author Topic: SQLite WideChar Field Not Treated The Same As Char(2000) Field  (Read 1250 times)

emhartner

  • Jr. Member
  • **
  • Posts: 52
I have a SQLite database that uses WideChar for most of its field types.  In previous versions of Lazarus, this was not a problem.  Now when I try to save into one of these fields,  I get the error "EXC_BAD_ACCESS (code=1, address=0x0)".  If I change the field type to Char(2000) I get no error at all.

Here's the code for creating the table with the Char(2000) fields:
Code: Pascal  [Select][+][-]
  1.   SQLQuery1.SQL.Text := 'create table testchar (field1 Char(2000), field2 Char(2000), field3 Char(2000), hiddenfield Char(2000))';
  2.   SQLQuery1.ExecSQL;
  3.  

Here's the code for creating the table with the WideChar fields:
Code: Pascal  [Select][+][-]
  1.   SQLQuery2.SQL.Text := 'create table testwidechar (field1 WideChar, field2 WideChar, field3 WideChar, hiddenfield WideChar)';
  2.   SQLQuery2.ExecSQL;

Editing is done from the grid.  And here's the code for creating my field definitions:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.CreateFields(sqlQuery: TSQLQuery);
  2. begin
  3.   // add fields
  4.   sqlQuery.Fields.Clear;
  5.  
  6.   with TStringField.Create(sqlQuery) do
  7.   begin
  8.     FieldName    := 'field1';
  9.     DisplayLabel := 'Field 1';
  10.     DataSet      := sqlQuery;
  11.     DisplayWidth := 15;
  12.     Size         := 2000;
  13.   end;
  14.  
  15.   with TStringField.Create(sqlQuery) do
  16.   begin
  17.     FieldName    := 'field2';
  18.     DisplayLabel := 'Field 2';
  19.     DataSet      := sqlQuery;
  20.     DisplayWidth := 15;
  21.     Size         := 2000;
  22.   end;
  23.  
  24.   with TStringField.Create(sqlQuery) do
  25.   begin
  26.     FieldName    := 'field3';
  27.     DisplayLabel := 'Field 3';
  28.     DataSet      := sqlQuery;
  29.     DisplayWidth := 15;
  30.     Size         := 2000;
  31.   end;
  32.  
  33.   with TStringField.Create(sqlQuery) do
  34.   begin
  35.     FieldName    := 'hiddenfield';
  36.     DisplayLabel := 'Hidden Field';
  37.     DataSet      := sqlQuery;
  38.     DisplayWidth := 15;
  39.     Size         := 2000;
  40.     Visible      := False;
  41.   end;
  42.  
  43. end;
  44.  

Can you please tell me why this is happening now?

Project attached for reproducing the error.

Thank you,
Eric


Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: SQLite WideChar Field Not Treated The Same As Char(2000) Field
« Reply #1 on: August 22, 2022, 09:14:06 am »
*sigh*
....and if you've actually read the SQLite-Documentation, you would know there is no Datatype Char(2000) or widechar in SQLite.
Use Storage-Class TEXT. Period!
If you need multibyte support other than default (UTF8 i think), you have to set if UTF8 or UTF16 BEFORE you create the Database (!!)
https://www.sqlite.org/pragma.html#pragma_encoding
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

emhartner

  • Jr. Member
  • **
  • Posts: 52
Re: SQLite WideChar Field Not Treated The Same As Char(2000) Field
« Reply #2 on: August 22, 2022, 01:55:11 pm »
Thank you for your reply Zvoni.  Yes, it would have been best for me to use Text when I created my database over 10 years ago.  However, please note that SQLite treats all of these as Text.  See here a table I tested with Text, WideChar and Char(2000:
Code: MySQL  [Select][+][-]
  1. create table testwidechar (field1 Text, field2 WideChar, field3 Char(2000), hiddenfield Text)

I then issued the following insert statement:
Code: MySQL  [Select][+][-]
  1. INSERT INTO testwidechar (field1, field2,  field3) VALUES ('1',2,'3')

When I issue the following, you can see that all of these values in these different field types are treated as Text:
sqlite> SELECT field1, typeof(field1), field2, typeof(field2), field3, typeof(field3) from testwidechar;
1|text|2|text|3|text

In addition, I have no issues at all storing the data I need to using Objective-C, Delphi 7 and Lazarus 1.8.  The issue I'm having now is new in Lazarus 2.2.2.

Finally, I get the same error when use the Text data type for all of my fields:
Code: MySQL  [Select][+][-]
  1. create table testwidechar (field1 Text, field2 Text, field3 Text, hiddenfield Text)

Thank you,
Eric


Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: SQLite WideChar Field Not Treated The Same As Char(2000) Field
« Reply #3 on: August 22, 2022, 02:27:06 pm »
After reading about it, "address=0x0" implies a NullPtr-Deference

Show me your UPDATE/INSERT-SQL
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

emhartner

  • Jr. Member
  • **
  • Posts: 52
Re: SQLite WideChar Field Not Treated The Same As Char(2000) Field
« Reply #4 on: August 22, 2022, 02:42:31 pm »
I'm simply using the DBGrid to edit.  When I move off of the new row, I get the error.  I have my test program attached where it can be easily reproduced in the right most DBGrid.

emhartner

  • Jr. Member
  • **
  • Posts: 52
Re: SQLite WideChar Field Not Treated The Same As Char(2000) Field
« Reply #5 on: September 01, 2022, 01:31:26 am »
After reading about it, "address=0x0" implies a NullPtr-Deference

Show me your UPDATE/INSERT-SQL

Hi Zvoni - have you been able to reproduce the error?

Thank you,
Eric

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: SQLite WideChar Field Not Treated The Same As Char(2000) Field
« Reply #6 on: September 01, 2022, 08:57:35 am »
After reading about it, "address=0x0" implies a NullPtr-Deference

Show me your UPDATE/INSERT-SQL

Hi Zvoni - have you been able to reproduce the error?

Thank you,
Eric
No, since i don't use (and haven't ever used) DB-bound controls
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: SQLite WideChar Field Not Treated The Same As Char(2000) Field
« Reply #7 on: October 07, 2022, 08:15:33 pm »
Code: MySQL  [Select][+][-]
  1. create table testwidechar (field1 Text, field2 WideChar, field3 Char(2000), hiddenfield Text)

If you want map TEXT columns from SQLite to WideChar fields in FPC try use something like this:
Code: Pascal  [Select][+][-]
  1.     create table testwidechar (field1 NCHAR(2000), field2 NVARCHAR(2000), field3 NCLOB)

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1313
Re: SQLite WideChar Field Not Treated The Same As Char(2000) Field
« Reply #8 on: October 17, 2022, 01:24:22 pm »
SQLite has two kinds of tables (since 2022): normal ones and STRICT ones. With normal tables, the data type of the fields is mostly treated like a recommendation, but the value is probably stored in a field of type: 'any'. With STRICT tables, you have the following types: 'integer', 'real', 'text', 'blob' and 'any'. No length or size, no encoding, no range. But there is conversion going on between those in the background.

If you specify other data types, this can result in strange errors, that are hard to track down.  The database engine and the FPC implementation try to guess what datatype it is, what you want to do with it and which conversions have to take place. If you specify unsupported data types and/or expect different things than can be delivered, weird things happen, which results in strange errors.

 

TinyPortal © 2005-2018