// Source File Name: Spserver.java 1.5
//
// Licensed Materials -- Property of IBM
//
// (c) Copyright International Business Machines Corporation, 1998, 1999.
// All Rights Reserved.
//
// US Government Users Restricted Rights -
// Use, duplication or disclosure restricted by
// GSA ADP Schedule Contract with IBM Corp.
// Sample Program Spserver - Stored Procedure
// Steps to run the sample:
// (1) create and populate the SAMPLE database (db2sampl)
// (2) compile Spserver (javac Spserver.java)
// (3) copy Spserver.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 Spclient (javac Spclient.java)
// (4) run Spclient (java Spclient)
// NOTES: (1) The jdk11_path database manager configuration parameter must
// be set
// (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.
// Class Spserver contains nine methods:
// (1) outLanguage: return language of the stored procedure library
// (2) outParameter: return median salary of EMPLOYEE table
// (3) inParams: accept 3 values and update salaries in EMPLOYEE table
// accordingly
// (4) inoutParam: accept 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) twoResultSets: return two result sets to the client application
// (7) clobExtract: return a section of a CLOB type as a string
// (8) decimalType: manipulates an INOUT DECIMAL parameter
// (9) allDataTypes: use all of the common data types in a stored procecure
import java.sql.*; // JDBC classes
import java.io.*; // Input/Output classes
import java.math.BigDecimal; // Packed Decimal class
// /////
// Java stored procedure is in this class
// /////
public class Spserver
{
public static void outLanguage (String[] outLang) throws Exception
{
try
{
String procName;
// Initialize variables
procName = "OUT_LANGUAGE";
// Get caller's connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connection");
String query = "SELECT language FROM syscat.procedures "
+ "WHERE procname = ? ";
PreparedStatement stmt = con.prepareStatement(query);
stmt.setString(1, procName);
ResultSet rs = stmt.executeQuery();
// move to first row of result set
rs.next();
// set value for the output parameter
outLang[0] = rs.getString(1);
// clean up resources
rs.close();
stmt.close();
con.close();
}
catch (Exception e)
{ throw e;
}
}
public static void outParameter (double[] medianSalary,
int[] errorCode, String[] errorLabel) throws SQLException /* :rk.1:erk. */
{
try
{
int counter, numRecords;
// Initialize variables
counter = 0;
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
// Get caller's connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connection");
errorLabel[0] = "GET CONNECTION";
String query = "SELECT COUNT(*) FROM staff";
errorLabel[0] = "PREPARE COUNT STATEMENT";
PreparedStatement stmt = con.prepareStatement(query);
errorLabel[0] = "GET COUNT RESULT SET";
ResultSet rs = stmt.executeQuery();
// move to first row of result set
rs.next();
// set value for the output parameter
errorLabel[0] = "GET NUMBER OF RECORDS";
numRecords = rs.getInt(1); /* :rk.3:erk. */
// clean up first result set
rs.close();
stmt.close();
// get salary result set
query = "SELECT CAST(salary AS DOUBLE) FROM staff "
+ "ORDER BY salary";
errorLabel[0] = "PREPARE SALARY STATEMENT";
PreparedStatement stmt2 = con.prepareStatement(query);
errorLabel[0] = "GET SALARY RESULT SET";
ResultSet rs2 = stmt2.executeQuery(); /* :rk.2:erk. */
while (counter < (numRecords / 2 + 1))
{
errorLabel[0] = "MOVE TO NEXT ROW";
rs2.next(); /* :rk.4:erk. */
counter++;
}
errorLabel[0] = "GET MEDIAN SALARY";
medianSalary[0] = rs2.getDouble(1); /* :rk.5:erk. */
// clean up resources
rs2.close();
stmt2.close();
con.close(); /* :rk.6:erk. */
}
catch (SQLException sqle)
{
errorCode[0] = sqle.getErrorCode();
}
}
public static void inParams (double inLowSal,
double inMedSal,
double inHighSal,
String inDepartment,
int[] errorCode,
String[] errorLabel) throws SQLException
{
double salary;
String cursorName;
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
// Initialize variables
salary = 0;
cursorName = "";
try
{
// Get caller's connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connection");
errorLabel[0] = "GET CONNECTION";
String query = "SELECT CAST(salary AS DOUBLE) " +
"FROM employee " +
"WHERE workdept = '" + inDepartment + "' " +
"FOR UPDATE";
errorLabel[0] = "PREPARE STATEMENT 1";
PreparedStatement stmt = con.prepareStatement(query);
errorLabel[0] = "GET RESULT SET";
ResultSet rs = stmt.executeQuery();
cursorName = rs.getCursorName();
errorLabel[0] = "GET FIRST ROW";
if (!rs.next())
{
/* Cursor contains no data, so return SQLCODE 100 to client */
errorCode[0] = 100;
}
else
{
boolean foundData = true;
String updateLow = "UPDATE employee SET salary = " + inLowSal +
" WHERE CURRENT OF " + cursorName;
String updateMed = "UPDATE employee SET salary = " + inMedSal +
" WHERE CURRENT OF " + cursorName;
String updateHigh = "UPDATE employee SET salary = " + inHighSal +
" WHERE CURRENT OF " + cursorName;
String updateFinal = "UPDATE employee SET salary = (salary * 1.10)" +
" WHERE CURRENT OF " + cursorName;
errorLabel[0] = "PREPARE STATEMENT 2";
PreparedStatement stmtLow = con.prepareStatement(updateLow);
PreparedStatement stmtMed = con.prepareStatement(updateMed);
PreparedStatement stmtHigh = con.prepareStatement(updateHigh);
PreparedStatement stmtFinal = con.prepareStatement(updateFinal);
while (foundData)
{
errorLabel[0] = "GET SALARY";
salary = rs.getDouble(1);
if (inLowSal > salary)
{
errorLabel[0] = "UPDATE -- LOW CASE";
stmtLow.executeUpdate();
}
else if (inMedSal > salary)
{
errorLabel[0] = "UPDATE -- MEDIUM CASE";
stmtMed.executeUpdate();
}
else if (inHighSal > salary)
{
errorLabel[0] = "UPDATE -- HIGH CASE";
stmtHigh.executeUpdate();
}
else
{
errorLabel[0] = "UPDATE -- FINAL CASE";
stmtFinal.executeUpdate();
}
if (!rs.next()) // if next row is not found
{
foundData = false;
}
}
stmtLow.close();
stmtMed.close();
stmtHigh.close();
stmtFinal.close();
}
rs.close();
stmt.close();
con.close();
}
catch (SQLException sqle)
{
errorCode[0] = sqle.getErrorCode();
}
}
public static void inoutParam (double[] inoutMedianSalary,
int[] errorCode,
String[] errorLabel) throws SQLException
{
int counter, numRecords;
double salary;
String cursorName;
// Initialize variables
counter = 0;
salary = 0;
cursorName = "";
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
try
{
// Get caller's connection to the database
errorLabel[0] = "GET CONNECTION";
Connection con = DriverManager.getConnection("jdbc:default:connection");
String query = "SELECT COUNT(*) FROM staff " +
"WHERE CAST(salary AS DOUBLE) > ? ";
errorLabel[0] = "PREPARE COUNT STATEMENT";
PreparedStatement stmt = con.prepareStatement(query);
stmt.setDouble(1, inoutMedianSalary[0]);
errorLabel[0] = "GET COUNT RESULT SET";
ResultSet rs = stmt.executeQuery();
// move to first row of result set
rs.next();
// set value for the output parameter
errorLabel[0] = "GET NUMBER OF RECORDS";
numRecords = rs.getInt(1);
// clean up first result set
rs.close();
stmt.close();
if (numRecords == 0)
{
// Set errorCode to SQL0100 to indicate data not found
errorCode[0] = 100;
}
else
{
// get salary result set
query = "SELECT CAST(salary AS DOUBLE) FROM staff " +
"WHERE CAST(salary AS DOUBLE) > ? " +
" ORDER BY salary";
errorLabel[0] = "PREPARE SALARY STATEMENT";
PreparedStatement stmt2 = con.prepareStatement(query);
stmt2.setDouble(1, inoutMedianSalary[0]);
errorLabel[0] = "GET SALARY RESULT SET";
ResultSet rs2 = stmt2.executeQuery();
while (counter < (numRecords / 2 + 1))
{
errorLabel[0] = "MOVE TO NEXT ROW";
rs2.next();
counter++;
}
errorLabel[0] = "GET MEDIAN SALARY";
inoutMedianSalary[0] = rs2.getDouble(1);
// clean up resources
rs2.close();
stmt2.close();
}
// close connection
con.close();
}
catch (SQLException sqle)
{
errorCode[0] = sqle.getErrorCode();
}
}
public static void resultSetToClient
(double inSalaryThreshold, // double input
int[] errorCode, // SQLCODE output
ResultSet[] rs) // ResultSet output
throws SQLException
{
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
try
{
// Get caller's connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connection");
// get salary result set using a parameter marker
String query = "SELECT name, job, CAST(salary AS DOUBLE) FROM staff " +
"WHERE salary > ? " +
"ORDER BY salary";
// prepare the SQL statement
PreparedStatement stmt = con.prepareStatement(query);
// set the value of the parameter marker (?)
stmt.setDouble(1, inSalaryThreshold);
// get the result set that will be returned to the client
rs[0] = stmt.executeQuery();
// to return a result set to the client, do not close ResultSet
con.close();
}
catch (SQLException sqle)
{
errorCode[0] = sqle.getErrorCode();
}
}
public static void twoResultSets
(double inSalaryThreshold, // double input
int[] errorCode, // SQLCODE output
ResultSet[] rs1, // first ResultSet output
ResultSet[] rs2) // second ResultSet output
throws SQLException
{
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
try
{
// Get caller's connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connection");
// get salary result set using a parameter marker
String query = "SELECT name, job, CAST(salary AS DOUBLE) FROM staff " +
"WHERE salary > ? " +
"ORDER BY salary";
// prepare the SQL statement
PreparedStatement stmt = con.prepareStatement(query);
// set the value of the parameter marker (?)
stmt.setDouble(1, inSalaryThreshold);
// get the result set that will be returned to the client
rs1[0] = stmt.executeQuery();
// get salary result set using a parameter marker
String query2 = "SELECT name, job, CAST(salary AS DOUBLE) FROM staff " +
"WHERE salary < ? " +
"ORDER BY salary DESC";
// prepare the SQL statement
PreparedStatement stmt2 = con.prepareStatement(query2);
// set the value of the parameter marker (?)
stmt2.setDouble(1, inSalaryThreshold);
// get the result set that will be returned to the client
rs2[0] = stmt2.executeQuery();
// to return the result sets to the client, do not close the ResultSet
con.close();
}
catch (SQLException sqle)
{
errorCode[0] = sqle.getErrorCode();
}
}
public static void clobExtract
(String empNo, // CHAR(6) input parameter
String[] interests, // VARCHAR(1000) output parameter
int errorCode[] // SQLINT32 output parameter
) throws IOException
{
int counter, index, maximumLength;
byte[] clobBytes;
char[] clobData;
try
{
// Get caller's connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connection");
// choose the employee resume that matches the employee number
Statement stmt = con.createStatement ();
ResultSet rs = stmt.executeQuery("SELECT resume FROM emp_resume WHERE " +
"empno = '" + empNo + "' AND resume_format = 'ascii'");
if (rs.next())
{
// copy the CLOB into an array of characters by converting all
// bytes into characters as they are read in
InputStream inStream = rs.getAsciiStream (1);
// InputStream.available() may not work on larger files
maximumLength = inStream.available();
clobBytes = new byte[maximumLength];
clobData = new char[maximumLength];
inStream.read(clobBytes);
for (counter = 0; counter < maximumLength; counter++)
{
clobData[counter] = (char)clobBytes[counter];
}
String clob = String.valueOf (clobData);
// Copy substring from "Department Info" to "Education" into OUT parameter
index = clob.indexOf("Department Info");
if (index == -1)
{
interests[0] = ("Resume does not contain a Department Info section.");
}
else
{
interests[0] = clob.substring (clob.indexOf ("Department Info"), clob.indexOf("Education"));
}
}
else
{
interests[0] = ("\nEmployee " + empNo + " does not have a resume.");
}
rs.close();
stmt.close();
}
catch (SQLException sqle)
{
errorCode[0] = sqle.getErrorCode();
}
}
public static void allDataTypes
(short[] inoutSmallint, // SMALLINT
int[] inoutInteger, // INTEGER
long[] inoutBigint, // BIGINT
float[] inoutReal, // REAL
double[] inoutDouble, // DOUBLE
String[] outChar, // CHAR(1)
String[] outChars, // CHAR(15)
String[] outVarchar, // VARCHAR(12)
Date[] outDate, // DATE
Time[] outTime, // TIME
int[] errorCode,
String[] errorLabel) throws SQLException
{
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
try
{
// Get caller's connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connection");
if (inoutSmallint[0] == 0) { inoutSmallint[0] = 1; }
else { inoutSmallint[0] = (short) (inoutSmallint[0] / 2); }
if (inoutInteger[0] == 0) { inoutInteger[0] = 1; }
else { inoutInteger[0] = (inoutInteger[0] / 2); }
if (inoutBigint[0] == 0) { inoutBigint[0] = 1; }
else { inoutBigint[0] = (inoutBigint[0] / 2); }
if (inoutReal[0] == 0) { inoutReal[0] = 1; }
else { inoutReal[0] = (inoutReal[0] / 2); }
if (inoutDouble[0] == 0) { inoutDouble[0] = 1; }
else { inoutDouble[0] = (inoutDouble[0] / 2); }
// get value of a CHAR(1) column (midinit)
String query = "SELECT midinit FROM employee " +
"WHERE empno = '000180' ";
errorLabel[0] = "create CHAR(1) select";
// create the SQL statement
Statement stmt = con.createStatement();
errorLabel[0] = "get result set for CHAR(1) select";
// get the result set
ResultSet rs = stmt.executeQuery(query);
errorLabel[0] = "move to first row for CHAR(1) select";
// move to first row of result set
rs.next();
errorLabel[0] = "get value for CHAR(1) select";
// get the value of the midinit column
outChar[0] = rs.getString(1);
// clean up resources
rs.close();
stmt.close();
// get value of a CHAR(15) column (lastname)
query = "SELECT lastname FROM employee " +
"WHERE empno = '000180' ";
errorLabel[0] = "create CHAR(15) select";
// create the SQL statement
stmt = con.createStatement();
// get the result set
rs = stmt.executeQuery(query);
// move to first row of result set
rs.next();
// get the value of the lastname column
outChars[0] = rs.getString(1);
// clean up resources
rs.close();
stmt.close();
// get value of a VARCHAR(12) column (firstnme)
query = "SELECT firstnme FROM employee " +
"WHERE empno = '000180' ";
errorLabel[0] = "create VARCHAR(12) select";
// create the SQL statement
stmt = con.createStatement();
// get the result set
rs = stmt.executeQuery(query);
// move to first row of result set
rs.next();
// get the value of the firstnme column
outVarchar[0] = rs.getString(1);
// clean up resources
rs.close();
stmt.close();
// get current date from DB2 server
query = "VALUES (CURRENT DATE)";
errorLabel[0] = "create VALUES (CURRENT DATE)";
// create the SQL statement
stmt = con.createStatement();
// get the result set
rs = stmt.executeQuery(query);
// move to first row of result set
rs.next();
// get the date value
outDate[0] = rs.getDate(1);
// clean up resources
rs.close();
stmt.close();
// get current time from DB2 server
query = "VALUES (CURRENT TIME)";
errorLabel[0] = "create VALUES (CURRENT TIME)";
// create the SQL statement
stmt = con.createStatement();
// get the result set
rs = stmt.executeQuery(query);
// move to first row of result set
rs.next();
// get the time value
outTime[0] = rs.getTime(1);
// clean up resources
rs.close();
stmt.close();
// close our connection
con.close();
}
catch (SQLException sqle)
{
errorCode[0] = sqle.getErrorCode();
}
}
public static void decimalType
(BigDecimal[] inoutDecimal, // DECIMAL(10,2)
int[] errorCode,
String[] errorLabel) throws SQLException
{
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
try
{
// Get caller's connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connection");
if (inoutDecimal[0].equals(BigDecimal.valueOf(0)))
{
inoutDecimal[0].add(BigDecimal.valueOf(1));
}
else
{
inoutDecimal[0] = inoutDecimal[0].divide(
BigDecimal.valueOf(2), BigDecimal.ROUND_HALF_UP);
}
// close our connection
con.close();
}
catch (SQLException sqle)
{
errorCode[0] = sqle.getErrorCode();
}
}
}