TIBCStoredProc:Param.AsString doesn't use OS date format??

[upscene]

Hi,

It seems setting a procedure parameter .AsString doesn't use Windows date formatting on date/time/timestamp parameters.

For example, despite my settings being dd/mm/yyyy, the following raises a conversion error:

Code: Select all

var d: TDate;
begin
  d := EncodeDate(2011, 3, 21);
  showmessage(datetostr(d));
  IBCStoredProc1.ParamByName('DEF_TIMESTAMP').AsString := DateToStr(d);
  IBCStoredProc1.ExecProc;
end;

Wouldn't it make sense to use Windows format?

[upscene]

It seems this only fails on SETTING the parameter value. Anyway, it fails.

[AndreyZ]

The point is that Firebird doesn't care about Windows locale date format when interpreting date literals. You can find more information about it here: http://www.firebirdsql.org/doc/contrib/FirebirdDateLiterals.html
You can solve this problem in two ways:
1) use the AsDate property in the following way:

Code: Select all

IBCStoredProc1.ParamByName('DEF_TIMESTAMP').AsDate := d;

2) convert date to appropriate string format for Firebird in the following way:

Code: Select all

DateTimeToString(str, 'dd.mm.yyyy', d);
IBCStoredProc1.ParamByName('DEF_TIMESTAMP').AsString := str;

[upscene]

Are you serious with your answer?

Your answer about Firebird date literals is WRONG as this involves date strings in SQL statements!! Is has NOTHING to do with parameters!!

If I use .AsString in DELPHI, this should be independent of what the DBMS wants to see, as it needs to be converted to the DBMS specific API anyway, done "under the hood" by the component set.

.AsString should ALWAYS uses the Windows settings!!

[AndreyZ]

This is not an IBDAC problem. IBDAC uses Windows settings and transfers date value in string format correctly to the server, but Firebird rejects date values in the 'dd/mm/yyyy' format. You can read about it in the 'Separators in Non-U.S. Dates' subtopic of the 'Firebird Date Literals' topic, the link to which I gave you in the previous post.

[upscene]

This is not an IBDAC problem. IBDAC uses Windows settings and transfers date value in string format correctly to the server, but Firebird rejects date values in the 'dd/mm/yyyy' format. You can read about it in the 'Separators in Non-U.S. Dates' subtopic of the 'Firebird Date Literals' topic, the link to which I gave you in the previous post.

Since when are date-value parameters transferred as STRINGS to the Firebird API? That defeats the purpose of using parameters!!

Given that Firebird returns you the parameter datatype, pass it as a DATE, TIME or TIMESTAMP, not as a string. Isn't this what you're doing??

[AndreyZ]

When you are using the AsString property, IBDAC treats such parameters as string parameters and transfers their values to server in string format. To avoid this problem, you should use the AsDate property instead of the AsString property.