inserting \0 into a CHAR/VARCHAR field

[Vivek]

Hi,

Is it possible to insert a '\0' value or for that matter any control
character into a DB2 CHAR/VARCHAR field ? So if i do a 'select
hex(column) from table' i should see a 0 in the output. How can i do it
from the command prompt ?

Thanks,
Vivek

[Tonkuma]

Vivek wrote:

Hi,

Is it possible to insert a '\0' value or for that matter any control
character into a DB2 CHAR/VARCHAR field ? So if i do a 'select
hex(column) from table' i should see a 0 in the output. How can i do it
from the command prompt ?

Thanks,
Vivek

For example:
'ABC' || CHR(0) || CHR(10) || CHR(13) || 'XYZ'

[Knut Stolze]

Tonkuma wrote:

>
Vivek wrote:

>Hi,

Is it possible to insert a '\0' value or for that matter any control
character into a DB2 CHAR/VARCHAR field ? So if i do a 'select
hex(column) from table' i should see a 0 in the output. How can i do it
from the command prompt ?

Thanks,
Vivek

For example:
'ABC' || CHR(0) || CHR(10) || CHR(13) || 'XYZ'

Unfortunately, this won't work because CHR(0) = 0x20 = ' ' = space.
http://publib.boulder.ibm.com/infoce...n/r0000778.htm

But an 0x00 = '\0' can be inserted by an application.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Vivek]

Knut,

What you said is right, but how is chr(0) mapped to 0x20 ?
i thought chr() took ascii values and 0 is the ascii for '\0'
character.

Knut Stolze wrote:

Tonkuma wrote:

Vivek wrote:

Hi,

Is it possible to insert a '\0' value or for that matter any control
character into a DB2 CHAR/VARCHAR field ? So if i do a 'select
hex(column) from table' i should see a 0 in the output. How can i do it
from the command prompt ?

Thanks,
Vivek

For example:
'ABC' || CHR(0) || CHR(10) || CHR(13) || 'XYZ'

Unfortunately, this won't work because CHR(0) = 0x20 = ' ' = space.
http://publib.boulder.ibm.com/infoce...n/r0000778.htm

But an 0x00 = '\0' can be inserted by an application.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Knut Stolze]

Vivek wrote:

Knut,

What you said is right, but how is chr(0) mapped to 0x20 ?
i thought chr() took ascii values and 0 is the ascii for '\0'
character.

The explanation is here, i.e. in the description of the CHR function that I
already cited:
http://publib.boulder.ibm.com/infoce...n/r0000778.htm

As for the "how", I assume that a simple test will cover the 0 input inside
the DB2 engine:

if (input == 0) {
output = ' ';
}
else {
output = input;
}

(I don't think that this was really your question?)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Dmitri]

x'00' - should do the trick.
db2 values ascii(x'00')
1
-----------
0

1 record(s) selected.

regards,
dmitri

"Vivek" <te*********@gmail.comwrote in message
news:11**********************@16g2000cwy.googlegro ups.com...

Hi,

Is it possible to insert a '\0' value or for that matter any control
character into a DB2 CHAR/VARCHAR field ? So if i do a 'select
hex(column) from table' i should see a 0 in the output. How can i do it
from the command prompt ?

Thanks,
Vivek

[SVK]

"""Knut Stolze ÐÉÓÁÌ(Á):
"""

>
Unfortunately, this won't work because CHR(0) = 0x20 = ' ' = space.
http://publib.boulder.ibm.com/infoce...n/r0000778.htm

1) It clearly says that CHR(0) = 0x00 in ASCII.
0x20 - is a senseless bullshit, nothing like this can be found anywhere

2) constant '00'X can be used for the same purpose

[Knut Stolze]

SVK wrote:

>
"""Knut Stolze пи�ал(а):
"""

>>
Unfortunately, this won't work because CHR(0) = 0x20 = ' ' = space.

http://publib.boulder.ibm.com/infoce...n/r0000778.htm

>>

1) It clearly says that CHR(0) = 0x00 in ASCII.
0x20 - is a senseless bullshit, nothing like this can be found anywhere

Sorry, I put the wrong link in there. It should have been:

http://publib.boulder.ibm.com/infoce...c/r0000778.htm

V8 has the exact same behavior as V9. Unfortunately, the V8 docs are not
detailed enough in this respect and do not mention the special treatment of
0. If you try the following SQL statement on a V8 system, you will surely
agree with me:

VALUES HEX(CHR(0))

2) constant '00'X can be used for the same purpose

This is correct:

VALUES HEX('abc' || x'00' || 'def')

1
--------------
61626300646566

The only thing to remember in all this is that 0x00 = '\0' is often the
end-of-line character. The DB2 command line interprets it as such and,
thus, you may not get the full output:

VALUES VARCHAR('abc' || x'00' || 'def')

1
-------
abc

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Vivek]

My question actually should have been "why" is it mapped that way.
chr(0) should be 0x00

Knut Stolze wrote:

Vivek wrote:

Knut,

What you said is right, but how is chr(0) mapped to 0x20 ?
i thought chr() took ascii values and 0 is the ascii for '\0'
character.

The explanation is here, i.e. in the description of the CHR function that I
already cited:
http://publib.boulder.ibm.com/infoce...n/r0000778.htm

As for the "how", I assume that a simple test will cover the 0 input inside
the DB2 engine:

if (input == 0) {
output = ' ';
}
else {
output = input;
}

(I don't think that this was really your question?)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Vivek]

The behaviour is definitely as you said.
I was able to insert the '\0' character as follows:

insert into table values(x'00')

and

select * from table : gave

----
00

Knut Stolze wrote:

SVK wrote:

"""Knut Stolze пи�ал(а):
"""

>
Unfortunately, this won't work because CHR(0) = 0x20 = ' ' = space.

http://publib.boulder.ibm.com/infoce...n/r0000778.htm

>

1) It clearly says that CHR(0) = 0x00 in ASCII.
0x20 - is a senseless bullshit, nothing like this can be found anywhere

Sorry, I put the wrong link in there. It should have been:

http://publib.boulder.ibm.com/infoce...c/r0000778.htm

V8 has the exact same behavior as V9. Unfortunately, the V8 docs are not
detailed enough in this respect and do not mention the special treatment of
0. If you try the following SQL statement on a V8 system, you will surely
agree with me:

VALUES HEX(CHR(0))

2) constant '00'X can be used for the same purpose

This is correct:

VALUES HEX('abc' || x'00' || 'def')

1
--------------
61626300646566

The only thing to remember in all this is that 0x00 = '\0' is often the
end-of-line character. The DB2 command line interprets it as such and,
thus, you may not get the full output:

VALUES VARCHAR('abc' || x'00' || 'def')

1
-------
abc

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Knut Stolze]

Vivek wrote:

The behaviour is definitely as you said.
I was able to insert the '\0' character as follows:

insert into table values(x'00')

and

select * from table : gave

----
00

The result does not match with what you have done. Either your query was
actually SELECT HEX(...) FROM table or your insert was different. x'00' is
a 0x00 byte. Your results show a string with two 0-s in it.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Knut Stolze]

Vivek wrote:

My question actually should have been "why" is it mapped that way.
chr(0) should be 0x00

I don't know. Probably some sort of historical reasons.

Storing non-printable characters in a VARCHAR is usually not a good idea in
the first place. If it is binary, you should use VARCHAR FOR BIT DATA and
treat it as binary data.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Jan Nelken]

Vivek wrote:

Hi,

Is it possible to insert a '\0' value or for that matter any control
character into a DB2 CHAR/VARCHAR field ? So if i do a 'select
hex(column) from table' i should see a 0 in the output. How can i do it
from the command prompt ?

Thanks,
Vivek

A bit of explanation:

Online documentation available at:
http://publib.boulder.ibm.com/infoce...w/v9/index.jsp

contains this (search for CHR in Version 8 incompatibilities):
http://publib.boulder.ibm.com/infoce...63%68%72%22%20

Output from CHR function

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 from the definition of the SYSFUN CHR function, and place this function
before SYSFUN on the SQL path.
For example, to find the source definition for SYSFUN.CHR located in column
IMPLEMENTATION:

SELECT IMPLEMENTATION, ROUTINENAME FROM SYSIBM.SYSROUTINES WHERE ROUTINENAME
LIKE '%CHR%';

IMPLEMENTATION ROUTINENAME
-------------- -----------
db2clifn!CLI_udfCHAR CHR
Then, you could create a new user-defined function from the definition
db2clifn!CLI_udfCHAR returned above.

CREATE FUNCTION DBS.CHR(INTEGER) RETURNS CHARACTER(1) FOR BIT DATA NOT FENCED
LANGUAGE C PARAMETER STYLE DB2SQL NO DBINFO EXTERNAL NAME db2clifn!CLI_udfCHAR'
You can also use Dmitri's suggestion of using x'00' instead of CHR(0)
Jan M. Nelken

[Jan Nelken]

Vivek wrote:

Hi,

Is it possible to insert a '\0' value or for that matter any control
character into a DB2 CHAR/VARCHAR field ? So if i do a 'select
hex(column) from table' i should see a 0 in the output. How can i do it
from the command prompt ?

Thanks,
Vivek

A bit of explanation:

Online documentation available at:
http://publib.boulder.ibm.com/infoce...w/v9/index.jsp

contains this (search for CHR in Version 8 incompatibilities):
http://publib.boulder.ibm.com/infoce...63%68%72%22%20

Output from CHR function

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 from the definition of the SYSFUN CHR function, and place this function
before SYSFUN on the SQL path.
For example, to find the source definition for SYSFUN.CHR located in column
IMPLEMENTATION:

SELECT IMPLEMENTATION, ROUTINENAME FROM SYSIBM.SYSROUTINES WHERE ROUTINENAME
LIKE '%CHR%';

IMPLEMENTATION ROUTINENAME
-------------- -----------
db2clifn!CLI_udfCHAR CHR
Then, you could create a new user-defined function from the definition
db2clifn!CLI_udfCHAR returned above.

CREATE FUNCTION DBS.CHR(INTEGER) RETURNS CHARACTER(1) FOR BIT DATA NOT FENCED
LANGUAGE C PARAMETER STYLE DB2SQL NO DBINFO EXTERNAL NAME db2clifn!CLI_udfCHAR'
You can also use Dmitri's suggestion of using x'00' instead of CHR(0)
Jan M. Nelken