//************************************************************************* // (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: SpClient.java // // SAMPLE: Call the set of stored procedures implemented in SpServer.java // // Steps to run the sample with command line window: // I) If you have a compatible make/nmake program on your system, // do the following: // 1. Compile the server source file SpServer.java (this will also // compile the utility file, Util.java, erase the existing // library/class files and copy the newly compiled class files, // SpServer.class, from the current directory to the // $(DB2PATH)\function directory): // nmake/make SpServer // 2. Compile the client source file SpClient.java (this will also // call the script 'spcat' to create and catalog the stored // procedures): // nmake/make SpClient // 3. Run the client SpClient: // java SpClient // // II) If you don't have a compatible make/nmake program on your // system do the following: // 1. Compile the utility file and the server source file with: // javac Util.java // javac SpServer.java // 2. Erase the existing library/class files (if exists), // SpServer.class from the following path, // $(DB2PATH)\function. // 3. Copy the class files, SpServer.class from the current // directory to the $(DB2PATH)\function. // 4. Catalog the stored procedures in the database with: // spcat // 5. Compile SpClient with: // javac SpClient.java // 6. Run SpClient with: // java SpClient // // SpClient calls nine methods that call stored procedures: // (1) callOutLanguage: Calls a stored procedure that returns the // implementation language of the stored procedure library // Parameter types used: OUT CHAR(8) // (2) callOutParameter: Calls a stored procedure that returns median // salary of employee salaries // Parameter types used: OUT DOUBLE // (3) callInParameters: Calls a stored procedure that accepts 3 salary // values and updates employee salaries in the EMPLOYEE table based // on these values for a given department. // Parameter types used: IN DOUBLE // IN DOUBLE // IN DOUBLE // IN CHAR(3) // (4) callInoutParameter: Calls a stored procedure that accepts an input // value and returns the median salary of those employees in the // EMPLOYEE table who earn more than the input value. Demonstrates how // to use null indicators in a client application. The stored procedure // has to be implemented in the following parameter styles for it to be // compatible with this client application. // Parameter style for a C stored procedure: SQL // Parameter style for a Java(JDBC/SQLJ) stored procedure: JAVA // Parameter style for an SQL stored procedure: SQL // Parameter types used: INOUT DOUBLE // (5) callClobExtract: Calls a stored procedure that extracts and returns a // portion of a CLOB data type // Parameter types used: IN CHAR(6) // OUT VARCHAR(1000) // (6) callDecimalType: Calls a stored procedure that passes and receives a // DECIMAL data type from a stored procedure // Parameter types used: INOUT DECIMAL // (7) callAllDataTypes: Calls a stored procedure that uses a variety of // common data types (not DECIMAL, GRAPHIC, VARGRAPHIC, BLOB, CLOB, // DBCLOB). This sample shows only a subset of DB2 supported data types. // For a full listing of DB2 data types, please see the SQL Reference. // Parameter types used: INOUT SMALLINT // INOUT INTEGER // INOUT BIGINT // INOUT REAL // INOUT DOUBLE // OUT CHAR(1) // OUT CHAR(15) // OUT VARCHAR(12) // OUT DATE // OUT TIME // (8) callOneResultSet: Calls a stored procedure that returns a result set to // the client application // Parameter types used: IN DOUBLE // (9) callTwoResultSets: Calls a stored procedure that returns two result sets // to the client application // Parameter types used: IN DOUBLE // // SQL Statements USED: // CALL // SELECT // // // Output will vary depending on the JDBC driver connectivity used. //************************************************************************* // // For more information on the sample programs, see the README file. // // For information on developing Java applications see the Developing Java Applications book. // // For information on using SQL statements, see the SQL Reference. // // For the latest information on programming, compiling, and running DB2 // applications, visit the DB2 Information Center at // http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp //**************************************************************************/ import java.sql.*; // JDBC classes import java.math.BigDecimal; // BigDecimal support for packed decimal type class SpClient { static double outMedian = 0; static Db db; public static void main(String argv[]) { String language = ""; Connection con = null; try { int prt=Integer.parseInt(argv[1]); javax.sql.DataSource ds=null; ds=new com.ibm.db2.jcc.DB2SimpleDataSource(); ((com.ibm.db2.jcc.DB2BaseDataSource) ds). setServerName(argv[0]); ((com.ibm.db2.jcc.DB2BaseDataSource) ds). setPortNumber(prt); ((com.ibm.db2.jcc.DB2BaseDataSource) ds). setDatabaseName("sample"); ((com.ibm.db2.jcc.DB2BaseDataSource) ds). setDriverType(4); ((com.ibm.db2.jcc.DB2BaseDataSource) ds). setTraceFile("jcctrace.txt"); ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setEnableNamedParameterMarkers(1); con = ds.getConnection(argv[2],argv[3]); System.out.println(" Connect to 'sample' database using JDBC Universal type 4 driver."); con.setAutoCommit(false); } catch (Exception e) { System.out.println(" Error loading DB2 Driver...\n"); System.out.println(e); System.exit(1); } try { System.out.println("HOW TO CALL VARIOUS STORED PROCEDURES.\n"); language = callOutLanguage(con); callOutParameter(con); callInParameters(con); // call INOUT_PARAM stored procedure using the median returned // by the call to OUT_PARAM System.out.println("\nCall stored procedure named INOUT_PARAM"); System.out.println("using the median returned by the call to " + "OUT_PARAM"); callInoutParameter(con, outMedian); // call INOUT_PARAM stored procedure again in order to depict a // NOT FOUND error condition System.out.println("\nCALL stored procedure INOUT_PARAM again"); System.out.println("with an input value that causes a NOT FOUND error"); callInoutParameter(con, 99999.99); callClobExtract("000140", con); callDecimalType(con); callAllDataTypes(con); callOneResultSet(con); callTwoResultSets(con); // roll back any changes to the database made by this sample con.rollback(); con.close(); System.out.println(); System.out.println(" Disconnect from 'sample' database."); } catch (Exception e) { try { con.rollback(); con.close(); } catch (Exception x) { } e.printStackTrace(); } } // end main public static String callOutLanguage(Connection con) { String outLang = ""; try { // prepare the CALL statement for OUT_LANGUAGE String procName = "OUT_LANGUAGE"; String sql = "CALL " + procName + "(:Typchar)"; CallableStatement callStmt = con.prepareCall(sql); // register the output parameter ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("Typchar", Types.CHAR); // call the stored procedure System.out.println(); System.out.println("Call stored procedure named " + procName); callStmt.execute(); // retrieve output parameters outLang = callStmt.getString(1); System.out.println("Stored procedures are implemented in language " + outLang); // clean up resources callStmt.close(); } catch (SQLException e) { System.out.println(e.getMessage()); } return(outLang); } // callOutLanguage public static void callOutParameter(Connection con) { try { // prepare the CALL statement for OUT_PARAM String procName = "OUT_PARAM"; String sql = "CALL " + procName + "(:Typdouble)"; CallableStatement callStmt = con.prepareCall(sql); // register the output parameter ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("Typdouble", Types.DOUBLE); // call the stored procedure System.out.println(); System.out.println("Call stored procedure named " + procName); callStmt.execute(); // retrieve output parameters outMedian = callStmt.getDouble(1); System.out.println(procName + " completed successfully"); System.out.println("Median salary returned from " + procName + " = " + outMedian); // clean up resources callStmt.close(); } catch (SQLException e) { System.out.println(e.getMessage()); } } // callOutParameter public static void callInParameters(Connection con) throws SQLException { try { // prepare the CALL statement for IN_PARAMS String procName = "IN_PARAMS"; String sql = "CALL " + procName + "(:lowsal, :medsal, :hisal, :dept)"; CallableStatement callStmt = con.prepareCall(sql); // display total salary before calling IN_PARAMS String query = "SELECT SUM(salary) FROM employee WHERE workdept = :dept"; PreparedStatement queryStmt = con.prepareStatement(query); ((com.ibm.db2.jcc.DB2PreparedStatement)queryStmt).setJccStringAtName("dept", "E11"); ResultSet queryRS = queryStmt.executeQuery(); queryRS.next(); double sumSalary = queryRS.getDouble(1); queryRS.close(); System.out.println(); System.out.println("Sum of salaries for dept. E11 = " + sumSalary + " before " + procName); // set input parameters ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccDoubleAtName("lowsal", 15000); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccDoubleAtName("medsal", 20000); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccDoubleAtName("hisal", 25000); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccStringAtName("dept", "E11"); // call the stored procedure System.out.println("Call stored procedure named " + procName); callStmt.execute(); System.out.println(procName + " completed successfully"); // display total salary after calling IN_PARAMS queryRS = queryStmt.executeQuery(); queryRS.next(); sumSalary = queryRS.getDouble(1); queryRS.close(); System.out.println("Sum of salaries for dept. E11 = " + sumSalary + " after " + procName); // clean up resources queryStmt.close(); callStmt.close(); } catch (SQLException e) { // roll back any UPDATE statements issued before the SQLException con.rollback(); System.out.println(e.getMessage()); } } // callInParameters public static void callInoutParameter(Connection con, double median) { try { // prepare the CALL statement for INOUT_PARAM String procName = "INOUT_PARAM"; String sql = "CALL " + procName + "(:median)"; CallableStatement callStmt = con.prepareCall(sql); // set input parameter to median value passed back by OUT_PARAM ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccDoubleAtName("median", median); // register the output parameters ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("median", Types.DOUBLE); if (median == 99999.99) { System.out.println("\n-- The following error report is " + "expected! --"); } callStmt.execute(); // retrieve output parameters double inoutMedian = callStmt.getDouble(1); System.out.println(procName + " completed successfully"); System.out.println("Median salary returned from " + procName + " = " + inoutMedian); // clean up resources callStmt.close(); } catch (SQLException e) { System.out.println(e.getMessage()); } } // callInoutParameter public static void callClobExtract(String empNo, Connection con) { String outResume; try { // prepare the CALL statement for CLOB_EXTRACT String procName = "CLOB_EXTRACT"; String sql = "CALL " + procName + "(:empNo, :Typvarchar)"; CallableStatement callStmt = con.prepareCall(sql); // set input parameter to median value passed back by OUT_PARAM ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccStringAtName("empNo", empNo); // register the output parameters ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("Typvarchar", Types.VARCHAR); // call the stored procedure System.out.println(); System.out.println("Call stored procedure named " + procName); callStmt.execute(); // retrieve output parameters outResume = callStmt.getString(2); System.out.println(procName + " completed successfully"); System.out.println("Resume section returned for employee " + empNo + "=\n" + outResume); } catch (Exception e) { System.out.println(e.getMessage()); } } // callClobExtract //************************************************************************* // PARAMETER STYLE JAVA procedures do not support the DBINFO clause. // The following PARAMETER STYLES can be used with DBINFO or PROGRAM TYPE // clauses: // - DB2SQL // - GENERAL // - GENERAL WITH NULLS // - SQL // Please see the SpClient implementation for C/C++/CLI language to // see this functionality. //************************************************************************* //************************************************************************* // PROGRAM TYPE MAIN is only valid for LANGUAGE C, COBOL or CLR, and // following PARAMETER STYLE: // - DB2SQL // - GENERAL // - GENERAL WITH NULLS // - SQL // Please see the SpClient implementation for C/C++/CLI language to // see this functionality. //************************************************************************* public static void callDecimalType(Connection con) { try { // prepare the CALL statement for DECIMAL_TYPE String procName = "DECIMAL_TYPE"; String sql = "CALL " + procName + "(:inoutDec)"; CallableStatement callStmt = con.prepareCall(sql); // declare and initialize input variable BigDecimal inoutDecimal = new BigDecimal("400000.00"); // set input parameter ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccBigDecimalAtName("inoutDec", inoutDecimal); // register the output parameters ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("inoutDec", Types.DECIMAL, 2); // call the stored procedure System.out.println(); System.out.println("Call stored procedure named " + procName); callStmt.execute(); System.out.println(procName + " completed successfully"); // retrieve output parameters inoutDecimal = callStmt.getBigDecimal(1).setScale( 2 ); System.out.println("Value of DECIMAL = " + inoutDecimal); callStmt.close(); } catch (SQLException e) { System.out.println(e.getMessage()); } } // callDecimalType public static void callAllDataTypes(Connection con) { try { // prepare the CALL statement for ALL_DATA_TYPES String procName = "ALL_DATA_TYPES"; String sql = "CALL " + procName + "(:iosmall, :ioint, :iobigint, :ioreal, :iodouble, :char1, :char2, :varchar, :date, :time)"; CallableStatement callStmt = con.prepareCall(sql); // declare and initialize input variables short inoutSmallint = 32000; int inoutInteger = 2147483000; long inoutBigint = 2147483000; float inoutReal = 100000; double inoutDouble = 2500000; // declare output variables String outChar, outChars, outVarchar; Date outDate; Time outTime; // set input parameters ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccShortAtName("iosmall", inoutSmallint); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccIntAtName("ioint", inoutInteger); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccLongAtName("iobigint", inoutBigint); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccFloatAtName("ioreal", inoutReal); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccDoubleAtName("iodouble", inoutDouble); // register the output parameters ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("iosmall", Types.SMALLINT); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("ioint", Types.INTEGER); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("iobigint", Types.BIGINT); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("ioreal", Types.REAL); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("iodouble", Types.DOUBLE); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("char1", Types.CHAR); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("char2", Types.CHAR); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("varchar", Types.VARCHAR); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("date", Types.DATE); ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("time", Types.TIME); // call the stored procedure System.out.println(); System.out.println("Call stored procedure named " + procName); callStmt.execute(); System.out.println(procName + " completed successfully"); // retrieve output parameters inoutSmallint = callStmt.getShort(1); inoutInteger = callStmt.getInt(2); inoutBigint = callStmt.getLong(3); inoutReal = callStmt.getFloat(4); inoutDouble = callStmt.getDouble(5); outChar = callStmt.getString(6); outChars = callStmt.getString(7); outVarchar = callStmt.getString(8); outDate = callStmt.getDate(9); outTime = callStmt.getTime(10); System.out.println("Value of SMALLINT = " + inoutSmallint); System.out.println("Value of INTEGER = " + inoutInteger); System.out.println("Value of BIGINT = " + inoutBigint); System.out.println("Value of REAL = " + inoutReal); System.out.println("Value of DOUBLE = " + inoutDouble); System.out.println("Value of CHAR(1) = " + outChar); System.out.println("Value of CHAR(15) = " + outChars.trim()); System.out.println("Value of VARCHAR(12) = " + outVarchar.trim()); System.out.println("Value of DATE = " + outDate); System.out.println("Value of TIME = " + outTime); callStmt.close(); } catch (SQLException e) { System.out.println(e.getMessage()); } } // callAllDataTypes public static void callOneResultSet(Connection con) { try { // prepare the CALL statement for ONE_RESULT_SET String procName = "ONE_RESULT_SET"; String sql = "CALL " + procName + "(:outMedian)"; CallableStatement callStmt = con.prepareCall(sql); // set input parameter to median value passed back by OUT_PARAM ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccDoubleAtName("outMedian", outMedian); // call the stored procedure System.out.println(); System.out.println("Call stored procedure named " + procName); callStmt.execute(); System.out.println(procName + " completed successfully"); ResultSet rs = callStmt.getResultSet(); fetchAll(rs); // close ResultSet and callStmt rs.close(); callStmt.close(); } catch (SQLException e) { System.out.println(e.getMessage()); } } // callOneResultSet public static void callTwoResultSets(Connection con) { try { // prepare the CALL statement for TWO_RESULT_SETS String procName = "TWO_RESULT_SETS"; String sql = "CALL " + procName + "(:outMedian)"; CallableStatement callStmt = con.prepareCall(sql); // set input parameter to median value passed back by OUT_PARAM ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccDoubleAtName("outMedian", outMedian); // call the stored procedure System.out.println(); System.out.println("Call stored procedure named " + procName); callStmt.execute(); System.out.println(procName + " completed successfully"); System.out.println( "Result set 1: Employees with salaries greater than " + outMedian); // get first result set ResultSet rs = callStmt.getResultSet(); fetchAll(rs); System.out.println(); System.out.println("Result set 2: Employees with salaries less than " + outMedian); // get second result set callStmt.getMoreResults(); rs = callStmt.getResultSet(); fetchAll(rs); // close ResultSet and callStmt rs.close(); callStmt.close(); } catch (SQLException e) { System.out.println(e.getMessage()); } } // callTwoResultSets //************************************************************************* // PARAMETER STYLE GENERAL and GENERAL WITH NULLS can be specified when // LANGUAGE C, COBOL, or CLR is used. // Please see the SpClient implementation for CLI language to see this // functionality. //************************************************************************* // ====================================================== // Method: fetchAll -- returns all rows from a result set // ====================================================== public static void fetchAll(ResultSet rs) { try { System.out.println( "============================================================="); // retrieve the number, types and properties of the // resultset's columns 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++) { if (i == 3) { System.out.print(Data.format(rs.getDouble(i), 7, 2)); } else { System.out.print(rs.getString(i)); } if (i != numOfColumns) { System.out.print(", "); } } System.out.println(); } } catch (Exception e) { System.out.println("Error: fetchALL: exception"); System.out.println(e.getMessage()); } } // fetchAll } // SpServer