TSqlite3Dataset don't work with boolean and DateTime
lazarus / 16-Databases

----------------------------------------------------------------------
Schaelle · 2007-03-16 14:54

Hi,

I'm using FPC including SQLite using the TSqlite3Dataset component,
connection and selecting works mainly.

But only for strings and integers.

If I want to get boolean it's everytime false,
if I want to get DateTime it's everytime the 12/31/1899.

The code:

Code: [Select]procedure SQLiteQuery(var DS: TSqlite3Dataset);
begin
  with DS do
    begin
      SQL := 'SELECT SimpleDate, SimpleBoolean FROM simpletest';
      Open;
      First;
    end;
end;

function SQLiteFetch(DS: TSqlite3Dataset): TLine;
begin
  with DS do
    begin
      Result.SimpleDate := Fields[0].AsDateTime;
      Result.SimpleBoolean := Fields[1].AsBoolean;
      Next;
    end;
end;

procedure SimpleTest;
var
  i: integer;
  line: TLine;
begin
  // DB is the TSqlite3Dataset component
  SQLiteQuery(DB);
  for i := 0 to DB.RecordCount-1 do
    begin
      line := SQLiteFetch(DB);
      ShowMessage(DateToStr(line.SimpleDate));
      if(line.SimpleBoolean) then ShowMessage('true') else
ShowMessage('false');
    end;
end;


It's out of a program, but I have cuted it so, that only the important
is pastet here.


greetings,
Schaelle

----------------------------------------------------------------------
LuizAmérico · 2007-03-16 23:48

I need some info:

 - What fpc version are you using?
 - Can you send a sample project with a db file to me?

Some hints

Quote
procedure SQLiteQuery(var DS: TSqlite3Dataset);
begin
  with DS do
    begin
>>>add this Close;<<<<<
      SQL := 'SELECT SimpleDate, SimpleBoolean FROM simpletest';
      Open;
>>>> not necessary      First; <<<<
    end;
end;

----------------------------------------------------------------------
Schaelle · 2007-03-17 10:28

Hi, I'm using FPC 2.0.4 and the Lazarus 0.9.20 build.

I have created an example, that shows that not the wrong values are
loaded, there are now values loaded.

In my earlier example it seems that Fields[n].AsFoobar set a default
value if there is no value in the database.

The source of the example: sqlite-test.rar

Greetings,
Schaelle

----------------------------------------------------------------------
LuizAmérico · 2007-03-17 15:20

The problem is that the datafile uses a different data format for
storing boolean and Date fields.

It uses Y/N to Boolean (TSqliteDataset expects 1/0)
It uses string for date (TSqliteDataset expects a double (TDateTime
format))

It seems that this file was not created with TSqliteDataset.

To use with TSqliteDataset will be necessary convert to the expected
format

----------------------------------------------------------------------
Schaelle · 2007-03-17 19:28

Yes, thats true, i created the Database using sqliteadmin.

Thanks for help.


Edit: Any way to implemented into the TSqlite3Dataset that it works
with the another format too?

----------------------------------------------------------------------
LuizAmérico · 2007-03-17 20:37

You can still create the tables with another tool but you must add the
data using the TSqliteDataset methods.

> Edit: Any way to implemented into the TSqlite3Dataset that it works
with the another format too?

No. It will stay in the current format which requires less memory

----------------------------------------------------------------------
Schaelle · 2007-03-18 01:56

Okay, thanks for all.