CODEWITHSUNDEEP
c#sql.netstored-proceduresibm-midrange
ehh
ehh

Reputation: 3480

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)
{
    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?

Upvotes: 1

Views: 4206

Answers (5)

Esperento57
Esperento57

Reputation: 17462

your should use dll ibm data provider for .net like here

But you can try to modify you code 

OdbcCommand cm = new OdbcCommand("{CALL RET_IMPL_STS('?','?','?','?')}", cn);
....
cm.Parameters["@P4"].Direction = ParameterDirection.InputOutput;

Upvotes: 0

Radinator
Radinator

Reputation: 1088

I recommend you to use the IBM cwbx libary. This is a IBM lib that is created for interop programming between Desktop and IBM AS400.

Take a look at here: http://timtrott.co.uk/calling-ibm-iseries-rpg-programs-api-calls/ This tells you, how to call a procedure with parameters located on a IBM AS400 and return some values. Trust me, I already have accomplished this task and it worked.

The whole thing is just including the cwbx.dll file, creating a instance of a machine and programm object, filling these with ProgrammParameters (defining input, output AND inout-parameter) and calling it.

using System;
using System.Collections.Generic;
using System.Text;
using cwbx;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string result = string.Empty;

            StringConverter stringConverter = new StringConverterClass();

            // Define an AS400 system and connect to it
            AS400System system = new AS400System();
            system.Define("AS400");
            system.UserID = "USERNAME";
            system.Password = "PASSWORD";
            system.IPAddress = "127.0.0.1";
            system.Connect(cwbcoServiceEnum.cwbcoServiceRemoteCmd);

            // Check the connection
            if (system.IsConnected(cwbcoServiceEnum.cwbcoServiceRemoteCmd) == 1)
            {
                // Create a program object and link to a system                
                cwbx.Program program = new cwbx.Program();
                program.LibraryName = "LIBRARY";
                program.ProgramName = "RPGPROG";
                program.system = system;

                // Sample parameter data
                char chrValue = '1';
                string strValue1 = "ABC";
                string strValue2 = "DEF";
                string outp = "";

                // Create a collection of parameters associated with the program
                ProgramParameters parameters = new ProgramParameters();

                parameters.Append("P1", cwbrcParameterTypeEnum.cwbrcInput, 1);
                parameters["P1"].Value = chrValue;

                parameters.Append("P2"), cwbrcParameterTypeEnum.cwbrcInput, 3);
                parameters["P2"].Value = strValue1;

                parameters.Append("P3"), cwbrcParameterTypeEnum.cwbrcInput, 3);
                parameters["P3"].Value = strValue1;

                parameters.Append("P4", cwbrcParameterTypeEnum.cwbrcOutput, 3);


                outp = stringConverter.FromBytes(parameters["P4"].Value);
            }

            system.Disconnect(cwbcoServiceEnum.cwbcoServiceAll);
            Console.WriteLine(result);
            Console.ReadKey();
        }
    }
}

This should almost work, you have to be carefull, since the interface dll is sensitive to false length of parameter. So if you want to transmit "TEXT" and provide a textlength of 3, then only "TEX" is transmitted.

Upvotes: 2

ehh
ehh

Reputation: 3480

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.

Upvotes: 2

McNets
McNets

Reputation: 10807

Reading @Andrei's link I've found this paragraph:

When a stored procedure returns a resultset, the output parameter(s) and return value are not available until the resultset has been accessed and closed. For example, if we omitted the line "dr.Close()" in the second sample above, we would be unable to retrieve the values for the output parameter and the return value.

Upvotes: 0

Andrei
Andrei

Reputation: 1

At first view, it seems like your are missing some curly braces. Try using the following command to create a new OdbcCommand object 

OdbcCommand cm = new OdbcCommand("{CALL RET_IMPL_STS('?','?','?','?')}", cn);

Please read more at: https://support.microsoft.com/en-us/kb/310130

Upvotes: -1

Related Questions