Devart Forums

Posted: **Mon 10 Nov 2014 06:57**

Using PgDAC 4.4.11. I've run into two separate errors when setting query parameters.

#1.

  Query.SQL.Text := 'SELECT CAST(:Date AS timestamp)';
  Query.ParamByName('Date').Value := '2015-01-01';

The query returns something like '2014-12-15 13:08:40', but the value is not consistent. The bug appears to be in TCustomPgTimeStamp.FromString() - when the string has no time portion, the TimeZoneOffset variable is never initialised.

#2.
When setting a param value as a variant, if the param's DataType is already defined, it does not get updated:

Code: Select all

  Query.SQL.Text := 'SELECT CAST(:Date AS timestamp)';
  Query.ParamByName('Date').Value := '2015-01-01';    // DataType is ftWideString
  Query.ParamByName('Date').AsDate := Now;            // DataType is ftDate
  Query.ParamByName('Date').Value := '2015-01-01';    // DataType is ftDate
  Query.Open;

In this particular case, it causes Query.Open to raise an exception - TCustomDASQL.AssignParamValue() has a case for "DataType = ftDate", and tries to cast the string to an integer, which fails with an EVariantTypeCastError.

It seems I can avoid this error by setting "Param.DataType := ftUnknown" before assigning Param.Value... Would this be a safe / sensible workaround?

Posted: **Mon 10 Nov 2014 10:52**

Hello,

Thank you for the information, we have reproduced the problem and will investigate the reasons
for such behavior.

Posted: **Thu 20 Nov 2014 15:09**

1) This behavior is due to that you are setting a timestamp parameter to a "date" value, and PostgreSQL documentation says the correct value is "datetime".
I.e. you should either assign parameter value as follows:

Code: Select all

PgQuery.ParamByName('Date').Value := '2015-01-01 00:00:00';

or set the property

Code: Select all

PgQuery.Options.UseParamTypes := True;

in order to specify for the server that the parameter will be a string - and the server itself will map string to date.
2) This problem is already fixed, but for correct work, data format must match regional settings of the client PC.

Devart Forums

Errors setting params

Errors setting params

Re: Errors setting params

Re: Errors setting params