TSqlite3Dataset don't work with boolean and DateTime

[Schaelle]

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]

I need some info:

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

Some hints

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]

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]

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]

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]

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]

Okay, thanks for all.