Add support of DB2 ARRAY type to the Connect for ADO.Net DB2 provider

[xufufei]

PROBLEM DESCRIPTION: "Unable to cast object of type 'System.Int32' to type 'System.Array'" error when using DataDirect ADO.NET driver connecting to DB2 and calling a store procedure with an array input parameter. PROBLEM DETAIL: 1. Intel reates a stored procedure in DB2 with 2 parameters ; Inpur param of type int[] ; Output param of type integer . Procedure sums the element in the array and returns the sum as output. -------------------------------------------------------- CREATE TYPE "D1D"."INTARRAY" AS INTEGER ARRAY [100]; create procedure d1d.sum_array_proc(in numList d1d.intArray, out total integer) begin declare i, n integer; set n = CARDINALITY(numList); set i = 1; set total = 0; while (i = n) do set total = total + numList; set i = i + 1; end while; end --------------------------------------------------------- 2. C# application connects to DB2 using DD ADO.NET driver and calls the procedure, gets an exception "Unable to cast object of type 'System.Int32' to type 'System.Array'." Stopwatch sw = Stopwatch.StartNew(); const string CONN_STRING = @"Pooling=true;Min Pool Size=0;Max Pool Size=20;Socket Buffer Size=1024;Cursor Description Cache=true;Cursors With Hold=false;Enlist=false;Host=rf3dxap420n1.ltdauto.intel.com;Port=50001;Database=feddb;User ID=db2inst1;Password=xxxxx"; const string DD_PASSWORD = "xxxx"; { DB2Connection.SetOEMLicenseInfo("DDFEE.lic", DD_PASSWORD); var conn = new DB2Connection(CONN_STRING); conn.Open(); var command = new DB2Command("", conn); DB2Command cmd = conn.CreateCommand(); cmd.CommandText = "D1D.SUM_ARRAY_PROC"; cmd.ArrayBindCount = 2; cmd.CommandType = CommandType.StoredProcedure; int[] integerArray = new int[] { 1, 2}; DB2Parameter p1 = new DB2Parameter(); p1.Value = integerArray; p1.Direction = ParameterDirection.Input; p1.DB2DbType = DB2DbType.Integer; p1.Size = 2; DB2Parameter p2 = new DB2Parameter(); p2.Direction = ParameterDirection.Output; p2.DB2DbType = DB2DbType.Integer; p2.Value = 0; //p2.Size = 1; cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.ExecuteNonQuery(); Console.WriteLine("Value of output : " + p2.Value); Console.ReadLine(); -------------------------------------------------------- ENHANCEMENT REQUEST: We need to add support of DB2 ARRAY type to the Connect for ADO.Net DB2 provider.

Continue reading...