// Source File Name: MRSPcli.java 1.2
//
// Licensed Materials -- Property of IBM
//
// (c) Copyright International Business Machines Corporation, 1999.
// All Rights Reserved.
//
// US Government Users Restricted Rights -
// Use, duplication or disclosure restricted by
// GSA ADP Schedule Contract with IBM Corp.
// Sample Program MRSPcli -
// This java code is the Client JDBC calling program
// to invoke the Java Stored Procedure which returns
// multiple resultsets. The Java Stored Procedure
// program is called MRSPsrv.java
// Steps to run the sample:
// (1) create and populate the SAMPLE database (db2sampl)
// (2) (n)make MRSPsrv
// (3) (n)make MRSPcli
// (4) run MRSPcli
// 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.
import java.lang.*;
import java.util.*;
import java.io.*;
import java.sql.*; // JDBC classes
import COM.ibm.db2.jdbc.app.*; // DB2 UDB JDBC classes
class MRSPcli
{ static
{ try
{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
}
catch (Exception e)
{ System.out.println ("\n Error loading DB2 Driver...\n");
System.out.println (e);
System.exit(1);
}
}
// main application: .connect to the database
// .register the stored procedure
// .call the stored procedure
public static void main (String argv[])
{ Connection con = null;
try
{ System.out.println (" Java Multiple Resultsets Stored Procedure Sample");
// Connect to Sample database
// URL is jdbc:db2:dbname
String url = "jdbc:db2:sample";
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
{ throw new Exception("\nUsage: java MRSPcli [username password]\n");
}
// Set AutoCommit
con.setAutoCommit(true);
Statement stmt = con.createStatement();
String callName = "MRSPsrv";
String storedProcName = "MRSPsrv!MRSPsrv";
String mode = "fenced";
try
{ // drop the stored procedure if it exists
stmt.executeUpdate("DROP PROCEDURE " + callName);
}
catch (SQLException e)
{ // ignore this error
}
try
{ // define the parameters for the Stored Procedure
String parameterList =
"(in q1 varchar(200), in q2 varchar(200), in q3 varchar(200))";
// construct the create procedure statement
String cpStr = "CREATE PROCEDURE " + callName + " " + parameterList +
" LANGUAGE JAVA " +
" PARAMETER STYLE DB2GENERAL " + mode +
" EXTERNAL NAME '" + storedProcName + "'";
System.out.println("\nRegistering Java Stored Procedure... \n");
// execute the create statement
stmt.executeUpdate (cpStr);
}
catch (SQLException e)
{ System.out.println ("\nError received registering stored procedure");
throw e;
}
// create the 3 sql statements to be executed in the Java Stored Procedure
String q1 = "select ID, NAME, DEPT, JOB from STAFF where SALARY > 20000.00";
String q2 = "select EMPNO, FIRSTNME from EMPLOYEE where SALARY > 20000.00";
String q3 = "select DEPTNO, DEPTNAME from DEPARTMENT";
// create callable statement to call the Stored Procedure
String callSql = "Call " + callName + "(?, ?, ?)";
System.out.println("Creating CallableStatement = " + callSql);
CallableStatement callStmt = con.prepareCall(callSql);
// set the parameters required for the Stored Procedure
System.out.println(" Param.1 = " +q1);
callStmt.setString (1, q1);
System.out.println(" Param.2 = " +q2);
callStmt.setString (2, q2);
System.out.println(" Param.3 = " +q3);
callStmt.setString (3, q3);
ResultSet rs = null;
System.out.println("\nExecuting the Java Stored Procedure now...\n");
callStmt.execute();
int rsCount = 0;
while( true )
{ int rowCount = callStmt.getUpdateCount();
if( rowCount > 0 )
{ System.out.println("=================================================================");
System.out.println("Rows changed = " + rowCount);
callStmt.getMoreResults();
System.out.println();
continue;
}
if( rowCount == 0 )
{ System.out.println("=================================================================");
System.out.println("No rows changed or sql was DDL command");
callStmt.getMoreResults();
System.out.println();
continue;
}
rs = callStmt.getResultSet();
if( rs != null )
{ rsCount++;
System.out.println("Fetching all the rows from the result set #" + rsCount);
fetchAll(rs);
callStmt.getMoreResults();
System.out.println();
continue;
}
break;
}
// close off everything before we leave
System.out.println("Closing statements and connection.");
callStmt.close ();
stmt.close ();
con.close ();
}
catch (Exception e)
{ try
{ if( con != null )
{ con.close();
}
}
catch (Exception x)
{ // ignore this exception
}
System.out.println (e);
}
}
// ================================================
// Method: fetchAll
// ================================================
static public 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);
}
}
}