trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dbirds...@apache.org
Subject [40/50] [abbrv] incubator-trafodion git commit: update
Date Thu, 31 Mar 2016 22:16:51 GMT
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/spj_guide/src/asciidoc/_chapters/sample_spjs.adoc
----------------------------------------------------------------------
diff --git a/docs/spj_guide/src/asciidoc/_chapters/sample_spjs.adoc b/docs/spj_guide/src/asciidoc/_chapters/sample_spjs.adoc
new file mode 100644
index 0000000..1c4fc8e
--- /dev/null
+++ b/docs/spj_guide/src/asciidoc/_chapters/sample_spjs.adoc
@@ -0,0 +1,2386 @@
+////
+/**
+ *@@@ START COPYRIGHT @@@
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * @@@ END COPYRIGHT @@@
+ */
+////
+
+[[a-sample-spjs]]
+= A Sample SPJs
+
+This appendix presents the SPJs that are shown in examples throughout
+this manual. The class files that contain the SPJ methods use JDBC
+method calls to access a sample database. For information about the
+sample database, see link:#_bookmark336[Appendix B (page 103)].
+
+* link:#_bookmark223["Procedures in the SALES Schema" (page 62)]
+* link:#_bookmark274["Procedures in the PERSNL Schema" (page 83)]
+* link:#_bookmark309["Procedures in the INVENT Schema" (page 94)]
+
+[[procedures-in-the-sales-schema]]
+== Procedures in the SALES Schema
+
+The Sales class contains these SPJ methods, which are useful for
+tracking orders and managing sales:
+
+* link:#_bookmark226["LOWERPRICE Procedure" (page 67)]
+* link:#_bookmark234["DAILYORDERS Procedure" (page 69)]
+* link:#_bookmark242["MONTHLYORDERS Procedure" (page 71)]
+* link:#_bookmark250["TOTALPRICE Procedure" (page 73)]
+* link:#_bookmark258["PARTDATA Procedure" (page 76)]
+* link:#_bookmark266["ORDERSUMMARY Procedure" (page 80)]
+
+Those methods are registered as stored procedures in the SALES schema.
+link:#_bookmark225[Example 1] shows the code of the Sales.java source
+file.
+
+*Example 1* `Sales.java` - The Sales Class
+
+```
+import java.sql.*; import java.math.*;
+
+public class Sales
+{
+   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 demo.sales.parts p " 
+                                 + "LEFT JOIN demo.sales.odetail o " 
+                                 + "ON p.partnum = o.partnum " 
+                                 + "GROUP BY p.partnum"
+                                 ) ;
+
+         PreparedStatement updateParts = 
+            conn.prepareStatement( "UPDATE demo.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();
+
+   } // See the link:#_bookmark226["LOWERPRICE Procedure" (page 67)].
+
+   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 demo.sales.orders " 
+                              + "WHERE order_date = ?"
+                              ) ;
+
+      getNumOrders.setDate( 1, date ) ;
+
+      ResultSet rs = getNumOrders.executeQuery() ; 
+      rs.next() ;
+
+      numOrders[0] = rs.getInt( 1 ) ; 
+      rs.close() ;
+
+      conn.close();
+
+   } // See the link:#_bookmark234["DAILYORDERS Procedure" (page 69)].
+
+   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 demo.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();
+
+   } // See the link:#_bookmark242["MONTHLYORDERS Procedure" (page 71)].
+
+   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;
+
+   } // See the link:#_bookmark250["TOTALPRICE Procedure" (page 73)].
+
+   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 demo.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   demo.sales.orders O " 
+                              + "     , ( select ordernum, sum(qty_ordered) as QTY_ORDERED " 
+                              + "         from demo.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 demo.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 demo.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 demo.persnl.employee " 
+                               + "WHERE empnum in ( SELECT O.salesrep " 
+                               + "                  FROM demo.sales.orders O, " 
+                               + "                  demo.sales.odetail D " 
+                               + "                  D.partnum = ? " 
+                               + "                  O.ordernum = D.ordernum ) " 
+                               + "ORDER BY empnum "
+                               ) ;
+
+        getReps.setInt( 1, partNum ) ; 
+        reps[0] = getReps.executeQuery() ;
+
+   } // See the link:#_bookmark258["PARTDATA Procedure" (page 76)].
+
+   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 demo.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 demo.sales.orders o, demo.sales.odetail d " 
+          + "       WHERE o.ordernum = d.ordernum " 
+          + "          AND o.order_date >= CAST(? AS DATE) " 
+          + "       GROUP BY o.ordernum "
+          + "      ) amounts " 
+          + "      , demo.sales.orders orders "
+          + "      , demo.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 demo.sales.odetail d, demo.sales.parts p, demo.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() ;
+
+   } // See the link:#_bookmark266["ORDERSUMMARY Procedure" (page 80)].
+}
+```
+
+See the following sections for more information about each SPJ method.
+
+[[lowerprice-procedure]]
+=== LOWERPRICE Procedure
+
+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.
+
+[[java-method-lowerprice]]
+==== Java Method: lowerPrice()
+
+Example 2 lowerPrice() Method
+
+```
+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 demo.sales.parts p " 
+                           + "LEFT JOIN demo.sales.odetail o " 
+                           + "   ON p.partnum = o.partnum " 
+                           + "GROUP BY p.partnum"
+                           ) ;
+
+   PreparedStatement updateParts = 
+      conn.prepareStatement( "UPDATE demo.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() ;
+}
+```
+
+[[creating-the-procedure-lowerprice]]
+==== Creating the Procedure: LOWERPRICE
+
+Before creating the procedure, use HPDM to create a library named SALES
+in the DEMO.SALES schema and select the Sales.jar file to upload to the
+Trafodion platform for that library. For more information, see
+link:#_bookmark95["Create a Library" (page 29)]. After creating the
+library, navigate to the Procedures folder in the SALES schema, launch
+the Create Procedure dialog box, and then enter or select these values.
+For more information, see link:#_bookmark116["Create a Procedure" (page
+37)].
+
+Table 3 HPDM Create Procedure Settings: LOWERPRICE Procedure
+
+[cols=",,",options="header",]
+|===
+| Group Box  | Field or Option | Value or Setting
+| Name
+| lowerprice | Code            | DEMO.SALES.SALES  | Sales
+lowerPrice
+Parameters
+None
+Attributes
+0
+selected
+Invoker
+NOTE: Depending on your security requirements, you can select Definer
+instead. For more information, see link:#_bookmark124["Understand
+External] link:#_bookmark124[Security" (page 41)].
+Yes
+|==
+
+[[calling-the-procedure-lowerprice]]
+==== Calling the Procedure: LOWERPRICE
+
+NOTE: Make sure that users who will be calling the stored procedure have
+the appropriate execute privileges. For more information, see
+link:#bookmark138["Granting Privileges for Executing SPJs" (page 45)].
+
+To invoke the LOWERPRICE procedure in trafci:
+
+```
+SQL> CALL demo.sales.lowerprice() ;
+
+--- SQL operation complete.
+```
+
+To view the prices and quantities of items in the database with 50 or
+fewer orders, issue this query before and after calling the LOWERPRICE
+procedure:
+
+```
+SELECT * 
+FROM
+   ( SELECT p.partnum
+   , SUM(qty_ordered) AS qtyOrdered
+   , p.price 
+     FROM demo.sales.parts p
+     LEFT OUTER JOIN demo.sales.odetail o ON p.partnum = o.partnum
+     GROUP BY p.partnum, p.price
+   ) AS allparts 
+WHERE qtyOrdered < 51 ;
+```
+
+The LOWERPRICE procedure lowers the price of items with 50 or fewer
+orders by 10 percent in the database. For example, part number 3103, the
+LASER PRINTER, X1, has 40 orders and a price of 4200.00:
+
+```
+PARTNUM QTYORDERED           PRICE
+------- -------------------- ----------
+    212                   20    2500.00
+   3201                    6     525.00
+    255                   38    4000.00
+   5101                    6     200.00
+   2002                   46    1500.00
+   7102                   18     275.00
+   3103                   40    4200.00
+    ...                  ...        ...
+
+--- 17 row(s) selected.
+```
+
+The invocation of LOWERPRICE lowers the price of this item from 4200.00
+to 3780.00:
+
+```
+PARTNUM QTYORDERED           PRICE
+------- -------------------- ----------
+   6500                   40      85.50
+   5504                   23     148.50
+   2002                   46    1350.00
+   3201                    6     472.50
+   7102                   18     247.50
+   3103                   40    3780.00
+... ... ...
+
+--- 17 row(s) selected.
+```
+
+[[dailyorders-procedure]]
+=== DAILYORDERS Procedure
+
+The DAILYORDERS procedure accepts a date and returns the number of
+orders on that date to an output parameter.
+
+[[java-method-numdailyorders]]
+==== Java Method: numDailyOrders()
+
+Example 3 numDailyOrders() Method
+
+```
+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 demo.sales.orders " 
+                           + "WHERE order_date = ?"
+                           ) ;
+
+   getNumOrders.setDate( 1, date ) ;
+
+   ResultSet rs = getNumOrders.executeQuery() ; 
+   rs.next() ;
+
+   numOrders[0] = rs.getInt( 1 ) ; 
+   rs.close() ;
+
+   conn.close() ;
+}
+```
+
+[[creating-the-procedure-dailyorders]]
+==== Creating the Procedure: DAILYORDERS
+
+Before creating the procedure, use HPDM to create a library named SALES
+in the DEMO.SALES schema and select the Sales.jar file to upload to the
+Trafodion platform for that library. For more information, see
+link:#_bookmark95["Create a Library" (page 29)]. After creating the
+library, navigate to the Procedures folder in the SALES schema, launch
+the Create Procedure dialog box, and then enter or select these values.
+For more information, see link:#_bookmark116["Create a Procedure" (page
+37)].
+
+Table 4 HPDM Create Procedure Settings: DAILYORDERS Procedure
+
+[cols="15%,20%,65%", options="header"]
+|===
+| Group Box  | Field or Option               | Value or Setting
+| Name       | Procedure Name                | dailyorders
+| Code       | Library                       | DEMO.SALES.SALES
+|            | Class Name                    | Sales
+|            | Method Name                   | numDailyOrders
+| Parameters |                               |
+| 1st        | Parameter Name                | date1
+|            | SQL Data Type                 | DATE
+|            | Direction                     | IN
+|            | Java Data Type                | Displays the signature of the Java method that you selected: `java.sql.Date`
+| 2nd        | Parameter Name                | number
+|            | SQL Data Type                 | SIGNED INTEGER
+|            | Direction                     | OUT
+|            | Java Data Type                | Displays the signature of the Java method that you selected: `int[]`
+| Attributes | Number of dynamic result sets | 0
+|            | Accesses Database             | selected
+|            | External security             | Invoker +
+ +
+*NOTE:( Depending on your security requirements, you can select Definer
+instead. For more information, see link:#_bookmark124["Understand
+External] link:#_bookmark124[Security" (page 41)].
+|            | Transaction required          | Yes
+|===
+
+[[calling-the-procedure-dailyorders]]
+==== Calling the Procedure: DAILYORDERS
+
+NOTE: Make sure that users who will be calling the stored procedure have
+the appropriate execute privileges. For more information, see
+link:#bookmark138["Granting Privileges for Executing SPJs" (page 45)].
+
+To invoke the DAILYORDERS procedure in trafci:
+
+```
+SQL> CALL demo.sales.dailyorders( DATE '2011-03-19', ? ) ;
+```
+
+The DAILYORDERS procedure determines the total number of orders on a
+specified date and returns this output in trafci:
+
+```
+NUMBER
+-----------
+2
+
+--- SQL operation complete.
+```
+
+On March 19, 2011, there were two orders.
+
+[[monthlyorders-procedure]]
+=== MONTHLYORDERS Procedure
+
+The MONTHLYORDERS procedure accepts an integer representing the month
+and returns the number of orders during that month to an output
+parameter.
+
+[[java-method-nummonthlyorders]]
+==== Java Method: numMonthlyOrders()
+
+Example 4 numMonthlyOrders() Method
+
+```
+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 demo.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() ;
+}
+
+[[creating-the-procedure-monthlyorders]]
+=== Creating the Procedure: MONTHLYORDERS
+
+Before creating the procedure, use HPDM to create a library named SALES
+in the DEMO.SALES schema and select the Sales.jar file to upload to the
+Trafodion platform for that library. For more information, see
+link:#_bookmark95["Create a Library" (page 29)]. After creating the
+library, navigate to the Procedures folder in the SALES schema, launch
+the Create Procedure dialog box, and then enter or select these values.
+For more information, see link:#_bookmark116["Create a Procedure" (page
+37)].
+
+Table 5 HPDM Create Procedure Settings: MONTHLYORDERS Procedure
+
+[cols=",,",options="header",]
+|===
+| Group Box | Field or Option | Value or Setting
+Name
+monthlyorders
+Code
+DEMO.SALES.SALES
+Sales
+numMonthlyOrders
+Parameters
+monthnum
+SIGNED INTEGER
+IN
+Displays the signature of the Java method that you selected: int
+ordernum
+SIGNED INTEGER
+OUT
+Displays the signature of the Java method that you selected: int[]
+Attributes
+0
+selected
+Invoker
+NOTE: Depending on your security requirements, you can select Definer
+instead. For more information, see link:#_bookmark124["Understand
+External] link:#_bookmark124[Security" (page 41)].
+Yes
+|===
+
+[[calling-the-procedure-monthlyorders]]
+==== Calling the Procedure: MONTHLYORDERS
+
+NOTE: Make sure that users who will be calling the stored procedure have
+the appropriate execute privileges. For more information, see
+link:#bookmark138["Granting Privileges for Executing SPJs" (page 45)].
+
+To invoke the MONTHLYORDERS procedure in trafci:
+
+```
+SQL> CALL demo.sales.monthlyorders( 3,? ) ;
+```
+
+The MONTHLYORDERS procedure determines the total number of orders during
+a specified month and returns this output in trafci:
+
+```
+ORDERNUM
+-----------
+4
+
+--- SQL operation complete.
+```
+
+In March, there were four orders.
+
+[[totalprice-procedure]]
+=== TOTALPRICE Procedure
+
+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.
+
+[[java-method-totalprice]]
+==== Java Method: totalPrice()
+
+Example 5 totalPrice() Method
+
+```
+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 ;
+
+}
+
+[[creating-the-procedure-totalprice]]
+==== Creating the Procedure: TOTALPRICE
+
+Before creating the procedure, use HPDM to create a library named SALES
+in the DEMO.SALES schema and select the Sales.jar file to upload to the
+Trafodion platform for that library. For more information, see
+link:#_bookmark95["Create a Library" (page 29)]. After creating the
+library, navigate to the Procedures folder in the SALES schema, launch
+the Create Procedure dialog box, and then enter or select these values.
+For more information, see link:#_bookmark116["Create a Procedure" (page
+37)].
+
+Table 6 HPDM Create Procedure Settings: TOTALPRICE Procedure
+
+[cols=",,",options="header",]
+|=======================================================================
+| Group Box | Field or Option | Value or Setting
+Name
+totalprice
+Code
+DEMO.SALES.SALES
+Sales
+totalPrice
+Parameters
+qty
+SIGNED NUMERIC
+*
+Precision: 18
+*
+Scale: 0
+IN
+Displays the signature of the Java method that you selected:
+java.math.BigDecimal
+rate
+VARCHAR
+*
+Length: 10
+*
+Upshift: not selected
+*
+Character set: ISO88591
+IN
+Displays the signature of the Java method that you selected:
+java.lang.String
+price
+SIGNED NUMERIC
+*
+Precision: 18
+*
+Scale: 2
+INOUT
+Displays the signature of the Java method that you selected:
+java.math.BigDecimal[]
+Attributes
+0
+not selected
+Invoker
+NOTE: External security is disabled because the stored procedure does
+not access the database.
+No
+|===
+
+[[calling-the-procedure-totalprice]]
+==== Calling the Procedure: TOTALPRICE
+
+NOTE: Make sure that users who will be calling the stored procedure have
+the appropriate execute privileges. For more information, see
+link:#bookmark138["Granting Privileges for Executing SPJs" (page 45)].
+
+To invoke the TOTALPRICE procedure in trafci:
+
+```
+SQL> SET PARAM ?p 10 ;
+SQL> CALL demo.sales.totalprice( 23, 'standard', ?p ) ;
+```
+
+The TOTALPRICE procedure calculates the total price of a purchase and
+returns this output in trafci:
+
+```
+p
+--------------------
+253.97
+
+--- SQL operation complete.
+```
+
+The total price of 23 items, which cost $10 each and which are shipped
+at the standard rate, is $253.97, including sales tax.
+
+[[partdata-procedure]]
+=== PARTDATA Procedure
+
+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
+
+[[java-method-partdata]]
+==== Java Method: partData()
+
+Example 6 partData() Method
+
+```
+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 demo.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   demo.sales.orders o "
+                           + "     , ( SELECT ordernum, SUM( qty_ordered ) AS qty_ordered " 
+                           + "         FROM demo.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 demo.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 demo.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 demo.persnl.employee " 
+                           + "WHERE empnum IN "
+                           + "   ( SELECT o.salesrep " 
+                           + "     FROM demo.sales.orders o, demo.sales.odetail d " 
+                           + "     WHERE d.partnum = ? " 
+                           + "       AND o.ordernum = d.ordernum ) " 
+                           + "ORDER BY empnum "
+                           ) ;
+
+   getReps.setInt( 1, partNum ) ;
+   reps[0] = getReps.executeQuery() ;
+}
+
+[[creating-the-procedure-partdata]]
+==== Creating the Procedure: PARTDATA
+
+Before creating the procedure, use HPDM to create a library named SALES
+in the DEMO.SALES schema and select the Sales.jar file to upload to the
+Trafodion platform for that library. For more information, see
+link:#_bookmark95["Create a Library" (page 29)]. After creating the
+library, navigate to the Procedures folder in the SALES schema, launch
+the Create Procedure dialog box, and then enter or select these values.
+For more information, see link:#_bookmark116["Create a Procedure" (page
+37)].
+
+Table 7 HPDM Create Procedure Settings: PARTDATA Procedure
+
+[cols=",,",options="header",]
+|===
+| Group Box | Field or Option | Value or Setting
+Name
+partdata
+Code
+DEMO.SALES.SALES
+Sales
+partData
+Parameters
+partnum
+SIGNED INTEGER
+IN
+Displays the signature of the Java method that you selected: int
+partdesc
+CHARACTER
+*
+Length: 18
+*
+Varying: not selected
+*
+Upshift: not selected
+*
+Character set: ISO88591
+OUT
+Displays the signature of the Java method that you selected:
+java.lang.String[]
+price
+SIGNED NUMERIC
+* 
+Precision: 8
+* 
+Scale: 2
+OUT
+Displays the signature of the Java method that you selected:
+java.math.BigDecimal[]
+qty_available
+SIGNED INTEGER
+OUT
+Displays the signature of the Java method that you selected: int[]
+Attributes
+4
+selected
+Invoker
+NOTE: Depending on your security requirements, you can select Definer
+instead. For more information, see link:#_bookmark124["Understand
+External] link:#_bookmark124[Security" (page 41)].
+Yes
+|===
+
+[[calling-the-procedure-partdata]]
+==== Calling the Procedure: PARTDATA
+
+NOTE: Make sure that users who will be calling the stored procedure have
+the appropriate execute privileges. For more information, see
+link:#bookmark138["Granting Privileges for Executing SPJs" (page 45)].
+
+To invoke the PARTDATA procedure in trafci:
+
+```
+SQL> CALL demo.sales.partdata(212,?,?,?) ;
+```
+
+The PARTDATA procedure returns this information about part 212:
+
+```
+PARTDESC           PRICE      QTY_AVAILABLE
+------------------ ---------- ------------- 
+PC SILVER, 20 MB      2500.00          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
+-------- ------- ----------- 
+G87          212          20
+A87          212          18
+
+--- 2 row(s) selected.
+
+PARTNUM SUPPNUM PARTCOST   QTY_RECEIVED
+------- ------- ---------- ------------ 
+    212       3    1900.00           35
+    212       1    2000.00           20
+
+--- 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.
+```
+
+[[ordersummary-procedure]]
+== ORDERSUMMARY Procedure
+
+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.
+
+[[java-method-ordersummary]]
+==== Java Method: orderSummary()
+
+Example 7 orderSummary() Method
+
+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 demo.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 demo.sales.orders o, demo.sales.odetail d " 
+       + "       WHERE o.ordernum = d.ordernum " 
+       + "         AND o.order_date >= CAST(? AS DATE) " 
+       + "       GROUP BY o.ordernum "
+       + "      ) amounts " 
+       + "      , demo.sales.orders orders "
+       + "      , demo.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 demo.sales.odetail d, demo.sales.parts p, demo.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() ;
+}
+
+[[creating-the-procedure-ordersummary]]
+==== Creating the Procedure: ORDERSUMMARY
+
+Before creating the procedure, use HPDM to create a library named SALES
+in the DEMO.SALES schema and select the Sales.jar file to upload to the
+Trafodion platform for that library. For more information, see
+link:#_bookmark95["Create a Library" (page 29)]. After creating the
+library, navigate to the Procedures folder in the SALES schema, launch
+the Create Procedure dialog box, and then enter or select these values.
+For more information, see link:#_bookmark116["Create a Procedure" (page
+37)].
+
+Table 8 HPDM Create Procedure Settings: ORDERSUMMARY Procedure
+
+[cols=",,",options="header",]
+|===
+Group Box
+Field or Option
+Value or Setting
+Name
+ordersummary
+Code
+DEMO.SALES.SALES
+Sales
+orderSummary
+Parameters
+on_or_after_date
+VARCHAR
+Length: 20
+Upshift: not selected
+Character set: ISO88591
+IN
+Displays the signature of the Java method that you selected:
+java.lang.String
+num_orders
+SIGNED LARGEINT
+OUT
+Displays the signature of the Java method that you selected: long[]
+Attributes
+2
+selected
+Invoker
+NOTE: Depending on your security requirements, you can select Definer
+instead. For more information, see link:#_bookmark124["Understand
+External] link:#_bookmark124[Security" (page 41)].
+Yes
+|===
+
+[[calling-the-procedure-ordersummary]]
+==== Calling the Procedure: ORDERSUMMARY
+
+NOTE: Make sure that users who will be calling the stored procedure have
+the appropriate execute privileges. For more information, see
+link:#bookmark138["Granting Privileges for Executing SPJs" (page 45)].
+
+To invoke the ORDERSUMMARY procedure in trafci:
+
+```
+SQL> CALL demo.sales.ordersummary('01-01-2011', ?);
+```
+
+The ORDERSUMMARY procedure returns this information about the orders on
+or after the specified date, 01-01-2011:
+
+```
+NUM_ORDERS
+--------------------
+13
+
+ORDERNUM NUM_PARTS            AMOUNT               ORDER_DATE LAST_NAME
+-------- -------------------- -------------------- ---------- --------------------
+HUGHES HUGHES SCHNABL
+...
+
+--- 13 row(s) selected.
+
+ORDERNUM PARTNUM UNIT_PRICE QTY_ORDERED PARTDESC
+-------- ------- ---------- ----------- ------------------ 
+  100210     244    3500.00           3 PC GOLD, 30 MB
+  100210    2001    1100.00           3 GRAPHIC PRINTER,M1
+   100210   2403     620.00           6 DAISY PRINTER,T2
+... ... ... ... ...
+
+--- 70 row(s) selected.
+
+--- SQL operation complete.
+```
+
+[[procedures-in-the-persnl-schema]]
+== Procedures in the PERSNL Schema
+
+The Payroll class contains these SPJ methods, which are useful for
+managing personnel data:
+
+* link:#_bookmark277["ADJUSTSALARY Procedure" (page 86)]
+* link:#_bookmark285["EMPLOYEEJOB Procedure" (page 88)]
+* link:#_bookmark293["PROJECTTEAM Procedure" (page 90)]
+* link:#_bookmark301["TOPSALESREPS Procedure" (page 92)]
+
+Those methods are registered as stored procedures in the PERSNL schema.
+link:#_bookmark276[Example 8] shows the code of the Payroll.java source
+file.
+
+Example 8 Payroll.java-The Payroll Class
+
+```
+import java.sql.*; 
+import java.math.*;
+
+public class Payroll
+{
+   public static void adjustSalary( BigDecimal empNum
+                                  , double percent
+                                  , BigDecimal[] newSalary
+                                  ) throws SQLException
+   {
+      Connection conn =
+         DriverManager.getConnection( "jdbc:default:connection" ) ;
+
+      PreparedStatement setSalary = 
+         conn.prepareStatement( "UPDATE demo.persnl.employee " 
+                              + "SET salary = salary * (1 + (? / 100)) " 
+                              + "WHERE empnum = ?"
+                              ) ;
+
+      PreparedStatement getSalary = 
+         conn.prepareStatement( "SELECT salary " 
+                              + "FROM demo.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() ;
+
+   } // See the link:#_bookmark277["ADJUSTSALARY Procedure" (page 86)].
+
+   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 demo.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() ;
+
+   } // See the link:#_bookmark285["EMPLOYEEJOB Procedure" (page 88)].
+
+   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 demo.persnl.employee E, demo.persnl.dept D, demo.persnl.project P "
+                              + "WHERE P.projcode = ? " 
+                              + "  AND P.empnum = E.empnum " 
+                              + "  AND E.deptnum = D.deptnum "
+                              ) ; 
+
+       getMembers.setInt( 1, projectCode ) ;
+       members[0] = getMembers.executeQuery() ;
+
+   } // See the link:#_bookmark293["PROJECTTEAM Procedure" (page 90)].
+
+   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 demo.persnl.employee e, " 
+                             + "   ( SELECT o.salesrep, " 
+                             + "     SUM( od.unit_price * od.qty_ordered ) as total " 
+                             + "     FROM demo.sales.orders o, demo.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() ;
+
+   } // See the link:#_bookmark301["TOPSALESREPS Procedure" (page 92)].
+}
+
+See the following sections for more information about each SPJ method.
+
+[[adjustsalary-procedure]]
+=== ADJUSTSALARY Procedure
+
+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.
+
+[[java-method-adjustsalary]]
+==== Java Method: adjustSalary()
+
+Example 9 adjustSalary() Method
+
+```
+public static void adjustSalary( BigDecimal empNum
+                               , double percent
+                               , BigDecimal[] newSalary
+                               ) throws SQLException
+{
+   Connection conn =
+      DriverManager.getConnection( "jdbc:default:connection" ) ;
+
+   PreparedStatement setSalary = 
+      conn.prepareStatement( "UPDATE demo.persnl.employee " 
+                           + "SET salary = salary * (1 + (? / 100)) " 
+                           + "WHERE empnum = ?"
+                           ) ;
+
+   PreparedStatement getSalary = 
+      conn.prepareStatement( "SELECT salary " 
+                           + "FROM demo.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() ;
+}
+```
+
+[[creating-the-procedure-adjustsalary]]
+==== Creating the Procedure: ADJUSTSALARY
+
+Before creating the procedure, use HPDM to create a library named
+PAYROLL in the DEMO.PERSNL schema and select the Payroll.jar file to
+upload to the Trafodion platform for that library. For more information,
+see link:#_bookmark95["Create a Library" (page 29)]. After creating the
+library, navigate to the Procedures folder in the PERSNL schema, launch
+the Create Procedure dialog box, and then enter or select these values.
+For more information, see link:#_bookmark116["Create a Procedure" (page
+37)].
+
+Table 9 HPDM Create Procedure Settings: ADJUSTSALARY Procedure
+
+[cols=",,",options="header",]
+|===
+Group Box
+Field or Option
+Value or Setting
+Name
+adjustsalary
+Code
+DEMO.PERSNL.PAYROLL
+Payroll
+adjustSalary
+Parameters
+empnum
+SIGNED NUMERIC
+Precision: 4
+Scale: 0
+Displays the signature of the Java method that you selected:
+java.math.BigDecimal
+percent
+FLOAT
+IN
+Displays the signature of the Java method that you selected: double
+newsalary
+SIGNED NUMERIC
+Precision: 8
+Scale: 2
+OUT
+Displays the signature of the Java method that you selected:
+java.math.BigDecimal[]
+Attributes
+0
+selected
+Invoker
+NOTE: Depending on your security requirements, you can select Definer
+instead. For more information, see link:#_bookmark124["Understand
+External] link:#_bookmark124[Security" (page 41)].
+Yes
+|===
+
+[[calling-the-procedure-adjustsalary]]
+==== Calling the Procedure: ADJUSTSALARY
+
+NOTE: Make sure that users who will be calling the stored procedure have
+the appropriate execute privileges. For more information, see
+link:#bookmark138["Granting Privileges for Executing SPJs" (page 45)].
+
+To invoke the ADJUSTSALARY procedure in trafci:
+
+```
+SQL> CALL demo.persnl.adjustsalary(29, 2.5, ?) ;
+```
+
+The ADJUSTSALARY procedure updates the salary of employee number 29 by
+2.5 percent and returns this output in trafci:
+
+```
+NEWSALARY
+---------- 
+ 139400.00
+
+--- SQL operation complete.
+```
+
+The salary of employee number 29 was originally $136,000.00 and became
+$139,400.00 after the invocation of ADJUSTSALARY.
+
+[[employeejob-procedure]]
+=== EMPLOYEEJOB Procedure
+
+The EMPLOYEEJOB procedure accepts an employee number and returns a job
+code or null value to an output parameter.
+
+[[java-method-employeejob]]
+==== Java Method: employeeJob()
+
+Example 10 employeeJob() Method
+
+```
+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 demo.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() ;
+}
+```
+
+[[creating-the-procedure-employeejob]]
+==== Creating the Procedure: EMPLOYEEJOB
+
+Before creating the procedure, use HPDM to create a library named
+PAYROLL in the DEMO.PERSNL schema and select the Payroll.jar file to
+upload to the Trafodion platform for that library. For more information,
+see link:#_bookmark95["Create a Library" (page 29)]. After creating the
+library, navigate to the Procedures folder in the PERSNL schema, launch
+the Create Procedure dialog box, and then enter or select these values.
+For more information, see link:#_bookmark116["Create a Procedure" (page
+37)].
+
+Table 10 HPDM Create Procedure Settings: EMPLOYEEJOB Procedure
+
+[cols=",,",options="header",]
+|===
+Group Box
+Field or Option
+Value or Setting
+Name
+employeejob
+Code
+DEMO.PERSNL.PAYROLL
+Payroll
+employeeJob
+Parameters
+empnum
+SIGNED INTEGER
+IN
+Displays the signature of the Java method that you selected: int
+jobcode
+SIGNED INTEGER
+OUT
+Displays the signature of the Java method that you selected:
+java.lang.Integer[]
+Attributes
+0
+selected
+Invoker
+NOTE: Depending on your security requirements, you can select Definer
+instead. For more information, see link:#_bookmark124["Understand
+External] link:#_bookmark124[Security" (page 41)].
+Yes
+|===
+
+[[calling-the-procedure-employeejob]]
+==== Calling the Procedure: EMPLOYEEJOB
+
+NOTE: Make sure that users who will be calling the stored procedure have
+the appropriate execute privileges. For more information, see
+link:#bookmark138["Granting Privileges for Executing SPJs" (page 45)].
+
+To invoke the EMPLOYEEJOB procedure in trafci:
+
+```
+SQL> CALL demo.persnl.employeejob(337, ?) ;
+```
+
+The EMPLOYEEJOB procedure accepts the employee number 337 and returns
+this output in trafci:
+
+```
+JOBCODE
+-----------
+900
+
+--- SQL operation complete.
+```
+
+The job code for employee number 337 is 900.
+
+[[projectteam-procedure]]
+=== PROJECTTEAM Procedure
+
+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.
+
+[[java-method-projectteam]]
+==== Java Method: projectTeam()
+
+Example 11 projectTeam() Method
+
+```
+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 demo.persnl.employee E, demo.persnl.dept D, demo.persnl.project P "
+                           + "WHERE P.projcode = ? "
+			   + "  AND P.empnum = E.empnum "
+			   + "  AND E.deptnum = D.deptnum "
+			   ) ;
+
+   getMembers.setInt( 1, projectCode ) ;
+   members[0] = getMembers.executeQuery() ;
+}
+
+[[creating-the-procedure-projectteam]]
+==== Creating the Procedure: PROJECTTEAM
+
+Before creating the procedure, use HPDM to create a library named
+PAYROLL in the DEMO.PERSNL schema and select the Payroll.jar file to
+upload to the Trafodion platform for that library. For more information,
+see link:#_bookmark95["Create a Library" (page 29)]. After creating the
+library, navigate to the Procedures folder in the PERSNL schema, launch
+the Create Procedure dialog box, and then enter or select these values.
+For more information, see link:#_bookmark116["Create a Procedure" (page
+37)].
+
+Table 11 HPDM Create Procedure Settings: PROJECTTEAM Procedure
+
+[cols=",,",options="header",]
+|===
+Group Box
+Field or Option
+Value or Setting
+Name
+projectteam
+Code
+DEMO.PERSNL.PAYROLL
+Payroll
+projectTeam
+Parameters
+projectcode
+SIGNED INTEGER
+IN
+Displays the signature of the Java method that you selected: int
+Attributes
+1
+selected
+Invoker
+NOTE: Depending on your security requirements, you can select Definer
+instead. For more information, see link:#_bookmark124["Understand
+External] link:#_bookmark124[Security" (page 41)].
+Yes
+|===
+
+[[calling-the-procedure-projectteam]]
+==== Calling the Procedure: PROJECTTEAM
+
+NOTE: Make sure that users who will be calling the stored procedure have
+the appropriate execute privileges. For more information, see
+link:#bookmark138["Granting Privileges for Executing SPJs" (page 45)].
+
+To invoke the PROJECTTEAM procedure in trafci:
+
+SQL> CALL demo.persnl.projectteam( 5000 ) ;
+
+The PROJECTTEAM procedure returns this information about the employees
+assigned to project number 5000:
+
+```
+EMPNUM FIRST_NAME      LAST_NAME             LOCATION
+------ --------------- -------------------- ------------------
+    65 RACHEL          MCKAY                NEW YORK
+   203 KATHRYN         HALL                 NEW YORK
+... ... ...
+
+--- 6 row(s) selected.
+
+--- SQL operation complete.
+```
+
+[[topsalesreps-procedure]]
+=== TOPSALESREPS Procedure
+
+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.
+
+[[java-method-topsalesreps]]
+==== Java Method: topSalesReps()
+
+Example 12 topSalesReps() Method
+
+```
+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 demo.persnl.employee e, "
+			   + "    ( SELECT o.salesrep, "
+			   + "      SUM( od.unit_price * od.qty_ordered) AS total "
+			   + "      FROM demo.sales.orders o, demo.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() ;
+
+}
+
+[[creating-the-procedure-topsalesreps]]
+==== Creating the Procedure: TOPSALESREPS
+
+Before creating the procedure, use HPDM to create a library named
+PAYROLL in the DEMO.PERSNL schema and select the Payroll.jar file to
+upload to the Trafodion platform for that library. For more information,
+see link:#_bookmark95["Create a Library" (page 29)]. After creating the
+library, navigate to the Procedures folder in the PERSNL schema, launch
+the Create Procedure dialog box, and then enter or select these values.
+For more information, see link:#_bookmark116["Create a Procedure" (page
+37)].
+
+Table 12 HPDM Create Procedure Settings: TOPSALESREPS Procedure
+
+[cols=",,",options="header",]
+|===
+Group Box
+Field or Option
+Value or Setting
+Name
+topsalesreps
+Code
+DEMO.PERSNL.PAYROLL
+Payroll
+topSalesReps
+Parameters
+whichquarter
+SIGNED INTEGER
+IN
+Displays the signature of the Java method that you selected: int
+Attributes
+1
+selected
+Invoker
+NOTE: Depending on your security requirements, you can select Definer
+instead. For more information, see link:#_bookmark124["Understand
+External] link:#_bookmark124[Security" (page 41)].
+Yes
+|===
+
+[[calling-the-procedure-topsalesreps]]
+==== Calling the Procedure: TOPSALESREPS
+
+NOTE: Make sure that users who will be calling the stored procedure have
+the appropriate execute privileges. For more information, see
+link:#bookmark138["Granting Privileges for Executing SPJs" (page 45)].
+
+To invoke the TOPSALESREPS procedure in trafci:
+
+```
+SQL> CALL demo.persnl.topsalesreps( 1 ) ;
+```
+
+The TOPSALESREPS procedure returns this information about the top five
+sales representatives during the first fiscal quarter:
+
+[cols=",,,",options="header",]
+|===
+FIRST_NAME      LAST_NAME            TOTAL
+--------------- -------------------- --------------------
+XAVIER          SEDLEMEYER                      172460.00
+HERB            ALBERT                           67025.00
+MARTIN          SCHAEFFER                        52000.00
+HEIDI           WEIGL                            28985.00
+JOHN            HUGHES                           22625.00
+
+--- 5 row(s) selected.
+
+--- SQL operation complete.
+```
+
+[[procedures-in-the-invent-schema]]
+== Procedures in the INVENT Schema
+
+The Inventory class contains these SPJ methods, which are useful for
+tracking parts and suppliers:
+
+* link:#_bookmark312["SUPPLIERINFO Procedure" (page 96)]
+* link:#_bookmark320["SUPPLYNUMBERS Procedure" (page 99)]
+* link:#_bookmark328["PARTLOCS Procedure" (page 101)]
+
+Those methods are registered as stored procedures in the INVENT schema.
+link:#_bookmark311[Example 13] shows the code of the Inventory.java
+source file.
+
+Example 13 Inventory.java The Inventory Class
+
+```
+import java.sql.*;
+import java.math.*;
+
+public class Inventory
+{
+   public static void supplierInfo( BigDecimal suppNum
+                                  , String[] suppName
+				  , String[] streetAddr
+				  , String[] cityName
+				  , String[] stateName
+				  , String[] postCode
+				  ) throws SQLException
+   {
+      Connection conn =
+         DriverManager.getConnection( "jdbc:default:connection" ) ;
+
+      PreparedStatement getSupplier =
+         conn.prepareStatement( "SELECT suppname, street, city, "
+	                      + "       state, postcode "
+			      + "FROM demo.invent.supplier "
+			      + "WHERE suppnum = ?"  
+			      ) ;
+
+      getSupplier.setBigDecimal( 1, suppNum ) ;
+      ResultSet rs = getSupplier.executeQuery() ;
+      rs.next() ;
+
+      suppName[0]   = rs.getString( 1 ) ;
+      streetAddr[0] = rs.getString( 2 ) ;
+      cityName[0]   = rs.getString( 3 ) ;
+      stateName[0]  = rs.getString( 4 ) ;
+      postCode[0]   = rs.getString( 5 ) ;
+
+      rs.close() ;
+      conn.close() ;
+
+   } // See the link:#_bookmark312["SUPPLIERINFO Procedure" (page 96)].
+
+   public static void supplyQuantities( int[] avgQty
+                                      , int[] minQty
+				      , int[] maxQty
+				      ) throws SQLException
+   {
+      Connection conn =
+         DriverManager.getConnection( "jdbc:default:connection" ) ;
+
+      PreparedStatement getQty =
+         conn.prepareStatement( "SELECT AVG(qty_on_hand), "
+	                      + "       MIN(qty_on_hand), "
+			      + "       MAX(qty_on_hand) "
+			      + "FROM demo.invent.partloc"
+			      ) ;
+
+      ResultSet rs = getQty.executeQuery() ;
+      rs.next() ;
+
+      avgQty[0] = rs.getInt( 1 ) ;
+      minQty[0] = rs.getInt( 2 ) ;
+      maxQty[0] = rs.getInt( 3 ) ;
+
+      rs.close() ;
+      conn.close() ;
+
+   } // See the link:#_bookmark320["SUPPLYNUMBERS Procedure" (page 99)].
+
+   public static void partLocations( int partNum
+                                   , int quantity
+				   , ResultSet exactly[]
+				   , ResultSet moreThan[]
+				   ) throws SQLException
+
+   {
+      Connection conn =
+         DriverManager.getConnection( "jdbc:default:connection" ) ;
+
+      PreparedStatement getLocationsExact =
+         conn.prepareStatement( "SELECT L.loc_code, L.partnum, L.qty_on_hand "
+	                      + "FROM demo.invent.partloc L "
+			      + "WHERE L.partnum = ? "
+			      + "  AND L.qty_on_hand = ? "
+			      + " ORDER BY L.partnum "
+			      ) ;
+
+      getLocationsExact.setInt( 1, partNum ) ;
+      getLocationsExact.setInt( 2, quantity) ;
+
+      PreparedStatement getLocationsMoreThan =
+         conn.prepareStatement( "SELECT L.loc_code, L.partnum, L.qty_on_hand "
+	                      + "FROM demo.invent.partloc L "
+			      + "WHERE L.partnum = ? "
+			      + "  AND L.qty_on_hand > ? "
+			      + "ORDER BY L.partnum "
+			      ) ;
+
+      getLocationsMoreThan.setInt( 1, partNum ) ;
+      getLocationsMoreThan.setInt( 2, quantity) ;
+
+      exactly[0]  = getLocationsExact.executeQuery() ;
+      moreThan[0] = getLocationsMoreThan.executeQuery() ;
+
+   } // See the link:#_bookmark328["PARTLOCS Procedure" (page 101)].
+}
+
+See the following sections for more information about each SPJ method.
+
+[[supplierinfo-procedure]]
+=== SUPPLIERINFO Procedure
+
+The SUPPLIERINFO procedure accepts a supplier number and returns the
+supplier's name, street, city, state, and post code to separate output
+parameters.
+
+[[java-method-supplierinfo]]
+==== Java Method: supplierInfo()
+
+Example 14 supplierInfo() Method
+
+```
+public static void supplierInfo( BigDecimal suppNum
+                               , String[] suppName
+			       , String[] streetAddr
+			       , String[] cityName
+			       , String[] stateName
+			       , String[] postCode
+			       ) throws SQLException
+{
+   Connection conn =
+      DriverManager.getConnection( "jdbc:default:connection" ) ;
+
+   PreparedStatement getSupplier =
+      conn.prepareStatement( "SELECT suppname, street, city, "
+                           + "       state, postcode "
+			   + "FROM demo.invent.supplier "
+			   + "WHERE suppnum = ?"
+			   ) ;
+
+   getSupplier.setBigDecimal( 1, suppNum ) ;
+   ResultSet rs = getSupplier.executeQuery() ;
+   rs.next() ;
+
+   suppName[0]   = rs.getString( 1 ) ;
+   streetAddr[0] = rs.getString( 2 ) ;
+   cityName[0]   = rs.getString( 3 ) ;
+   stateName[0]  = rs.getString( 4 ) ;
+   postCode[0]   = rs.getString( 5 ) ;
+
+   rs.close() ;
+   conn.close() ;
+}
+```
+
+[[creating-the-procedure-supplierinfo]]
+==== Creating the Procedure: SUPPLIERINFO
+
+Before creating the procedure, use HPDM to create a library named
+INVENTORY in the DEMO.INVENT schema and select the Inventory.jar file to
+upload to the Trafodion platform for that library. For more information,
+see link:#_bookmark95["Create a Library" (page 29)]. After creating the
+library, navigate to the Procedures folder in the INVENT schema, launch
+the Create Procedure dialog box, and then enter or select these values.
+For more information, see link:#_bookmark116["Create a Procedure" (page
+37)].
+
+Table 13 HPDM Create Procedure Settings: SUPPLIERINFO Procedure
+
+[cols=",,",options="header",]
+|===
+Group Box
+Field or Option
+Value or Setting
+Name
+supplierinfo
+Code
+DEMO.INVENT.INVENTORY
+Inventory
+supplierInfo
+Parameters
+empnum
+SIGNED NUMERIC
+Precision: 4
+Scale: 0
+IN
+Displays the signature of the Java method that you selected:
+java.math.BigDecimal
+suppname
+CHARACTER
+Length: 18
+Varying: not selected
+Upshift: not selected
+Character set: ISO88591
+OUT
+Displays the signature of the Java method that you selected:
+java.lang.String[]
+address
+CHARACTER
+Length: 22
+Varying: not selected
+Upshift: not selected
+Character set: ISO88591
+OUT
+Displays the signature of the Java method that you selected:
+java.lang.String[]
+city
+CHARACTER
+Length: 14
+Varying: not selected
+Upshift: not selected
+Character set: ISO88591
+OUT
+Displays the signature of the Java method that you selected:
+java.lang.String[]
+state
+CHARACTER
+Length: 12
+Varying: not selected
+Upshift: not selected
+Character set: ISO88591
+OUT
+Displays the signature of the Java method that you selected:
+java.lang.String[]
+zipcode
+Length: 10
+Varying: not selected
+Upshift: not selected
+Character set: ISO88591
+OUT
+Displays the signature of the Java method that you selected:
+java.lang.String[]
+Attributes
+0
+selected
+Invoker
+NOTE: Depending on your security requirements, you can select Definer
+instead. For more information, see link:#_bookmark124["Understand
+External] link:#_bookmark124[Security" (page 41)].
+Yes
+|===
+
+[[calling-the-procedure-supplierinfo]]
+==== Calling the Procedure: SUPPLIERINFO
+
+NOTE: Make sure that users who will be calling the stored procedure have
+the appropriate execute privileges. For more information, see
+link:#bookmark138["Granting Privileges for Executing SPJs" (page 45)].
+
+To invoke the SUPPLIERINFO procedure in trafci:
+
+```
+SQL> CALL demo.invent.supplierinfo( 25,?,?,?,?,? ) ;
+```
+
+The SUPPLIERINFO procedure accepts the supplier number 25 and returns
+this output in trafci:
+
+SUPPNAME           ADDRESS                CITY           STATE        ZIPCODE
+------------------ ---------------------- -------------- ------------ ---------
+Schroeder's Ltd    212 Strasse Blvd West  Hamburg        Rhode Island     22222
+
+--- SQL operation complete.
+```
+
+Supplier number 25 is Schroeder's Ltd. and is located in Hamburg, Rhode Island.
+
+[[supplynumbers-procedure]]
+=== SUPPLYNUMBERS Procedure
+
+The SUPPLYNUMBERS procedure returns the average, minimum, and maximum
+quantities of available parts in inventory to separate output
+parameters.
+
+[[java-method-supplyquantities]]
+==== Java Method: supplyQuantities()
+
+Example 15 supplyQuantities() Method
+
+```
+public static void supplyQuantities( int[] avgQty
+                                   , int[] minQty
+				   , int[] maxQty
+				   ) throws SQLException
+{
+   Connection conn =
+      DriverManager.getConnection( "jdbc:default:connection" ) ;
+
+   PreparedStatement getQty =
+      conn.prepareStatement( "SELECT AVG(qty_on_hand), "
+                           + "MIN(qty_on_hand), "
+			   + "MAX(qty_on_hand) "
+			   + "FROM demo.invent.partloc"
+			   ) ;
+
+   ResultSet rs = getQty.executeQuery() ;
+   rs.next() ;
+
+   avgQty[0] = rs.getInt( 1 ) ;
+   minQty[0] = rs.getInt( 2 ) ;
+   maxQty[0] = rs.getInt( 3 ) ;
+
+   rs.close() ;
+   conn.close() ;
+
+}
+
+[[creating-the-procedure-supplynumbers]]
+==== Creating the Procedure: SUPPLYNUMBERS
+
+Before creating the procedure, use HPDM to create a library named
+INVENTORY in the DEMO.INVENT schema and select the Inventory.jar file to
+upload to the Trafodion platform for that library. For more information,
+see link:#_bookmark95["Create a Library" (page 29)]. After creating the
+library, navigate to the Procedures folder in the INVENT schema, launch
+the Create Procedure dialog box, and then enter or select these values.
+For more information, see link:#_bookmark116["Create a Procedure" (page
+37)].
+
+Table 14 HPDM Create Procedure Settings: SUPPLYNUMBERS Procedure
+
+[cols=",,",options="header",]
+|===
+Group Box
+Field or Option
+Value or Setting
+Name
+supplynumbers
+Code
+DEMO.INVENT.INVENTORY
+Inventory
+supplyQuantities
+Parameters
+avrg
+SIGNED INTEGER
+OUT
+Displays the signature of the Java method that you selected: int[]
+minm
+SIGNED INTEGER
+OUT
+Displays the signature of the Java method that you selected: int[]
+maxm
+SIGNED INTEGER
+OUT
+Displays the signature of the Java method that you selected: int[]
+Attributes
+0
+selected
+Invoker
+NOTE: Depending on your security requirements, you can select Definer
+instead. For more information, see link:#_bookmark124["Understand
+External] link:#_bookmark124[Security" (page 41)].
+Yes
+|===
+
+[[calling-the-procedure-supplynumbers]]
+==== Calling the Procedure: SUPPLYNUMBERS
+
+NOTE: Make sure that users who will be calling the stored procedure have
+the appropriate execute privileges. For more information, see
+link:#bookmark138["Granting Privileges for Executing SPJs" (page 45)].
+
+To invoke the SUPPLYNUMBERS procedure in trafci:
+
+```
+SQL> CALL demo.invent.supplynumbers( ?,?,? ) ;
+```
+
+The SUPPLYNUMBERS procedure returns this output in trafci:
+
+AVRG        MINM        MAXM
+----------- ----------- -----------
+        167           0        1132
+
+--- SQL operation complete.
+```
+
+The average number of items in inventory is 167, the minimum number is
+0, and the maximum number is 1132.
+
+[[partlocs-procedure]]
+=== PARTLOCS Procedure
+
+The PARTLOCS procedure accepts a part number and quantity and returns a
+set of location codes that have the exact quantity and a set of location
+codes that have more than that quantity.
+
+[[java-method-partlocations]]
+==== Java Method: partLocations()
+
+Example 16 partLocations() Method
+
+```
+public static void partLocations( int partNum
+                                , int quantity
+				, ResultSet exactly[]
+				, ResultSet moreThan[]
+				) throws SQLException
+{
+   Connection conn =
+      DriverManager.getConnection( "jdbc:default:connection" ) ;
+
+   PreparedStatement getLocationsExact =
+      conn.prepareStatement( "SELECT L.loc_code, L.partnum, L.qty_on_hand "
+                           + "FROM demo.invent.partloc L "
+			   + "WHERE L.partnum = ? "
+			   + "  AND L.qty_on_hand = ? "
+			   + " ORDER BY L.partnum "
+			   ) ;
+
+   getLocationsExact.setInt( 1, partNum ) ;
+   getLocationsExact.setInt( 2, quantity ) ;
+
+   PreparedStatement getLocationsMoreThan =
+      conn.prepareStatement( "SELECT L.loc_code, L.partnum, L.qty_on_hand "
+                           + "FROM demo.invent.partloc L "
+			   + "WHERE L.partnum = ? "
+			   + "  AND L.qty_on_hand > ? "
+			   + " ORDER BY L.partnum "
+			   ) ;
+
+   getLocationsMoreThan.setInt( 1, partNum ) ;
+   getLocationsMoreThan.setInt( 2, quantity ) ;
+
+   exactly[0]  = getLocationsExact.executeQuery() ;
+   moreThan[0] = getLocationsMoreThan.executeQuery() ;
+}
+```
+
+[[creating-the-procedure-partlocs]]
+==== Creating the Procedure: PARTLOCS
+
+Before creating the procedure, use HPDM to create a library named
+INVENTORY in the DEMO.INVENT schema and select the Inventory.jar file to
+upload to the Trafodion platform for that library. For more information,
+see link:#_bookmark95["Create a Library" (page 29)]. After creating the
+library, navigate to the Procedures folder in the INVENT schema, launch
+the Create Procedure dialog box, and then enter or select these values.
+For more information, see link:#_bookmark116["Create a Procedure" (page
+37)].
+
+Table 15 HPDM Create Procedure Settings: PARTLOCS Procedure
+
+[cols=",,",options="header",]
+|===
+Group Box
+Field or Option
+Value or Setting
+Name
+partlocs
+Code
+DEMO.INVENT.INVENTORY
+Inventory
+partLocations
+Parameters
+partnum
+SIGNED INTEGER
+IN
+Displays the signature of the Java method that you selected: int
+qty
+SIGNED INTEGER
+IN
+Displays the signature of the Java method that you selected: int
+Attributes
+2
+selected
+Invoker
+NOTE: Depending on your security requirements, you can select Definer
+instead. For more information, see link:#_bookmark124["Understand
+External] link:#_bookmark124[Security" (page 41)].
+Yes
+|===
+
+[[calling-the-procedure-partlocs]]
+==== Calling the Procedure: PARTLOCS
+
+NOTE: Make sure that users who will be calling the stored procedure have
+the appropriate execute privileges. For more information, see
+link:#bookmark138["Granting Privileges for Executing SPJs" (page 45)].
+
+To invoke the PARTLOCS procedure in trafci:
+
+```
+SQL> CALL demo.invent.partlocs( 212, 18 ) ;
+```
+
+The PARTLOCS procedure accepts the part number 212 and returns a set of
+locations that have 18 of those parts and a set of locations that have
+more than 18 of those parts:
+
+```
+LOC_CODE PARTNUM QTY_ON_HAND
+-------- ------- -----------
+A87          212           18
+
+--- 1 row(s) selected.
+
+LOC_CODE PARTNUM QTY_ON_HAND
+-------- ------- -----------
+G87          212          20
+
+--- 1 row(s) selected.
+
+--- SQL operation complete.
+```
+

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/spj_guide/src/asciidoc/index.adoc
----------------------------------------------------------------------
diff --git a/docs/spj_guide/src/asciidoc/index.adoc b/docs/spj_guide/src/asciidoc/index.adoc
new file mode 100644
index 0000000..17afeb7
--- /dev/null
+++ b/docs/spj_guide/src/asciidoc/index.adoc
@@ -0,0 +1,113 @@
+////
+* @@@ START COPYRIGHT @@@                                                         
+*
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements.  See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership.  The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License.  You may obtain a copy of the License at
+*
+*   http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing,
+* software distributed under the License is distributed on an
+* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+* KIND, either express or implied.  See the License for the
+* specific language governing permissions and limitations
+* under the License.
+*
+* @@@ END COPYRIGHT @@@ 
+////
+
+= Stored Procedures in Java (SPJs) Guide
+:doctype: book
+:numbered:
+:toc: left
+:toclevels: 3
+:toc-title: Table of Contents
+:icons: font
+:iconsdir: icons
+:experimental:
+:source-language: text
+:revnumber: {project-version}
+:title-logo-image: ../../../shared/trafodion-logo.jpg
+
+:images: ../images
+:sourcedir: ../../resources/source
+
+:leveloffset: 1
+
+// The directory is called _chapters because asciidoctor skips direct
+// processing of files found in directories starting with an _. This
+// prevents each chapter being built as its own book.
+
+**License Statement**
+
+Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements.  See the NOTICE file
+distributed with this work for additional information regarding copyright ownership.  The ASF licenses this file
+to you under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with
+the License.  You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.  See the License for the
+specific language governing permissions and limitations under the License.
+
+*Acknowledgements*
+
+Microsoft®, Windows®, Windows NT®, Windows® XP, and Windows Vista® are
+U.S. registered trademarks of Microsoft Corporation. Intel® and Intel®
+Itanium® are trademarks of Intel Corporation in the U.S. and other
+countries. Java® is a registered trademark of Oracle and/or its
+affiliates. Motif, OSF/1, UNIX®, X/Open®, and the X device is a
+trademark of X/Open Company Ltd. in the UK and other countries. OSF,
+OSF/1, OSF/Motif, Motif, and Open Software Foundation are trademarks of
+the Open Software Foundation in the U.S. and other countries.
+
+© 1990, 1991, 1992, 1993 Open Software Foundation, Inc. The OSF
+documentation and the OSF software to which it relates are derived in
+part from materials supplied by the following: © 1987, 1988, 1989
+Carnegie-Mellon University. © 1989, 1990, 1991 Digital Equipment
+Corporation.
+
+<<<
+© 1985, 1988, 1989, 1990 Encore Computer Corporation. © 1988 Free
+Software Foundation, Inc. © 1987, 1988, 1989, 1990, 1991 Hewlett-Packard
+Company. © 1985, 1987, 1988, 1989, 1990, 1991, 1992 International
+Business Machines Corporation. © 1988, 1989 Massachusetts Institute of
+Technology. © 1988, 1989, 1990 Mentat Inc. © 1988 Microsoft Corporation.
+© 1987, 1988, 1989, 1990, 1991,
+1992 SecureWare, Inc. © 1990, 1991 Siemens Nixdorf Informations systeme
+AG. © 1986, 1989, 1996, 1997 Sun Microsystems, Inc. © 1989, 1990, 1991
+Transarc Corporation.
+
+OSF software and documentation are based in part
+on the Fourth Berkeley Software Distribution under license from The
+Regents of the University of California. OSF acknowledges the following
+individuals and institutions for their role in its development: Kenneth
+C.R.C. Arnold, Gregory S. Couch, Conrad C. Huang, Ed James, Symmetric
+Computer Systems, Robert Elz. © 1980, 1981, 1982, 1983, 1985, 1986,
+1987, 1988, 1989 Regents of the University of California. OSF MAKES NO
+WARRANTY OF ANY KIND WITH REGARD TO THE OSF MATERIAL PROVIDED HEREIN,
+INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
+AND FITNESS FOR A PARTICULAR PURPOSE. OSF shall not be liable for errors
+contained herein or for incidental consequential damages in connection
+with the furnishing, performance, or use of this material.
+
+*Revision History*
+
+[cols="2",options="header"]
+|===
+| Version    | Date
+| 2.0.0      | To be announced.
+| 1.3.0      | January, 2016  
+|===
+
+include::asciidoc/_chapters/about.adoc[]
+include::asciidoc/_chapters/introduction.adoc[]
+include::asciidoc/_chapters/get_started.adoc[]
+include::asciidoc/_chapters/develop_spjs.adoc[]
+include::asciidoc/_chapters/execute_spjs.adoc[]
+
+

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/src/site/markdown/documentation.md
----------------------------------------------------------------------
diff --git a/docs/src/site/markdown/documentation.md b/docs/src/site/markdown/documentation.md
index a3848eb..89155b5 100644
--- a/docs/src/site/markdown/documentation.md
+++ b/docs/src/site/markdown/documentation.md
@@ -18,16 +18,20 @@ This page provides links to the per-release Trafodion documentation.
 
 Document                                              | Formats
 ------------------------------------------------------|-----------------------------------
+Scalar UDFs in C                                      | [wiki](https://cwiki.apache.org/confluence/display/TRAFODION/Scalar+UDFs+-+In+C)
 Trafodion Client Installation Guide                   | [Web Book](docs/client_install/index.html),[PDF](docs/client_install/Trafodion_Client_Installation_Guide.pdf)
+Trafodion Code Examples                               | [wiki](https://cwiki.apache.org/confluence/display/TRAFODION/Trafodion+Code+Examples)
 Trafodion Command Interface Guide                     | [Web Book](docs/command_interface/index.html),[PDF](docs/command_interface/Trafodion_Command_Interface_Guide.pdf)
 Trafodion Control Query Default (CQD) Reference Guide | [Web Book](docs/cqd_reference/index.html),[PDF](docs/cqd_interface/Trafodion_CQD_Reference_Guide.pdf)
 Trafodion Database Connectivity Services Guide        | [Web Book](docs/dcs_reference/index.html),[API](docs/dcs_reference/apidocs/index.html)
 Trafodion Load and Transform Guide                    | [Web Book](docs/load_transform/index.html),[PDF](docs/load_transform/Trafodion_Load_Transform_Guide.pdf)
 Trafodion Messages Guide                              | [Web Book](docs/messages_guide/index.html),[PDF](docs/messages_guide/Trafodion_Messages_Guide.pdf)
+Trafodion Manageability                               | [wiki](https://cwiki.apache.org/confluence/display/TRAFODION/Trafodion+Manageability)
 Trafodion odb User Guide                              | [Web Book](docs/odb/index.html),[PDF](docs/odb/Trafodion_odb_User_Guide.pdf)
 Trafodion Provisioning Guide                          | [Web Book](docs/provisioning_guide/index.html),[PDF](docs/provisioning_guide/Trafodion_Provisioning_Guide.pdf)
 Trafodion REST Server Reference Guide                 | [Web Book](docs/rest_reference/index.html),[API](docs/rest_reference/apidocs/index.html)
 Trafodion SQL Reference Manual                        | [Web Book](docs/sql_reference/index.html),[PDF](docs/sql_reference/Trafodion_SQL_Reference_Manual.pdf)
+UDF Tutorial                                          | [wiki](https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface)
 
 # 2.0.0 (In Development)
 

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/src/site/markdown/download.md
----------------------------------------------------------------------
diff --git a/docs/src/site/markdown/download.md b/docs/src/site/markdown/download.md
index 66e0c2f..356e568 100644
--- a/docs/src/site/markdown/download.md
+++ b/docs/src/site/markdown/download.md
@@ -26,7 +26,7 @@ The Trafodion end-user environment is installed using the Trafodion Installer, w
 * [log4c++ RPM](http://traf-builds.esgyn.com/downloads/trafodion/publish/release/1.3.0/log4cxx-0.10.0-13.el6.x86_64.rpm)
 * [Trafodion Installer](http://traf-builds.esgyn.com/downloads/trafodion/publish/release/1.3.0/apache-trafodion-installer-1.3.0-incubating-bin.tar.gz)
 * [Trafodion Server](http://traf-builds.esgyn.com/downloads/trafodion/publish/release/1.3.0/apache-trafodion-1.3.0-incubating-bin.tar.gz)
-* [Trafodion Clients](http://traf-builds.esgyn.com/downloads/trafodion/publish/release/1.3.0/apache-trafodion-clients-1.3.0-incubating-bin.tar.gz)
+* [Trafodion Clients](http://traf-builds.esgyn.com/downloads/trafodion/publish/release/1.3.0/apache-trafodion-clients-1.3.0-incubating-bin.tar.gz) (JDBC, odb, ODBC, trafci)
 
 # Install
 

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/install/installer/rest_installer
----------------------------------------------------------------------
diff --git a/install/installer/rest_installer b/install/installer/rest_installer
index 68888c2..2907c2a 100755
--- a/install/installer/rest_installer
+++ b/install/installer/rest_installer
@@ -44,7 +44,7 @@ EOF
 # Default parameters
 typeset REST_BUILD_FILE=""
 typeset REST_INSTALL_PATH="$MY_SQROOT"
-
+typeset TRAF_CONFIG="/etc/trafodion/trafodion_config"
 # Parse input parameters
 while [[ $# -gt 0 ]]; do
     case "$1" in
@@ -76,7 +76,9 @@ while [[ $# -gt 0 ]]; do
   shift
 done
 
+source 
 echo "***INFO: Start of REST Server install"
+source $TRAF_CONFIG
 
 # Make sure required env vars have been set first
 if [[ -z "$MY_SQROOT" ]]; then
@@ -108,13 +110,6 @@ mv $MY_SQROOT/sqenvcom.temp $MY_SQROOT/sqenvcom.sh
 cd $REST_DIR/conf
 
 echo "***INFO: modifying $REST_DIR/conf/rest-site.xml"
-# get zookeeper quorum
-
-lineNumber=$(grep -n "zookeeper\.quorum" /etc/hbase/conf/hbase-site.xml | sed 's/\:.*//')
-lineNumber=$((lineNumber+1))
-
-ZOOKEEPER_NODES=`sed "$lineNumber!d" /etc/hbase/conf/hbase-site.xml | sed 's/\/value.*//' | sed 's/.*>//' | sed 's/.$//'`
-
 # add zookeeper quorum property to end of configuration
 rm rest-site.temp 2>/dev/null
 cat rest-site.xml | sed -e "s@</configuration>@  <property>\n    <name>rest.zookeeper.quorum</name>\n    <value>$ZOOKEEPER_NODES</value>\n  </property>\n </configuration>@" > rest-site.temp


Mime
View raw message