[fcl-db] TParam: ambiguous datetime format is used that fails when regional settings are used (MSSQL)
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:
MSSQL: If you change DATEFORMAT from ymd to e.g. dmy, TParam-queries are wrongly filled which causes the query to fail.
Steps to reproduce:
See this program:
program DBconn;
uses
db, mssqlconn, sqldb, sysutils;
var
xSQL: TMSSQLConnection;
Q: TSQLQuery;
T: TSQLTransaction;
begin
Q := nil;
T := nil;
xSQL := TMSSQLConnection.Create(nil);
try
xSQL.HostName := 'your-connection';
xSQL.DatabaseName := 'your-database';
xSQL.Connected := True;
Q := TSQLQuery.Create(nil);
T := TSQLTransaction.Create(xSQL);
T.DataBase := xSQL;
Q.DataBase := xSQL;
Q.Transaction := T;
Q.SQL.Text := 'SET DATEFORMAT dmy';
Q.ExecSQL;
T.Commit;
Q.SQL.Text := 'DROP TABLE mytest';
Q.ExecSQL;
Q.SQL.Text := 'CREATE TABLE mytest (mydate DATETIME NOT NULL)';
Q.ExecSQL;
Q.SQL.Text := 'INSERT INTO mytest (mydate) VALUES (:mydate)';
Q.ParamByName('mydate').AsDateTime := 43025.819861111115;
Q.ExecSQL; // FAIL HERE
Q.SQL.Text := 'SELECT mydate from mytest';
Q.Open;
Writeln(Q.Fields[0].AsString);
T.Commit;
finally
Q.Free;
xSQL.Free;
end;
Readln;
end.
!!! The use of TParams MUST BE independent on user settings !!!
Additional information:
A possible patch included. It changes the datetime format to ISO yyyy-mm-ddThh:mm:ss.zzz, which is unambiguous in MSSQL. On the other hand, the 'yyyy-mm-dd hh:mm:ss.zzz' is ambiguous in MSSQL!!!
The ISO format is supported by Postgres as well - but I am not sure about all different DB, so maybe you may want to create a TSQLConnection-dependent datetime format.
!!! Note that FSQLFormatSettings are ignored anyway !!!
Mantis conversion info:
- Mantis ID: 32979
- Build: revision 37897
- Version: 3.1.1
- Fixed in version: 3.1.1
- Fixed in revision: 37974 (#d47be9c3)
- Target version: 3.2.0