Stored Procedures in Java (SPJs) Guide

public static void supplyQuantities( int[] avgQty
                                   , int[] minQty
                                   , int[] maxQty
                                   ) throws SQLException
{

...

}

String s = System.property( "sqlj.defaultconnection" ) ;
if ( s == null )
{
   s = other-url ;
}

Connection c = DriverManager.getConnection( s ) ;

java.util.Properties props = new Properties() ;

props.setProperty( "maxPoolSize", "10" ) ;
props.setProperty( "minPoolSize", "5" ) ;
props.setProperty( "initialPoolSize", "5" ) ;

Connection conn =
   DriverManager.getConnection( "jdbc:default:connection", props ) ;

// The LOWERPRICE procedure determines which items are selling poorly (that
// is, have less than 50 orders) and lowers the price of these items in the
// database by 10 percent.
//
// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#lowerprice-procedure
// for more documentation.
public static void lowerPrice() throws SQLException
{
   Connection conn =
      DriverManager.getConnection( "jdbc:default:connection" ) ;

      PreparedStatement getParts =
         conn.prepareStatement( "SELECT p.partnum, "
                              + "SUM(qty_ordered) AS qtyOrdered "
                              + "FROM trafodion.sales.parts p "
                              + "LEFT JOIN trafodion.sales.odetail o "
                              + "ON p.partnum = o.partnum "
                              + "GROUP BY p.partnum"
                              ) ;

      PreparedStatement updateParts =
         conn.prepareStatement( "UPDATE trafodion.sales.parts "
                              + "SET price = price * 0.9 "
                              + "WHERE partnum = ?"
                              ) ;

      ResultSet rs = getParts.executeQuery() ;
      while ( rs.next() )
      {
         BigDecimal qtyOrdered = rs.getBigDecimal( 2 ) ;

         if (( qtyOrdered == null ) || ( qtyOrdered.intValue() < 50 ) )
         {
            BigDecimal partnum = rs.getBigDecimal( 1 ) ;
            updateParts.setBigDecimal( 1, partnum ) ;
            updateParts.executeUpdate() ;
         }
      }

      rs.close() ;
      conn.close() ;

}

CREATE PROCEDURE trafodion.sales.lowerprice( )
   EXTERNAL NAME 'Sales.lowerPrice'
   LIBRARY trafodion.sales.sales
   LANGUAGE JAVA
   PARAMETER STYLE JAVA
   MODIFIES SQL DATA
   ;

SQL> CALL trafodion.sales.lowerprice() ;

--- SQL operation complete.

SELECT *
FROM
   ( SELECT p.partnum
   , SUM(qty_ordered) AS qtyOrdered
   , p.price
     FROM trafodion.sales.parts p
     LEFT OUTER JOIN trafodion.sales.odetail o ON p.partnum = o.partnum
     GROUP BY p.partnum, p.price
   ) AS allparts
WHERE qtyOrdered < 51
ORDER BY partnum ASC
;

PARTNUM QTYORDERED           PRICE
------- -------------------- ----------
    212                   20    2025.00
    244                   47    2430.00
    255                   38    3240.00
   2002                   46    1215.00
   2405                   18     643.95
   3103                   40    3402.00
    ...                  ...        ...

--- 17 row(s) selected.

PARTNUM QTYORDERED           PRICE
------- -------------------- ----------
    212                   20    1822.50
    244                   47    2187.00
    255                   38    2916.00
   2002                   46    1093.50
   2405                   18     579.55
   3103                   40    3061.80
... ... ...

--- 17 row(s) selected.

// The DAILYORDERS procedure accepts a date and returns the number of
//orders on that date to an output parameter.
//
// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#dailyorders-procedure
// for additional documenation.
public static void numDailyOrders( Date date
                                 , int[] numOrders
                                 ) throws SQLException
{
   Connection conn =
      DriverManager.getConnection( "jdbc:default:connection" ) ;

   PreparedStatement getNumOrders =
      conn.prepareStatement( "SELECT COUNT(order_date) "
                           + "FROM trafodion.sales.orders "
                           + "WHERE order_date = ?"
                           ) ;

   getNumOrders.setDate( 1, date ) ;

   ResultSet rs = getNumOrders.executeQuery() ;
   rs.next() ;

   numOrders[0] = rs.getInt( 1 ) ;

   rs.close() ;
   conn.close() ;

}

CREATE PROCEDURE trafodion.sales.dailyorders( IN date1 DATE
                                            , OUT number INTEGER
                                            )
   EXTERNAL NAME 'Sales.numDailyOrders'
   LIBRARY trafodion.sales.sales
   LANGUAGE JAVA
   PARAMETER STYLE JAVA
   READS SQL DATA
   ;

SQL> CALL trafodion.sales.dailyorders( DATE '2011-03-19', ? ) ;

NUMBER
-----------
          2

--- SQL operation complete.

// The MONTHLYORDERS procedure accepts an integer representing the month
// and returns the number of orders during that month to an output parameter.
//
// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#monthlyorders-procedure
// for more documentation.
public static void numMonthlyOrders( int month
                                   , int[] numOrders
                                   ) throws SQLException

{
   if ( month < 1 || month > 12 )
   {
      throw new SQLException( "Invalid value for month. "
                            + "Retry the CALL statement "
                            + "using a number from 1 to 12 "
                            + "to represent the month."
                            , "38001"
                            ) ;
   }

   Connection conn =
      DriverManager.getConnection( "jdbc:default:connection" ) ;

   PreparedStatement getNumOrders =
      conn.prepareStatement( "SELECT COUNT( month( order_date ) ) "
                           + "FROM trafodion.sales.orders "
                           + "WHERE month( order_date ) = ?"
                           ) ;

   getNumOrders.setInt( 1, month ) ;

   ResultSet rs = getNumOrders.executeQuery() ;
   rs.next() ;

   numOrders[0] = rs.getInt(1) ;

   rs.close() ;
   conn.close();

}

CREATE PROCEDURE trafodion.sales.monthlyorders( IN monthnum INTEGER
                                              , OUT ordernum INTEGER
                                              )
   EXTERNAL NAME 'Sales.numMonthlyOrders'
   LIBRARY trafodion.sales.sales
   LANGUAGE JAVA
   PARAMETER STYLE JAVA
   READS SQL DATA
   ;

SQL> CALL trafodion.sales.monthlyorders( 3, ? ) ;

ORDERNUM
-----------
          4

--- SQL operation complete.

// The TOTALPRICE procedure accepts the quantity, shipping speed, and price
// of an item, calculates the total price, including tax and shipping
// charges, and returns the total price to an input/output parameter.
//
// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#totalprice-procedure
// for more documentation.
public static void totalPrice( BigDecimal qtyOrdered
                             , String shippingSpeed
                             , BigDecimal[] price
                             ) throws SQLException
{
   BigDecimal shipcharge = new BigDecimal( 0 ) ;

   if ( shippingSpeed.equals( "economy" ) )
   {
      shipcharge = new BigDecimal( 1.95 ) ;
   }
   else if ( shippingSpeed.equals( "standard" ) )
   {
      shipcharge = new BigDecimal( 4.99 ) ;
   }
   else if ( shippingSpeed.equals( "nextday" ) )
   {
      shipcharge = new BigDecimal( 14.99 ) ;
   }
   else
   {
      throw new SQLException( "Invalid value for shipping speed. "
                            + "Retry the CALL statement using "
                            + "'economy' for 7 to 9 days, "
                            + "'standard' for 3 to 5 days, or "
                            + "'nextday' for one day."
                            , "38002"
                            ) ;
   }

   BigDecimal subtotal   = price[0].multiply( qtyOrdered ) ;
   BigDecimal tax        = new BigDecimal( 0.0825 ) ;
   BigDecimal taxcharge  = subtotal.multiply( tax ) ;
   BigDecimal charges    = taxcharge.add( shipcharge ) ;
   BigDecimal totalprice = subtotal.add( charges ) ;

   totalprice = totalprice.setScale( 2, BigDecimal.ROUND_HALF_EVEN ) ;
   price[0] = totalprice ;

}

CREATE PROCEDURE trafodion.sales.totalprice( IN gty NUMERIC(18)
                                           , IN speed VARCHAR(10)
                                           , INOUT price NUMERIC(18,2)
                                           )
   EXTERNAL NAME 'Sales.totalPrice ( java.math.BigDecimal, java.lang.String, java.math.BigDecimal[] )'
   LIBRARY trafodion.sales.sales
   LANGUAGE JAVA
   PARAMETER STYLE JAVA
   NO SQL
   ;

SQL> SET PARAM ?p 10 ;
SQL> CALL trafodion.sales.totalprice( 23, 'standard', ?p ) ;

p
--------------------
              253.97

--- SQL operation complete.

// The PARTDATA procedure accepts a part number and returns this
// information about the part:
//
// * Part description, price, and quantity available as output parameters.
// * A result set that contains rows from the ORDERS table about when this part was ordered.
// * A result set that contains rows from the PARTLOC table, listing locations that have this
//   part in stock and the quantity they have on hand.
// * A result set that contains rows from the PARTSUPP table for suppliers who carry this part.
// * A result set that contains rows from the EMPLOYEE table for sales reps who have sold this part.
//
// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#partdata-procedure
// for more documentation.
public static void partData( int partNum
                           , String[] partDescription
                           , BigDecimal[] unitPrice
                           , int[] qtyAvailable
                           , ResultSet[] orders
                           , ResultSet[] locations
                           , ResultSet[] suppliers
                           , ResultSet[] reps
                           ) throws SQLException
{

   Connection conn =
      DriverManager.getConnection( "jdbc:default:connection" ) ;

   // Retrieve detail about this part into the output parameters
   PreparedStatement getPartInfo =
     conn.prepareStatement( "SELECT P.partdesc, P.price, P.qty_available "
                          + "FROM trafodion.sales.parts P "
                          + "WHERE partnum = ? "
                          ) ;

   getPartInfo.setInt( 1, partNum ) ;

   ResultSet rs = getPartInfo.executeQuery() ;
   rs.next() ;

   partDescription[0] = rs.getString( 1 ) ;
   unitPrice[0]       = rs.getBigDecimal( 2 ) ;
   qtyAvailable[0]    = rs.getInt( 3 ) ;

   rs.close();

   // Return a result set of rows from the ORDERS table listing orders
   // that included this part. Each ORDERS row is augmented with the
   // quantity of this part that was ordered.
   PreparedStatement getOrders =
      conn.prepareStatement( "SELECT O.*, QTY.QTY_ORDERED "
                           + "FROM   trafodion.sales.orders O "
                           + "     , ( select ordernum, sum(qty_ordered) as QTY_ORDERED "
                           + "         from trafodion.sales.odetail "
                           + "         where partnum = ? "
                           + "         group by ordernum ) QTY "
                           + "WHERE O.ordernum = QTY.ordernum "
                           + "ORDER BY O.ordernum "
                           ) ;


    getOrders.setInt( 1, partNum ) ;
    orders[0] = getOrders.executeQuery() ;

    // Return a result set of rows from the PARTLOC table listing
    // locations that have this part in stock and the quantity they
    // have on hand.
    PreparedStatement getLocations =
       conn.prepareStatement( "SELECT * "
                            + "FROM trafodion.invent.partloc "
                            + "WHERE partnum = ? "
                            ) ;

    getLocations.setInt( 1, partNum ) ;
    locations[0] = getLocations.executeQuery() ;

    // Return a result set of rows from the PARTSUPP table listing
    // suppliers who supply this part.
    PreparedStatement getSuppliers =
       conn.prepareStatement( "SELECT * "
                            + "FROM trafodion.invent.partsupp "
                            + "WHERE partnum = ? "
                            ) ;

    getSuppliers.setInt( 1, partNum ) ;
    suppliers[0] = getSuppliers.executeQuery() ;

    // Return a result set of rows from the EMPLOYEE table listing
    // sales reps that have sold this part.
    PreparedStatement getReps =
       conn.prepareStatement( "SELECT * "
                            + "FROM trafodion.persnl.employee "
                            + "WHERE empnum in ( SELECT O.salesrep "
                            + "                  FROM trafodion.sales.orders O, "
                            + "                       trafodion.sales.odetail D "
                            + "                  WHERE D.partnum = ? "
                            + "                    AND O.ordernum = D.ordernum ) "
                            + "ORDER BY empnum "
                            ) ;

     getReps.setInt( 1, partNum ) ;
     reps[0] = getReps.executeQuery() ;

}

CREATE PROCEDURE trafodion.sales.partdata( IN partnum INTEGER
                                         , OUT partdesc CHARACTER(18)
                                         , OUT price NUMERIC(18,2)
                                         , OUT qty_available INTEGER
                                         )
   EXTERNAL NAME 'Sales.partData( int, java.lang.String[], java.math.BigDecimal[], int[] )'
   LIBRARY trafodion.sales.sales
   LANGUAGE JAVA
   PARAMETER STYLE JAVA
   DYNAMIC RESULT SETS 4
   READS SQL DATA
   ;

SQL> CALL trafodion.sales.partdata( 212, ?, ?, ?) ;

PARTDESC           PRICE                QTY_AVAILABLE
------------------ -------------------- -------------
PC SILVER, 20 MB                1822.50          3525

ORDERNUM ORDER_DATE DELIV_DATE SALESREP CUSTNUM QTY_ORDERED
-------- ---------- ---------- -------- ------- --------------------
  400410 2011-03-27 2011-09-01      227    7654                   12
  500450 2011-04-20 2011-09-15      220     324                    8

--- 2 row(s) selected.

LOC_CODE PARTNUM QTY_ON_HAND
-------- ------- -----------
A87          212          18
G87          212          20

--- 2 row(s) selected.

PARTNUM SUPPNUM PARTCOST   QTY_RECEIVED
------- ------- ---------- ------------
    212       1    2000.00           20
    212       3    1900.00           35

--- 2 row(s) selected.

EMPNUM FIRST_NAME      LAST_NAME            DEPTNUM JOBCODE SALARY
------ --------------- -------------------- ------- ------- ----------
   220 JOHN            HUGHES                  3200     300   33000.10
   227 XAVIER          SEDLEMEYER              3300     300   30000.00

--- 2 row(s) selected.

--- SQL operation complete.

// The ORDERSUMMARY procedure accepts a date, which is formatted as a
// string, and returns this information about the orders on or after that
// date:
//
// * The number of orders as an output parameter
// * A result set that contains one row for each order. Each row contains
//   fields for the order number, the number of parts ordered, total dollar
//   amount, order date, and the name of the sales representative.
// * A result set that contains details about each order. Each order has
//   one or more rows that provide details about the ordered parts. Each row
//   contains fields for the order number, part number, unit price, quantity
//   ordered, and part description.
//
// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#ordersummary-procedure
// for more documentation.
public static void orderSummary( java.lang.String onOrAfter
                               , long[] numOrders
                               , java.sql.ResultSet[] orders
                               , java.sql.ResultSet[] detail
                               ) throws SQLException
{
   java.lang.String s ;

   java.sql.Connection conn =
      DriverManager.getConnection( "jdbc:default:connection" ) ;

   // Get the number of orders on or after this date
   s =   "SELECT COUNT(ordernum) FROM trafodion.sales.orders "
       + "WHERE order_date >= CAST(? AS DATE) "
       ;

   java.sql.PreparedStatement ps1 = conn.prepareStatement( s ) ;
   ps1.setString( 1, onOrAfter ) ;

   java.sql.ResultSet rs = ps1.executeQuery() ;
   rs.next() ;

   numOrders[0] = rs.getLong( 1 ) ;
   rs.close() ;

   // Open a result set for order num, order info rows
   s =   "SELECT amounts.*, orders.order_date, emps.last_name "
       + "FROM ( SELECT "
       + "         o.ordernum "
       + "       , COUNT(d.partnum) AS num_parts "
       + "       , SUM(d.unit_price * d.qty_ordered) AS amount "
       + "       FROM trafodion.sales.orders o, trafodion.sales.odetail d "
       + "       WHERE o.ordernum = d.ordernum "
       + "          AND o.order_date >= CAST(? AS DATE) "
       + "       GROUP BY o.ordernum "
       + "      ) amounts "
       + "      , trafodion.sales.orders orders "
       + "      , trafodion.persnl.employee emps "
       + "WHERE amounts.ordernum = orders.ordernum "
       + "   AND orders.salesrep = emps.empnum "
       + "ORDER BY orders.ordernum "
       ;

    java.sql.PreparedStatement ps2 = conn.prepareStatement( s ) ;
    ps2.setString( 1, onOrAfter ) ;
    orders[0] = ps2.executeQuery() ;

    // Open a result set for order detail rows
    s =   "SELECT d.*, p.partdesc "
        + "FROM trafodion.sales.odetail d, trafodion.sales.parts p, trafodion.sales.orders O "
        + "WHERE d.partnum = p.partnum AND d.ordernum = o.ordernum "
        + "   AND o.order_date >= CAST(? AS DATE) "
        + "ORDER BY d.ordernum "
        ;

    java.sql.PreparedStatement ps3 = conn.prepareStatement( s ) ;
    ps3.setString( 1, onOrAfter ) ;
    detail[0] = ps3.executeQuery() ;

}

CREATE PROCEDURE trafodion.sales.ordersummary( IN on_or_after_date VARCHAR(20)
                                             , OUT num_orders LARGEINT
                                             )
   EXTERNAL NAME 'Sales.orderSummary ( java.lang.String, long[] )'
   LIBRARY trafodion.sales.sales
   LANGUAGE JAVA
   PARAMETER STYLE JAVA
   DYNAMIC RESULT SETS 2
   READS SQL DATA
   ;

SQL> CALL trafodion.sales.ordersummary( '2011-01-01', ? ) ;

NUM_ORDERS
--------------------
                  13

ORDERNUM NUM_PARTS            AMOUNT               ORDER_DATE LAST_NAME
-------- -------------------- -------------------- ---------- --------------------
  100210                    4             19020.00 2011-04-10 HUGHES
  100250                    4             22625.00 2011-01-23 HUGHES
  101220                    4             45525.00 2011-07-21 SCHNABL
     ...                  ...                  ...        ... ...
--- 13 row(s) selected.

ORDERNUM PARTNUM UNIT_PRICE QTY_ORDERED PARTDESC
-------- ------- ---------- ----------- ------------------
  100210     244    3500.00           3 PC GOLD, 30 MB
  100210    5100     150.00          10 MONITOR BW, TYPE 1
  100210    2403     620.00           6 DAISY PRINTER,T2
  100210    2001    1100.00           3 GRAPHIC PRINTER,M1
  100250    6500      95.00          10 DISK CONTROLLER
  100250    6301     245.00          15 GRAPHIC CARD, HR
  100250     244    3500.00           4 PC GOLD, 30 MB
  100250    5103     400.00          10 MONITOR COLOR, M1
     ...     ...        ...         ... ...

--- 70 row(s) selected.

--- SQL operation complete.

// The ADJUSTSALARY procedure accepts an employee number and a percentage
// value and updates the employee's salary in the database based on that
// percentage. This method also returns the updated salary to an output
// parameter.
//
// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#adjustsalary-procedure
// for more documentation.
public static void adjustSalary( BigDecimal empNum
                               , double percent
                               , BigDecimal[] newSalary
                               ) throws SQLException
{
   Connection conn =
      DriverManager.getConnection( "jdbc:default:connection" ) ;

   PreparedStatement setSalary =
      conn.prepareStatement( "UPDATE trafodion.persnl.employee "
                           + "SET salary = salary * (1 + (? / 100)) "
                           + "WHERE empnum = ?"
                           ) ;

   PreparedStatement getSalary =
      conn.prepareStatement( "SELECT salary "
                           + "FROM trafodion.persnl.employee "
                           + "WHERE empnum = ?"
                           ) ;

   setSalary.setDouble( 1, percent ) ;
   setSalary.setBigDecimal( 2, empNum ) ;
   setSalary.executeUpdate() ;

   getSalary.setBigDecimal( 1, empNum ) ;
   ResultSet rs = getSalary.executeQuery() ;
   rs.next() ;

   newSalary[0] = rs.getBigDecimal( 1 ) ;

   rs.close() ;
   conn.close() ;

}

CREATE PROCEDURE trafodion.persnl.adjustsalary( IN empnum NUMERIC(4)
                                              , IN percent FLOAT
                                              , OUT newsalary NUMERIC(8,2)
                                              )
   EXTERNAL NAME 'Payroll.adjustSalary'
   LIBRARY trafodion.persnl.payroll
   LANGUAGE JAVA
   PARAMETER STYLE JAVA
   MODIFIES SQL DATA
   ;

SQL> CALL trafodion.persnl.adjustsalary(29, 2.5, ?) ;

NEWSALARY
----------
 139400.00

--- SQL operation complete.

// The EMPLOYEEJOB procedure accepts an employee number and returns a job
// code or null value to an output parameter.
//
// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#employeejob-procedure
// for more documentation.
public static void employeeJob( int empNum
                              , java.lang.Integer[] jobCode
                              ) throws SQLException
{
   Connection conn =
      DriverManager.getConnection( "jdbc:default:connection" ) ;

   PreparedStatement getJobcode =
      conn.prepareStatement( "SELECT jobcode "
                           + "FROM trafodion.persnl.employee "
                           + "WHERE empnum = ?"
                           ) ;

   getJobcode.setInt( 1, empNum ) ;
   ResultSet rs = getJobcode.executeQuery() ;
   rs.next() ;

   int num = rs.getInt(1) ;
   if ( rs.wasNull() )
      jobCode[0] = null ;
   else
      jobCode[0] = new Integer(num) ;

   rs.close() ;
   conn.close() ;

}

CREATE PROCEDURE trafodion.persnl.employeejob( IN empnum INT
                                              , OUT jobcode INT
                                              )
   EXTERNAL NAME 'Payroll.employeeJob ( int, java.lang.Integer[] )'
   LIBRARY trafodion.persnl.payroll
   LANGUAGE JAVA
   PARAMETER STYLE JAVA
   READS SQL DATA
   ;

SQL> CALL trafodion.persnl.employeejob(337, ?) ;

JOBCODE
-----------
        900

--- SQL operation complete.

// The PROJECTTEAM procedure accepts a project code and returns the
// employee number, first name, last name, and location of the employees
// assigned to that project.
//
// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#projectteam-procedure
// for more documentation.
public static void projectTeam( int projectCode
                              , ResultSet[] members
                              ) throws SQLException
{
   Connection conn =
      DriverManager.getConnection( "jdbc:default:connection" ) ;

   PreparedStatement getMembers =
      conn.prepareStatement( "SELECT E.empnum, E.first_name, E.last_name, D.location "
                           + "FROM trafodion.persnl.employee E, trafodion.persnl.dept D, trafodion.persnl.project P "
                           + "WHERE P.projcode = ? "
                           + "  AND P.empnum = E.empnum "
                           + "  AND E.deptnum = D.deptnum "
                           ) ;

    getMembers.setInt( 1, projectCode ) ;
    members[0] = getMembers.executeQuery() ;

}

CREATE PROCEDURE trafodion.persnl.projectteam( IN projectcode INTEGER
                                             )
   EXTERNAL NAME 'Payroll.projectTeam'
   LIBRARY trafodion.persnl.payroll
   LANGUAGE JAVA
   PARAMETER STYLE JAVA
   DYNAMIC RESULT SETS 1
   READS SQL DATA
   ;

SQL> CALL trafodion.persnl.projectteam( 5000 ) ;

EMPNUM FIRST_NAME      LAST_NAME             LOCATION
------ --------------- -------------------- ------------------
    65 RACHEL          MCKAY                NEW YORK
   203 KATHRYN         HALL                 NEW YORK
... ... ...

--- 6 row(s) selected.

--- SQL operation complete.

// The TOPSALESREPS procedure accepts a number representing the fiscal
// quarter (1, 2, 3, and 4, with each number representing a range of
// months) and returns the employee number, first name, last name, and sale
// figures of the top five sales representatives who had the highest sales
// (unit_price * qty_ordered) that quarter.
//
// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#topsalesreps-procedure
// for more documentation.
public static void topSalesReps( int whichQuarter
                               , ResultSet[] topReps
                               ) throws SQLException
{
   if ( whichQuarter < 1 || whichQuarter > 4 )
   {
      throw new SQLException ( "Invalid value for quarter. "
                             + "Retry the CALL statement "
                             + "using a number from 1 to 4 "
                             + "to represent the quarter."
                             , "38001"
                             ) ;
   }

   Connection conn =
      DriverManager.getConnection( "jdbc:default:connection" ) ;

   PreparedStatement getTopReps =
     conn.prepareStatement( "SELECT [first 5] e.empnum, e.first_name, "
                          + "e.last_name, totals.total "
                          + "FROM trafodion.persnl.employee e, "
                          + "   ( SELECT o.salesrep, "
                          + "     SUM( od.unit_price * od.qty_ordered ) as total "
                          + "     FROM trafodion.sales.orders o, trafodion.sales.odetail od "
                          + "     WHERE o.ordernum = od.ordernum "
                          + "       AND QUARTER( o.order_date ) = ? "
                          + "     GROUP BY o.salesrep "
                          + "    ) totals "
                          + "WHERE e.empnum = totals.salesrep "
                          + "ORDER BY totals.total DESCENDING "
                          ) ;

   getTopReps.setInt( 1, whichQuarter ) ;
   topReps[0] = getTopReps.executeQuery() ;
}

CREATE PROCEDURE trafodion.persnl.topsalesreps( IN whichquarter INTEGER
                                              )
   EXTERNAL NAME 'Payroll.topSalesReps'
   LIBRARY trafodion.persnl.payroll
   LANGUAGE JAVA
   PARAMETER STYLE JAVA
   DYNAMIC RESULT SETS 1
   READS SQL DATA
   ;

SQL> CALL trafodion.persnl.topsalesreps( 1 ) ;

EMPNUM FIRST_NAME      LAST_NAME            TOTAL
------ --------------- -------------------- --------------------
   227 XAVIER          SEDLEMEYER                      172460.00
   231 HERB            ALBERT                           67025.00
   222 MARTIN          SCHAEFFER                        52000.00
   226 HEIDI           WEIGL                            28985.00
   220 JOHN            HUGHES                           22625.00

--- 5 row(s) selected.

--- SQL operation complete.

Unresolved directive in asciidoc/_chapters/sample_spjs.adoc - include::../../resources/source/supplierInfo.java[supplierInfo procedure source code]