--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:

>
> --- In firebird-support@yahoogroups.com, "lutterot" <lutteroth@> wrote:
> >
> > --- In firebird-support@yahoogroups.com, "Adam" <s3057043@> wrote:
> > >
> > > --- In firebird-support@yahoogroups.com, "lutterot" <lutteroth@>
> wrote:
> > > >
> > > > Hello!
> > > >
> > > > I have written a UDF varchar_to_guid which seems to work fine:
> > > >
> > > > /* Gives back the 16 byte GUID for a VARCHAR */
> > > > DECLARE EXTERNAL FUNCTION VARCHAR_TO_GUID
> > > > VARCHAR(32765) BY DESCRIPTOR
> > > > RETURNS CHAR(16) CHARACTER SET OCTETS FREE_IT
> > > > ENTRY_POINT 'udf_varchar_to_guid' MODULE_NAME 'guid_udf';
> > > >
> > > > Now I am using this UDF in a stored procedure CreateStringId:
> > > >
> > > > CREATE PROCEDURE CreateStringId(val VARCHAR(32765))
> > > > RETURNS (id CHAR(16))
> > > > AS BEGIN
> > > > id = varchar_to_guid(:val);
> > > > SUSPEND;
> > > > END^
> > > >
> > > > Now when I am using this procedure like this
> > > >
> > > > select id from CreateStringId('abc')
> > > >
> > > > then I get a different result from using the UDF directly like

this

> > > >
> > > > select varchar_to_guid('abc') from SOMETABLE
> > > >
> > > > And when I hard-code the string 'abc' into the store procedure
> > like this
> > > >
> > > > CREATE PROCEDURE CreateStringId(val VARCHAR(32765))
> > > > RETURNS (id CHAR(16))
> > > > AS BEGIN
> > > > id = varchar_to_guid('abc');
> > > > SUSPEND;
> > > > END^
> > > >
> > > > then the result is the correct one (i.e. the same as using the UDF
> > > > directly).
> > > >
> > > > Can anybody help me out and explain this behavior to me?
> > >
> > > Does the following query return the same 'wrong' value?
> > > select varchar_to_guid(cast('abc' as varchar(32765)) from SOMETABLE;
> >
> > Yes, the same wrong value as the stored procedure.
>
> Then you have a problem with conversions between char and varchar.
> Using 'abc' is sending through a char datatype, whereas when called
> from the stored procedure, it is using a varchar.
>
> I am not sure whether this is a bug in Firebird (should it
> automatically convert it to a varchar because it knows the input type
> of a UDF and it does this for other DML), or whether your UDF is
> behaving incorrectly. Maybe someone else can chime in on what the
> expected behaviour should be.
>
> CREATE PROCEDURE CreateStringId(val VARCHAR(32765))
> RETURNS (id CHAR(16))
> AS
> BEGIN
> id = varchar_to_guid(cast(:val as char(32765)));
> SUSPEND;
> END^
>
> Should give the right result.

First of all, thank you for your replies. Now I know what went wrong.
I tested the UDF doing something like

select varchar_to_guid('abc') from SOMETABLE

without knowing that 'abc' is not converted into a VARCHAR(32765)
automatically, despite the UDF requiring that type. 'abc' seems to
have type CHAR(3).

Sometime before, I was treating the parameter of the UDF correctly as
a VARCHAR, using the PARAMVARY struct. But because this didn't work
with the tests as above, I changed the UDF so that it would treat the
parameter as CHAR(n).

Now I changed the UDF back using the PARAMVARY struct, which takes
into account that in memory the varchar starts with two bytes of
length information before the actual characters. When using it with
string literals such as 'abc', I have to cast explicitly:

select varchar_to_guid(cast('abc' as VARCHAR(32765))) from SOMETABLE

...and it works.

It might be a good idea to implement some kind of auto-casting between
CHAR(n) and VARCHAR(n) depending on the parameter definition of a UDF,
or some type check reminding the user to perform such a cast.

Again, thanks for your help,
Christof