iSeries call procedure with INOUT parameter

Date: 2021-01-19
using IBM.Data.DB2.iSeries;
using System;
using System.Data;

namespace iSeriesTestProcedure
{
    class Program
    {
        static void Main(string[] args) => TestiSeries();

        private static void Run(IDbConnection c, string sql) {
            var cmd = c.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        private static object RunRead(IDbConnection c, string sql)
        {
            var cmd = c.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            object result = null;
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    result = reader.GetValue(0);
                }
            }
            return result;
        }
        private static void TestiSeries()
        {
            var connectionString = "DataSource=***;UserID=***;Password=***;DataCompression=False;DEFAULT COLLECTION=***;Pooling=false;";
            using (var con = new iDB2Connection(connectionString))
            {
                con.Open();
                try
                {
                    var transation = con.BeginTransaction();
                    var cmd = con.CreateCommand();

                    Run(con, "CREATE OR REPLACE VARIABLE P1 CHAR(435)");
                    Run(con, "SET P1 = '147900701066'");
                    var sql = string.Join(Environment.NewLine, new string[] {
                        "BEGIN",
                        " DECLARE P2 CHAR(435);",
                        " SET P2 = P1;",
                        " CALL \"@TESTALL\".\"ADR006C\"(P2);",
                        " VALUES (P2) INTO P1;",
                        "END"
                    });
                    Run(con, sql);
                    var data = RunRead(con, "VALUES (P1)");
                    Console.WriteLine($"{data}");
                }
                catch (Exception ex)
                {
                    var error = ex.ToString();
                    Console.WriteLine(error);
                }
            }
        }
    }
}

Optimized generic function:

public T1 ExecuteCallWithSingleInputAndOutputParam<T1>(string callName, string callParamsAsString)
{​​​​​​​​
    var connection = _connection;
    var paramLength = callParamsAsString.Length;
    var callnamePrepared = PrepareCallName(callName);

    RunStatement(connection, $"CREATE OR REPLACE VARIABLE P1 CHAR({​​​​​​​​callParamsAsString.Length}​​​​​​​​)");
    RunStatement(connection, $"SET P1 = '{​​​​​​​​callParamsAsString}​​​​​​​​'");
    RunStatement(connection, string.Join(Environment.NewLine, new string[] {​​​​​​​​
        "BEGIN",
        $"  DECLARE P2 CHAR({​​​​​​​​paramLength}​​​​​​​​);",
        "  SET P2 = P1;",
        $" CALL {​​​​​​​​callnamePrepared}​​​​​​​​(P2);",
        "  VALUES (P2) INTO P1;",
        "END"
    }​​​​​​​​));

    return RunRead<T1>(connection, "VALUES (P1)");
}​​​​​​​​

private string PrepareCallName(string callName)
{​​​​​​​​
    var splitted = callName.Split('.');

    if (splitted.Length != 2)
        throw new Exception("invalid callname");

    var a = splitted[0];
    var b = splitted[1];

    return $"\"{​​​​​​​​a}​​​​​​​​\".\"{​​​​​​​​b}​​​​​​​​\"";
}​​​​​​​​

private static void RunStatement(DataConnection c, string sql)
{​​​​​​​​
    var cmd = CreateCommand(c, sql);
    cmd.ExecuteNonQuery();
}​​​​​​​​

private static T1 RunRead<T1>(DataConnection c, string sql)
{​​​​​​​​
    var cmd = CreateCommand(c, sql);
    var result = cmd.ExecuteScalar();
    return (T1)result;
}​​​​​​​​

private static IDbCommand CreateCommand(DataConnection c, string sql)
{​​​​​​​​
    var cmd = c.CreateCommand();
    cmd.CommandText = sql;
    cmd.CommandType = CommandType.Text;
    return cmd;
}​​​​​​​​

44710cookie-checkiSeries call procedure with INOUT parameter