Returning ResultSet from AS400 Stored Procedure

Question

I'm Struggling returning a result set from an AS400 database using a C# console app... I can connect to the database and i have verified the connection through the AS400 logs however I seem to run into the following error:

System.InvalidCastException was unhandled Message=The data type returned is currently not supported by the provider. Source=IBM.Data.DB2.iSeries StackTrace: at IBM.Data.DB2.iSeries.iDB2DbTypeUtility.MapSQLTypeToDCPCType(DcSqlTypes sqlType, Int32 colccsid, UInt32 length) at IBM.Data.DB2.iSeries.iDB2Command.setRowOfParameterData(MpDcData[]& dcDataRow) at IBM.Data.DB2.iSeries.iDB2Command.execute() at IBM.Data.DB2.iSeries.iDB2Command.ExecuteNonQuery() at GeacFutureDelivery.Program.Main(String[] args) in C:\Users\harlim\documents\visual studio 2010\Projects\GeacFutureDelivery\GeacFutureDelivery\Program.cs:line 40 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException:

My source is:

var connectionString = "<Connection String>";
        int startDate = 1120530;
        int endDate = 1120602;

        try
        {
            using (var connection = new iDB2Connection(connectionString))
            {
                connection.Open();

                iDB2Transaction trans = connection.BeginTransaction();

                iDB2Command command = connection.CreateCommand();


                string query = "DRLOBJ01.GETORDPCD";
                command.Transaction = trans;
                command.CommandType = CommandType.StoredProcedure; 
                command.CommandText = query;
                command.CommandTimeout = 0;

                command.Parameters.Add("DTSTR", iDB2DbType.iDB2Integer).Value = startDate;
                command.Parameters.Add("DTEND", iDB2DbType.iDB2Integer).Value = endDate;

                command.Prepare();


                using (iDB2DataReader reader = command.ExecuteReader())
                 {
                    int iCUSO51 = reader.GetOrdinal("CUSO51");
                    int iORDN51 = reader.GetOrdinal("ORDN51");
                    int iDTDR51 = reader.GetOrdinal("DTDR51");
                    int iOPST45 = reader.GetOrdinal("OPST45");

                    while (reader.Read())
                    {
                        if (!string.IsNullOrEmpty(reader.GetString(iCUSO51)))
                        {
                            Console.WriteLine((string)reader[iCUSO51]);
                            Console.WriteLine((string)reader[iORDN51]);
                            Console.WriteLine((string)reader[iDTDR51]);
                            Console.WriteLine((string)reader[iOPST45]);
                        }
                    }
                }
            }
        }
        catch (iDB2CommErrorException ex)
        {
            Console.WriteLine(ex.Message);
        }
    }

Any thoughts?

What are the expected data types for the 4 columns? If you remove the entire if block inside the while loop, does it run successfully? This would help narrow down whether it's the query bits or pulling data out of the datareader. Also if you have a chance, could you review your open questions and either mark as answered or provide some feedback on them? — billinkc
The data types are CUSO51 Char(15), ORDN51 Char(7), DTDR51 DEC(7,0) and OPST45 Char(12). I'll close my open questions now — Matt
What happens if you don't try to access the values of the datareader? I assume the code can iterate through the resultset fine, it's the accessing of data that will be troublesome. — billinkc

CrazyMetal CrazyMetal · Accepted Answer · 2012-07-06T12:33:26

If your value is a Byte[], I think you got some trouble with CCSID65535.

If so, here is a code to resolve it :-)

 private DataTable parseCCSID65535(DataTable p_dt)
 {
     DataTable dt = new DataTable();

     // Build a new DataTable
     for (int i = 0; i < p_dt.Columns.Count; i++)
     {
         dt.Columns.Add(p_dt.Columns[i].Caption);
     }

     //loop through the rows
     for (int r = 0; r < p_dt.Rows.Count; r++)
     {
         //create a new row
         string[] row = new string[p_dt.Columns.Count];

         //loop through all columns
         for (int c = 0; c < p_dt.Columns.Count; c++)
         {
             if (p_dt.Rows[r][c].GetType() == typeof(System.Byte[]))
             {
                 // if this value is CCSID65535, change it ;-)
                 iDB2CharBitData cbd = new iDB2CharBitData((Byte[])p_dt.Rows[r][c]);
                 row[c] = cbd.ToString(65535);
             }
             else
             {
                 // else: go on.
                 row[c] = p_dt.Rows[r][c].ToString();
             }
         }
         // passing to the new DataTable.
         dt.Rows.Add(row);
     }
     return dt;
 }

Returning ResultSet from AS400 Stored Procedure

1 Answers