Errors setting params

[nickbarnes]

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

#1.

Code: Select all

  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?

[AlexP]

Hello,

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

[AlexP]

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.