ehh ehh - 4 months ago 29
C# Question

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

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

using (OdbcConnection cn = ODBC.Instance.AmulibConnection)

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;


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?

ehh ehh

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.