473,320 Members | 1,810 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
10 4232
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Sean | last post by:
Hi all, In VB.NET you can use default parameters to stored procedures using the Nothing keyword, as described in: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q321902 I used to...
2
by: Benoist LUGNIER | last post by:
Hello VB .NET - ADO .NET - SQL Server (MSDE) I want to retrieve the parameters of a stored procedure before execute it. I've already try the fillschema but it return the "return object (table)"...
4
by: Andrew Baker | last post by:
I have the following code that calles a stored proc in SQLServer. When the output parameter @custref is null (System.DBNull) I cant seem to find a test for this and I get an exception. I know I...
1
by: lnd | last post by:
Is there any bad side effect in recompiling PG with increased stored procedures maximum parameters count, for example, to 128? Thank you in advance, Laimis ---------------------------(end...
4
by: xAvailx | last post by:
Hello: I didn't find any documentation that notes save point names are case sensitive, but I guess they are... Stored Proc to reproduce: /* START CODE SNIPPET */ If Exists (Select * From...
6
by: Paul M | last post by:
Hi All, I'm currently writing a z/OS DB2 Stored Proc in C, using an example from the IBM Stored Procedure guide (SG24-7083-00). The database calls to read and update the database work...
3
by: Byron | last post by:
I'm trying to iterate stored parameters and populate any public properties of an object with the parameter value. I've suceeded in doing it the other way around, but when I try it to the object I...
10
by: dlesandrini | last post by:
Ok, I know I've done this, but it doesn't seem to work for me anymore. I have a form based on a stored proc, usp_PubsAuthors and it has a single parameter, @FilterState varchar(2) which...
3
by: bogdan | last post by:
Hi, I have a stored procedure that returns a single value. Example: SELECT @RowCount = COUNT(*) FROM t WHERE RETURN @RowCount I created a data set, table adapter, and adapter's method...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.