How do set the IBM.Data.DB2.iSeries provider to Translate CCSID 655535?

maybe try this

 dim x as string = Encoding.ASCII.GetString( cusdb.tables(0).Rows(0)("CUSNM"))

or

Dim x As String = System.Text.Encoding.Default.GetString(cusdb.tables(0).Rows(0)("CUSNM"))

Doesn't work out too well...


>Debug.print Encoding.ASCII.GetString(cusdb.tables(0).Rows(0)(3))
"AAI@CVYWVYAcIVU@@@@@@@@@@@@@@@@@@@@"

same for both....


>Debug.print System.Text.Encoding.Default.GetString(cusdb.tables(0).Rows(0)(3))
"ÁÁÉ@ÃÖÙ×ÖÙÁãÉÖÕ@@@@@@@@@@@@@@@@@@@@"
>

do you know what is returned in the array, it looks like unicode but what kind ?
Is it a byte array of UTF8 or UTF7

maybe try this

Debug.print Encoding.UTF8.GetString(cusdb.tables(0).Rows(0)(3))

I have no idea.


This is what I tried...


>Debug.print Encoding.UTF8.GetString(cusdb.tables(0).Rows(0)(3))
"@@@@@@@@@@@@@@@@@@@@@"
>Debug.print Encoding.UTF7.GetString(cusdb.tables(0).Rows(0)(3))
"ÁÁÉ@ÃÖÙ×ÖÙÁãÉÖÕ@@@@@@@@@@@@@@@@@@@@"
>

I found something....I imported the IBM AS/400 iSeries Access for Windows ActiveX Object Library.  CWBX.dll and then added the following import to the top of the class.

Imports cwbx

Then, down in a method....

           'Get all customers from Mapics
            mapDB2 = New IBMProvider(mdMain.getDB2ConnStr, mdMain.UserAS400Environment)
            Dim cusDB As New DataSet
            cusDB = mapDB2.GetAllCustomers()
            Me.cmbMapicsCustomers.DataSource = cusDB.Tables("CUSTOMER MASTER")
            Me.cmbMapicsCustomers.DisplayMember = "CUSNM"

            Dim x As New cwbx.StringConverter
            Debug.Write(x.FromBytes(cusDB.Tables(0).Rows(0)(3)))

The contents of the Output Window...
AAI CORPORATION    

So this works.  I can now convert data from an AS/400 to valid information.  However, the solution is horrible.  I'll have to convert every record and I'll have to use interoperability.  That's not good.

There has to be a way for the IBM.Data.DB2.iSeries data provider to convert this data automatically.  At least, I would think they wouldn't just leave this funcationality out.  It's a common conversion from AS/400 to PC.

Any ideas?

I'm sorry, but I have no idea. :-(

thanks for trying

try this

reference to cwbx - IBM AS400 Client Access Express ActiveX Object Library

' Create a StringConverter object to perform Unicode to EBCDIC conversion
 Dim strCvtr As New cwbx.StringConverter
 DIm s As String

'set As400 name
            as400.Define("Sxxxx")
            dq.system = as400

            dq.LibraryName = DQLIB
            dq.QueueName = DQNAME

s = strCvtr.FromBytes(dq.Read)

That handles just one field at a time.  I need to be able to set the adapter so that it converts all string fields to UTF-16 automatically.  It's possible with the old Client Access ODBC, so it should be with the new adapter.  At least, one would think it would be possible.

or actually to convert from UTF-16

How are u connecting to the as400, via client access?

If so what version do u have?

I have v5r3

Imports System.Data.Oledb

    'form level declarations
    Dim fsConn As String = "Provider=IBMDA400;Data Source=S102CC4D;"
    Dim objDs As DataSet
    Dim ObjDa As OleDbDataAdapter
    Dim objRow As DataRow
    Dim objTable As DataTable
    Dim objColumn As DataColumn
    Dim fsSQL As String = "SELECT * FROM IBLIB.TEST order by ID"

    Private Sub ADONETAS400_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dg.ReadOnly = True 'lock DataGrid
        objDs = New DataSet()
        'Connect to database and specify sSQL
        ObjDa = New OleDbDataAdapter(fsSQL, fsConn)
        Try
            'using DataAdapter enter records from table to DataSet
            ObjDa.Fill(objDs, "IBLIB.TEST")
            'Bind dataGrid to DataSet
            dg.DataSource = objDs.Tables("IBLIB.TEST")

            'Clean memory (Like Setting to Nothing)
            objDs.Dispose()
            ObjDa.Dispose()
        Catch objException As Exception
            'if error happend then write the error to TextBox2
            TextBox2.Text = objException.ToString
        End Try

    End Sub

I'm on v5r3 and I'm using the IBM.Data.DB2.iSeries data adapter.  

I know how to get data, the problem is that I have to convert strings from UTF16 to human readable text string by string.  I need a way to have the adapter do this for all strings.  It should support this, maybe not....since nobody has the answer.

Ok, I am not at my 1 pc today, I will be there tomorrow, but what i did with 5.1 ibm client access odbc.


Windows XP
control panel; Administrative Tools; Data Sources (ODBC)

then go to the source u arer using to connect to the as400 database

hit configure button and one of the tabs should have a checkbox that u can check that says something like
Translation to CCSID 655535. If it is not check then check it and save settings. It worked for me, I write all kinds of programs for the as400 using vb.net, c#. IF u can't find it let me know and tomorrow I will type up exactly how to get to the settings. Now I have 5.1, but I am sure the 5.3 would have that option too.


Let me know
Later

I don't think this possible. I can't use ODBC, need to have the entire connection through a connection string.  So the only way to really do this is with an ODBC.  

onyak,
just curious did u ever find another solution. I am running into the same problem using the .net as400 framework. If I use the odbc I do not because it has a property to have it translate to ccsid. The only problem is it isn't as fast as the .net framework that IBM created.

The only thing I can find is how to translate it using the reader, not the data adapter.

No I didn't.  I just translated it by sending strings returned from the AS/400 to a method in the client access API that converts it for me.  It's horrible design, but it gets the job done.

With the reader, is it automatically translated? Can you post the code?

look here.....
 Use a DataReader to retrieve an iDB2CharBitData or iDB2VarCharBitData object, then use the ToString() or ToString(ccsid) method to read the data. ToString() translates the binary data using the host server job's CCSID, and ToString(ccsid) enables you to specify which CCSID to use for translation. Example 4-75 shows how to use the DataReader with both of these methods to read the non-binary data.

Example 4-75 Using a DataReader with GetiDB2CharBitData to read data marked with CCSID 65535

 

// Create and open a connection to the iSeries.iDB2Connection cn = new iDB2Connection("DataSource=myiseries;DefaultCollection=sampledb;");cn.Open();
// Create a command to query the BITTABLEiDB2Command cmd = new iDB2Command("select * from bittable", cn);
// Run the command and read from the DataReader.iDB2DataReader dr = cmd.ExecuteReader();if (dr.Read()){      // Read the data and retrieve the values into      // objects of the appropriate data type.      iDB2CharBitData bit1 = dr.GetiDB2CharBitData(0);      iDB2CharBitData bit2 = dr.GetiDB2CharBitData(1);      iDB2VarCharBitData varbit1 = dr.GetiDB2VarCharBitData(2);      iDB2VarCharBitData varbit2 = dr.GetiDB2VarCharBitData(3);
      // Now use ToString() to display the values.      // Data is translated using the server job's CCSID.      Console.WriteLine("ToString() method:");      Console.WriteLine("  {0} {1} {2} {3}",            bit1.ToString(), bit2.ToString(), varbit1.ToString(), varbit2.ToString());
      // You can also explicitly specify the CCSID       // using the example below.      Console.WriteLine("ToString(ccsid) method:");      Console.WriteLine("  {0} {1} {2} {3}",            bit1.ToString(37), bit2.ToString(37), varbit1.ToString(37), varbit2.ToString(37));}
// Close the DataReader since we no longer need it.dr.Close();                                    // Dispose the command since we no longer need it.cmd.Dispose();
// Close the connection.cn.Close();


 Note: If your data is encoded using a CCSID other than 37, use that CCSID in this example instead of 37.
 

the whole document is ===

http://www.redbooks.ibm.com/redbooks/SG246440/wwhelp/wwhimpl/java/html/wwhelp.htm