Delphi TBytesField - How to see the text properly - Source is HIT OLEDB AS400

Question

We are connecting to a multi-member AS400 iSeries table via HIT OLEDB and HIT ODBC.

You connect to this table via an alias to access a specific multi-member. We create the alias on the AS400 this way:

CREATE ALIAS aliasname FOR table(membername)

We can then query each member of the table this way:

SELECT * FROM aliasname

We are testing this in Delphi6 first, but will move it to D2010 later

We are using HIT OLEDB for the AS400.

We are pulling down records from a table and the field is being seen as a tBytesField. I have also tried ODBC driver and it sees as tBytesField as well.

Directly on the AS400 I can query the data and see readable text. I can use the iSeries Navigation tool and see readable text as well.

However when I bring it down to the Delphi client via the HIT OLEDB or HIT ODBC and try to view via asString then I just see unreadable text.. something like this:

ñðð@ðõñððððñ÷@õôððõñòøóóöøñðÂÁÕÒ@ÖÆ@ÁÔÅÙÉÃÁ@@@@@@@@ÂÈÙÉâãæÁðòñè@ÔK@k@ÉÕÃK@@@@@@@@@ç

I jumbled up the text above, but that is the character types that show up.

When I did a test in D2010 the text looks like japanse or chinese characters, but if I display as AnsiString then it looks like what it does in Delphi 6.

I am thinking this may have something to do with code pages or character sets, but I have no experience in this are so it is new to me if it is related. When I look at the Coded Character Set on the AS400 it is set to 65535.

What do I need to do to make this text readable?

We do have a third party component (Delphi400) that makes things behave in a more native AS400 manner. When I use its AS400 connection and AS400 query components it shows the field as a tStringField and displays just fine. BUT we are phasing out this product (for a number of reasons) and would really like the OLEDB with the ADO components work.

Just for clarification the HIT OLEDB with tADOQuery do have some fields showing as tStringFields for many of the other tables we use... not sure why it is showing as a tBytesField in this case. I am not an AS400 expert, but looking at the field definititions on the AS400 the ones showing up as tBytesField look the same as the ones showing up as tStringFields... but there must be a difference. Maybe due to being a multi-member?

So... does anyone have any guidance on how to get the correct string data that is readable?

If you need more info please ask.

Greg

Answer 1

One problem is that your client doesn't know that it ought to convert the data from EBCDIC to ASCII because the CCSID on the server's table was set incorrectly.

A CCSID of 65535 is supposed to mean that the field contains binary data. Your client doesn't know that the column contains an EBCDIC encoded string, and therefore doesn't try to convert it.

On my servers, all of our character fields have a CCSID of 37, which is EBCDIC.

Answer 2

I found the answer... on both HIT ODBC 400 and HIT OLEDB 400 there is a property called: "Convert CCSID 65535=True" or in the OLEDB UDL it looks like "Binary Characters=True".

Don't know how I missed those, but that did the trick!

Thanks for the feedback.