--- 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.

Adam