// Source File Name: Stserver.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 Stserver - Stored Procedure
// Steps to run the sample:
// (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 Stcreate.db2 CLP script:
// (i) db2 connect to sample
// (ii) db2 -td@ -vf Stcreate.db2
// (3) compile Stclient (javac Stclient.java)
// (4) run Stclient (java Stclient)
// 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 Stserver 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: pass a Java BigDecimal type as a DECIMAL data type
// (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
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
#sql iterator StserverSalary (double);
#sql iterator StserverEmployees (String, String, double);
class Stserver
{
public static void outLanguage (String[] outLang) throws Exception
{ try
{
String procName = "OUT_LANGUAGE";
String language;
#sql { SELECT language INTO :language FROM syscat.procedures WHERE procname = :procName };
outLang[0] = language;
}
catch (Exception e)
{ throw e;
}
}
public static void outParameter (double[] medianSalary,
int[] errorCode, String[] errorLabel) throws SQLException /* :rk.1:erk. */
{ try
{
StserverSalary c1;
double salary;
int counter, numRecords;
// Initialize variables
salary = 0;
counter = 0;
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
#sql { SELECT COUNT(*) INTO :numRecords FROM staff };
errorLabel[0] = "COUNT NUMBER OF ROWS";
// declare salary iterator
#sql c1 = { SELECT CAST(salary AS DOUBLE) FROM staff ORDER BY salary };
errorLabel[0] = "DECLARE SALARY ITERATOR";
while (counter < (numRecords / 2 + 1))
{
#sql { FETCH :c1 INTO :salary };
errorLabel[0] = "MOVE TO ROW: " +counter;
if (c1.endFetch()) break;
counter++;
}
medianSalary[0] = salary;
c1.close();
}
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
{
StserverIterator updateIterator;
double salary, lowSal, medSal, highSal;
String cursorName;
// Initialize variables
salary = 0;
cursorName = "";
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
try
{
errorLabel[0] = "DECLARE ITERATOR";
#sql updateIterator = { SELECT CAST(salary AS DOUBLE)
FROM employee WHERE workdept = :inDepartment };
lowSal = inLowSal;
medSal = inMedSal;
highSal = inHighSal;
errorLabel[0] = "FETCH FIRST ROW";
#sql { FETCH :updateIterator INTO :salary };
if (updateIterator.endFetch())
{
/* Iterator contains no data, so return SQLCODE 100 to client */
errorCode[0] = 100;
}
else
{
while (!updateIterator.endFetch())
{
if (lowSal > salary)
{
#sql { UPDATE employee SET salary = :lowSal WHERE CURRENT OF :updateIterator };
errorLabel[0] = "UPDATE -- LOW CASE";
}
else if (medSal > salary)
{
#sql { UPDATE employee SET salary = :medSal WHERE CURRENT OF :updateIterator };
errorLabel[0] = "UPDATE -- MEDIUM CASE";
}
else if (highSal > salary)
{
#sql { UPDATE employee SET salary = :highSal WHERE CURRENT OF :updateIterator };
errorLabel[0] = "UPDATE -- HIGH CASE";
}
else
{
#sql { UPDATE employee SET salary = (salary * 1.10) WHERE CURRENT OF :updateIterator };
errorLabel[0] = "UPDATE -- FINAL CASE";
}
#sql { FETCH :updateIterator INTO :salary };
}
}
updateIterator.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, inSalary;
StserverSalary c1;
// Initialize variables
counter = 0;
salary = 0;
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
inSalary = inoutMedianSalary[0];
try
{
errorLabel[0] = "GET NUMBER OF RECORDS";
#sql { SELECT COUNT(*) INTO :numRecords FROM staff WHERE CAST(salary AS DOUBLE) > :inSalary };
if (numRecords == 0)
{
// Set errorCode to SQL0100 to indicate that no data was found
errorCode[0] = 100;
}
else
{
errorLabel[0] = "GET SALARY RESULT SET";
#sql c1 = { SELECT CAST(salary AS DOUBLE) FROM staff WHERE CAST(salary AS DOUBLE) > :inSalary ORDER BY salary };
errorLabel[0] = "PREPARE SALARY STATEMENT";
while (counter < (numRecords / 2 + 1))
{
errorLabel[0] = "MOVE TO ROW: " + counter;
#sql { FETCH :c1 INTO :salary };
counter++;
}
inoutMedianSalary[0] = salary;
c1.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
{
StserverEmployees c1;
double inSalary;
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
inSalary = inSalaryThreshold;
try {
// Declare iterator for result set
#sql c1 = { SELECT name, job, CAST(salary AS DOUBLE) FROM staff WHERE salary > :inSalary ORDER BY salary };
// get the result set that is returned to the client
rs[0] = c1.getResultSet();
}
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
{
StserverEmployees c1, c2;
double inSalary;
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
inSalary = inSalaryThreshold;
try
{
// Declare iterator for first result set
#sql c1 = { SELECT name, job, CAST(salary AS DOUBLE) FROM staff WHERE salary > :inSalary ORDER BY salary };
// get the first result set that is returned to the client
rs1[0] = c1.getResultSet();
// Declare iterator for first result set
#sql c2 = { SELECT name, job, CAST(salary AS DOUBLE) FROM staff WHERE salary < :inSalary ORDER BY salary DESC };
// get the second result set that is returned to the client
rs2[0] = c2.getResultSet();
}
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 decimalType
(BigDecimal[] inoutDecimal, // DECIMAL(10,2)
int[] errorCode,
String[] errorLabel) throws Exception
{
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
try
{
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);
}
}
catch (Exception sqle)
{
errorCode[0] = 0;
}
}
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
{
String query, hvOutChar, hvOutChars, hvOutVarchar;
ResultSet rs;
Statement stmt;
Date hvOutDate;
Time hvOutTime;
errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs
errorLabel[0] = "START ALL_DATA_TYPES";
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)
errorLabel[0] = "GET CHAR(1)";
#sql { SELECT midinit INTO :hvOutChar FROM employee WHERE empno = '000180' };
outChar[0] = hvOutChar;
// get value of a CHAR(15) column (lastname)
errorLabel[0] = "GET CHAR(15)";
#sql { SELECT lastname INTO :hvOutChars FROM employee WHERE empno = '000180' };
outChars[0] = hvOutChars;
// get value of a VARCHAR(12) column (firstnme)
errorLabel[0] = "GET VARCHAR(12)";
#sql { SELECT firstnme INTO :hvOutVarchar FROM employee WHERE empno = '000180' };
outVarchar[0] = hvOutVarchar;
// get current date using JDBC
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 using JDBC
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();
}
}
}