CODEWITHSUNDEEP
.netoledbibm-midrange
Tjaart
Tjaart

Reputation: 4119

Calling AS400 Programs through OleDb causes InvalidCastException

I am trying to call an RPG program on our AS/400 machine using OleDb. As soon as there are more complex types such as dates and decimals I seem to be getting the following error:

The data value could not be converted for reasons other than sign mismatch or data overflow. For example, the data was corrupted in the data store but the row was still retrievable.

Here is the code that produces the problem:

            //            'p_refno                     9a     ()
        //'p_projdat                   *date (output parm)
        //'p_amount                    13p, 2 (output parm)
        //'p_rtnsts                    1a (output parm)
        using (OleDbConnection cn = new OleDbConnection("Provider=IBMDA400;Data Source=MyAs400;User ID=MyUser;Password=MyPassword;"))
        {
            cn.Open();
            OleDbCommand cmd = new OleDbCommand("{{call PGMLIB.MYPROGRAM(?,?,?,?,?,?,?)}}", cn);
            cmd.CommandType = System.Data.CommandType.Text;

            OleDbParameter p_refno = new OleDbParameter("p_refno","ED4565654");
            OleDbParameter p_projdat = new OleDbParameter("p_projdat", new DateTime());
            p_projdat.Direction = System.Data.ParameterDirection.Output;
            p_projdat.Size = 8;
            OleDbParameter p_amount = new OleDbParameter("p_amount", new Decimal());
            p_amount.Direction = System.Data.ParameterDirection.Output;
            OleDbParameter p_rtnsts = new OleDbParameter("p_rtnsts", " ");
            p_rtnsts.Direction = System.Data.ParameterDirection.Output;
            p_rtnsts.Size = 1;
            p_amount.DbType = System.Data.DbType.Decimal;
            cmd.Parameters.Add(p_refno);
            cmd.Parameters.Add(p_mode);
            cmd.Parameters.Add(p_source);
            cmd.Parameters.Add(p_type);
            cmd.Parameters.Add(p_projdat);
            cmd.Parameters.Add(p_amount);
            cmd.Parameters.Add(p_rtnsts);
            cmd.ExecuteNonQuery();
            Console.WriteLine(p_projdat.Value.ToString());
            Console.WriteLine(p_amount.Value.ToString());
            Console.WriteLine(p_rtnsts.Value.ToString());

        }
        Console.WriteLine("Press any key to quit...");
        Console.ReadKey();

What am I doing wrong?

Upvotes: 1

Views: 2455

Answers (2)

Mike Wills
Mike Wills

Reputation: 21265

If you can, use the IBM i Access ADO.NET drivers and place the program call in a stored procedure. They can convert the data from i to PC.

Upvotes: 1

FastAl
FastAl

Reputation: 6280

Date formats (and binary numeric e.g., int/single, etc) differ between PC and mainframe platforms. You will need to convert them to text and then back again, unless the communications platform takes care of this for you - which obviously this one isn't doing.

There is probably a way to figure out how to do this, but if you are the only one using the mainframe piece and have the ability to change it, you will be hours ahead by forgetting about it and doing the converts yourself on both ends. (unless somebody who knows how reads this ;-)

If you can't change the as400 piece, well, don't accept this answer...

Upvotes: 1

Related Questions