|
Problem IC61553
|
Status: Closed |
COMMAS IN STRING VALUES FOR ARRAYS ARE NOT RESPECTED BY CLP IN THE PARSING |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
The following scenario is triggering a -104 error because the
CLP code is not properly handling commas when parsing array
elements.
create TYPE VARIABLE_VALUES AS VARCHAR(1200) ARRAY[100]
create procedure TEST1(
IN VARIABLES VARIABLE_VALUES )
LANGUAGE SQL
BEGIN ATOMIC
END@
When the procedure with a comma in the first array element is
called, getting the following error :
call TEST1( ARRAY[ '${email/attachments}=d:/tmp/a.a,
d:/tmp/b.b', '${email/sender}= jpparkin@ca.ibm.com' ] )
SQL0104N An unexpected token "ARRAY[" was found following "".
Expected tokens may include: "<name>". SQLSTATE=42601
If the comma out of the string value is left out, then the SQL
works.
call TEST1( ARRAY[ '${email/attachments}=d:/tmp/a.a d:/tmp/b.b',
'${email/sender}= jpparkin@ca.ibm.com' ] )
Return Status = 0 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All db2 users which are using array element values with *
* comma as a part inside stored procedure. *
****************************************************************
* PROBLEM DESCRIPTION: *
* The following scenario is triggering a -104 error because *
* the *
* CLP code is not properly handling commas when parsing array *
* *
* elements. *
* *
* create TYPE VARIABLE_VALUES AS VARCHAR(1200) ARRAY[100] *
* *
* create procedure TEST1( *
* IN VARIABLES VARIABLE_VALUES ) *
* LANGUAGE SQL *
* *
* BEGIN ATOMIC *
* END@ *
* *
* When the procedure with a comma in the first array element *
* is *
* called, getting the following error : *
* *
* call TEST1( ARRAY[ '${email/attachments}=d:/tmp/a.a, *
* d:/tmp/b.b', '${email/sender}= jpparkin@ca.ibm.com' ] ) *
* *
* SQL0104N An unexpected token "ARRAY[" was found following *
* "". *
* Expected tokens may include: "<name>". SQLSTATE=42601 *
* *
* If the comma out of the string value is left out, then the *
* SQL *
* works. *
* *
* call TEST1( ARRAY[ '${email/attachments}=d:/tmp/a.a *
* d:/tmp/b.b','${email/sender}= jpparkin@ca.ibm.com' ] ) *
* *
* Return Status = 0 *
****************************************************************
* RECOMMENDATION: *
* Upgrade to db2_v95fp5 or db2_v97fp1 or heigher releases. *
**************************************************************** |
| Local Fix: |
Possible workaround:
CREATE PROCEDURE caller()
BEGIN
call TEST1( ARRAY[ '${email/attachments}=d:/tmp/a.a,
d:/tmp/b.b', '${email/sender}= jpparkin@ca.ibm.com' ] );
END
CALL caller() |
| available fix packs: |
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
db2_v95fp5 contains the fix of this issue. After this fix, user
could use comma under string elements of array inside a stored
procedure. The sql command will execute successfully. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC61662
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 18.06.2009
22.12.2009
22.12.2009 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.5.0.5
|
 |
