|
Problem IC62955
|
Status: Closed |
SQL16061 RETURNED WHEN CASTING HEXBINARY, BASE64BINARY TO VARCHAR FOR BIT DATA DATA TYPE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
A sql16061 error may be incorrectly raised when casting XML
value of xs:hexBinary, xs:base64Binary encoding to VARCHAR FOR
BIT DATA data type.
The error indicates that the length specified for the varchar
for bit data type is not large enough for the encoded xml value.
For example, in the following query, if the element
<executable_id> is encoded in a 32-byte array using
xs:hexBinary(which means it is encoded using 64 characters),
extracting this element as type varchar(32) for bit data will
raise an error when it should not. This is because we are
checking the length of the character string value of 64 bytes
instead of checking the length of the encoded binary
(hexBinary value) which is 32 bytes.
SELECT EXECUTABLE_ID
FROM
XMLTABLE (
'$doc/details' PASSING XMLPARSE( DOCUMENT
DETAILS) as "doc" COLUMNS "EXECUTABLE_ID" VARCHAR(32) FOR
BIT DATA PATH 'executable_id cast as xs:hexBinary'
) AS DETAILS;
Sample Doc:
<details>
...
<executable_id>0100000000000000010000000000000000000000020020090
421100437514686</executable_id>
...
</details> |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* DB2 LUW All Platforms *
****************************************************************
* PROBLEM DESCRIPTION: *
* A sql16061 error may be incorrectly raised when casting XML *
* *
* value of xs:hexBinary, xs:base64Binary encoding to VARCHAR *
* FOR *
* BIT DATA data type. *
* *
* *
* *
* The error indicates that the length specified for the *
* varchar *
* for bit data type is not large enough for the encoded xml *
* value. *
* *
* *
* For example, in the following query, if the element *
* *
* <executable_id> is encoded in a 32-byte array using *
* *
* xs:hexBinary(which means it is encoded using 64 characters), *
* *
* extracting this element as type varchar(32) for bit data *
* will *
* raise an error when it should not. This is because we are *
* *
* checking the length of the character string value of 64 *
* bytes *
* instead of checking the length of the encoded binary *
* *
* (hexBinary value) which is 32 bytes. *
* *
* *
* *
* SELECT EXECUTABLE_ID *
* *
* FROM *
* *
* XMLTABLE ( *
* *
* '$doc/details' PASSING XMLPARSE( DOCUMENT *
* *
* DETAILS) as "doc" COLUMNS "EXECUTABLE_ID" VARCHAR(32) *
* FOR *
* BIT DATA PATH 'executable_id cast as xs:hexBinary' *
* *
* ) AS DETAILS; *
* *
* *
* *
* Sample Doc: *
* *
* *
* *
* <details> *
* *
* ... *
* *
* <executable_id>01000000000000000100000000000000000000000200200
* 421100437514686</executable_id> *
* *
* ... *
* *
* </details> *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 v97 Fixpack 1 *
**************************************************************** |
| Local Fix: |
For the VARCHAR FOR BIT length, use a length value which is
equal or larger to the character string size before its encoded
to hexBinary or base64Binary. In the example listed the work
around would be using VARCHAR(64) FOR BIT DATA |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
The problem occurs because we are checking the length of the
character string value of 64 bytes
instead of checking the length of the encoded binary (hexBinary
value) which is 32 bytes. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 02.09.2009
23.02.2010
23.02.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
