[fcl-db] MSSQL: An SQL statement is always prepared even if it's been prepared already
Original Reporter info from Mantis: kluug.net @onpok
-
Reporter name: Ondrej Pokorny
Original Reporter info from Mantis: kluug.net @onpok
- Reporter name: Ondrej Pokorny
Description:
In MSSQL, when the statement is prepared, the FPrepared flag is not set to true so that it has to be prepared all over again which is useless.
Steps to reproduce:
1.) Apply the attached patch (but comment out the "FPrepared := True;" line).
2.) Run this program:
program DBconn;
uses
db, mssqlconn, sqldb, sysutils;
var
xSQL: TMSSQLConnection;
Q: TSQLQuery;
T: TSQLTransaction;
begin
xSQL := TMSSQLConnection.Create(nil);
try
xSQL.HostName := 'your-server';
xSQL.DatabaseName := 'your-database';
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 mydate from mytest WHERE CAST(mydate as DATE)<>:mydate';
Q.ParamByName('mydate').AsDate := now;
Q.Open;
Writeln(sLineBreak, '1: ', Q.Fields[0].AsString, sLineBreak);
Q.Close;
Q.ParamByName('mydate').AsDate := StrToDate('17.10.2017');
Q.Open;
Writeln('2: ', Q.Fields[0].AsString);
finally
xSQL.Free;
end;
Readln;
end.
3.) Check the output - you'll see that the SQL is parsed several times and the statement is prepared all over again.
4.) Uncomment the "FPrepared := True;" line in the patch.
5.) See again, the needed prepare/format calls have reduced.
Additional information:
The patch includes Writeln debug lines. Please delete them before applying to FPC.
Mantis conversion info:
- Mantis ID: 33010
- Version: 3.1.1
- Fixed in version: 3.1.1
- Fixed in revision: 37951 (#f930d186)
- Target version: 3.2.0