Brian,

The server and the client are both at V8 FP 12. I'm running the Admin
Client on my workstation.

Here's the results of casting the null to a varchar(26) using RapidSQL
7.4 over ODBC:

call rg.nulltest(cast(null as varchar(26)))
R1
PARM1 IS NOT NULL

call rg.nulltest(cast(NULL as varchar(26)))

R1
PARM1 IS NOT NULL

The error seems to be ODBC related. Whenever we have used the DB2
Command Line Processor, the results return correctly.

FYI - I've submitted the case to our DBA team who will be creating the
PMR and will update the thread with the number when it gets created.

Thanks,

Bob

Brian Tkatch wrote:
> syntego@gmail.com wrote:
> > I think I have discovered a bug in the handling of null values (vs NULL
> > values) passed as parameters to a stored proc.
> >
> > I have always believed that the database handled NULL and null the
> > same. The following statement returns the expected results:
> >
> > select case when NULL is null then 'SAME' else 'DIFFERENT' end from
> > sysibm.sysdummy1;
> >
> > returns SAME.
> >
> > BUT, If you call a proc with lower case null as a parameter, it will
> > not be treated as a NULL.
> >
> > Here's an example proc:
> >
> > CREATE PROCEDURE RG.NULLTEST(IN PARM1 VARCHAR(26))
> > SPECIFIC NULLTEST
> > MODIFIES SQL DATA
> > NOT DETERMINISTIC
> > NULL CALL
> > LANGUAGE SQL
> >
> > P1: BEGIN
> >
> > DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
> > SELECT 'PARM1 IS NULL' AS R1 FROM SYSIBM.SYSDUMMY1;
> >
> > DECLARE c2 CURSOR WITH RETURN TO CALLER FOR
> > SELECT 'PARM1 IS NOT NULL' AS R1 FROM SYSIBM.SYSDUMMY1;
> >
> > if PARM1 IS null THEN
> >
> > OPEN c1;
> >
> > ELSE
> >
> > OPEN c2;
> >
> > END IF;
> >
> > END P1
> > ;
> >
> >
> > CALL RG.NULLTEST(null);
> >
> > returns PARM1 IS NOT NULL
> >
> > BUT
> >
> > CALL RG.NULLTEST(NULL);
> >
> > PARM1 IS NULL
> >
> > It's easy enough to code around, but frustrating when the database
> > violates a basic assumption.
> >
> > Has anyone else encountered this issue?
> >
> > Bob
>
> Works properly for me, on Solaris (UDB 8.1.6).
>
> Two points.
>
> 1) Is the version of the client and the server the same? (We had issues
> here with the server crashing, when they were not.)
>
> 2) Have you tried casting the NULL as a VARCHAR to match the IN
> parameter?
>
> B.