View Issue Details

IDProjectCategoryView StatusLast Update
0036461FPCDatabasepublic2020-01-02 10:58
ReporterPascal RiekenbergAssigned ToMichael Van Canneyt 
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Platformi386OSWindows 10 x64OS Version1903
Product Version3.3.1Product Buildr43677 
Target Version4.0.0Fixed in Version3.3.1 
Summary0036461: TSQLQuery: new parameters are not recognized
DescriptionI have a prepared statement with paramters.
When i set new parameters they are not reconized.
If i do: "SQL.Text := SQL.Text;" before seting the parameters everything works as expected.

Additional Informationfunction TTest.Test(pNameUp: String): TObject;
var
  lRef: String;
  lRefField: TField;
begin
  if not Assigned(fQuery) then begin
    fQuery := TSQLQuery.Create(Nil);
    with fQuery do begin
      SQLConnection := fCon;
      SQL.Text :=
        'select' + 0000010 +
        ' substr(hkey, 9, 8) ref' + 0000010 +
        'from' + 0000010 +
        ' d738u' + 0000010 +
        'where' + 0000010 +
        ' hkey like concat(:name8, ''________SG32ANW %'');';
      Prepare;
    end;
  end;
  Result := Nil;
  with fQuery do begin
    //SQL.Text := SQL.Text;
    Params[0].AsString := PadRight(Copy(pNameUp, 1, 8), 8);
    Active := True;
    try
      lRefField := Fields[0];
      while not EOF do begin
        lRef := TrimRight(lRefField.AsString);
        Result := FindObject(lRef);
        if Assigned(Result) then
          Exit;
        Next;
      end;
    finally
      Active := False;
    end;
  end;
end;

TagsNo tags attached.
Fixed in Revision43702
FPCOldBugId
FPCTarget4.0.0
Attached Files
  • sqldb.pp.patch (827 bytes)
    Index: packages/fcl-db/src/sqldb/sqldb.pp
    ===================================================================
    --- packages/fcl-db/src/sqldb/sqldb.pp	(revision 43700)
    +++ packages/fcl-db/src/sqldb/sqldb.pp	(working copy)
    @@ -2945,8 +2945,11 @@
     begin
       if Not Refreshing then
         CheckInactive;
    -  If Assigned(FStatement) then
    +  If Assigned(FStatement) then begin
    +    if Assigned(Cursor) and Cursor.FSelectable then
    +      FreeFldBuffers;
         FStatement.Unprepare;
    +  end;
     end;
     
     procedure TCustomSQLQuery.FreeFldBuffers;
    @@ -3056,8 +3059,6 @@
     
       if assigned(Cursor) then
         begin
    -    if Cursor.FSelectable then
    -      FreeFldBuffers;
         CheckUnPrepare;
         // Some SQLConnections does not support statement [un]preparation,
         //  so let them do cleanup f.e. cancel pending queries and/or free resultset
    
    sqldb.pp.patch (827 bytes)

Activities

Pascal Riekenberg

2019-12-19 16:05

reporter   ~0119952

Second option to fix wrong behaviour:
Do "Unprepare," after "Active := False;"

Pascal Riekenberg

2019-12-20 09:07

reporter   ~0119968

Last edited: 2019-12-20 10:55

View 2 revisions

Test TTestTSQLQuery.TestPrepareCount in TestSQLDB.pas should catch this!
I tried with MySQL57 but get an AV while running the second Open:

0 LOADFIELD(0x1515cf0, 0x14dfcd0, 0x5eaebb0, 0x5f2bee1, false) at ..\src\sqldb\mysql\mysqlconn.inc:886
1 LOADFIELD(0x1516050, 0x5eaebb0, 0x5f2bee1, false) at ..\src\sqldb\sqldb.pp:3037
2 LOADBUFFER(0x1516050, 0x5f2bee1 '') at ..\src\base\bufdataset.pas:2550
3 GETNEXTPACKET(0x1516050) at ..\src\base\bufdataset.pas:2430
4 GETRECORD(0x1516050, 0x5f36f20 0000255, GMNEXT, true) at ..\src\base\bufdataset.pas:2040
5 GETNEXTRECORD(0x1516050) at ..\src\base\dataset.inc:762
6 GETNEXTRECORDS(0x1516050) at ..\src\base\dataset.inc:790
7 RECALCBUFLISTSIZE(0x1516050) at ..\src\base\dataset.inc:1164
8 DOINTERNALOPEN(0x1516050) at ..\src\base\dataset.inc:419
9 OPENCURSOR(0x1516050, false) at ..\src\base\dataset.inc:958
10 OPENCURSOR(0x1516050, false) at ..\src\sqldb\sqldb.pp:2807
11 SETACTIVE(0x1516050, true) at ..\src\base\dataset.inc:1093
13 TESTPREPARECOUNT(0x14dd3d0) at testsqldb.pas:790
12 OPEN(0x1516050) at ..\src\base\dataset.inc:2075
...

Pascal Riekenberg

2019-12-20 11:22

reporter   ~0119970

sqldb: TCustomSQLQuery.InternalClose:

if Cursor.FSelectable then
  FreeFldBuffers;

FreeFldBuffers shouldn't be called in InternalClose. Maybe in Unprepare.

Pascal Riekenberg

2019-12-20 11:35

reporter  

sqldb.pp.patch (827 bytes)
Index: packages/fcl-db/src/sqldb/sqldb.pp
===================================================================
--- packages/fcl-db/src/sqldb/sqldb.pp	(revision 43700)
+++ packages/fcl-db/src/sqldb/sqldb.pp	(working copy)
@@ -2945,8 +2945,11 @@
 begin
   if Not Refreshing then
     CheckInactive;
-  If Assigned(FStatement) then
+  If Assigned(FStatement) then begin
+    if Assigned(Cursor) and Cursor.FSelectable then
+      FreeFldBuffers;
     FStatement.Unprepare;
+  end;
 end;
 
 procedure TCustomSQLQuery.FreeFldBuffers;
@@ -3056,8 +3059,6 @@
 
   if assigned(Cursor) then
     begin
-    if Cursor.FSelectable then
-      FreeFldBuffers;
     CheckUnPrepare;
     // Some SQLConnections does not support statement [un]preparation,
     //  so let them do cleanup f.e. cancel pending queries and/or free resultset
sqldb.pp.patch (827 bytes)

Michael Van Canneyt

2019-12-20 17:30

administrator   ~0119975

Your fix is not correct. The problem was MySQL specific, some values were not correctly re-initialized.

I have extended the test somewhat, and added a second test.

Pascal Riekenberg

2020-01-02 10:58

reporter   ~0120179

Thanks, fix was just a guess!

Issue History

Date Modified Username Field Change
2019-12-19 16:02 Pascal Riekenberg New Issue
2019-12-19 16:05 Pascal Riekenberg Note Added: 0119952
2019-12-20 09:07 Pascal Riekenberg Note Added: 0119968
2019-12-20 10:55 Pascal Riekenberg Note Edited: 0119968 View Revisions
2019-12-20 11:22 Pascal Riekenberg Note Added: 0119970
2019-12-20 11:35 Pascal Riekenberg File Added: sqldb.pp.patch
2019-12-20 16:31 Michael Van Canneyt Assigned To => Michael Van Canneyt
2019-12-20 16:31 Michael Van Canneyt Status new => assigned
2019-12-20 17:30 Michael Van Canneyt Status assigned => resolved
2019-12-20 17:30 Michael Van Canneyt Resolution open => fixed
2019-12-20 17:30 Michael Van Canneyt Fixed in Version => 3.3.1
2019-12-20 17:30 Michael Van Canneyt Fixed in Revision => 43702
2019-12-20 17:30 Michael Van Canneyt FPCTarget => 3.2.0
2019-12-20 17:30 Michael Van Canneyt Note Added: 0119975
2019-12-20 17:41 Michael Van Canneyt Target Version => 4.0.0
2019-12-20 17:41 Michael Van Canneyt FPCTarget 3.2.0 => 4.0.0
2020-01-02 10:58 Pascal Riekenberg Status resolved => closed
2020-01-02 10:58 Pascal Riekenberg Note Added: 0120179