mORMot Open Source

firstfriday

Member

Registered: 2015-07-21

Posts: 26

Re-use of TQuery does not work

Why is it not possibe to use Tquery more than 1 time?
Code to reproduce:

procedure TForm2.cmdErrorClick(Sender: TObject);
var
  i:integer;
  SQL,s:string;
  Props:TOleDBMSSQL2012ConnectionProperties;
  SQLDBConnection:TSQLDBConnection;
  Query : TQuery;
begin
  (*
    create a mssql database execute this
    create table [dbo].[test] (
      [id] [int] not null ,
      [id_u] [int] null,
      [fkey] [nvarchar] (255) collate sql_latin1_general_cp1_ci_as null ,
      [svalue] [nvarchar] (255) collate sql_latin1_general_cp1_ci_as null ,
      [ivalue] [int] null ,
      [fvalue] [float] null
    ) on [primary]
    go
  *)
  Props:=TOleDBMSSQL2012ConnectionProperties.create('localhost','test_db','sa','');
  SQLDBConnection:=props.NewConnection;

  SQL := 'TRUNCATE TABLE test';
  query:=Tquery.Create(SQLDBConnection);
  query.SQL.clear;
  query.SQL.Add(SQL);
  query.ExecSQL;
  query.free;

  s:='String';
  query:=Tquery.Create(SQLDBConnection);
  for i:=1 to 100000 do
  begin
    //SQL := format('insert into test (id,ivalue,fkey,svalue) values (%d,%d,''%s'',''%s'')',[i,i,s,s]); // Version 1=> works
    SQL := 'insert into test (id,ivalue,fkey,svalue) values (:id,:ivalue,:fkey,:svalue)' ;// Version 2 =>memory leak when executed
    query.SQL.Clear;
    query.SQL.Add(SQL);
    // Version 2
    query.ParamByName('id').AsInteger     :=i;
    query.ParamByName('ivalue').AsInteger :=i;
    query.ParamByName('fkey').AsString   :='String '+inttostr(i);
    query.ParamByName('svalue').AsString :='String '+inttostr(i);

    query.ExecSQL;
  end;
  query.free;
end;

Last edited by firstfriday (2016-12-18 13:04:30)

jbroussia

Member

From: France

Registered: 2011-04-09

Posts: 74

Re: Re-use of TQuery does not work

I'm using a global TQuery for all my queries (SQLite) and don't have that problem. Don't forget to Close your query before each use.

firstfriday

Member

Registered: 2015-07-21

Posts: 26

Re: Re-use of TQuery does not work

Thank you for your answer but the query is closed anyway, look:

procedure TQuery.ExecSQL;
begin
  Execute(false);
  Close;
end;

may be it is a problem with mssql?
I will create a sample with sqlite and compare....

firstfriday

Member

Registered: 2015-07-21

Posts: 26

Re: Re-use of TQuery does not work

ok, I testet it: With SQLite there is no problem.

After taken the latest version of mormot it is possible to re-use Tquery it with MSSQL.

Last edited by firstfriday (2016-12-18 13:07:22)