/****************************************************************************
** (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: SpServer.cs
**
** SAMPLE: C# implementation of procedures created with SpCat.bat
**
*****************************************************************************
**
** 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 this file, SpServer.cs, containing the C# stored
** procedure methods that implement the stored
** procedure-bodies, with bldrn.bat. This batch file first
** erases any existing assembly of the same name. Then it
** compiles SpServer.cs and copies the assembly file
** SpServer.dll from the current directory to the DB2 function
** directory: $(DB2PATH)\function.
**
** bldrtn SpServer
**
**
** 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:
**
** Class SpServer contains the following methods:
**
** 1. outLanguage: Return language of the stored procedure library
** 2. outParameter: Return median salary of EMPLOYEE table
** 3. inParams: Accept 3 values, use them to update salaries in
** the EMPLOYEE table
** 4. inoutParam: Aaccept an input value and return the median
** salary of the EMPLOYEE table for employees who
** make more than the input value
** 5. resultSetToClient: Return a result set to the client application
** 6. clobExtract: Return a section of a CLOB type as a string
** 7. allDataTypes: Use all of the common data types
**
******************************************************************************
** 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 in other parameter styles,
** please see the related 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:
** SELECT
** UPDATE
**
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing JDBC 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:**www.software.ibm.com/data/db2/udb/ad
****************************************************************************/
using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;
class SpServer
{
/****************************************************************
Routine: OutLanguage
Purpose: Returns the code implementation language of
routine 'OutLanguage' (as it appears in the
database catalog) in an output parameter.
Shows how to:
- define an OUT parameter in PARAMETER STYLE GENERAL
- execute an SQL query
Presumptions:
- that there is only one routine OUT_LANGUAGE
in the database. Specifically, that there are not
two routines with the same name and different
properties (different parameter data types)
- The predicate can be modified to search for any
other criteria.
Parameters:
IN: (none)
OUT: outLang - The programming language of
this routine's implementation
outReturnCode - A return code to indicate a caught error
outErrorMsg - To hold an optional error message
******************************************************************/
public static void OutLanguage(out string outLang,
out int outReturnCode,
out string outErrorMsg)
{
// OUT_LANGUAGE is the name of the DB2 stored procedure
// for which this is the code implementation
String routineName = "OUT_LANGUAGE";
DB2DataReader reader = null;
outReturnCode = 0;
outErrorMsg = "";
// The DB2 system tables, in schema SYSIBM, store
// information about the objects in the database.
// Query the sysroutines table to get the implementation
// language of the routine named OUT_LANGUAGE.
DB2Command myCommand = DB2Context.GetCommand();
myCommand.CommandText = "SELECT LANGUAGE "
+ "FROM sysibm.sysroutines "
+ "WHERE routinename = '" + routineName + "'";
try
{
reader = myCommand.ExecuteReader();
if (reader.Read())
{
// Set the output parameter value and
// indicate that it is a non-NULL value
outLang = reader.GetString(0);
}
else
{
// Set the output parameter to an empty string.
// Set the returnCode tonull value
// Set the SQLSTATE to a user-defined state
// and the diagnostic message string with
// a meaningful error message - these will
// be passed back to the client in the SQLCA.
outLang = "";
outReturnCode = -1;
outErrorMsg = "ERR1: OUT_LANGUAGE not found";
}
}
catch (Exception e)
{
outLang = "";
outReturnCode = -1;
outErrorMsg = e.Message;
return;
}
finally
{
// Close the reader
if (reader != null)
{
reader.Close();
}
}
} // OutLanguage procedure
/***************************************************************
Routine: OutParameter
Purpose: Sorts table STAFF by salary, locates and returns
the median salary
Shows how to:
- define OUT parameters in PARAMETER STYLE GENERAL
- set an ouput parameter
- set an output return code
- set an output string parameter to indicate last
SQL statement executed when an error is caught
(useful for debugging)
Parameters:
IN: (none)
OUT: outMedianSalary - median salary in table STAFF
outReturnCode - A return code to indicate caught errors
outErrorMsg - To hold an optional error message
*****************************************************************/
public static void OutParameter(out decimal outMedianSal,
out int outReturnCode,
out string outErrorMsg)
{
DB2DataReader reader = null;
DB2Command cmd;
Int32 numRecords;
Int32 counter = 0;
outReturnCode = 0;
outErrorMsg = "";
outMedianSal = 0;
cmd = DB2Context.GetCommand();
cmd.CommandText = "SELECT COUNT(*) "
+ "FROM staff";
try
{
reader = cmd.ExecuteReader();
reader.Read();
numRecords = reader.GetInt32(0);
}
catch
{
outReturnCode = -1;
outErrorMsg = "ERR1: Getting count(*) from staff";
return;
}
if (numRecords == 0)
{
outReturnCode = -1;
outErrorMsg = "ERR2: No records in staff table";
return;
}
reader.Close();
cmd = DB2Context.GetCommand();
cmd.CommandText = "SELECT salary "
+ "FROM staff "
+ "ORDER BY salary";
try
{
reader = cmd.ExecuteReader();
}
catch
{
outReturnCode = -1;
outErrorMsg = "ERR3: Getting records in staff";
return;
}
if (!reader.HasRows)
{
outReturnCode = -1;
outErrorMsg = "ERR4: No records in staff table";
return;
}
try
{
// Move to record in the median position
while (counter < ((Int32)(numRecords/2) + 1))
{
reader.Read();
counter++;
}
}
catch
{
outReturnCode = -1;
outErrorMsg = "ERR5: Iterating over records";
return;
}
// Set the output to the median salary
outMedianSal = reader.GetDecimal(0);
if (reader != null)
{
reader.Close();
}
} // OutParameter procedure
/***************************************************************
Routine: InParameters
Purpose: Updates the salaries of employees in department
'indept' using inputs inLowSal, inMedSal, inHighSal
as salary salary adjustment values.
Shows how to:
- define IN parameters using PARAMETER STYLE GENERAL
- execute SQL operations on the database
- catch exceptions and return an output returncode
- return an error message with a return code
Parameters:
IN: inLowSal - new salary for low salary employees
inMedSal - new salary for mid salary employees
inHighSal - new salary for high salary employees
inDept - department to use in SELECT predicate
outReturnCode - A return code to indicate caught errors
outErrorMsg - To hold an optional error message
*****************************************************************/
public static void InParameters( decimal inLowSal,
decimal inMedSal,
decimal inHighSal,
string inDept,
out int outReturnCode,
out string outErrorMsg)
{
Int32 numRecords = 0;
DB2Parameter parm;
DB2DataReader reader = null;
DB2Command cmd;
outReturnCode = 0;
outErrorMsg = "";
// Check input parameters
if (inLowSal < 0 ||
inMedSal < 0 ||
inHighSal < 0)
{
outReturnCode = -1;
outErrorMsg = "ERR1: Bad input: salaries < 0";
return;
}
cmd = DB2Context.GetCommand();
try
{
cmd.CommandText = "SELECT COUNT(*) "
+ "FROM employee "
+ "WHERE workdept = ?";
parm = cmd.Parameters.Add("@inDept", DB2Type.Char, 3);
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@inDept"].Value = inDept;
reader = cmd.ExecuteReader();
reader.Read();
numRecords = reader.GetInt32(0);
}
catch
{
outReturnCode = -1;
outErrorMsg = "ERR2: Reading COUNT(*)";
return;
}
if (numRecords == 0)
{
outReturnCode = -1;
outErrorMsg = "ERR3: No records satisfy query";
return;
}
reader.Close();
try
{
// Use the MERGE statement to update employee salaries
// based on update criteria.
cmd.CommandText = "MERGE INTO EMPLOYEE as T "
+ "USING (SELECT empno, salary "
+ "FROM employee "
+ "WHERE workdept = ?) as S "
+ "ON T.empno = S.empno "
+ "WHEN MATCHED THEN "
+ "UPDATE SET salary = "
+ "(CASE WHEN salary < ? THEN ? "
+ " WHEN salary < ? THEN ? "
+ " WHEN salary < ? THEN ? "
+ " ELSE salary * 1.10 "
+ " END)";
// Set up parameter marker information for the query
cmd.Parameters.Clear();
parm = cmd.Parameters.Add("@inDept", DB2Type.Char, 3);
parm.Direction = ParameterDirection.Input;
parm = cmd.Parameters.Add("@inLowSal", DB2Type.Decimal, 9);
parm.Direction = ParameterDirection.Input;
parm = cmd.Parameters.Add("@inLowSalSet", DB2Type.Decimal, 9);
parm.Direction = ParameterDirection.Input;
parm = cmd.Parameters.Add("@inMedSal", DB2Type.Decimal, 9);
parm.Direction = ParameterDirection.Input;
parm = cmd.Parameters.Add("@inMedSalSet", DB2Type.Decimal, 9);
parm.Direction = ParameterDirection.Input;
parm = cmd.Parameters.Add("@inHighSal", DB2Type.Decimal, 9);
parm.Direction = ParameterDirection.Input;
parm = cmd.Parameters.Add("@inHighSalSet", DB2Type.Decimal, 9);
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@inDept"].Value = inDept;
cmd.Parameters["@inLowSal"].Value = inLowSal;
cmd.Parameters["@inLowSalSet"].Value = inLowSal;
cmd.Parameters["@inMedSal"].Value = inMedSal;
cmd.Parameters["@inMedSalSet"].Value = inMedSal;
cmd.Parameters["@inHighSal"].Value = inHighSal;
cmd.Parameters["@inHighSalSet"].Value = inHighSal;
cmd.ExecuteNonQuery();
}
catch
{
outReturnCode = -1;
outErrorMsg = "ERR4: Updating employee table";
return;
}
} // InParameters procedure
/***************************************************************
Routine: InOutParameter
Purpose: Calculates the median salary of all salaries above
the input median salary.
Shows how to:
- define INOUT/OUT parameters with
PARAMETER STYLE GENERA
- catch errors and set an output return code
- set an output string parameter to indicate where
error was caught (useful for debugging)
Parameters:
IN/OUT: inOutMedian - median salary;
(input value used in SELECT predicate
output set to median salary found)
outReturnCode - A return code to indicate caught errors
outErrorMsg - To hold an optional error message
*******************************************************************/
public static void InOutParameter(ref decimal inoutMedianSal,
out int outReturnCode,
out string outErrorMsg)
{
int counter = 0;
Int32 numRecords = 0;
DB2Parameter parm;
DB2DataReader reader;
DB2Command cmd;
outReturnCode = 0;
outErrorMsg = "";
try
{
cmd = DB2Context.GetCommand();
cmd.CommandText = "SELECT COUNT(*)"
+ " FROM staff "
+ " WHERE salary > ? ";
parm = cmd.Parameters.Add("@inoutMedianSal", DB2Type.Decimal, 7);
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@inoutMedianSal"].Value = inoutMedianSal;
reader = cmd.ExecuteReader();
reader.Read();
numRecords = reader.GetInt32(0);
reader.Close();
}
catch
{
outReturnCode = -1;
outErrorMsg = "ERR1: COUNT(*) from staff failed";
return;
}
if (numRecords == 0)
{
outReturnCode = -1;
outErrorMsg = "ERR2: No records satisfy query";
return;
}
try
{
cmd.CommandText = "SELECT salary"
+ " FROM staff "
+ " WHERE salary > ?"
+ " ORDER BY salary";
// Reuse existing parameter marker values
cmd.Parameters.Clear();
parm = cmd.Parameters.Add("@inoutMedianSal2", DB2Type.Decimal, 7);
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@inoutMedianSal2"].Value = inoutMedianSal;
reader = cmd.ExecuteReader();
while (counter < (numRecords / 2 + 1))
{
reader.Read();
counter++;
}
}
catch
{
outReturnCode = -1;
outErrorMsg = " ERR3: Iterating records in reader";
return;
}
try
{
// Set the input/output parameter value before returning
inoutMedianSal = reader.GetDecimal(0);
}
catch
{
outReturnCode = -1;
outErrorMsg = " ERR4: Reading median salary";
return;
}
finally
{
reader.Close();
}
} // InOutParameters procedure
/***************************************************************
Routine: OneResultSetToClient
Purpose: Return a resultset to the caller that contains
records of employees with salaries
greater than the value of input parameter insalary.
Shows how to:
- define IN parameters in PARAMETER STYLE GENERAL
- return a result set to the client
- catch errors, and if any occur, set an output
return code and optionally set an error message.
Parameters:
IN: inSalary - salary to be used in SQL query
OUT: outReturnCode - A return code to indicate caught errors
outErrorMsg - To hold an optional error message
*****************************************************************/
public static void OneResultSetToClient( decimal inSalary,
out int outReturnCode,
out string outErrorMsg)
{
DB2Parameter parm;
outReturnCode = 0;
outErrorMsg = "";
DB2DataReader reader = null;
try
{
// Get a command object from the thread's context object
DB2Command cmd = DB2Context.GetCommand();
// Get a resultset
cmd.CommandText = "SELECT name, job, salary"
+ " FROM staff "
+ " WHERE salary > ?"
+ " ORDER BY salary";
parm = cmd.Parameters.Add("@inSalary", DB2Type.Decimal, 7);
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@inSalary"].Value = inSalary;
reader = cmd.ExecuteReader();
}
catch
{
outReturnCode = -1;
outErrorMsg = "ERR1: Error on SELECT from staff";
return;
}
// The DB2DataReader contains the result of the query.
// This result set can be returned with the procedure,
// by simply NOT closing the DB2DataReader.
// Specifically, do NOT execute reader.Close();
} // OneResultSetToClient
/***************************************************************
Routine: ClobExtract
Purpose: Copies a portion of a resume in a CLOB data type
into an output VARCHAR parameter. The portion
of the resume pertains to Department Information.
Shows how to:
- define IN and OUT parameters in STYLE GENERAL
- locate information within a formatted clob
- extract information from within a clob and
copy it to an output parameter
IN: empNumber - employee number
OUT: outDept - department number
outReturnCode - A return code to indicate caught errors
outErrorMsg - To hold an optional error message
*****************************************************************/
public static void ClobExtract( string inEmpNo,
out string outDeptInfo,
out int outReturnCode,
out string outErrorMsg)
{
DB2Parameter parm;
DB2DataReader reader = null;
int depPos = 0;
int eduPos = 0;
string strclob;
outDeptInfo = "";
outReturnCode = 0;
outErrorMsg = "";
try
{
DB2Command cmd = DB2Context.GetCommand();
cmd.CommandText = "SELECT resume"
+ " FROM emp_resume"
+ " WHERE empno = ?"
+ " AND resume_format = 'ascii'";
parm = cmd.Parameters.Add("@empNo", DB2Type.Char, 6);
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@empNo"].Value = inEmpNo;
reader = cmd.ExecuteReader();
}
catch
{
outReturnCode = -1;
outErrorMsg = "ERR1: SELECT resume from emp_resume failed";
if (reader != null)
{
reader.Close();
}
return;
}
if (!reader.HasRows)
{
outDeptInfo = ("\nEmployee " + inEmpNo + " does not have a resume.");
reader.Close();
return;
}
else
{
try
{
reader.Read();
// Get the data as a string
strclob = reader.GetString(0);
// Locate the position of Department related information
depPos = strclob.IndexOf("Department Information", 1);
eduPos = strclob.IndexOf("Education", (int)depPos);
if (depPos == -1)
{
outDeptInfo = "Resume does not contain a Department Info section.";
}
else
{
// Get the information between Department Info and Education
outDeptInfo = strclob.Substring(depPos, eduPos - depPos);
}
}
catch
{
outReturnCode = -1;
outErrorMsg = "ERR2: Accessing portions of resume";
if (reader != null)
{
reader.Close();
}
return;
}
}
} // ClobExtract procedure
/***************************************************************
Routine: AllDataTypes
Purpose: Take each parameter and set it to a new output value.
If an error is caught, indicate this with an output
parameter for the return code and optionally also
set an output parameter with an error message.
Shows how to:
- define INOUT/OUT parameters in PARAMETER STYLE GENERAL
- assign output values to INOUT/OUT parameters
INOUT: smallInOut, intInOut, bigInOut, realInOut
OUT: charOut, charsOut, varcharOut, dateOut, timeOut,
outReturnCode, outErrorMsg
*****************************************************************/
public static void AllDataTypes(
ref Int16 inoutSmallint, // SMALLINT
ref Int32 inoutInteger, // INTEGER
ref Int64 inoutBigint, // BIGINT
ref Single inoutReal, // REAL
ref double inoutDouble, // DOUBLE
out string outChar, // CHAR(1)
out string outChars, // CHAR(15)
out string outVarchar, // VARCHAR(12)
out DateTime outDate, // DATE
out TimeSpan outTime, // TIME
out int outReturnCode,
out string outErrorMsg)
{
// Initialize output parameters
DB2DataReader reader = null;
DB2Command cmd;
outChar = "";
outChars = "";
outVarchar = "";
outDate = new DateTime(1,1,1);
outTime = new TimeSpan(1,1,1);
outReturnCode = 0;
outErrorMsg = "";
// Use the input value of inputSmallint
// to determine its output value
if (inoutSmallint == 0)
{
inoutSmallint = 1;
}
else
{
inoutSmallint = (Int16)(inoutSmallint / 2);
}
// Use the input value of inoutInteger
// to set its output value
if (inoutInteger == 0)
{
inoutInteger = 1;
}
else
{
inoutInteger = (Int32)(inoutInteger / 2);
}
// Use the input value of inoutBigint
// to set its output value
if (inoutBigint == 0)
{
inoutBigint = 1;
}
else
{
inoutBigint = (Int64)(inoutBigint / 2);
}
// Use the input value of inoutReal
// to set its output value
if (inoutReal == 0)
{
inoutReal = 1;
}
else
{
inoutReal = (inoutReal / 2);
}
// Use the input value of inoutDouble
// to set its output value
if (inoutDouble == 0)
{
inoutDouble = 1;
}
else
{
inoutDouble = (inoutDouble / 2);
}
try
{
// Query table employee
cmd = DB2Context.GetCommand();
cmd.CommandText = "SELECT midinit, firstnme, lastname"
+ " FROM employee"
+ " WHERE empno = '000180'";
reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
outReturnCode = -1;
outErrorMsg = "No record with empNo='000180'";
return;
}
reader.Read();
}
catch
{
outReturnCode = -1;
outErrorMsg = "ERR1: Selecting/reading employee data";
reader.Close();
return;
}
// Set outChar, outChars, outVarchars with values
// from the query result set
outChar = reader.GetString(0); // INITIAL
outChars = reader.GetString(2); // EMPLOYEE LAST NAME
outVarchar = reader.GetString(1); // EMPLOYEE FIRST NAME
reader.Close();
try
{
// Set outDate to the current date (retrieved by a query)
cmd.CommandText = "VALUES(CURRENT DATE)";
reader = cmd.ExecuteReader();
reader.Read();
outDate = reader.GetDate(0);
reader.Close();
}
catch
{
outReturnCode = -1;
outErrorMsg = "ERR2: Getting current date failed";
reader.Close();
return;
}
try
{
// Set outTime to the current time (retrieved by a query)
cmd.CommandText = "VALUES(CURRENT TIME)";
reader = cmd.ExecuteReader();
reader.Read();
outTime = reader.GetTimeSpan(0);
reader.Close();
}
catch
{
outReturnCode = -1;
outErrorMsg = "ERR2: Getting current date failed";
reader.Close();
return;
}
} // AllDataTypes procedure
} // SpServer class