DB2 and extended ASCII table
in [DB2]
|
Prev: Result set from ADMIN_CMD
Next: SQL error 805
From: Gregor Kova? on 2 Nov 2006 09:52 Knut Stolze wrote: > Gregor Kova? wrote: > >> Knut Stolze wrote: >> >>> Now we have two questions here: >>> (1) You say that NULL is shown instead of the VARCHAR value. What's >>> your >>> query? I'd guess that there is some sort of problem with the >>> replace >>> and how you use it. Otherwise, there shouldn't be a NULL. >>> (2) Once you get a non-NULL, compare the code points of '?' and the >>> respective character in your VARCHAR value. It should be the same >>> if you create the correct character on DB2 side. >>> >> As an example you can use this query: >> VALUES(CASE WHEN '?' = '?' THEN CHR(129) ELSE '?' END) should return >> CHR(129) (u with umlaut), but it dows not return anything: >> $ db2 "values(case when '?' = '?' then chr(129) else '?' end)" >> >> 1 >> -- >> >> >> 1 record(s) selected. > > Not very surprising. You get the same result if you run "VALUES CHR(129)" > directly. CHR(129) is not a printable character. So your query returns a > single row with one value. The length of that value is 1. Nevertheless, > your shell won't print this character - neither does mine. > >> On the other hand, query >> VALUES(CASE WHEN '?' = '?' THEN CHR(129) ELSE '?' END) does the >> following: $ db2 "values(case when '?' = '?' then chr(129) else '?' end)" >> >> 1 >> -- >> ? >> >> 1 record(s) selected. > > This is not surprising either. The comparison evaluates to FALSE, so that > your query is equivalent to "VALUES '?'". The length of that is 2. This > tells you that '?' is a multi-byte character comprised of the two bytes > 0xC4 and 0x8D (in UTF-8). > > My suggestion would be that you do not rely on anything that does not > belong to the ASCII character set, i.e. anything above the code point 127, > or that you stick with multi-bytes if you have characters that are not > part of > ASCII. Otherwise, you will probably run into a lot of troubles on > different platforms and environments. > Hi... Thanks for the in.depth explaination. But... :) The thing is that I do need those chars above 127. We have a scale that contains PLU codes for products (you know when yo go to shop and you buy apples, you put them on a scale, press 45 for example and out comes a sticker with the bar code, name of the product, .... This bar code is then scanned by the cashier when you pay.) For the scale, if we want it to properly display our characters (like ? in the above example) we have to translate our characters into couple of characters that are above ASCII 127. We could translate our characters into the ones without the caron, but that is not pretty. :)) Best regards, Kovi -- -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- | In A World Without Fences Who Needs Gates? | | Experience Linux. | -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
From: Knut Stolze on 2 Nov 2006 10:28 Gregor Kova? wrote: > The thing is that I do need those chars above 127. > We have a scale that contains PLU codes for products (you know when yo go > to shop and you buy apples, you put them on a scale, press 45 for example > and out comes a sticker with the bar code, name of the product, .... This > bar code is then scanned by the cashier when you pay.) For the scale, if > we want it to properly display our characters (like ? in the above > example) we have to translate our characters into couple of characters > that are above ASCII 127. We could translate our characters into the ones > without the caron, but that is not pretty. :)) If I got this right, then the situation is (a) your shell cannot display the CHR(129) correctly, (b) but the scale (or whichever tool may use this in the end) can handle it? Then I believe you will need to set up some environment equivalent to the scale, i.e. one that can properly show the '?' and similar characters. In particular, it has to support the exact same character set as the scale. Then you should also see the '?' in the output because it would not be interpreted as non-printable character. That's pretty much the same as if you would query DB2 on LUW and have EBCDIC at the client - w/o proper conversion in between. The client gets some data that it can't correctly handle because the code points in ASCII and EBCDIC are not exactly identical. -- Knut Stolze DB2 Information Integration Development IBM Germany
From: Gregor Kova? on 2 Nov 2006 12:07
Knut Stolze wrote: > Gregor Kova? wrote: > >> The thing is that I do need those chars above 127. >> We have a scale that contains PLU codes for products (you know when yo go >> to shop and you buy apples, you put them on a scale, press 45 for example >> and out comes a sticker with the bar code, name of the product, .... This >> bar code is then scanned by the cashier when you pay.) For the scale, if >> we want it to properly display our characters (like ? in the above >> example) we have to translate our characters into couple of characters >> that are above ASCII 127. We could translate our characters into the ones >> without the caron, but that is not pretty. :)) > > If I got this right, then the situation is > (a) your shell cannot display the CHR(129) correctly, > (b) but the scale (or whichever tool may use this in the end) can handle > it? > > Then I believe you will need to set up some environment equivalent to the > scale, i.e. one that can properly show the '?' and similar characters. In > particular, it has to support the exact same character set as the scale. > Then you should also see the '?' in the output because it would not be > interpreted as non-printable character. > > That's pretty much the same as if you would query DB2 on LUW and have > EBCDIC > at the client - w/o proper conversion in between. The client gets some > data that it can't correctly handle because the code points in ASCII and > EBCDIC are not exactly identical. > You are right. I was thinking of replacing ? with something like ${c} and then feeding the output through a Java program and replace ${c} with CHR(219). Best regards, Kovi -- -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- | In A World Without Fences Who Needs Gates? | | Experience Linux. | -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- |