//*************************************************************************** // (c) Copyright IBM Corp. 2007 All rights reserved. // // The following sample of source code ("Sample") is owned by International // Business Machines Corporation or one of its subsidiaries ("IBM") and is // copyrighted and licensed, not sold. You may use, copy, modify, and // distribute the Sample in any form without payment to IBM, for the purpose of // assisting you in the development of your applications. // // The Sample code is provided to you on an "AS IS" basis, without warranty of // any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR // IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF // MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do // not allow for the exclusion or limitation of implied warranties, so the above // limitations or exclusions may not apply to you. IBM shall not be liable for // any damages you suffer as a result of using, copying, modifying or // distributing the Sample, even if IBM has been advised of the possibility of // such damages. //*************************************************************************** // // SOURCE FILE NAME: NestedSP.java // // SAMPLE: Client application for invoking nested stored procedures // // This sample calls the method callNestedSP() which invokes the // stored procedures created in nestedsp.db2. // // The function callNestSP() demonstrates 3 levels of nesting. // It first calls the stored procedure OUT_AVERAGE which calls the // stored procedure OUT_MEDIAN, which then calls the stored procedure // MAX_SALARY. // The output consists of the following information in order: // (1) The average salary of the EMPLOYEE table // (2) The median salary of the EMPLOYEE table // (3) The maximum salary of the EMPLOYEE table // (4) a list of employees who make more than average salary // (5) a list of employees who make less than average salary. // // To run this sample, perform the following steps: // (1) create and populate the SAMPLE database by running the command: // db2sampl // (2) connect to sample database with: // db2 connect to sample // (3) register the stored procedures using the nestedsp.db2 script: // db2 -td@ -vf nestedsp.db2 // (4) compile NestedSP with: // (n)make NestedSP // (5) run NestedSP with: // java NestedSP // (6) to drop the stored procedures run the nestedspdrop.db2 script: // db2 -td@ -vf nestedspdrop.db2 // // NOTES: The CLASSPATH and shared library path environment variables // must be set, as for any JDBC application // // // //*************************************************************************** // For more information about the sample programs, see the README file. // // For information on creating SQL procedures and developing JDBC applications, // see the Application Development Guide. // // For information on using SQL statements, see the SQL Reference. // // For the latest information on programming, building, and running DB2 // applications, visit the DB2 Information Center: // http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp //*************************************************************************** import java.sql.*; class NestedSP { static { try { System.out.println(); System.out.println("JAVA STORED PROCEDURE SAMPLE"); Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); } catch (Exception e) { System.out.println("\nError loading DB2 Driver...\n"); e.printStackTrace(); } } public static void main(String argv[]) { Db db = null; try { // process command line arguments for database connection db = new Db(argv); System.out.print("THIS SAMPLE SHOWS HOW NESTED STORED PROCEDURES WORK."); System.out.println(); // connect to the 'sample' database db.connect(); // calling NestedSP function callNestedSP(db.con); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e); jdbcExc.handle(); } finally { try { db.disconnect(); } catch( Exception e ) { } } } // end main public static void callNestedSP(Connection con) { ResultSet rs = null; CallableStatement callStmt = null; try { double outMedian = 0.0; double outAverage = 0.0; double outMaxSalary = 0.0; String procName = "OUT_AVERAGE"; String sql = "CALL " + procName + "(?, ?, ?)"; callStmt = con.prepareCall(sql); // register the output parameter callStmt.registerOutParameter (1, Types.DOUBLE); callStmt.registerOutParameter (2, Types.DOUBLE); callStmt.registerOutParameter (3, Types.DOUBLE); // call the stored procedure System.out.println ("\nCall stored procedure named " + procName); callStmt.execute(); // retrieve output parameters outAverage = callStmt.getDouble(1); outMedian = callStmt.getDouble(2); outMaxSalary = callStmt.getDouble(3); System.out.println(procName + " completed successfully"); System.out.println(); System.out.println ("Average salary returned from " + procName + " = " + outAverage); System.out.println(); System.out.println ("Median salary returned from OUT_MEDAIN = " + outMedian); System.out.println(); System.out.println ("Max salary returned from MAX_SALARY = " + outMaxSalary); System.out.println(); System.out.println("Result set 1: Employees who make more than " + outAverage); // get the first result set rs = callStmt.getResultSet(); fetchAll(rs); System.out.println("\nResult set 2: Employees who make less than " + outAverage); // get the second result set callStmt.getMoreResults(); rs = callStmt.getResultSet(); fetchAll(rs); } catch (Exception e) { JdbcException jdbcExc = new JdbcException(e, con); jdbcExc.handle(); } finally { // cleanup - close the result set and the statement try { rs.close(); } catch (Exception e) { } try { callStmt.close(); } catch (Exception e) { } } } //method fetchAll returns all rows from result set public static void fetchAll( ResultSet rs) throws SQLException { System.out.println( "============================================================="); ResultSetMetaData stmtInfo = rs.getMetaData(); int numOfColumns = stmtInfo.getColumnCount(); // Do not need to print the last column int numColumns = numOfColumns - 1; int r = 0; while( rs.next() ) { r++; System.out.print("Row: " + r + ": "); for( int i=1; i <= numColumns; i++ ) { System.out.print(rs.getString(i)); if( i != numColumns ) System.out.print(" , "); } System.out.println(""); } } }