// 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); } } }