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
