/****************************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM, for the purpose of
** assisting you in the development of your applications.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: SpClient.cs
**
** SAMPLE: Call different types of stored procedures from SpServer.cs
**
*****************************************************************************
**
** Steps to run the sample with the command line window:
**
** I) If you have a compatible nmake program on your system,
** do the following:
**
** 1. Compile this server source file, SpServer.cs. This
** erases any existing assembly of the same name and copies
** the newly compiled assembly SpServer.dll from the
** current directory to the $(DB2PATH)\function directory).
** This also runs a batchfile SpCat that executes SQL to
** drop (if already existing) and create the stored
** procedures in DB2 that have their procedure-body
** implemented by the methods in this file, SpServer.cs:
**
** nmake/make SpServer
**
** To run SpClient successfully you must have succesfully
** completed step 1.
**
** 2. Compile the client source file SpClient.cs.
**
** nmake/make SpClient
**
** 3. Run the client SpClient:
**
** SpClient
**
** II) If you don't have a compatible nmake program on your
** system do the following:
**
** 1. Compile the SpServer.cs file with bldrn.bat, to compile
** the C# stored procedure implementations that the client
** application will call. Do this by entering the following
** at the command prompt:
**
** bldrtn SpServer
**
** This will compile SpServer.cs and copy the assembly file
** SpServer.dll from the current directory to the DB2 function
** directory: $(DB2PATH)\function. It will first remove any
** existing assemblies with the same name first.
**
** 2. Create the stored procedures in DB2 by entering the following
** at the command prompt:
**
** spcat
**
** This will drop the procedures from DB2, if they exist, and
** then executes SQL CREATE PROCEDURE statements to create the
** procedures in DB2. These statements associate each procedure
** name with an external C# method in the assembly SpServer.dll
** that is in the DB2 function directory.
**
** 3. Compile the SpClient.cs file with the bldapp.bat batch file by
** entering the following at the command prompt:
**
** bldapp SpClient
**
** 4. Run the SpClient program by entering the program name at
** the command prompt:
**
** SpClient
**
** SpClient calls several methods that call the stored procedures:
**
** 1. CallOutLanguage: returns language of the stored procedure
** library
** Parameter types used: OUT CHAR(8), OUT INTEGER, OUT VARCHAR(32)
** 2. CallOutParameter: return median salary of EMPLOYEE table
** Parameter types used: OUT DECIMAL, OUT INTEGER, OUT VARCHAR(32)
**
** 3. CallInParameters: accepts 3 salary values and a department name
** and updates the salaries in the EMPLOYEE table according to
** some logic.
**
** Parameter types used: IN DECIMAL(9,2)
** IN DECIMAL(9,2)
** IN DECIMAL(9,2)
** IN CHAR(3)
** OUT INTEGER,
** OUT VARCHAR(32)
** 4. CallInOutParameter: accepts an input value and returns
** the median salary of the EMPLOYEE table for employees who
** make more than the input value.
** Parameter types used: INOUT DECIMAL, OUT INTEGER, OUT VARCHAR(32)
**
** 5. CallOneResultSet: returns a result set to the client
** application
** Parameter types used: IN DECIMAL, OUT INTEGER, OUT VARCHAR(32)
**
** 6. callClobExtract: returns a string sub-section of a CLOB
** Parameter types used: IN CHAR(6)
** OUT VARCHAR(1000)
** OUT INTEGER
** OUT VARCHAR(32)
** 7. CallAllDataTypes: uses all of the common data types in a
** stored procedure
** Parameter types used: INOUT SMALLINT
** INOUT INTEGER
** INOUT BIGINT
** INOUT REAL
** INOUT DOUBLE
** OUT CHAR(1)
** OUT CHAR(15)
** OUT VARCHAR(12)
** OUT DATE
** OUT TIME
** OUT INTEGER
** OUT VARCHAR(32)
**
** ***************************************************************************
** The .NET stored procedures in this sample are implemented using
** parameter style GENERAL; this is the recommended parameter style.
**
** For examples of .NET procedures written with other parameter styles,
** please see the documentation topics in the Information Center.
** or in the Application Development Guide: Server Applications.
** Search for: Examples of CLR procedures in C#
** ***************************************************************************
**
** SQL Statements USED:
** CALL
** SELECT
**
** DB2 .NET Data Provider Classes USED:
** DB2Connection
** DB2Command
** DB2Transaction
**
** SpClient SpClient
** ***************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing applications, see the Application
** Development Guide.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, compiling, and running DB2
** applications, visit the DB2 Information Center at
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
**
****************************************************************************/
using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;
class SpClient
{
static decimal outMedian = 0; // Global variable - set by CallOutParameter
public static void Main(String[] args)
{
// Declare a DB2Connection and a DB2Transaction
DB2Connection conn = null;
DB2Transaction trans = null;
// Declare a String to store the output from CallOutLanguage()
String language = "";
try
{
Console.WriteLine("\n THIS SAMPLE SHOWS HOW TO CALL THE STORED" +
" PROCEDURES IN SpServer.cs.\n");
// Connect to a database
Console.WriteLine(" Connecting to a database ...");
conn = ConnectDb(args);
// Return the name of the programming language in which the
// invoked stored procedure is implemented
trans=conn.BeginTransaction();
language = CallOutLanguage(conn, trans);
// Display the mean salary of the EMPLOYEE table
trans=conn.BeginTransaction();
CallOutParameter(conn, trans);
// Update salaries in the EMPLOYEE table
trans=conn.BeginTransaction();
CallInParameters(conn, trans);
// Display the median salary of the EMPLOYEE
// table for employees who
// make more than 'outMedian'
trans=conn.BeginTransaction();
CallInoutParameter(conn, outMedian, trans);
// Display the median salary of the EMPLOYEE
// table for employees who make more than 99999.99
trans=conn.BeginTransaction();
CallInoutParameter(conn, (decimal)(99999.99), trans);
// Obtain and display a result set
trans=conn.BeginTransaction();
CallOneResultSet(conn, trans);
// Obtain and display a sub-section of a CLOB data type
trans=conn.BeginTransaction();
CallClobExtract("000140", conn, trans);
// Use all the common data types in a stored procedure
trans=conn.BeginTransaction();
CallAllDataTypes(conn, trans);
// Disconnect from the database
Console.WriteLine("\n Disconnect from the database");
conn.Close();
}
catch (Exception e)
{
try
{
Console.WriteLine("Error caught in client of SpServer App.");
trans.Rollback();
conn.Close();
}
catch (Exception x)
{
Console.WriteLine(x.Message);
}
Console.WriteLine(e.Message);
}
} // Main
// This method establishes a connection to a database
public static DB2Connection ConnectDb(String[] argv)
{
String server = "";
String alias = "";
String userId = "";
String password = "";
Int32 portNumber = -1;
String connectString;
if( argv.Length > 5 ||
( argv.Length == 1 &&
( String.Compare(argv[0],"?") == 0 ||
String.Compare(argv[0],"-?") == 0 ||
String.Compare(argv[0],"/?") == 0 ||
String.Compare(argv[0],"-h",true) == 0 ||
String.Compare(argv[0],"/h",true) == 0 ||
String.Compare(argv[0],"-help",true) == 0 ||
String.Compare(argv[0],"/help",true) == 0 ) ) )
{
throw new Exception(
"Usage: prog_name [dbAlias] [userId passwd] \n" +
" prog_name [dbAlias] server portNum userId passwd");
}
switch (argv.Length)
{
case 0: // Use all defaults
alias = "sample";
userId = "";
password = "";
break;
case 1: // dbAlias specified
alias = argv[0];
userId = "";
password = "";
break;
case 2: // userId & passwd specified
alias = "sample";
userId = argv[0];
password = argv[1];
break;
case 3: // dbAlias, userId & passwd specified
alias = argv[0];
userId = argv[1];
password = argv[2];
break;
case 4: // use default dbAlias
alias = "sample";
server = argv[0];
portNumber = Convert.ToInt32(argv[1]);
userId = argv[2];
password = argv[3];
break;
case 5: // everything specified
alias = argv[0];
server = argv[1];
portNumber = Convert.ToInt32(argv[2]);
userId = argv[3];
password = argv[4];
break;
}
if(portNumber==-1)
{
connectString = "Database=" + alias;
}
else
{
connectString = "Server=" + server + ":" + portNumber +
";Database=" + alias;
}
if(userId != "")
{
connectString += ";UID=" + userId + ";PWD=" + password;
}
DB2Connection conn = new DB2Connection(connectString);
conn.Open();
Console.WriteLine(" Connected to the " + alias + " database");
return conn;
} // ConnectDb
// This method returns the name of the programming language
// that was was used to implement the stored procedure
public static String CallOutLanguage(DB2Connection conn,
DB2Transaction trans)
{
String outLang = "";
String outErrorMsg = "";
Int32 outReturnCode = 0;
try
{
// Create a DB2Command to execute the stored procedure OUT_LANGUAGE
String procName = "OUT_LANGUAGE";
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
// Register output parameters for the DB2Command
DB2Parameter parm = cmd.Parameters.Add("@language", DB2Type.VarChar, 8);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@returnCode", DB2Type.Integer, 8);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@errorMsg", DB2Type.VarChar, 32);
parm.Direction = ParameterDirection.Output;
// Call the stored procedure
Console.WriteLine();
Console.WriteLine(" Call stored procedure named " + procName);
cmd.ExecuteNonQuery();
// Retrieve output parameters
outReturnCode = (Int32)cmd.Parameters["@returnCode"].Value;
outErrorMsg = (String)cmd.Parameters["@errorMsg"].Value;
// Check the output return code
if (outReturnCode < 0)
{
// Error caught in the procedure
Console.WriteLine(" " + procName + " failed with return code: " + outReturnCode);
if (outErrorMsg != "")
{
Console.WriteLine(" Error message: " +
outErrorMsg.Trim());
}
else
{
Console.WriteLine(" No error message was returned.");
}
}
else
{
// Successful execution of procedure
outLang = (String)cmd.Parameters["@language"].Value;
Console.WriteLine(" Stored procedure is implemented in language " +
outLang);
}
trans.Rollback();
}
catch (Exception e)
{
trans.Rollback();
Console.WriteLine(e.Message);
}
return(outLang);
} // CallOutLanguage
// This method displays the mean salary of the EMPLOYEE table
public static void CallOutParameter(DB2Connection conn,
DB2Transaction trans)
{
String outErrorMsg = "";
Int32 outReturnCode = 0;
try
{
// Create a DB2Command to execute the stored procedure OUT_PARAM
String procName = "OUT_PARAM";
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
// Register the output parameters for the DB2Command
DB2Parameter parm = cmd.Parameters.Add("@medianSalary",
DB2Type.Decimal,
7);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@returnCode", DB2Type.Integer, 8);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@errorMsg", DB2Type.VarChar, 32);
parm.Direction = ParameterDirection.Output;
cmd.Parameters["@medianSalary"].Value = 0;
cmd.Parameters["@returnCode"].Value = 0;
cmd.Parameters["@errorMsg"].Value = "";
// Call the stored procedure
Console.WriteLine();
Console.WriteLine(" Call stored procedure named " + procName);
cmd.ExecuteNonQuery();
// Retrieve the output parameters
outMedian = (Decimal)cmd.Parameters["@medianSalary"].Value;
outReturnCode = (Int32)cmd.Parameters["@returnCode"].Value;
outErrorMsg = (String)cmd.Parameters["@errorMsg"].Value;
// Check the output return code
if (outReturnCode < 0)
{
// Error caught in the procedure
Console.WriteLine(" " + procName + " failed with return code: "
+ outReturnCode);
if (outErrorMsg != "")
{
Console.WriteLine(" Error message: " + outErrorMsg.Trim());
}
else
{
Console.WriteLine(" No error message was returned.");
}
}
else
{
// Successful execution
Console.WriteLine(" " + procName + " completed successfully");
Console.WriteLine(" Median salary returned from " + procName +
" = " + String.Format("{0:f2}", outMedian));
}
// Rollback the effect of this method to restore
// the SAMPLE database to its original state
trans.Rollback();
}
catch (Exception e)
{
trans.Rollback();
Console.WriteLine(e.Message);
}
} // CallOutParameter
// This method updates salaries in the EMPLOYEE table according to 3 values
public static void CallInParameters(DB2Connection conn,
DB2Transaction trans)
{
String outErrorMsg = "";
Int32 outReturnCode = 0;
try
{
// Create a DB2Command to be used to execute a query
// on table 'employee' and to execute the CALL
// statement to invoke procedure IN_PARAMS
String procName = "IN_PARAMS";
String callsmt = "CALL " + procName + "(?, ?, ?, ?, ?, ?)";
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
// Display the total salary before calling procedure IN_PARAMS
cmd.CommandText = "SELECT SUM(salary) " +
" FROM employee " +
" WHERE workdept = ?";
cmd.Parameters.Add("@dept", DB2Type.Char, 3).Value = "E11";
DB2DataReader reader = cmd.ExecuteReader();
reader.Read();
Double sumSalary = (Double)reader.GetDecimal(0);
reader.Close();
Console.WriteLine();
Console.WriteLine(" Sum of salaries for dept. E11 = " +
String.Format("{0:f2}" ,sumSalary) +
" before " + procName);
// Prepare DB2Command for calling procedure IN_PARAMS
// and register the input and output parameters
cmd.CommandText = callsmt;
cmd.Parameters.RemoveAt("@dept");
DB2Parameter parm = cmd.Parameters.Add("@lowSal", DB2Type.Decimal, 7);
parm.Direction = ParameterDirection.Input;
parm = cmd.Parameters.Add("@medSal", DB2Type.Decimal, 7);
parm.Direction = ParameterDirection.Input;
parm = cmd.Parameters.Add("@highSal", DB2Type.Decimal, 7);
parm.Direction = ParameterDirection.Input;
parm = cmd.Parameters.Add("@dept", DB2Type.VarChar, 8);
parm.Direction = ParameterDirection.Input;
parm = cmd.Parameters.Add("@returnCode", DB2Type.Integer, 8);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@errorMsg", DB2Type.VarChar, 32);
parm.Direction = ParameterDirection.Output;
// Initialize the parameters
cmd.Parameters["@lowSal"].Value = 15000;
cmd.Parameters["@medSal"].Value = 20000;
cmd.Parameters["@highSal"].Value = 25000;
cmd.Parameters["@dept"].Value = "E11";
cmd.Parameters["@returnCode"].Value = 0;
cmd.Parameters["@errorMsg"].Value = "";
// Call the stored procedure
Console.WriteLine();
Console.WriteLine(" Call stored procedure named " + procName);
cmd.ExecuteNonQuery();
// Retrieve the output parameters
outReturnCode = (Int32)cmd.Parameters["@returnCode"].Value;
outErrorMsg = (String)cmd.Parameters["@errorMsg"].Value;
// Check the output return code
if (outReturnCode < 0)
{
// Error caught in the procedure
Console.WriteLine(" " + procName + " failed with return code: " + outReturnCode);
if (outErrorMsg != "")
{
Console.WriteLine(" Error message: " + outErrorMsg.Trim());
}
else
{
Console.WriteLine(" No error message was returned.");
}
}
else
{
// Successful execution
Console.WriteLine(" " + procName + " completed successfully");
// Display total salary after calling IN_PARAMS
cmd.CommandText = "SELECT SUM(salary)" +
" FROM employee " +
" WHERE workdept = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add("@dept",DB2Type.VarChar,8).Value = "E11";
reader = cmd.ExecuteReader();
reader.Read();
sumSalary = (Double)reader.GetDecimal(0);
reader.Close();
Console.WriteLine(" Sum of salaries for dept. E11 = " +
String.Format("{0:f2}" , sumSalary) +
" after " + procName);
}
// Rollback the effect of this method to restore
// the SAMPLE database to its original state
trans.Rollback();
}
catch (Exception e)
{
trans.Rollback();
Console.WriteLine(e.Message);
}
} // CallInParameters
// This method displays the median salary of the EMPLOYEE table for
// employees who make more than 'median'
public static void CallInoutParameter(DB2Connection conn,
Decimal median,
DB2Transaction trans)
{
String outErrorMsg = "";
Int32 outReturnCode = 0;
try
{
// Create a DB2Command to execute the stored procedure INOUT_PARAM
String procName = "INOUT_PARAM";
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
// Register input-output and output parameters for the DB2Command
DB2Parameter parm = cmd.Parameters.Add("@medianSalary",
DB2Type.Decimal,
7);
parm.Direction = ParameterDirection.InputOutput;
// Set input parameter to median value passed back by OUT_PARAM
parm.Value = median;
parm = cmd.Parameters.Add("@returnCode", DB2Type.Integer, 8);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@errorMsg", DB2Type.VarChar, 32);
parm.Direction = ParameterDirection.Output;
// Call the stored procedure
Console.WriteLine();
Console.WriteLine(" Call stored procedure named " + procName);
if (median == (decimal) 99999.99)
{
Console.WriteLine(" with an input value that causes a " +
"NOT FOUND error");
}
cmd.ExecuteNonQuery();
// Retrieve output parameters
Decimal inoutMedian = (Decimal)cmd.Parameters["@medianSalary"].Value;
outReturnCode = (Int32)cmd.Parameters["@returnCode"].Value;
outErrorMsg = (String)cmd.Parameters["@errorMsg"].Value;
// Check the output return code
if (outReturnCode < 0)
{
// Error caught in the procedure
Console.WriteLine(" " + procName + " failed with return code: "
+ outReturnCode);
if (outErrorMsg != "")
{
Console.WriteLine(" Error message: " + outErrorMsg.Trim());
}
else
{
Console.WriteLine(" No error message was returned.");
}
}
else
{
// Successful execution
Console.WriteLine(" " + procName + " completed successfully");
Console.WriteLine(" Median salary returned from " + procName +
" = " + String.Format("{0:f2}" ,inoutMedian));
}
// Rollback the effect of this method to restore
// the SAMPLE database to its original state
trans.Rollback();
}
catch (Exception e)
{
trans.Rollback();
Console.WriteLine(e.Message);
}
} // CallInoutParameter
// This method obtains and displays a result set
public static void CallOneResultSet(DB2Connection conn,
DB2Transaction trans)
{
String outErrorMsg = "";
Int32 outReturnCode = 0;
try
{
// Create a DB2Command to execute the CALL statement for
// ONE_RESULT_SET
String procName = "ONE_RESULT_SET";
String callsmt = "CALL " + procName + "(?, ?, ?)";
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = callsmt;
cmd.CommandType = CommandType.Text;
// Register input and output parameters for the CALL statement
DB2Parameter parm = cmd.Parameters.Add("@salThreshold",
DB2Type.Decimal,
7);
parm.Direction = ParameterDirection.Input;
// Set input parameter to median value passed back by OUT_PARAM
parm.Value = outMedian;
parm = cmd.Parameters.Add("@returnCode", DB2Type.Integer, 8);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@errorMsg", DB2Type.VarChar, 32);
parm.Direction = ParameterDirection.Output;
// Call the stored procedure
Console.WriteLine();
Console.WriteLine(" Call stored procedure named " + procName);
DB2DataReader reader= cmd.ExecuteReader();
// Retrieve the output parameters
outReturnCode = (Int32)cmd.Parameters["@returnCode"].Value;
outErrorMsg = (String)cmd.Parameters["@errorMsg"].Value;
// Check the output return code
if (outReturnCode < 0)
{
// Error caught in the procedure
Console.WriteLine(" " + procName + " failed with return code:"
+ outReturnCode);
if (outErrorMsg != "")
{
Console.WriteLine(" " + procName + " failed with message: "
+ outErrorMsg.Trim());
}
else
{
Console.WriteLine(" No error message was returned.");
}
}
else
{
// Successful execution
Console.WriteLine(" " + procName + " completed successfully");
Console.WriteLine(" ===================================" +
"====================");
// Display the result set
DisplayResultSet(reader);
reader.Close();
}
trans.Rollback();
}
catch (Exception e)
{
trans.Rollback();
Console.WriteLine(e.Message);
}
} // CallOneResultSet
// This method obtains and displays a sub-section of a CLOB data type
public static void CallClobExtract(String empNo,
DB2Connection conn,
DB2Transaction trans)
{
String outInterests;
String outErrorMsg = "";
Int32 outReturnCode = 0;
try
{
// Create a DB2Command to execute the stored procedure CLOB_EXTRACT
String procName = "CLOB_EXTRACT";
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
// Register input and output parameters for the DB2Command
DB2Parameter parm = cmd.Parameters.Add("@empno", DB2Type.VarChar, 6);
parm.Direction = ParameterDirection.Input;
parm.Value = empNo;
parm = cmd.Parameters.Add("@interests", DB2Type.VarChar,1000);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@returnCode", DB2Type.Integer, 8);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@errorMsg", DB2Type.VarChar, 32);
parm.Direction = ParameterDirection.Output;
// Call the stored procedure
Console.WriteLine();
Console.WriteLine(" Call stored procedure named " + procName);
cmd.ExecuteNonQuery();
// Retrieve output parameters
outInterests = (String)cmd.Parameters["@interests"].Value;
outReturnCode = (Int32)cmd.Parameters["@returnCode"].Value;
outErrorMsg = (String)cmd.Parameters["@errorMsg"].Value;
// Check the output return code
if (outReturnCode < 0)
{
// Error caught in the procedure
Console.WriteLine(" " + procName + " failed with return code:"
+ outReturnCode);
if (outErrorMsg != "")
{
Console.WriteLine(" " + procName + " failed with message: "
+ outErrorMsg.Trim());
}
else
{
Console.WriteLine(" No error message was returned.");
}
}
else
{
// Successful execution
Console.WriteLine(" " + procName + " completed successfully");
Console.WriteLine(" Resume section returned for employee "
+ empNo + " =\n\n" + " " + outInterests);
}
trans.Rollback();
}
catch (Exception e)
{
trans.Rollback();
Console.WriteLine(e.Message);
}
} // CallClobExtract
// This method uses all the common data types in a stored procedure
public static void CallAllDataTypes(DB2Connection conn,
DB2Transaction trans)
{
Int32 outReturnCode = 0;
String outErrorMsg = "";
try
{
// Create a DB2Command to execute the CALL statement for
// ALL_DATA_TYPES
String procName = "ALL_DATA_TYPES";
String callsmt = "CALL " +
procName +
"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = callsmt;
cmd.CommandType = CommandType.Text;
// Register input-output and output parameters for the CALL statement
// and initialize input-output parameters
DB2Parameter parm = cmd.Parameters.Add("@inoutSmallint",
DB2Type.SmallInt);
parm.Direction = ParameterDirection.InputOutput;
parm.Value = 32000;
parm = cmd.Parameters.Add("@inoutInteger", DB2Type.Integer);
parm.Direction = ParameterDirection.InputOutput;
parm.Value = 2147483000;
parm = cmd.Parameters.Add("@inoutBigInt", DB2Type.BigInt);
parm.Direction = ParameterDirection.InputOutput;
parm.Value = 2147483000;
parm = cmd.Parameters.Add("@inoutReal", DB2Type.Real);
parm.Direction = ParameterDirection.InputOutput;
parm.Value = 100000;
parm = cmd.Parameters.Add("@inoutDouble", DB2Type.Double);
parm.Direction = ParameterDirection.InputOutput;
parm.Value = 2500000;
parm = cmd.Parameters.Add("@outChar", DB2Type.Char, 1);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@outChars", DB2Type.Char, 15);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@outVarchar", DB2Type.VarChar, 12);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@outDate", DB2Type.Date);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@outTime", DB2Type.Time);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@returnCode", DB2Type.Integer);
parm.Direction = ParameterDirection.Output;
parm = cmd.Parameters.Add("@errorMsg", DB2Type.VarChar,32);
parm.Direction = ParameterDirection.Output;
// Call the stored procedure
Console.WriteLine();
Console.WriteLine(" Call stored procedure named " + procName);
cmd.ExecuteNonQuery();
// Retrieve return code and possible error message
outReturnCode = (Int32)cmd.Parameters["@returnCode"].Value;
outErrorMsg = (String)cmd.Parameters["@errorMsg"].Value;
// Check the output return code
if (outReturnCode < 0)
{
// Error caught in the procedure
Console.WriteLine(" " + procName + " failed with return code:"
+ outReturnCode);
if (outErrorMsg != "")
{
Console.WriteLine(" " + procName + " failed with message: "
+ outErrorMsg.Trim());
}
else
{
Console.WriteLine(" No error message was returned.");
}
}
else
{
// Successful execution
Console.WriteLine(" " + procName + " completed successfully");
// Retrieve and display output parameters
Console.WriteLine(" Value of SMALLINT = " +
cmd.Parameters["@inoutSmallint"].Value);
Console.WriteLine(" Value of INTEGER = " +
cmd.Parameters["@inoutInteger"].Value);
Console.WriteLine(" Value of BIGINT = " +
cmd.Parameters["@inoutBigint"].Value);
Console.WriteLine(" Value of REAL = " +
cmd.Parameters["@inoutReal"].Value);
Console.WriteLine(" Value of DOUBLE = " +
cmd.Parameters["@inoutDouble"].Value);
Console.WriteLine(" Value of CHAR(1) = " +
cmd.Parameters["@outChar"].Value);
Console.WriteLine( " Value of CHAR(15) = " +
cmd.Parameters["@outChars"].Value.ToString().Trim());
Console.WriteLine(" Value of VARCHAR(12) = " +
cmd.Parameters["@outVarchar"].Value.ToString().Trim());
Console.WriteLine(" Value of DATE = " +
cmd.Parameters["@outDate"].Value);
Console.WriteLine(" Value of TIME = " +
cmd.Parameters["@outTime"].Value);
}
trans.Rollback();
}
catch (Exception e)
{
trans.Rollback();
Console.WriteLine(e.Message);
}
} // CallAllDataTypes
// This method displays the result set contained in the
// DB2DataReader input parameter.
public static void DisplayResultSet(DB2DataReader reader)
{
while(reader.Read())
{
Console.Write(" ");
for(int k=0;k<reader.FieldCount;k++)
{
String str;
if (Equals(reader.GetFieldType(k),Type.GetType("System.Double")))
{
str = String.Format("{0:f2}", (Decimal)reader.GetValue(k));
}
else
{
str = reader.GetValue(k).ToString();
}
if(str.Length < 8)
{
Console.Write(str.PadRight(8,' ') + " ");
}
else
{
Console.Write(str.Substring(0,8) + " ");
}
}
Console.WriteLine();
}
} // DisplayResultSet
} // SpClient