Subject Re: [IBO] :PARAM inside function call not working!
Author Helen Borrie
At 08:09 PM 7/06/2005 +0200, you wrote:
>I put an IBOQuery with SQL set to:
>where (my_udf_function(ar_code, :AR_CODE)=0)
>whenever I use ParamByName("AR_CODE") i get error code -804: data type
>I get this error even when I do ParamByName("AR_CODE")->DataType=ftString!
>Setting DataType and InputType to AR_CODE param in IDE does'nt help...
>What's wrong?

The exception is coming from the server and it could be due to one thing or
to multiple things.

First, one assumes you are using the TDataset-compatible components, since
you are trying to refer to the property of a TField or TParam object
(DataType) and are not getting a local exception.

1. Make sure the statement is prepared before you try to refer to its
Params. A simple
if not MyStatement->Prepared

(Apols if the C++ is wrong - I do Delphi!)

1. Use an explicit cast in the parameter assignment, i.e.


This should cause IBO to do the right thing explicitly with the parameter's
SQLType when it populates the sqlvar structure.

2. Check whether the UDF declaration is by value, by reference or by
descriptor. If it is not by descriptor, then the engine may be having
trouble identifying the data type of the parameter at Prepare time. In
this case, a CAST in the statement, along with explicit use of AsString in
the parameter assignment, might work:

where (my_udf_function(
cast(:AR_CODE as varchar(whatever))

3. Note also that you might get a different exception, or a wrong
result, if the UDF isn't designed to return an integer or a fixed numeric