Q01. I executed ExecSql('Select * from MyTable') and the dataset does not load the data
Q02. Why the date/time/boolean fields of files created with program XXX are not show correctly?
Q03. How can i create SQL queries that interacts with date/time fields?
Q04. I can't open files with accented characters!
Q05. The field REAL/TIMESTAMP are recognized as a String field.
Q06. ApplyUpdates does not work . The data is not saved to the database!
__________________________________________________________________________________________Q01. I executed ExecSql('Select * from MyTable') and the dataset does not load the data
ExecSql is not intended to be used to execute query that load data (Select). It's only used to execute SQL commands that alters table state (Insert, Update, Delete, Drop Table, Create Index etc).
To load data execute the following sequence:
Sqlite3Dataset.Close;
Sqlite3Dataset.SQL := 'Select * from MyTable';
Sqlite3Dataset.Open;
Q02. Why the date/time/boolean fields of files created with program XXX are not show correctly?
Sqlite allows to store any type in any field. Many programs store the values of date/time/boolean fields as strings. In the name of performance, reduced memory usage, reduced database size and reduced code size TSqlite*Dataset stores the Date/Time values as numbers (Double) and Boolean as integer(1 = True, 0 = False)
Q03. How can i create SQL queries that interacts with date/time fields?
Due
to the fact exposed in Q02, you cannot use textual representation of
date/time values in SQL queries. A SQL like ' Select * from MyTable
where TimeField < "10:11:00" ' probably will lead in wrong results. To get the string value of date/time field convert the TDateTime type to String using FloatToStr or the Format function.
Some examples:
'Select * from MyTable where TimeField < ' + FloatToStr(StrToTime('10:11:00'))
'Select * from MyTable where TimeField < ' + FloatToStr(Time)
Format('Select * from MyTable where TimeField < %f', [Time])
'Select * from MyTable where DateField = ' + FloatToStr(StrToDate('01/11/2009'))
'Select * from MyTable where DateField = ' + FloatToStr(Date)
Format('Select * from MyTable where DateField = %f', [Date])
* All options above are unsafe because depends of the locale settings, e.g., in some countries a comma is used as thousand separator and the ShortDateFormat and ShortTimeFormat may not match with the passed string date representation.
The safest way is converting from TDateTime/Double to String using the Str procedure. The function below can be used in place of FloatToStr.
function FloatToSQL(D: Double): String;
begin
Str(D, Result);
end;
'Select * from MyTable where DateField = ' + FloatToSQL(Date)
Q04. I can't open files with accented characters!
Sqlite expects that the filename be encoded in UTF-8. Setting Filename to a string with accented chars encoded in Ansi, e.g. cópia, will truncate the filename (from cópia to cpia).
Currently fpc does not provide a unicode aware RTL and the encoding returned by the RTL change from system to system (even in the same operating system the encoding can be different). Lazarus/LCL uses UTF-8 as default encoding so all strings returned by file dialogs or controls can be used to set the TSqlite*Dataset FileName property. The LCL also comes with the FileUtil unit that implements UTF-8 versions of some RTL functions.Q05. The field REAL/TIMESTAMP is recognized as a String field.
Sqlite does not have predefined data types so it's possible to store any value in any field (the field type declared in the Create Table statement is just a hint). TSqlite*Dataset recognizes some declared field types and set appropriated TDataset field type. If it's not recognized than it's mapped to a string field.So when creating tables to work with TSqite*Dataset through ExecSql or third party tool is necessary to have in mind following mapping:
| TDataset field type | Declared field type(s) |
| ftInteger | INT, INTEGER |
| ftString | VARCHAR |
| ftBoolean | BOOL, BOOLEAN |
| ftAutoInc | AUTOINC |
| ftFloat | FLOAT, NUMERIC |
| ftDateTime | DATETIME |
| ftDate | DATE |
| ftTime | TIME |
| ftLargeInt | LARGEINT |
| ftMemo | TEXT |
| ftCurrency | CURRENCY |
| ftWord | WORD |
Anyway, the safest and easiest way to create a table is to use the provided functions:
if not TableExists then
begin
FieldDefs.Clear;
FieldDefs.Add('Id', ftAutoInc);
FieldDefs.Add('Name', ftString, 100);
FieldDefs.Add('BirthDate', ftDate);
CreateTable;
end;
Q06. ApplyUpdates does not work . The data is not saved to the database!
In order to ApplyUpdates work is necessary to define a primary key. It can be done in two ways:- Set PrimaryKey property to the name of a Primary Key field
- Add an AutoInc field (This is easier since the TSqlite*DataSet automatically handles it as a Primary Key)
