Could params not use "AsDateTime"?

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for PostgreSQL in Delphi and C++Builder
Post Reply
Eden0928
Posts: 62
Joined: Sun 22 Apr 2012 14:08

Could params not use "AsDateTime"?

Post by Eden0928 » Sun 02 Nov 2014 01:28

I used:
Delphi 2009 Version 12.0.3420.21218
dbx driver for postgreSQL V3.5.7
PostgreSQL 9.3.5

My table struct:

Code: Select all

CREATE TABLE tb(
  AID Integer NOT NULL,
  ADateTime TIMESTAMP,
  primary key(AID)
);

insert INTO tb values(1, '2014/10/01 01:01:01');
Delphi Code is:

Code: Select all

  SQLQuery1.SQL.Text := 'SELECT * FROM tb WHERE adatetime >= :adateTime ';
  SQLQuery1.Params.ParamByName('adateTime').AsDateTime := StrToDate('2010/01/01');
  SQLQuery1.Open;
I get a error message:
Image

Why?

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Could params not use "AsDateTime"?

Post by azyk » Mon 03 Nov 2014 10:42

Thank you for the information. We have reproduced the problem and will investigate it. We will inform you when we have any results.

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Could params not use "AsDateTime"?

Post by azyk » Wed 12 Nov 2014 09:52

The error is not due to our dbExpress driver for PostgreSQL, but due to a bug in dbExpress in some versions of Delphi. Unfortunately we can't affect this behaviour. A similar report exists in Embarcadero's Quality Central at: http://qc.embarcadero.com/wc/qcmain.aspx?d=9172 .

To solve the problem, you can use a workaround suggested in the above report - when setting the value of the parameter, use the AsSQLTimeStamp property instead of AsDateTime. For example:

Code: Select all

  SQLQuery1.SQL.Text := 'SELECT * FROM tb WHERE adatetime >= :adateTime ';
  SQLQuery1.Params.ParamByName('adateTime').AsSQLTimeStamp := DateTimeToSQLTimeStamp(StrToDate('2010/01/01'));
  SQLQuery1.Open;

Post Reply