SEO

#1 (**permalink**) 02-26-2008, 04:40 PM

hallo!

given the following table

create table s2 (a_key integer, a_varchar varchar(200))

for testing i have some scripts which makes inserts of chr(0) values like

insert into s2 (a_key, a_varchar) values (1, 'bla'||chr(0)||'bla2')

when i do this on a v7.2 database and i select it with a cli
application, then i get the 0 byte on the correct position. by the way a
select from the command line (db2cmd: db2 select * from s2) returns only
'bla' as value of the a_varchar column.

when i do this on v8 database, the cli application gets a 0x20 byte
value on the position of the 0 byte that means it gets a blank on the
position of the 0 byte. in this case the command line also returns the
wrong string with the blank instead of the 0 byte.

how can i get the old behavior?

thanks
andreas lederer

#2 (**permalink**) 02-26-2008, 04:40 PM

alederer wrote:
> hallo!
>
> given the following table
>
> create table s2 (a_key integer, a_varchar varchar(200))
>
> for testing i have some scripts which makes inserts of chr(0) values like
>
> insert into s2 (a_key, a_varchar) values (1, 'bla'||chr(0)||'bla2')
>
> when i do this on a v7.2 database and i select it with a cli
> application, then i get the 0 byte on the correct position. by the way a
> select from the command line (db2cmd: db2 select * from s2) returns only
> 'bla' as value of the a_varchar column.
>
> when i do this on v8 database, the cli application gets a 0x20 byte
> value on the position of the 0 byte that means it gets a blank on the
> position of the 0 byte. in this case the command line also returns the
> wrong string with the blank instead of the 0 byte.
>
> how can i get the old behavior?
>
> thanks
> andreas lederer
>

From the Db2 documentation (Version 8 incompatibilities with previous
releases):

|Change

|CHR(0) returns a blank (X'20') instead of the character with code
|point X'00'.

|Symptom

|Output from the CHR function with X'00' as the argument returns
|different results.

|Explanation

|String handling when invoking and returning from user-defined functions
|interprets X'00' as end of string.
|Resolution

|Change the application code to handle the new output value.
|Alternatively, define a user-defined function that returns CHAR(1) FOR
|BIT |DATA which is sourced on the SYSFUN CHR function, and place this
|function |before SYSFUN on the SQL path.

Jan M. Nelken

#3 (**permalink**) 02-26-2008, 04:41 PM

Documented change

http://publib.boulder.ibm.com/infoce...help/index.jsp

Version 8 incompatibilities with previous releases
Output from CHR function

Output from CHR function
| Windows UNIX

|Change
|CHR(0) returns a blank (X'20') instead of the character with code |point
X'00'.

|Symptom
|Output from the CHR function with X'00' as the argument returns |different
results.

|Explanation
|String handling when invoking and returning from user-defined functions
|interprets X'00' as end of string.

|Resolution
|Change the application code to handle the new output value. |Alternatively,
define a user-defined function that returns CHAR(1) FOR BIT |DATA which is
sourced on the SYSFUN CHR function, and place this function |before SYSFUN
on the SQL path.

PM

"alederer" <alederer@gmx.at> a écrit dans le message de
news:400d019b$1@e-post.inode.at...
> hallo!
>
> given the following table
>
> create table s2 (a_key integer, a_varchar varchar(200))
>
> for testing i have some scripts which makes inserts of chr(0) values like
>
> insert into s2 (a_key, a_varchar) values (1, 'bla'||chr(0)||'bla2')
>
> when i do this on a v7.2 database and i select it with a cli
> application, then i get the 0 byte on the correct position. by the way a
> select from the command line (db2cmd: db2 select * from s2) returns only
> 'bla' as value of the a_varchar column.
>
> when i do this on v8 database, the cli application gets a 0x20 byte
> value on the position of the 0 byte that means it gets a blank on the
> position of the 0 byte. in this case the command line also returns the
> wrong string with the blank instead of the 0 byte.
>
> how can i get the old behavior?
>
> thanks
> andreas lederer
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

different chr(0) handling in v7.2 and v8?