.NET applications can call stored procedures with a DB2Command object.
Procedure
- Make a connection to a target database. For steps to establish
database connection, see Connecting to a database from an application using the IBM Data Server Provider for .NET.
- Create the DB2Command object and set
the CommandType property as either CommandType.StoredProcedure or CommandType.Text.
The default value of the CommandType property is CommandType.Text.
The CommandType.Text value can be used to call
stored procedures. However, calling stored procedures is easier when
you set the CommandType property to CommandType.StoredProcedure.
When you use the CommandType.StoredProcedure object
to call a stored procedure, you must specify the stored procedure
name and parameters that are associated with the stored procedure.
A stored procedure with same name and same parameters can exist under
different schemas. To avoid calling an incorrect stored procedure,
fully qualify the stored procedure name with the correct schema name. A C# code example of the CommandType.Text object
follows:
DB2Command cmd = conn.CreateCommand();
String procCall = "CALL TEST_PROC (@input_param1)";
cmd.CommandType = CommandType.Text;
cmd.CommandText = procCall;
Note: When the CommandType property
is CommandType.Text, both CALL and EXECUTE
PROCEDURE statements are supported.
A C# code example
of the CommandType.StoredProcedure object follows:
DB2Command cmd = conn.CreateCommand();
String procName = "TEST_PROC";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
Note: When the CommandType property
is CommandType.StoredProcedure, named parameters
are not supported.
- Create the DB2Command.Parameters objects
that correspond to the IN, INOUT and OUT parameters. If you are using
parameter markers for stored procedure parameters, create the DB2Parameter objects
and bind the DB2Parameter objects to the DB2Command.Parameters object
with the Add method. A C# code example
follows:
DB2Parameter p1 = new DB2Parameter(“input_param1”, DB2Type.Integer);
p1.Value = (Int32) 123;
db2Command.Parameters.Add(p1);
You can pass the store procedure
parameters with host variables, named parameters, or positioned parameters.
However, you cannot use different methods to pass the stored procedure
parameters within the same SQL statement. Parameters can be passed
to the stored procedure in any order, when qualified by the parameter
name as shown in following C# code example:
CREATE PROCEDURE schema.my_proc ( IN var1 int, INOUT var2 int )
LANGUAGE SQL
BEGIN
-- procedure code here
END
String procCall = "CALL my_proc (var2=>@param2, var1=>@param1");
IBM Data Server
Provider for .NET supports
calling stored procedures with ARRAY data types as input (IN) parameters
in
DB2® for Linux, UNIX, and Windows servers.
ARRAY data types are not supported for the OUT and INOUT parameters.
The ARRAY length value must be specified in the
DB2Parameter.ArrayLength object
for each ARRAY parameter. A C# code example follows:
Int32 integerArray = new Int32[] { 12, 34, 45, 67 };
DB2Parameter p1 = new DB2Parameter(“input_param1”, DB2Type.Integer);
p1.Value = integerArray;
p1.ArrayLength = 3;
db2Command.Parameters.Add(p1);
The
Cursor enumeration
member can be used when binding INOUT (
InputOutput)
or OUT (
Output) parameters of the type cursor.
A C# code example of output parameters follows:
DB2Command cmd = new DB2Command("cursor_test", conn)
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("cursor1", DB2Type.Cursor).Direction =
ParameterDirection.Output;
cmd.Parameters.Add("cursor2", DB2Type.Cursor).Direction =
ParameterDirection.Output;
cmd.ExecuteNonQuery();
If
your application is connecting to DB2 for z/OS® Version
10 and later servers, your application must specify the correct data
type for the input parameters of the stored procedure that you are
calling. If your application specifies parameters that do not match
the data type of the input parameter, an invalid conversion error
is returned.
- Run the DB2Command.ExecuteNonQuery() function
to call a stored procedure. A C# code example follows:
cmd.ExecuteNonQuery();
If
there are any OUT or INOUT parameters, you can obtain the parameter
values with DB2DataReader object. A C# code example follows:
DB2DataReader drOutput2 = cmd.Parameters[1].Value;
DB2DataReader drOutput1 = cmd.Parameters[0].Value
Example
A C# code with
CommandType.Text example
follows:
// assume a DB2Connection conn
DB2Transaction trans = conn.BeginTransaction();
DB2Command cmd = conn.CreateCommand();
String procName = "INOUT_PARAM";
String procCall = "CALL INOUT_PARAM (@param1, @param2, @param3)";
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
cmd.CommandText = procCall;
// Register input-output and output parameters for the DB2Command
cmd.Parameters.Add( new DB2Parameter("@param1", "Value1");
cmd.Parameters.Add( new DB2Parameter("@param2", "Value2");
DB2Parameter param3 = new DB2Parameter("@param3", IfxType.Integer);
param3.Direction = ParameterDirection.Output;
cmd.Parameters.Add( param3 );
// Call the stored procedure
Console.WriteLine(" Call stored procedure named " + procName);
cmd.ExecuteNonQuery();
A Visual Basic code with
CommandType.Text example
follows:
' assume a DB2Connection conn
Dim trans As DB2Transaction = conn.BeginTransaction()
Dim cmd As DB2Command = conn.CreateCommand()
Dim procName As String = "INOUT_PARAM"
Dim procCall As String = "CALL INOUT_PARAM (?, ?, ?)"
cmd.Transaction = trans
cmd.CommandType = CommandType.Text
cmd.CommandText = procCall
' Register input-output and output parameters for the DB2Command
...
' Call the stored procedure
Console.WriteLine(" Call stored procedure named " & procName)
cmd.ExecuteNonQuery()
A C# code with
CommandType.StoredProcedure example
follows:
// assume a DB2Connection conn
DB2Transaction trans = conn.BeginTransaction();
DB2Command cmd = conn.CreateCommand();
String procName = "INOUT_PARAM";
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
// Register input-output and output parameters for the DB2Command
...
// Call the stored procedure
Console.WriteLine(" Call stored procedure named " + procName);
cmd.ExecuteNonQuery();
A Visual Basic code with
CommandType.StoredProcedure example
follows:
' assume a DB2Connection conn
Dim trans As DB2Transaction = conn.BeginTransaction()
Dim cmd As DB2Command = conn.CreateCommand()
Dim procName As String = "INOUT_PARAM"
cmd.Transaction = trans
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = procName
' Register input-output and output parameters for the DB2Command
...
' Call the stored procedure
Console.WriteLine(" Call stored procedure named " & procName)
cmd.ExecuteNonQuery()
A C# code example with ARRAY input
parameter follows:
db2Command.CommandText = “arrayparamprocedure”;
db2Command.CommandType = CommandType.StoredProcedure;
Int32 integerArray = new Int32[] { 12, 34, 45, 67 };
DB2Parameter p1 = new DB2Parameter(“numbers_in”, DB2Type.Integer);
p1.Value = integerArray;
p1.ArrayLength = 3;
String[] stringArray = new String[] {“i think i know”, “but you never know”, “how much i know” };
DB2Parameter p2 = new DB2Parameter(“varchars_in”, DB2Type.Varchar, 30);
p2.Value = stringArray;
p2.ArrayLength = 2;
db2Command.Parameters.Add(p1);
db2Command.Parameters.Add(p2);
db2Command.ExecuteNonQuery();