View Issue Details

IDProjectCategoryView StatusLast Update
0033025FPCPackagespublic2019-11-30 13:44
ReporterOndrej PokornyAssigned ToMichael Van Canneyt 
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
Product Version3.1.1Product Build 
Target Version4.0.0Fixed in Version3.3.1 
Summary0033025: [fcl-db] An SQL statement is always prepared in open/close loop even if the SQL doesn't change
DescriptionAn SQL staement should be prepared only once if executed repeatedly. Instead, every time you open/close a TSQLQuery, the statement is prepared and unprepared.

I tested MSSQL and Postgres - both show this unwanted behavior.
Steps To ReproduceApply the attached db-debug.patch to get output debug lines and run the following program (you can easily change it for a different database system):

program DBconn;

uses
  db, pqconnection, sqldb, sysutils;

var
  xSQL: TPQConnection;
  Q: TSQLQuery;
  T: TSQLTransaction;
  I: Integer;
begin
  xSQL := TPQConnection.Create(nil);
  try
    xSQL.HostName := 'localhost';
    xSQL.DatabaseName := 'postgres';
    xSQL.UserName := 'postgres';
    xSQL.Password := 'password';
    xSQL.Params.Add('port=5432');
    xSQL.Connected := True;

    Q := TSQLQuery.Create(xSQL);
    T := TSQLTransaction.Create(xSQL);
    T.DataBase := xSQL;
    Q.DataBase := xSQL;
    Q.Transaction := T;

    Writeln(sLineBreak, 'Statement start here', sLineBreak);
    Q.SQL.Text := 'SELECT :ID';
    for I := 0 to 1 do
    begin
      Q.ParamByName('ID').AsInteger := I;
      Q.Open;
      Writeln(' -->> Result ', I, ': ', Q.Fields[0].AsInteger);
      Q.Close;
    end;
  finally
    xSQL.Free;
  end;

  Readln;
end.
TagsNo tags attached.
Fixed in Revision43024
FPCOldBugId
FPCTarget-
Attached Files
  • db-debug.patch (971 bytes)
    Index: packages/fcl-db/src/base/dsparams.inc
    ===================================================================
    --- packages/fcl-db/src/base/dsparams.inc	(revision 37966)
    +++ packages/fcl-db/src/base/dsparams.inc	(working copy)
    @@ -303,6 +303,7 @@
       tmpParam:TParam;
     
     begin
    +  Writeln('TParams.ParseSQL: ', Copy(SQL, 1, 20));
       if DoCreate then Clear;
       // Parse the SQL and build ParamBinding
       ParamCount:=0;
    Index: packages/fcl-db/src/sqldb/sqldb.pp
    ===================================================================
    --- packages/fcl-db/src/sqldb/sqldb.pp	(revision 37966)
    +++ packages/fcl-db/src/sqldb/sqldb.pp	(working copy)
    @@ -1096,6 +1096,7 @@
         FOrigSQL := TrimRight(FSQL.Text)
       else
         FOrigSQL := Database.GetSchemaInfoSQL(GetSchemaType, GetSchemaObjectName, GetSchemaPattern);
    +  Writeln('TCustomSQLStatement.DoPrepare: ', Copy(FOrigSQL, 1, 20));
       if (FOrigSQL='') then
         DatabaseError(SErrNoStatement);
       FServerSQL:=FOrigSQL;
    
    db-debug.patch (971 bytes)
  • testprep.pp (1,377 bytes)
    program DBconn;
    {$mode objfpc}
    {$h+}
    
    uses
      db, pqconnection, sqldb, sysutils;
    
    type
    TA = Class 
      procedure DoLog(Sender : TSQLConnection; EventType : TDBEventType; Const Msg : String);
      procedure Execute;
    end;
    
    procedure TA.DoLog(Sender : TSQLConnection; EventType : TDBEventType; Const Msg : String);
    
    begin
      writeln(eventtype,' : ',msg);
    end;
    
    Procedure TA.Execute;
    
     
    var
      xSQL: TPQConnection;
      Q: TSQLQuery;
      T: TSQLTransaction;
      I: Integer;
      
    begin
       
      xSQL := TPQConnection.Create(nil);
      try
        xSQL.OnLog:=@DoLog;
        xSQL.HostName := 'localhost';
        xSQL.DatabaseName := 'postgres';
        xSQL.UserName := 'fpc';
        xSQL.Password := 'Shimrod';
        xSQL.Connected := True;
    
        Q := TSQLQuery.Create(xSQL);
        T := TSQLTransaction.Create(xSQL);
        T.DataBase := xSQL;
        Q.DataBase := xSQL;
        Q.Transaction := T;
    
        Writeln(sLineBreak, 'Statement start here', sLineBreak);
        Q.SQL.Text := 'SELECT (:ID)';
        Q.ParamByName('ID').AsInteger := 1;
        Q.Prepare;
        for I := 0 to 1 do
        begin
          Writeln('Setting parameter');
          Q.ParamByName('ID').AsInteger := i;
          Q.Open;
          Writeln('Recordcount : ',Q.RecordCount);
          Writeln(' -->> Result ', I, ': ', Q.Fields[0].AsInteger);
          Q.Close;
        end;
      finally
        xSQL.Free;
      end;
    end;
    
    begin
      With TA.Create do
        try
          Execute;
        finally
          Free;
        end;
     
    end.
    
    testprep.pp (1,377 bytes)

Relationships

related to 0031231 resolvedMichael Van Canneyt procedure TCustomSQLQuery.Prepare does not work 

Activities

Ondrej Pokorny

2018-01-14 16:14

developer  

db-debug.patch (971 bytes)
Index: packages/fcl-db/src/base/dsparams.inc
===================================================================
--- packages/fcl-db/src/base/dsparams.inc	(revision 37966)
+++ packages/fcl-db/src/base/dsparams.inc	(working copy)
@@ -303,6 +303,7 @@
   tmpParam:TParam;
 
 begin
+  Writeln('TParams.ParseSQL: ', Copy(SQL, 1, 20));
   if DoCreate then Clear;
   // Parse the SQL and build ParamBinding
   ParamCount:=0;
Index: packages/fcl-db/src/sqldb/sqldb.pp
===================================================================
--- packages/fcl-db/src/sqldb/sqldb.pp	(revision 37966)
+++ packages/fcl-db/src/sqldb/sqldb.pp	(working copy)
@@ -1096,6 +1096,7 @@
     FOrigSQL := TrimRight(FSQL.Text)
   else
     FOrigSQL := Database.GetSchemaInfoSQL(GetSchemaType, GetSchemaObjectName, GetSchemaPattern);
+  Writeln('TCustomSQLStatement.DoPrepare: ', Copy(FOrigSQL, 1, 20));
   if (FOrigSQL='') then
     DatabaseError(SErrNoStatement);
   FServerSQL:=FOrigSQL;
db-debug.patch (971 bytes)

LacaK

2018-01-15 10:46

developer   ~0105839

I did partial patch to MSSQL in rev.37975:
cancel all pending rows and resultsets in case of
- unpreparation statement (UnprepareStatement) and
- case dataset is closed (FreeFldBuffers).
This should minimize impact of possible changes in sqldb to mssqlconn.

Ondrej Pokorny

2018-06-27 20:53

developer   ~0109087

I tested MySQL - and MySQL is affected as well :/

Michael Van Canneyt

2019-09-17 11:40

administrator   ~0118095

Test program that does not need modifications in sqldb

testprep.pp (1,377 bytes)
program DBconn;
{$mode objfpc}
{$h+}

uses
  db, pqconnection, sqldb, sysutils;

type
TA = Class 
  procedure DoLog(Sender : TSQLConnection; EventType : TDBEventType; Const Msg : String);
  procedure Execute;
end;

procedure TA.DoLog(Sender : TSQLConnection; EventType : TDBEventType; Const Msg : String);

begin
  writeln(eventtype,' : ',msg);
end;

Procedure TA.Execute;

 
var
  xSQL: TPQConnection;
  Q: TSQLQuery;
  T: TSQLTransaction;
  I: Integer;
  
begin
   
  xSQL := TPQConnection.Create(nil);
  try
    xSQL.OnLog:=@DoLog;
    xSQL.HostName := 'localhost';
    xSQL.DatabaseName := 'postgres';
    xSQL.UserName := 'fpc';
    xSQL.Password := 'Shimrod';
    xSQL.Connected := True;

    Q := TSQLQuery.Create(xSQL);
    T := TSQLTransaction.Create(xSQL);
    T.DataBase := xSQL;
    Q.DataBase := xSQL;
    Q.Transaction := T;

    Writeln(sLineBreak, 'Statement start here', sLineBreak);
    Q.SQL.Text := 'SELECT (:ID)';
    Q.ParamByName('ID').AsInteger := 1;
    Q.Prepare;
    for I := 0 to 1 do
    begin
      Writeln('Setting parameter');
      Q.ParamByName('ID').AsInteger := i;
      Q.Open;
      Writeln('Recordcount : ',Q.RecordCount);
      Writeln(' -->> Result ', I, ': ', Q.Fields[0].AsInteger);
      Q.Close;
    end;
  finally
    xSQL.Free;
  end;
end;

begin
  With TA.Create do
    try
      Execute;
    finally
      Free;
    end;
 
end.
testprep.pp (1,377 bytes)

Michael Van Canneyt

2019-09-17 11:41

administrator   ~0118096

Fixed. Added a testcase for this in the testsuite. Fixing this forced me to fix some other (unnoticed) issues as well.

Issue History

Date Modified Username Field Change
2018-01-14 16:14 Ondrej Pokorny New Issue
2018-01-14 16:14 Ondrej Pokorny File Added: db-debug.patch
2018-01-14 16:19 Michael Van Canneyt Assigned To => Michael Van Canneyt
2018-01-14 16:19 Michael Van Canneyt Status new => assigned
2018-01-15 10:46 LacaK Note Added: 0105839
2018-06-27 20:53 Ondrej Pokorny Note Added: 0109087
2019-09-17 11:40 Michael Van Canneyt File Added: testprep.pp
2019-09-17 11:40 Michael Van Canneyt Note Added: 0118095
2019-09-17 11:41 Michael Van Canneyt Status assigned => resolved
2019-09-17 11:41 Michael Van Canneyt Resolution open => fixed
2019-09-17 11:41 Michael Van Canneyt Fixed in Version => 3.3.1
2019-09-17 11:41 Michael Van Canneyt Fixed in Revision => 43024
2019-09-17 11:41 Michael Van Canneyt FPCTarget => 3.2.0
2019-09-17 11:41 Michael Van Canneyt Note Added: 0118096
2019-09-17 13:55 Michael Van Canneyt FPCTarget 3.2.0 => -
2019-09-17 13:56 Michael Van Canneyt Target Version => 4.0.0
2019-11-30 13:44 Joost van der Sluis Relationship added related to 0031231