:::::::::::::::::Client Application:::::::::::::::::::::::
// Source File Name: Outcli.java 1.2
//
// Licensed Materials -- Property of IBM
// Sample Program Outcli - Calling the SQLJ Output Stored Procedure // Steps to run the sample:
// (1) create and populate the SAMPLE database (db2sampl)
// (2) (n)make Outsrv
// (3) (n)make Outcli
// (4) run Outcli // Class Outcli does the following:
// (1) register the stored procedure
// (2) call the stored procedure import java.sql.*; // JDBC classes class Outcli
{ static
{ try
{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
}
catch (Exception e)
{ System.out.println ("\n Error loading DB2 Driver...\n" + e);
System.exit(1);
}
} public static void main (String argv[])
{ try
{ System.out.println (" Java Output Stored Procedure Sample");
// Connect to Sample database Connection con = null;
// URL is jdbc:db2:dbname
String url = "jdbc:db2:sample"; if (argv.length == 0) /* :rk.2:erk. */
{ // 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 Outcli [username password]\n");
} // Set AutoCommit
con.setAutoCommit(true); // Register and call the Remote Procedure
String callName = "Outsrv";
String storedProcName = "Outsrv!outputStoredProcedure";
String mode = "fenced"; Statement stmt = con.createStatement (); // for DROP/CREATE
// drop the stored procedure if it exists
try
{ stmt.executeUpdate ("DROP PROCEDURE " + callName);
}
catch (Exception e)
{ // ignore this error
} // construct a parameter list for the stored procedure and
// register it in the system catalogs /* :rk.3:erk. */
String parameterList = "(out medianSalary double)"; System.out.println ("\n Registering Java stored procedure " + callName +
"\n as " + storedProcName +
"\n in " + mode + " mode"); stmt.executeUpdate ("CREATE PROCEDURE " + callName + parameterList +
" LANGUAGE JAVA " +
" PARAMETER STYLE JAVA " + mode +
" EXTERNAL NAME '" + storedProcName + "'" +
" READS SQL DATA");
stmt.close (); // prepare the CALL statement
CallableStatement callableStmt;
String sql = "Call " + callName + "(?) ";
callableStmt = con.prepareCall (sql); // register the output parameters /* :rk.4:erk. */
callableStmt.registerOutParameter (1, Types.DOUBLE); // set all parameters
callableStmt.setDouble (1, 0.00);
// call the stored procedure
con.setAutoCommit(false); // Enable transactions
try
{ System.out.println ("\n Calling stored procedure : " + callName );
callableStmt.execute (); /* :rk.5:erk. */ // Commit the transaction
con.commit();
}
catch (Exception e)
{ // Rollback the transaction
con.rollback();
throw e;
}
finally
{ // Restore initial AutoCommit
con.setAutoCommit(true);
}
// retrieve output parameters
double medianSalary = callableStmt.getDouble (1); // display the information returned from the stored procedure
System.out.println ("\n Median Salary: " + medianSalary);
callableStmt.close (); /* :rk.6:erk. */
}
catch (Exception e)
{ System.out.println (e);
}
}
}
// Source File Name: Outcli.java 1.2
//
// Licensed Materials -- Property of IBM
// Sample Program Outcli - Calling the SQLJ Output Stored Procedure // Steps to run the sample:
// (1) create and populate the SAMPLE database (db2sampl)
// (2) (n)make Outsrv
// (3) (n)make Outcli
// (4) run Outcli // Class Outcli does the following:
// (1) register the stored procedure
// (2) call the stored procedure import java.sql.*; // JDBC classes class Outcli
{ static
{ try
{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
}
catch (Exception e)
{ System.out.println ("\n Error loading DB2 Driver...\n" + e);
System.exit(1);
}
} public static void main (String argv[])
{ try
{ System.out.println (" Java Output Stored Procedure Sample");
// Connect to Sample database Connection con = null;
// URL is jdbc:db2:dbname
String url = "jdbc:db2:sample"; if (argv.length == 0) /* :rk.2:erk. */
{ // 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 Outcli [username password]\n");
} // Set AutoCommit
con.setAutoCommit(true); // Register and call the Remote Procedure
String callName = "Outsrv";
String storedProcName = "Outsrv!outputStoredProcedure";
String mode = "fenced"; Statement stmt = con.createStatement (); // for DROP/CREATE
// drop the stored procedure if it exists
try
{ stmt.executeUpdate ("DROP PROCEDURE " + callName);
}
catch (Exception e)
{ // ignore this error
} // construct a parameter list for the stored procedure and
// register it in the system catalogs /* :rk.3:erk. */
String parameterList = "(out medianSalary double)"; System.out.println ("\n Registering Java stored procedure " + callName +
"\n as " + storedProcName +
"\n in " + mode + " mode"); stmt.executeUpdate ("CREATE PROCEDURE " + callName + parameterList +
" LANGUAGE JAVA " +
" PARAMETER STYLE JAVA " + mode +
" EXTERNAL NAME '" + storedProcName + "'" +
" READS SQL DATA");
stmt.close (); // prepare the CALL statement
CallableStatement callableStmt;
String sql = "Call " + callName + "(?) ";
callableStmt = con.prepareCall (sql); // register the output parameters /* :rk.4:erk. */
callableStmt.registerOutParameter (1, Types.DOUBLE); // set all parameters
callableStmt.setDouble (1, 0.00);
// call the stored procedure
con.setAutoCommit(false); // Enable transactions
try
{ System.out.println ("\n Calling stored procedure : " + callName );
callableStmt.execute (); /* :rk.5:erk. */ // Commit the transaction
con.commit();
}
catch (Exception e)
{ // Rollback the transaction
con.rollback();
throw e;
}
finally
{ // Restore initial AutoCommit
con.setAutoCommit(true);
}
// retrieve output parameters
double medianSalary = callableStmt.getDouble (1); // display the information returned from the stored procedure
System.out.println ("\n Median Salary: " + medianSalary);
callableStmt.close (); /* :rk.6:erk. */
}
catch (Exception e)
{ System.out.println (e);
}
}
}
//
// Licensed Materials -- Property of IBM // Sample Program Outsrv - SQLJ Output Stored Procedure // Steps to run the sample:
// (1) create and populate the SAMPLE database (db2sampl)
// (2) (n)make Outsrv
// (3) (n)make Outcli
// (4) run Outcli
// Class Outsrv contains one method:
// (1) outputStoredProcedure: stored procedure body import java.sql.*; // JDBC classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;#sql iterator Outsrv_Cursor1 (double salary) ;// /////
// Java stored procedure is in this class
// /////
public class Outsrv
{ // (1) stored procedure body
public static void outputStoredProcedure ( /* :rk.2:erk. */
double[] medianSalary) throws Exception
{ try
{
// Declare Variables
Outsrv_Cursor1 cursor1;
short numRecords;
int counter = 0; // Determine the Total Number of Records
#sql { SELECT COUNT(*) INTO :numRecords FROM STAFF }; /* :rk.3:erk. */
// Prepare a Statement to Obtain and Order all Salaries
#sql cursor1 = { SELECT salary FROM STAFF ORDER BY salary }; // Fetch Salaries until the Median Salary is Obtained
while (counter < numRecords/2 + 1) /* :rk.4:erk. */
{ cursor1.next();
counter++;
}
// set value for the output parameter /* :rk.5:erk. */
medianSalary[0] = cursor1.salary();
cursor1.close();
}
catch (Exception e)
{ throw e;
}
}
}