// Source File Name: Stclient.java %
//
// Licensed Materials -- Property of IBM
//
// (c) Copyright International Business Machines Corporation, 1998, 2000.
// All Rights Reserved.
//
// US Government Users Restricted Rights -
// Use, duplication or disclosure restricted by
// GSA ADP Schedule Contract with IBM Corp.
// Sample Program Stclient - Client Application for Stored Procedure
// To run this sample, perform the following steps:
// (1) create and populate the SAMPLE database (db2sampl)
// (2) compile Stserver (javac Stserver.java)
// (3) copy Stserver.class to the sqllib/function directory
// (4) register the stored procedures using the Spcreate.db2 CLP script:
// (i) db2 connect to sample
// (ii) db2 -td@ -vf Spcreate.db2
// (3) compile Stclient (javac Stclient.java)
// (4) run Stclient (java Stclient)
// NOTES: (1) The jdk11_path database manager configuration parameter must
// be set on the server
// (2) The CLASSPATH and shared library path environment variables
// must be set, as for any JDBC application
// (3) Visit http://www.software.ibm.com/data/db2/java
// for current DB2 Java information
// For more information about this sample, refer to the README file.
// For more information on Programming in Java, refer to the
// "Programming in Java" section of the Application Development Guide.
// For more information on creating stored procedures, refer to the
// "Writing Stored Procedures" section of the Application Development Guide.
// For more information on building and running Java programs for DB2,
// refer to the "Building Java Applets and Applications" section of the
// Application Building Guide.
// For more information on the SQL language, refer to the SQL Reference.
// Stclient calls twelve methods that call stored procedures:
// (1) callOutLanguage: returns language of the stored procedure library
// Parameter types used: OUT CHAR(8)
// (2) callOutParameter: return median salary of EMPLOYEE table
// Parameter types used: OUT DOUBLE
// OUT INTEGER
// OUT CHAR(32)
// (3) callInParameters: accept 3 values and update salaries in EMPLOYEE table
// accordingly
// Parameter types used: IN DOUBLE
// IN CHAR(3)
// OUT INTEGER
// OUT CHAR(32)
// (4) callIncallOutParameter: accept an input value and return the median
// salary of the EMPLOYEE table for employees who make more
// than the input value. Demonstrates how to use null indicators
// in a client application.
// Parameter style (C): GENERAL WITH NULLS
// Parameter style (Java): JAVA
// Parameter style (SQL): SQL
// Parameter types used: INOUT DOUBLE
// OUT INTEGER
// OUT CHAR(32)
// (5) callOneResultSet: return a result set to the client application
// Parameter types used: IN DOUBLE
// OUT INTEGER
// (6) callTwoResultSets: return two result sets to the client application
// Parameter types used: IN DOUBLE
// OUT INTEGER
// (7) callClobExtract: return a sub-section of a CLOB data type
// Parameter types used: IN CHAR(6)
// OUT VARCHAR(1000)
// OUT INTEGER
// (8) callDecimalType: pass and receive a DECIMAL data type from a
// stored procedure
// Parameter types used: INOUT DECIMAL
// (9) callAllDataTypes: use 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
// (10) callDB2SQL: calls a DB2SQL parameter style stored procedure
// Parameter style: DB2SQL
// Parameter types used: IN CHAR(8)
// OUT DOUBLE
// (11) callDBINFO: calls a stored procedure that receives a DBINFO
// structure and returns elements of the structure to the client
// Parameter types used: IN CHAR(8)
// OUT DOUBLE
// OUT CHAR(128)
// OUT CHAR(8)
// OUT INTEGER
// (12) callProgramTypeMain: calls a stored procedure implemented with
// PROGRAM TYPE MAIN parameter passing style
// Parameter types used: IN CHAR(8)
// OUT DOUBLE
import java.sql.*; // JDBC classes /* :rk.1:erk. */
import java.math.BigDecimal; // BigDecimal support for packed decimal type
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
class Stclient
{
static
{
try
{
System.out.println();
System.out.println("Java Stored Procedure Sample");
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
}
catch (Exception e)
{
System.out.println("\nError loading DB2 Driver...\n");
e.printStackTrace();
}
}
public static void main(String argv[])
{
Connection con = null;
// URL is jdbc:db2:dbname
String url = "jdbc:db2:sample";
double outMedian = 0;
String language = "";
try
{
if (argv.length == 0) {
// connect to sample database
// connect with default id/password
con = DriverManager.getConnection(url); /* :rk.2:erk. */
}
else if (argv.length == 2) {
// connect to sample database
// connect with user-provided username and password
String userid = argv[0];
String passwd = argv[1];
con = DriverManager.getConnection(url, userid, passwd);
}
else if (argv.length == 3) {
// connect to user-provided database
url = "jdbc:db2:" + argv[2];
String userid = argv[0];
String passwd = argv[1];
// connect with user-provided username and password
con = DriverManager.getConnection(url, userid, passwd);
}
else {
System.out.println("\nUsage: java Stclient "
+ "[username password [database]]\n");
System.exit(0);
}
// Set the default context
DefaultContext ctx = new DefaultContext(con);
DefaultContext.setDefaultContext(ctx);
// turn off autocommit
con.setAutoCommit(false); /* :rk.3:erk. */
language = callOutLanguage(con);
outMedian = callOutParameter(con);
callInParameters(con);
callInoutParameter(con, outMedian);
// Cause the stored procedure to return a NOT FOUND error
callInoutParameter(con, 99999.99);
callOneResultSet(con, outMedian, ctx);
callTwoResultSets(con, outMedian, ctx);
// SQL procedures cannot handle LOB data types
if (language.trim().equals("SQL"))
{
System.out.println("\nStored procedures are implemented in SQL,\n" +
"which cannot handle LOB datatypes.\n" +
"Skipping the call to CLOB_EXTRACT.\n");
}
else if (language.trim().equals("C"))
{
/* Warn the user that the CLI stored procedure
* requires a change to the UDF_MEM_SZ variable */
System.out.println(
"\n\n If the CLOB EXTRACT stored procedure is implemented\n" +
" using CLI, you must increase the value of the UDF_MEM_SZ\n" +
" database manager configuration variable to at least two\n" +
" pages larger than the size of the input arguments and\n" +
" the result of the stored procedure. To do this, issue\n" +
" the following command from the CLP:\n" +
" db2 UPDATE DBM CFG USING UDF_MEM_SZ 2048\n" +
" For the change to take effect, you must then stop and\n" +
" restart the DB2 server by issuing the following\n" +
" commands from the CLP:\n" +
" db2stop\n" +
" db2start");
callClobExtract("000140", con);
}
else if (language.trim().equals("JAVA"))
{
callClobExtract("000140", con);
}
// Java and SQL procedures can handle DECIMAL data types
if (language.trim().equals("SQL") || language.trim().equals("JAVA"))
{
callDecimalType(con);
}
else if (language.trim().equals("C"))
{
// C does not provide a native data type for decimal
// values, so neither a C client application nor a
// C stored procedure can pass DECIMAL SQL data types.
//
// Do not call the DECIMAL_TYPE stored procedure,
// because the stored procedure is written in C.
}
callAllDataTypes(con);
// the following stored procedures can only be implemented in C/C++
if (language.trim().equals("C"))
{
callDB2SQL("CLERK", con);
callDB2SQL("", con);
callDBINFO("MANAGER", con);
callProgramTypeMain("DESIGNER", con);
}
// roll back any changes to the database
con.rollback(); /* :rk.8:erk. */
con.close();
}
catch (Exception e)
{
try
{
con.rollback();
con.close();
}
catch (Exception x)
{ }
e.printStackTrace ();
}
} // end main
public static String callOutLanguage(Connection con) throws SQLException
{
String out_lang = "";
try
{
String procName = "OUT_LANGUAGE";
System.out.println ("\nCall stored procedure named " + procName);
#sql { CALL OUT_LANGUAGE (:out out_lang) };
System.out.println ("Stored procedures are implemented in language "
+ out_lang);
}
catch (SQLException sqle)
{
System.out.println(sqle);
}
return(out_lang);
}
public static double callOutParameter(Connection con) throws SQLException
{
double median = 0;
try
{
int outErrorCode = 0;
String outErrorLabel = "";
String procName = "OUT_PARAM";
// call the stored procedure
System.out.println ("\nCall stored procedure named " + procName);
#sql { CALL OUT_PARAM(:out median, :out outErrorCode, :out outErrorLabel) };
if (outErrorCode == 0)
{
System.out.println(procName + " completed successfully");
System.out.println ("Median salary returned from " + procName + " = "
+ median);
}
else
{ // stored procedure failed
System.out.println(procName + " failed with SQLCODE "
+ outErrorCode);
System.out.println(procName + " failed at " + outErrorLabel);
}
}
catch (SQLException sqle)
{
System.out.println(sqle);
}
return(median);
}
public static void callInParameters(Connection con) throws SQLException
{
double sumSalary, inSalary1, inSalary2, inSalary3;
int outErrorCode = 0;
String outErrorLabel = "";
String inDept;
try
{
// prepare the CALL statement for IN_PARAMS
String procName = "IN_PARAMS";
inDept = "E11";
// Display total salary before calling IN_PARAMS
#sql { SELECT SUM(salary) INTO :sumSalary FROM employee WHERE workdept = :inDept };
System.out.println("\nSum of salaries for dept. E11 = "
+ sumSalary + " before " + procName);
// call the stored procedure
inSalary1 = 15000;
inSalary2 = 20000;
inSalary3 = 25000;
System.out.println ("\nCall stored procedure named " + procName);
#sql { CALL IN_PARAMS (:in inSalary1, :in inSalary2, :in inSalary3, :in inDept, :outErrorCode, :out outErrorLabel) };
if (outErrorCode == 0)
{
System.out.println(procName + " completed successfully");
// Display total salary after calling IN_PARAMS
#sql { SELECT SUM(salary) INTO :sumSalary FROM employee WHERE workdept = :inDept };
System.out.println("\nSum of salaries for dept. E11 = "
+ sumSalary + " after " + procName);
}
else
{ // stored procedure failed
System.out.println(procName + " failed with SQLCODE " + outErrorCode);
System.out.println(procName + " failed at " + outErrorLabel.trim());
}
}
catch (SQLException sqle)
{
System.out.println(sqle);
}
}
public static void callInoutParameter(Connection con, double median) throws SQLException
{
double inoutMedian;
int outErrorCode = 0;
String outErrorLabel = "";
try
{
String procName = "INOUT_PARAM";
inoutMedian = median;
// call the stored procedure
System.out.println ("\nCall stored procedure named " + procName);
if (median == 99999.99)
{
System.out.println("with an input value that causes a NOT FOUND error");
}
#sql { CALL INOUT_PARAM(:inout inoutMedian, :out outErrorCode, :out outErrorLabel) };
if (outErrorCode == 0) {
System.out.println(procName + " completed successfully");
System.out.println("Median salary returned from " + procName + " = "
+ inoutMedian);
}
else { // stored procedure failed
System.out.println(procName + " failed with SQLCODE " + outErrorCode);
System.out.println(procName + " failed at " + outErrorLabel.trim());
}
}
catch (SQLException sqle)
{
System.out.println(sqle);
}
}
public static void callOneResultSet(Connection con, double median, DefaultContext ctx) throws SQLException
{
int outErrorCode = 0;
ResultSet rs;
ExecutionContext execCtx = ctx.getExecutionContext();
// prepare the CALL statement for ONE_RESULT_SET
String procName = "ONE_RESULT_SET";
// call the stored procedure
System.out.println ("\nCall stored procedure named " + procName);
#sql { CALL ONE_RESULT_SET (:in median, :out outErrorCode) };
if (outErrorCode == 0)
{
System.out.println(procName + " completed successfully");
if ((rs = execCtx.getNextResultSet()) != null)
{
fetchAll(rs);
// close ResultSet
rs.close();
}
}
else { // stored procedure failed
System.out.println(procName + " failed with SQLCODE " + outErrorCode);
}
}
public static void callTwoResultSets(Connection con, double median, DefaultContext ctx) throws SQLException
{
int outErrorCode = 0;
ResultSet rs;
ExecutionContext execCtx = ctx.getExecutionContext();
// prepare the CALL statement for TWO_RESULT_SETS
String procName = "TWO_RESULT_SETS";
// call the stored procedure
System.out.println ("\nCall stored procedure named " + procName);
#sql { CALL TWO_RESULT_SETS (:in median, :out outErrorCode) };
if (outErrorCode == 0)
{
System.out.println(procName + " completed successfully");
if ((rs = execCtx.getNextResultSet()) != null)
{
System.out.println("Result set 1: Employees who make more than " + median);
fetchAll(rs);
}
if ((rs = execCtx.getNextResultSet()) != null)
{
System.out.println("\nResult set 2: Employees who make less than " + median);
fetchAll(rs);
// close ResultSet
rs.close();
}
}
else { // stored procedure failed
System.out.println(procName + " failed with SQLCODE " + outErrorCode);
}
}
public static void callClobExtract(String empNo, Connection con)
{
String inEmpNo, outInterests;
int outErrorCode = 0;
inEmpNo = empNo;
try
{
String procName = "CLOB_EXTRACT";
// call the stored procedure
System.out.println ("\nCall stored procedure named " + procName);
#sql { CALL CLOB_EXTRACT (:in inEmpNo, :out outInterests, :out outErrorCode) };
if (outErrorCode == 0)
{
System.out.println(procName + " completed successfully");
System.out.println ("Resume section returned for employee "
+ inEmpNo + " =\n" + outInterests);
}
else
{ // stored procedure failed
System.out.println(procName + " failed with SQLCODE "
+ outErrorCode);
System.out.println("from procedure section labelled "
+ outInterests);
}
}
catch (SQLException e)
{
System.out.println(e);
}
}
public static void callDecimalType(Connection con)
{
int outErrorCode = 0;
String outErrorLabel = "";
try
{
String procName = "DECIMAL_TYPE";
// declare and initialize input variable
BigDecimal inoutDecimal = new BigDecimal("400000.00");
// call the stored procedure
System.out.println ("\nCall stored procedure named " + procName);
#sql { CALL DECIMAL_TYPE (:inout inoutDecimal, :out outErrorCode, :out outErrorLabel) };
if (outErrorCode == 0) {
System.out.println(procName + " completed successfully");
System.out.println("Value of DECIMAL = " + inoutDecimal);
}
else { // stored procedure failed
System.out.println(procName + " failed with SQLCODE " + outErrorCode);
System.out.println(" from procedure section labelled " + outErrorLabel);
}
}
catch (SQLException e)
{
System.out.println(e);
}
}
public static void callAllDataTypes(Connection con)
{
int outErrorCode = 0;
String outErrorLabel = "";
try
{
String procName = "ALL_DATA_TYPES";
// declare and initialize input variables
short inoutSmallint = 32000;
int inoutInteger = 2147483000;
long inoutBigint = 2147480000;
float inoutReal = 100000;
double inoutDouble = 2500000;
// declare output variables
String outChar, outChars, outVarchar;
Date outDate;
Time outTime;
// CALL stored procedure ALL_DATA_TYPES
System.out.println ("\nCall stored procedure named " + procName);
#sql { CALL ALL_DATA_TYPES (:inout inoutSmallint, :inout inoutInteger, :inout inoutBigint, :inout inoutReal, :inout inoutDouble, :out outChar, :out outChars, :out outVarchar, :out outDate, :out outTime, :out outErrorCode, :out outErrorLabel) };
if (outErrorCode == 0) {
System.out.println(procName + " completed successfully");
System.out.println("Value of SMALLINT = " + inoutSmallint);
System.out.println("Value of INTEGER = " + inoutInteger);
System.out.println("Value of BIGINT = " + inoutBigint);
System.out.println("Value of REAL = " + inoutReal);
System.out.println("Value of DOUBLE = " + inoutDouble);
System.out.println("Value of CHAR(1) = " + outChar);
System.out.println("Value of CHAR(15) = " + outChars.trim());
System.out.println("Value of VARCHAR(12) = " + outVarchar.trim());
System.out.println("Value of DATE = " + outDate);
System.out.println("Value of TIME = " + outTime);
}
else { // stored procedure failed
System.out.println(procName + " failed with SQLCODE " + outErrorCode);
System.out.println(" from procedure section labelled " + outErrorLabel);
}
}
catch (SQLException e)
{
System.out.println(e);
}
}
public static void callDB2SQL(String job, Connection con)
{
try
{
double median = 0;
String inJob;
inJob = job;
String procName = "DB2SQL_EXAMPLE";
// CALL stored procedure DB2SQL_EXAMPLE
System.out.println ("\nCall stored procedure named " + procName);
#sql { CALL DB2SQL_EXAMPLE (:in inJob, :out median) };
System.out.println(procName + " completed successfully");
System.out.println ("Median salary returned from " + procName + " = "
+ median);
}
catch (SQLException e)
{
// Note that the JDBC specification only allows us to
// get the SQLSTATE as a method called on an instance of
// an SQLException. If you call DB2SQL_EXAMPLE with
// null input, so that the stored procedure returns the
// custom SQLSTATE ('38100') and message, the Java client
// considers it a successful call because the returned SQLCODE = 0
// and an SQLException is not raised.
System.out.println(e);
}
}
public static void callDBINFO(String job, Connection con)
throws SQLException
{
double averageSalary = 0;
int outErrorCode = 0;
String inJob, databaseName, databaseVersion;
String procName = "DBINFO_EXAMPLE";
inJob = job;
// call the stored procedure
System.out.println ("\nCall stored procedure named " + procName);
#sql {CALL DBINFO_EXAMPLE (:in inJob, :out averageSalary, :out databaseName, :out databaseVersion, :out outErrorCode) };
if (outErrorCode == 0) {
System.out.println(procName + " completed successfully");
System.out.println ("Average salary for job " + inJob + " = "
+ averageSalary);
System.out.println("Database name from DBINFO structure = "
+ databaseName.trim());
System.out.println("Database version from DBINFO structure = "
+ databaseVersion.trim());
}
else { // stored procedure failed
System.out.println(procName + " failed with SQLCODE "
+ outErrorCode);
}
}
public static void callProgramTypeMain(String job, Connection con)
throws SQLException
{
String inJob;
double averageSalary = 0;
int outErrorCode = 0;
inJob = job;
String procName = "MAIN_EXAMPLE";
// call stored procedure MAIN_EXAMPLE
System.out.println ("\nCall stored procedure named " + procName);
#sql { CALL MAIN_EXAMPLE (:in inJob, :out averageSalary, :out outErrorCode) };
if (outErrorCode == 0) {
System.out.println(procName + " completed successfully");
System.out.println ("Average salary for job " + inJob + " = "
+ averageSalary);
}
else { // stored procedure failed
System.out.println(procName + " failed with SQLCODE "
+ outErrorCode);
}
}
// ======================================================
// Method: fetchAll -- returns all rows from a result set
// ======================================================
public static void fetchAll( ResultSet rs )
{ try
{ System.out.println("=================================================================");
ResultSetMetaData stmtInfo = rs.getMetaData();
int numOfColumns = stmtInfo.getColumnCount();
int r = 0;
while( rs.next() )
{ r++;
System.out.print("Row: " + r + ": ");
for( int i=1; i <= numOfColumns; i++ )
{ System.out.print(rs.getString(i));
if( i != numOfColumns ) System.out.print(" , ");
}
System.out.println("");
}
}
catch (SQLException e)
{ System.out.println("Error: fetchALL: exception");
System.out.println (e);
}
}
}