By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,274 Members | 2,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,274 IT Pros & Developers. It's quick & easy.

BUG DB2 V8 FP12 Stored Proc Parameters - NULL value is case sensitive

P: n/a
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

Aug 21 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
sy*****@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?
I cannot reproduce this on DB2 9 GA.
Nonetheleless if your observation turns out to be confirmed it would be
a bug in the CLP, not the engine.
CLP does some amount of shallow parsing of the CALL statement to replace
literals with ? for IN/OUT arguments. Also in the very early days of
DB2 V7(!) CLP accepted strings without quotes. Here the NULL vs null
('null') would make sense.
Could it be you are on an old DB2 V7 client?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 21 '06 #2

P: n/a
I tested through both ODBC and CLP and here's what I found:

When I tested through RapidSQL using the IBM DB2 ODBC Driver
8.01.12.99, the proc returned incorrectly.

R1
PARM1 IS NOT NULL

I tried through the Command Line Processor and the proc returned
correctly.

(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 SDK 8.2.5

db2 =call rg.NULLTEST(null)
Result set 1
--------------

R1
-------------
PARM1 IS NULL

1 record(s) selected.

Return Status = 0

so it appears the problem may be with the ODBC driver.

-Bob

Serge Rielau wrote:
sy*****@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?
I cannot reproduce this on DB2 9 GA.
Nonetheleless if your observation turns out to be confirmed it would be
a bug in the CLP, not the engine.
CLP does some amount of shallow parsing of the CALL statement to replace
literals with ? for IN/OUT arguments. Also in the very early days of
DB2 V7(!) CLP accepted strings without quotes. Here the NULL vs null
('null') would make sense.
Could it be you are on an old DB2 V7 client?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 22 '06 #3

P: n/a
OK, that starts to make sense. Can you open a PMR?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 22 '06 #4

P: n/a
Will do. Thanks.

Serge Rielau wrote:
OK, that starts to make sense. Can you open a PMR?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 22 '06 #5

P: n/a
sy*****@gmail.com wrote:
Will do. Thanks.
Thanks, I asked backstage and they tried to repro. Without success.
So we are looking forward to your PMR to get to the bottom of this.

Cheers
Serge

Result:
sqlprepare 1 "call RG.NULLTEST(null)" -3
SQLPrepare: rc = 0 (SQL_SUCCESS)
sqlexecute 1
SQLExecute: rc = 0 (SQL_SUCCESS)
fetchall 1
FetchAll: Columns: 1
R1
PARM1 IS NULL
FetchAll: 1 rows fetched.
sqlfreestmt 1 SQL_CLOSE
SQLFreeStmt: rc = 0 (SQL_SUCCESS)
>
sqlprepare 1 "call RG.NULLTEST(NULL)" -3
SQLPrepare: rc = 0 (SQL_SUCCESS)
sqlexecute 1
SQLExecute: rc = 0 (SQL_SUCCESS)
fetchall 1
FetchAll: Columns: 1
R1
PARM1 IS NULL
FetchAll: 1 rows fetched.
sqlfreestmt 1 SQL_CLOSE
SQLFreeStmt: rc = 0 (SQL_SUCCESS)
>
sqlprepare 1 "call RG.NULLTEST('null')" -3
SQLPrepare: rc = 0 (SQL_SUCCESS)
sqlexecute 1
SQLExecute: rc = 0 (SQL_SUCCESS)
fetchall 1
FetchAll: Columns: 1
R1
PARM1 IS not NULL
FetchAll: 1 rows fetched.
sqlfreestmt 1 SQL_CLOSE
SQLFreeStmt: rc = 0 (SQL_SUCCESS)
>
sqlprepare 1 "call RG.NULLTEST('NULL')" -3
SQLPrepare: rc = 0 (SQL_SUCCESS)
sqlexecute 1
SQLExecute: rc = 0 (SQL_SUCCESS)
fetchall 1
FetchAll: Columns: 1
R1
PARM1 IS not NULL
FetchAll: 1 rows fetched.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 22 '06 #6

P: n/a
sy*****@gmail.com wrote:
Will do. Thanks.
Let me know when you open PMR (PMR number will be handy ...)

Jan M. Nelken
Aug 22 '06 #7

P: n/a
sy*****@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.

Aug 23 '06 #8

P: n/a
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:
sy*****@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.
Aug 28 '06 #9

P: n/a
Here's the PMR:

PMR27545

Thanks All.

Jan M. Nelken wrote:
sy*****@gmail.com wrote:
Will do. Thanks.

Let me know when you open PMR (PMR number will be handy ...)

Jan M. Nelken
Aug 28 '06 #10

P: n/a
sy*****@gmail.com wrote:
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
Our error was also ODBC releated but only when the server was V8 and
the client V7. The CLP returned the error, ODBC crashed the server (not
the connection, the server had to be brought back up). Ironic as it may
be, one case was with a CHAR(26) (being passed to a VARCHAR(25), which
worked but crashed when accessing the final character via SUBSTR).

Inbteresting that both are now NOT NULL. That is highly odd.

B.

Aug 29 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.