Inserting two spaces into a char(8), DB2 omits a space

[Mesan]

Here's the question:
How do you get DB2 to update a char(n) field with multiple spaces right
next to each other without having the additional spaces being omitted?

Here's the background:
We have a field "codes2" that's defined as char(8) that we use to keep
eight different codes (i.e. codes2[0] = something, codes2[1] =
something else, etc.). I'm sure that idea is nothing new to anyone but
our problem arises when we try to store two spaces (' ') right next to
each other.

This command:

EXEC SQL UPDATE TBLNAME SET CODES2 = '5H 7B3d' WHERE ...;

Completes without errors but when you look in the table to see what was
actually done, codes2 = '5H 7B3d', the DB2 just decided to omit one of
the spaces and slide the rest of the string over. You can imagine our
problem when we've got programs looking for the value in codes2[3] and
instead of finding a space (' ') it finds a 7...not good.

[Brian Tkatch]

I took a peek and did not find a problem. Could you try the following
code?

DECLARE GLOBAL TEMPORARY TABLE A (A CHAR(8))
INSERT INTO SESSION.A VALUES('5H 7B3d')
SELECT 1, '==>' || SUBSTR(A, 1, 1) || '<==' FROM SESSION.A UNION ALL \
SELECT 2, '==>' || SUBSTR(A, 2, 1) || '<==' FROM SESSION.A UNION ALL \
SELECT 3, '==>' || SUBSTR(A, 3, 1) || '<==' FROM SESSION.A UNION ALL \
SELECT 4, '==>' || SUBSTR(A, 4, 1) || '<==' FROM SESSION.A UNION ALL \
SELECT 5, '==>' || SUBSTR(A, 5, 1) || '<==' FROM SESSION.A UNION ALL \
SELECT 6, '==>' || SUBSTR(A, 6, 1) || '<==' FROM SESSION.A UNION ALL \
SELECT 7, '==>' || SUBSTR(A, 7, 1) || '<==' FROM SESSION.A UNION ALL \
SELECT 8, '==>' || SUBSTR(A, 8, 1) || '<==' FROM SESSION.A ORDER BY 1

UPDATE TBLNAME SET A = '5H 7B3d'

SELECT 1, '==>' || SUBSTR(A, 1, 1) || '<==' FROM SESSION.A UNION ALL \
SELECT 2, '==>' || SUBSTR(A, 2, 1) || '<==' FROM SESSION.A UNION ALL \
SELECT 3, '==>' || SUBSTR(A, 3, 1) || '<==' FROM SESSION.A UNION ALL \
SELECT 4, '==>' || SUBSTR(A, 4, 1) || '<==' FROM SESSION.A UNION ALL \
SELECT 5, '==>' || SUBSTR(A, 5, 1) || '<==' FROM SESSION.A UNION ALL \
SELECT 6, '==>' || SUBSTR(A, 6, 1) || '<==' FROM SESSION.A UNION ALL \
SELECT 7, '==>' || SUBSTR(A, 7, 1) || '<==' FROM SESSION.A UNION ALL \
SELECT 8, '==>' || SUBSTR(A, 8, 1) || '<==' FROM SESSION.A ORDER BY 1

DROP TABLE SESSION.A

B.

[Brian Tkatch]

Hmm... Just playing with WITH.

DECLARE GLOBAL TEMPORARY TABLE A (A CHAR(8))
INSERT INTO SESSION.A VALUES('5H 7B3d')

WITH B (B) AS (VALUES 1, 2, 3, 4, 5, 6, 7, 8) SELECT B, (SELECT '==>'
|| SUBSTR(A, B, 1) || '<==' FROM SESSION.A) FROM B

UPDATE SESSION.A SET A = '5H 7B3d'

WITH B (B) AS (VALUES 1, 2, 3, 4, 5, 6, 7, 8) SELECT B, (SELECT '==>'
|| SUBSTR(A, B, 1) || '<==' FROM SESSION.A) FROM B

DROP TABLE SESSION.A

[Serge Rielau]

Mesan wrote:

Here's the question:
How do you get DB2 to update a char(n) field with multiple spaces right
next to each other without having the additional spaces being omitted?

Here's the background:
We have a field "codes2" that's defined as char(8) that we use to keep
eight different codes (i.e. codes2[0] = something, codes2[1] =
something else, etc.). I'm sure that idea is nothing new to anyone but
our problem arises when we try to store two spaces (' ') right next to
each other.

This command:

EXEC SQL UPDATE TBLNAME SET CODES2 = '5H 7B3d' WHERE ...;

Completes without errors but when you look in the table to see what was
actually done, codes2 = '5H 7B3d', the DB2 just decided to omit one of
the spaces and slide the rest of the string over. You can imagine our
problem when we've got programs looking for the value in codes2[3] and
instead of finding a space (' ') it finds a 7...not good.

What's the language the SQL is embedded into?
This does have a taste of a client bug.
Also which codepage is used. There are umpteen versions of space in
Unicode...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mesan]

The SQL is embedded into a C program running on OS/2 compiled with IBM
Visual Age C++ 3.0. If I remember right, the code page our OS/2
clients use to talk to our OS/2 server is 437, but I could be wrong
(although I'm almost positive). I could reproduce it through the
command line with manual insert commands. Very weird eh? I got the
problem fixed in the program by using a cursor (instead of a normal
"EXEC SQL ..." command) but still, it's a very odd problem.

Thanks for your reply,

Mesan