SQLite WideChar Field Not Treated The Same As Char(2000) Field
lazarus / 16-Databases

----------------------------------------------------------------------
emhartner · 2022-08-21 17:48

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery1.SQL.Text := 'create table testchar (field1 Char(2000),
field2 Char(2000), field3 Char(2000), hiddenfield Char(2000))'; 
SQLQuery1.ExecSQL; 
Here's the code for creating the table with the WideChar fields:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery2.SQL.Text := 'create table testwidechar (field1 WideChar,
field2 WideChar, field3 WideChar, hiddenfield WideChar)'; 
SQLQuery2.ExecSQL;
Editing is done from the grid.  And here's the code for creating my
field definitions:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.CreateFields(sqlQuery: TSQLQuery);begin  // add fields 
sqlQuery.Fields.Clear;   with TStringField.Create(sqlQuery) do  begin 
  FieldName    := 'field1';    DisplayLabel := 'Field 1';    DataSet 
   := sqlQuery;    DisplayWidth := 15;    Size         := 2000; 
end;   with TStringField.Create(sqlQuery) do  begin    FieldName    :=
'field2';    DisplayLabel := 'Field 2';    DataSet      := sqlQuery; 
 DisplayWidth := 15;    Size         := 2000;  end;   with
TStringField.Create(sqlQuery) do  begin    FieldName    := 'field3'; 
 DisplayLabel := 'Field 3';    DataSet      := sqlQuery;   
DisplayWidth := 15;    Size         := 2000;  end;   with
TStringField.Create(sqlQuery) do  begin    FieldName    :=
'hiddenfield';    DisplayLabel := 'Hidden Field';    DataSet      :=
sqlQuery;    DisplayWidth := 15;    Size         := 2000;    Visible 
   := False;  end; end; 
Can you please tell me why this is happening now?

Project attached for reproducing the error.

Thank you,
Eric

----------------------------------------------------------------------
Zvoni · 2022-08-22 07:14

*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

----------------------------------------------------------------------
emhartner · 2022-08-22 11:55

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i create
table testwidechar (field1 Text, field2 WideChar, field3 Char(2000),
hiddenfield Text)
I then issued the following insert statement:
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i create
table testwidechar (field1 Text, field2 Text, field3 Text, hiddenfield
Text)
Thank you,
Eric

----------------------------------------------------------------------
Zvoni · 2022-08-22 12:27

After reading about it, "address=0x0" implies a NullPtr-Deference

Show me your UPDATE/INSERT-SQL

----------------------------------------------------------------------
emhartner · 2022-08-22 12:42

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 · 2022-08-31 23:31

Quote from: Zvoni on August 22, 2022, 02:27:06 pmAfter 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 · 2022-09-01 06:57

Quote from: emhartner on September 01, 2022, 01:31:26 amQuote from:
Zvoni on August 22, 2022, 02:27:06 pmAfter 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

----------------------------------------------------------------------
LacaK · 2022-10-07 18:15

Quote from: emhartner on August 22, 2022, 01:55:11 pmCode: MySQL
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     create
table testwidechar (field1 NCHAR(2000), field2 NVARCHAR(2000), field3
NCLOB)

----------------------------------------------------------------------
SymbolicFrank · 2022-10-17 11:24

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.