Calling an external AS400 stored procedure from C# with output parameter

Question

My stored procedure has 4 parameters: 3 strings as input and one string as output.

using (OdbcConnection cn = ODBC.Instance.AmulibConnection)
{
    cn.Open();

    using (OdbcCommand cm = cn.CreateCommand())
    {
        cm.CommandText = "CALL RET_IMPL_STS('?','?','?','?')";
        cm.CommandType = CommandType.StoredProcedure;

        cm.Parameters.Add("@P1", OdbcType.Char).Value = "1";
        cm.Parameters["@P1"].Size = 1;
        cm.Parameters["@P1"].Direction = ParameterDirection.Input;

        cm.Parameters.Add("@P2", OdbcType.Char).Value = "ABC";
        cm.Parameters["@P2"].Size = 15;
        cm.Parameters["@P2"].Direction = ParameterDirection.Input;

        cm.Parameters.Add("@P3", OdbcType.Char).Value = "DEF";
        cm.Parameters["@P3"].Size = 6;
        cm.Parameters["@P3"].Direction = ParameterDirection.Input;

        cm.Parameters.Add("@P4", OdbcType.Char);
        cm.Parameters["@P4"].Size = 5;
        cm.Parameters["@P4"].Direction = ParameterDirection.Output;

        cm.Prepare();
        cm.ExecuteNonQuery();

        string result = cm.Parameters["@P4"].Value.ToString();

        return result;
    }
}

I am getting the following error:

Additional information: ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0469 - IN, OUT, or INOUT not valid for parameter 4 in procedure RET_IMPL_STS in *N.

What is the correct syntax for the output parameter?

Answer 1

Following are 2 possible options:

1. We need to remove the quotes even if the parameters are string:

   cm.CommandText = "CALL RET_IMPL_STS(?,?,?,?)";

   Then we need to specify each 4 parameters

2. The following syntax will also work fine:

   cm.CommandText = string.Format("CALL RET_IMPL_STS ('{0}', '{1}', '{2}', ?)", STIDAD, ITNOAD, ITRVAD);

   In that case we need to specify only the output parameter. Note that in this such format, the quotes are required for string parameters.