Subject Re: [ib-support] TIMESTAMP as UDF argument
Author Helen Borrie
At 11:30 PM 12-11-01 +0000, you wrote:
>hi, i have one problem with udfs
>when they take TIMESTAMP as argument.
>for example udf returns an integer.
> Everything is perfect till i do something like this in C++Builder

I think you have more than one problem here:

>Query->SQL->Add("SELECT * FROM T WHERE UDF(:dd) = TABLE.ID");

should be SELECT * FROM T WHERE ID = UDF(:dd);

>Query->Prepare(); //Here is thrown exception

Too many unknowns in your statement, to begin with.
e.g. is the table named 'T' or 'TABLE' ? What is 'UDF' ? What is the datatype of the parameter 'dd' ?

Assuming you are trying to call a UDF that takes a date argument of some sort and returns an integer (I guess some sort of decoding function, maybe YEAR) you'll have to be certain that the UDF expects a TIMESTAMP.

>Query->ParamByName("dd")->AsDateTime = Date();

As far as I know, you are going to come unstuck using FreeUDFLib or the Borland version of ib_udf because they have not been updated to recognise the Dialect 3 TIMESTAMP and DATE types. The DATE type referred to in those libs is a date-and-time, whereas the Dialect 3 DATE is a date-only.

You'll have to give us more info about which UDF library you are using and which dialect you are using it with. Firebird has a number of new date-related things in Dialect 3 that make it unnecessary to use UDFs to decompose dates... also, show us the ACTUAL SQL statement that is throwing the errors...

>Exception is "Unknown data type SQL error code = -804"
>platform: FireBird 0.9-4 Test1 on w2k

That's a very old beta. You should upgrade to the Beta 2 version (or wait a few days and get the RC 1 version, I guess).


All for Open and Open for All
InterBase Developer Initiative ยท