mORMot Open Source

jbroussia

Member

From: France

Registered: 2011-04-09

Posts: 74

TQuery: TSQLDBSQLite3Statement.Step(SeekFirst=true) not implemented

Hi ab,

I'm playing with your TQuery wrapper to query a local SQLite3 file but I get this exception when using TQuery.First procedure for the second time; that is it seems to works once, then on the second run of the code, the exception raises.

Here a simple project demonstrating my problem :-\

unit fmTest;

interface

uses
	Windows, Messages, SysUtils, Classes, Controls, Forms, StdCtrls,
	SynCommons, SynDB, SynDBSQLite3, SynSQLite3, SynSQLite3Static;

type
	TfrmTest = class(TForm)
		btnTest: TButton;
		mmoLog: TMemo;
		procedure FormCreate(Sender: TObject);
		procedure FormDestroy(Sender: TObject);
		procedure btnTestClick(Sender: TObject);
	private
		{ Private declarations }
	public
		{ Public declarations }
	end;

const
	DB_FILENAME = 'Tests.sqlite';
	
var
	frmTest: TfrmTest;
	Props: TSQLDBConnectionProperties;
	Conn: TSQLDBConnection;
	Qry: TQuery;

implementation

{$R *.dfm}

procedure TfrmTest.btnTestClick(Sender: TObject);
var
	Timer: TPrecisionTimer;
	s: string;
	i1, iTrue, iFalse: Integer;
begin
	mmoLog.Clear;
	
	with Qry do begin
		Close;
		
		SQL.Text := 'DELETE FROM Test;';
		ExecSQL;

		Timer.Start;
		
		SQL.Text := 'INSERT OR IGNORE INTO Test VALUES (:Key, :Value);';
		Conn.StartTransaction;
		for i1 := 0 to 999 do begin
			ParamByName('Key').AsString := Format('Clé %d', [i1]);
			ParamByName('Value').AsBoolean := (Random(2) = 1);
			ExecSQL;
		end;
		Conn.Commit;

		SQL.Text := 'SELECT * FROM Test;';
		Open;
		iTrue := 0;
		iFalse := 0;
		while not Eof do begin
			if Fields[1].AsBoolean then
				Inc(iTrue)
			else
				Inc(iFalse);
			Next;
		end;
	
		Timer.Stop;

		mmoLog.Lines.BeginUpdate;
		SQL.Text := 'SELECT * FROM test;';
		Open;
		(* while not Eof do begin
			Next;
			// mmoLog.Lines.Add(FieldByName('Key').AsString);
		end; *)
		First; // <<<<<<<<<<<< Happens here, but also happens if I comment this line out, so the problem could be on the next line of code !
		mmoLog.Lines.Add(Fields[0].AsString);
		mmoLog.Lines.EndUpdate;

		s := 	Format('%d valeurs à True, %d valeurs à False', [iTrue, iFalse]);
		mmoLog.Lines.Add(s);
		s := Format('Terminé en %s', [Timer.Time]);
		mmoLog.Lines.Add(s);
	end;
end;

procedure TfrmTest.FormCreate(Sender: TObject);
begin
	Randomize;
	
	Props := TSQLDBSQLite3ConnectionProperties.Create(StringToUTF8(DB_FILENAME), '', '', '');
	Conn := Props.NewConnection;
	Conn.Connect;
	Qry := TQuery.Create(Conn);
	with Qry	do begin
		SQL.Text := 'PRAGMA synchronous = OFF;';
		ExecSQL;
	end;
end;

procedure TfrmTest.FormDestroy(Sender: TObject);
begin
	Qry.Free;
	Props.Free;
end;

end.

Am I doing something wrong ? (probably :-p)
Also, not sure about when to use the TQuery.Close procedure, is it required after each query is finished or only certain ones ? Sometimes the debugger will complain about a missing Close (exception 'TQuery.Prepare called with no previous Close'), sometimes it will run smoothly...

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: TQuery: TSQLDBSQLite3Statement.Step(SeekFirst=true) not implemented

AFAIR TQuery does not support the "rewind" feature.

TQuery.Close is not mandatory if you call TQuery.Free.
But this version of TQuery is single-use: you create it, browse all rows once, then free it.

If you want full features, use the SynDB native classes, i.e. TSQLDBStatement.

jbroussia

Member

From: France

Registered: 2011-04-09

Posts: 74

Re: TQuery: TSQLDBSQLite3Statement.Step(SeekFirst=true) not implemented

Hi Arnaud,

I want to use your wrapper only because I'm trying to convert some old project from another set of SQLite components (Aducom) to Synopse and the project uses a TQuery component already.
I get the expected behavior if I comment out the line of code generating the exception in SynDBSQLite3.pas.

Now as I'm continuing to play with the wrapper, I get a problem with Date/DateTime fields:

SQL.Text := 'INSERT OR IGNORE INTO Test3 VALUES (:key, :value);';
ParamByName('key').AsString := Format('Clé %d', [i1]);
ParamByName('value').AsDateTime := Now;
ExecSQL;

Using some external tool to check my DB, I see the values are stored in this format: "2015-07-07T16:17:51".

The problem is when I want to read the values again using the wrapper:

SQL.Text := 'SELECT * FROM Test3;';
Open;
while not Eof do begin
	if FieldByName('value').AsDateTime > Now then // ***
		// ...
	Next;
end;

*** I'm getting the following error:
Project ... raised exception class EVariantTypeCastError with message 'Could not convert variant of type (UnicodeString) into type (Double)'

Also while I'm here, why do you use "65535" as the "True" value for storing booleans in SQLite DBs ?

Thanks !

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: TQuery: TSQLDBSQLite3Statement.Step(SeekFirst=true) not implemented

Please try http://synopse.info/fossil/info/0c8774be5

In fact, TRUE is stored as 65535 inside a variant.
It should be fixed by http://synopse.info/fossil/info/74e94d3ca8

jbroussia

Member

From: France

Registered: 2011-04-09

Posts: 74

Re: TQuery: TSQLDBSQLite3Statement.Step(SeekFirst=true) not implemented

We need a "Thumbs Up" button on this forum :-)
Thanks !