The OUT and INOUT Parameters

As of Version 3.50.xC4, HCL OneDB™ Client Software Development Kit supports the use of OUT and INOUT parameters during execution of SPL.

The following data types are supported:

BIGINT
BLOB
BOOLEAN
DATETIME
CHAR
CLOB
DECIMAL
FLOAT
INT8
INTEGER
INTERVAL
LVARCHAR
MONEY
NCHAR
NVARCHAR
SMALLFLOAT
SMALLINT
VARCHAR

These restrictions exist when using OUT or INOUT parameters in SPL execution:

Collection data types such as LIST, MULTISET, ROW and SET are not supported.
Returning result sets is not supported. After executing SPL with OUT or INOUT parameters, you cannot call SQLFetch or SQL GetData.
Only one value can be returned; that is, only one set of OUT or INOUT parameters can be returned per individual SPL execution.
The following SPL execution example creates one OUT, one INOUT, and one IN (default) parameter and one return value.
```
create procedure myproc(OUT intparam INT, INOUT charparam char(20),
 inparam int) returns int
<body of SPL>
end procedure;
```

The following code example shows how to use OUT and INOUT parameters.

using System;
using System.Data;
using System.IO;
using Informix.Net.Core;


namespace SPLInOutParamTest
{
    class Program
    {
        static void Main(string[] args)
      {
            /* Build connection string and create connection object */
            IfxConnection conn = new IfxConnection("Server=ol_ids1150;
            Database=common_db1;UID=informix;PWD=informix");

            /* Create command object */
            IfxCommand cmd = new IfxCommand();

            /* Connect to the server */
            conn.Open();

            /* Associate connection object to command object */
            cmd.Connection = conn;

            try
            {
                try
                {
                    /* Drop the procedure */
                    cmd.CommandText = "DROP PROCEDURE test_proc;";
                    cmd.ExecuteNonQuery();
                }
                catch { /* Ignore the exception */ };

                /* Create procedure with INOUT params */
                cmd.CommandText = "CREATE PROCEDURE test_proc
                (INOUT arg1 int, OUT arg2 int, INOUT arg3 int) " +
                                  "returning int " +
                                  "define ret int; " +
                                  "let ret = (arg1 + arg3);" +
                                  "let arg1 = 1; " +
                                  "let arg2 = 2; " +
                                  "let arg3 = 3;" +
                                  "return ret; " +
                                  "end procedure;";

               cmd.ExecuteNonQuery();

                cmd.CommandText = "{? = call test_proc(?,?,?)};";
                
                /* Bind the required parameters */
                IfxParameter p1 = cmd.Parameters.Add("ID", IfxType.Integer);
                IfxParameter p2 = cmd.Parameters.Add("ID1", IfxType.Integer);
                IfxParameter p3 = cmd.Parameters.Add("ID2", IfxType.Integer);
                IfxParameter p4 = cmd.Parameters.Add("ID3", IfxType.Integer);

                /* Initialize the values for the parameters */
                p1.Value = 0;
                p2.Value = 5;
                p3.Value = 4;
                p4.Value = 10;

                /* Bind the appropriate direction */
                p1.Direction = ParameterDirection.Output;
                p2.Direction = ParameterDirection.InputOutput;
                p3.Direction = ParameterDirection.Output;
                p4.Direction = ParameterDirection.InputOutput;

                /* Execute the procedure */
                cmd.ExecuteNonQuery();

                /* Print the data */
                Console.WriteLine("\n Return value from procedure
                   = " + (Int32)p1.Value);
                Console.WriteLine("\n Out param1 value = " + (Int32)p2.Value);
                Console.WriteLine("\n Out param2 value = " + (Int32)p3.Value);
                Console.WriteLine("\n Out param3 value = " + (Int32)p4.Value);
            }
            catch (IfxException e)
            {
                Console.WriteLine(e.Message);
            }
        }
    }
}