// Source File Name: StpCli.java 1.3
//
// Licensed Materials -- Property of IBM
//
// (c) Copyright International Business Machines Corporation, 1998.
// All Rights Reserved.
//
// US Government Users Restricted Rights -
// Use, duplication or disclosure restricted by
// GSA ADP Schedule Contract with IBM Corp.
// Sample Program StpCli - Client program to call SQLJ Stored Procedure Stp
// Catalogs and calls a PARAMETER STYLE DB2GENERAL stored procedure
// Steps to run the sample:
// On the server:
// (1) compile Stp and copy its class files to sqllib/function
// (as explained in the Stp.sqlj file)
// On the client:
// (2) Catalog the "sample" database
// (3) Compile this java file (javac StpCli.java)
// (4) Run the sample (java StpCli [username password])
//
// 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 StpCli contains these 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
import COM.ibm.db2.app.*; // StoredProc and associated classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
class StpCli
{
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 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 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 (3, Types.DOUBLE);
stmt.registerOutParameter (4, Types.DOUBLE);
stmt.registerOutParameter (5, Types.DOUBLE);
stmt.registerOutParameter (6, Types.DOUBLE);
stmt.registerOutParameter (7, Types.INTEGER);
stmt.registerOutParameter (8, 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.setInt (1, percentModification);
stmt.setString (2, department);
stmt.setDouble (3, totalPayrollBefore);
stmt.setDouble (4, totalPayrollAfter);
stmt.setDouble (5, averageSalaryBefore);
stmt.setDouble (6, averageSalaryAfter);
stmt.setInt (7, numberOfUpdates);
stmt.setString (8, 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 (3));
BigDecimal totPayAfter = new BigDecimal (stmt.getDouble (4));
BigDecimal avgSalBefore = new BigDecimal (stmt.getDouble (5));
BigDecimal avgSalAfter = new BigDecimal (stmt.getDouble (6));
numberOfUpdates = stmt.getInt (7);
medianEmployeeName = stmt.getString (8);
// 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;
// URL is jdbc:db2:dbname
String url = "jdbc:db2:sample";
try
{
String callName = "SAMPLESTOREDPROC";
String storedProcName = "Stp!salaryModification";
String mode = "fenced";
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 StpCli [username password]\n");
System.exit(0);
}
// register the stored procedure
registerStoredProc (con, callName, storedProcName, mode);
// call the stored procedure
callStoredProc (con, callName, percentModification, department);
con.close ();
}
catch (Exception e)
{
try { con.close(); } catch (Exception x) { }
e.printStackTrace ();
}
}
}