Transylvania 65535

Hey, Howard:

I am using OLEDB classes and the OLEDB provider to retrieve data from my AS/400 in ASP.Net. On the AS/400, the fields are Char(4) and Char(35). When I show the returned data in a repeater, the repeater shows System.Byte[] instead of the data.

How can I convert data from System.Byte[] to String?

--Shafiq

Thanks for your question, Dear Shafiq. It allows me to illuminate for our readers a capability of the VARCHAR scalar function that they may not be aware of.

The CCSID of the columns you are attempting to retrieve is 65535. When retrieved via OLEDB, these are treated as byte data, and they are not translated by the provider. To translate the columns, you can use the VARCHAR scalar function in your SQL query. Your original query looked like this:

SELECT STORE, STORENAME FROM lib.file

The VARCHAR function takes three arguments: Expression, Max Length, and CCSID. The Max Length and CCSID arguments are optional. To translate your fields, you should execute a statement like the following:

SELECT VARCHAR(STORE, 4, 37) as STORE, 
       VARCHAR(STORENAME, 50,37) as STORENAME
       FROM lib.file

This instructs the iSeries to convert the data from 65535 to CCSID 37. Also, note that I created aliases for the columns using the AS clauses. I created aliases so that I could continue to refer to the columns by their names, instead of offset in the SQL statement. If you use a scalar function and do not alias the column, you will get a generated column name, which may not be very useful in your program.

Another way to accomplish this is to set the Force Translate property of the connection. This property can be set using the following connection string:

Dim Con1 as new ADODB.Connection
Con1.Provider = "IBMDA400"
Con1.Properties("Force Translate")=0

Or you can do it with the connection string:

Dim Con1 as new ADODB.Connection
Con1.Open "Provider=IBMDA400;Data Source=MyAs400;Force 
   Translate=0",UserID,Pwd)

Note that setting the force translate option to zero (0) causes all CCSID 65535 columns to be translated, so sometimes you may want to leave this option off and use the VARCHAR scalar to translate the data only when you need to translate. I have not used the Force Translate option with the OLEDB provider, but it should be similar to using the Convert Binary Data to Text translation option available in ODBC. I use the VARCHAR scalar to convert the CCSID in many of my queries.

Howard F. Arner Jr. is a consultant with Client Server Development in Jacksonville, Florida. Howard works primarily on SQL and n-tier performance problems and tuning for the iSeries. Howard's book iSeries and AS/400 SQL at Work is available for purchase from his Web site, at www.SQLThing.com, where you can also find out more information about programming and iSeries SQL performance.

Sponsored By COMMON

COMMON Fall 2003 Conference and Expo IT in the Palm of Your Hand September 7 - 11, in Orlando, Florida. · Education: Hundreds of sessions over five days · Networking: Meet more than 3,000 IT professionals, IBM executives, and developers · Influence IBM: Attend sound-off sessions · First-Hand Look at Latest Products: Talk to the industry's top solution providers COMMON is the best value in IT education, so don't miss out! Click and visit www.common.org for details!