// Source File Name: DB2SpCli.java 1.2
//
// Licensed Materials -- Property of IBM
//
// (c) Copyright International Business Machines Corporation, 1996, 1997.
// All Rights Reserved.
//
// US Government Users Restricted Rights -
// Use, duplication or disclosure restricted by
// GSA ADP Schedule Contract with IBM Corp.
// Sample Program - Client side of Java Stored Procedure Sample
// Steps to run the sample:
// (1) you must have the "sample" database cataloged on the client
// (2) compile the stored procedure (javac DB2Stp.java) and copy it
// to the sqllib/function directory
// (3) compile this java file (javac DB2SpCli.java)
// (4) run the sample (java DB2SpCli [username password])
// NOTES: (1) The CLASSPATH and shared library path environment variables
// must be set, as for any JDBC application.
// (2) 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 DB2SpCli contains four methods:
// (1) registerStoredProc: register the stored procedure
// (2) callStoredProc: call the stored procedure
// (3) main: application body (register and call the stored procedure)
import java.sql.*; // JDBC classes
import java.math.*; // BigDecimal
import COM.ibm.db2.jdbc.app.*; // DB2 UDB JDBC classes
class DB2SpCli
{
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 ("\n Error loading DB2 Driver...\n");
e.printStackTrace ();
}
}
// (1) register the stored procedure
public static void registerStoredProc (Connection con,
String name,
String classMethod,
String mode) throws Exception
{
try
{
// drop the stored procedure if it exists
Statement dropStmt = con.createStatement ();
dropStmt.executeUpdate ("DROP PROCEDURE " + name);
dropStmt.close ();
}
catch (SQLException e)
{
// ignore this error
}
try
{
Statement stmt = con.createStatement ();
// construct a parameter list for the stored procedure and
// register it in the system catalogs
String parameterList =
"(in table varchar(20)," +
" in modifier int," +
" inout department varchar(10)," +
" out payrollBefore double," +
" out payrollAfter double," +
" out avgSalBefore double," +
" out avgSalAfter double," +
" out numOfUpdates int," +
" out medianEmpName varchar(40))";
System.out.println ("\n Registering Java stored procedure " + name +
"\n as " + classMethod +
"\n in " + mode + " mode");
stmt.executeUpdate ("CREATE PROCEDURE " + name + parameterList +
" LANGUAGE JAVA " +
" PARAMETER STYLE DB2GENERAL " + mode +
" EXTERNAL NAME '" + classMethod + "'");
stmt.close ();
}
catch (SQLException e)
{
System.out.println ("\n Error received registering stored procedure");
throw e;
}
}
// (2) call the requested stored procedure and display results
public static void callStoredProc (Connection con,
String tableName,
String name,
int percentModification,
String department) throws Exception
{
// prepare the CALL statement
CallableStatement stmt;
String sql = "Call " + name + "(?,?,?,?,?,?,?,?,?) ";
stmt = con.prepareCall (sql);
// register the output parameters
stmt.registerOutParameter (4, Types.DOUBLE);
stmt.registerOutParameter (5, Types.DOUBLE);
stmt.registerOutParameter (6, Types.DOUBLE);
stmt.registerOutParameter (7, Types.DOUBLE);
stmt.registerOutParameter (8, Types.INTEGER);
stmt.registerOutParameter (9, Types.CHAR);
// set all parameters (input and output)
double totalPayrollBefore = 0.00;
double totalPayrollAfter = 0.00;
double averageSalaryBefore = 0.00;
double averageSalaryAfter = 0.00;
String medianEmployeeName = "This field is not defined yet";
int numberOfUpdates = 0;
stmt.setString (1, tableName);
stmt.setInt (2, percentModification);
stmt.setString (3, department);
stmt.setDouble (4, totalPayrollBefore);
stmt.setDouble (5, totalPayrollAfter);
stmt.setDouble (6, averageSalaryBefore);
stmt.setDouble (7, averageSalaryAfter);
stmt.setInt (8, numberOfUpdates);
stmt.setString (9, medianEmployeeName);
// call the stored procedure
System.out.println ("\n Calling stored procedure: " + name);
stmt.execute ();
System.out.println ("\n Returned from stored procedure: " + name);
// retrieve output parameters
BigDecimal totPayBefore = new BigDecimal (stmt.getDouble (4));
BigDecimal totPayAfter = new BigDecimal (stmt.getDouble (5));
BigDecimal avgSalBefore = new BigDecimal (stmt.getDouble (6));
BigDecimal avgSalAfter = new BigDecimal (stmt.getDouble (7));
numberOfUpdates = stmt.getInt (8);
medianEmployeeName = stmt.getString (9);
// display the information returned from the stored procedure
System.out.println ();
System.out.println (" Percent modification: " +
percentModification + "%");
System.out.println (" Department being modified: " +
department);
System.out.println ();
System.out.println (" Total payroll before: $ " +
totPayBefore.setScale (2, totPayBefore.ROUND_HALF_UP));
System.out.println (" Average salary before: $ " +
avgSalBefore.setScale (2, avgSalBefore.ROUND_HALF_UP));
System.out.println ();
System.out.println (" Number of salary modifications: " +
numberOfUpdates);
System.out.println ();
System.out.println (" Total payroll after: $ " +
totPayAfter.setScale (2, totPayAfter.ROUND_HALF_UP));
System.out.println (" Average salary after: $ " +
avgSalAfter.setScale (2, avgSalAfter.ROUND_HALF_UP));
System.out.println ();
System.out.println (" Median Employee Name: " +
medianEmployeeName);
stmt.close ();
}
// (3) main application: .connect to the database
// .register the stored procedure
// .call the stored procedure
public static void main (String argv[])
{
Connection con = null;
try
{
String url = "jdbc:db2:sample";
String callName = "SAMPLESTOREDPROC";
// you may specify the schema name
// String callName = "MYSCHEMA.SAMPLESTOREDPROC";
String storedProcName = "DB2Stp!salaryModification";
String mode = "not fenced";
String table = "EMPLOYEE";
String department = "C01";
int percentModification = 5;
if (argv.length == 0) {
// connect with default id/password
con = DriverManager.getConnection(url);
}
else if (argv.length == 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 DB2SpCli [username password]\n");
System.exit(0);
}
// register the stored procedure
registerStoredProc (con, callName, storedProcName, mode);
// call the stored procedure
callStoredProc (con, table, callName, percentModification, department);
con.close ();
}
catch (Exception e)
{
try { con.close(); } catch (Exception x) { }
e.printStackTrace ();
}
}
}