CODEWITHSUNDEEP
c#ado.netconnection-stringibm-midrangebinary-data
Sarov
Sarov

Reputation: 645

How to convert CCSID 65535 characters in AS400 ADO.Net connection string

I'm trying to set things up such that I can read binary data from the AS400 database without having to put "CAST(TABLE.COL AS CHAR(29) CCSID 37) COL" on every selected column. It's a pain to put that every time, not to mention coupling the column size to the C# code (I suppose I could just cast to VARCHAR(9999) for everything, but...).

I assumed I could do so via the connection string for ADO.Net. However, after trying every suggested setting from various pages I've read to try to get it to work, they all give a "The ConnectionString property is invalid." error.

I've tried each of the following, and they all give the same error:

var connectionString = "DataSource=" + /*blah blah...*/ + 
//"; CCSID=37; TRANSLATE=1" +
//"; BinAsChar=true; CCSID=37;" +
//"; BinAsChar=true; CCSID=37; TRANSLATE=1" +
//"; BinAsChar=true; CCSID=37; Force Translate=1" +
//"; BinAsChar=true; CCSID=37; ForceTranslation=1" +
//"; CCSID=37; ForceTranslation=1" +
//"; ForceTranslation=1" +
//"; CCSID=37" +
//"; Host CCSID=37" +
//"; CharsetFor65535=0" +
//"; CCSID=EBCDIC" +
"";

Is there any way to get this to work? Or do I have to just continue polluting all of my queries?

Upvotes: 2

Views: 3719

Answers (2)

Charles
Charles

Reputation: 23783

Looks like there's been enhancements since the Redbook Mike references was published.

In the 7.1 DB2 for i .NET Provider Technical Reference (installed as part of the programmer's toolkit component of iAccess for Windows or the newer ACS Windows Application Package) there's a couple of properties of interest...

iDB2Connection.CharBitDataAsString Property
Gets a boolean value which indicates whether character bit data tagged with CCSID 65535 should be translated as string data.

iDB2ProviderSettings.CharBitDataCcsid Property
Specifies the CCSID used to translate iDB2CharBitData and iDB2VarCharBitData types when the CharBitDataAsString property is set to True. This property is ignored when CharBitDataAsString is set to False.

Upvotes: 4

Mike
Mike

Reputation: 1324

The best resource for this kind of question is the IBM Redbook Integrating DB2 Universal Database for iSeries with Microsoft ADO.NET.

On page 151, there is a chart that compares the features of the IBMDA400 or IBMDASQL OLE DB providers to the IBM.Data.DB2.iSeries .NET provider. It says:

Force Translate Not implemented by the IBM.Data.DB2.iSeries provider. Read about handling character data tagged with CCSID 65535 in “iDB2CharBitData and iDB2VarCharBitData” on page 90.

It is also noteworthy that in section 4.5.2 where it lists attributes that you can put on a connection string, there is nothing similar to what you are trying to specify. This implies that you may be able to do this transformation on the connection string if you use an OLE DB provider, but not the .NET provider.

It does give some techniques that you should be able to use to clean up your query and allow you to stay with the IBM.Data.DB2.iSeries provider (which I would usually prefer). They show the CAST technique you are currently using on page 91 and then on page 93 they show an alternative technique that can be used on an iDB2DataReader, calling method GetiDB2CharBitData or GetiDB2VarCharBitData. There is also a .ToString overload that you might prefer: .ToString(CCSID). I think this second technique performs the translation on the client side (in the .NET provider) whereas your current technique performs the translation on the server side (in the iSeries SQL code).

Upvotes: 2

Related Questions