HP Neoview Guide to Stored Procedures in Java HP Part Number: 613605-001 Published: July 2010 Edition: HP Neoview Release 2.
© Copyright 2010 Hewlett-Packard Development Company, L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor’s standard commercial license. The information contained herein is subject to change without notice.
Table of Contents About This Document.......................................................................................................11 Intended Audience................................................................................................................................11 New and Changed Information in This Edition...................................................................................11 Document Organization.......................................................................
Connection Pooling....................................................................................................................28 Using JDBC Method Calls...............................................................................................................29 Referring to Database Objects in an SPJ Method............................................................................30 Exception Handling...............................................................................................
Using Unnamed Parameters............................................................................................................55 Returning Result Sets in NCI...........................................................................................................55 Calling SPJs From an ODBC Client Application..................................................................................56 Returning Result Sets in an ODBC Client Application...........................................................
Calling the Procedure: PROJECTTEAM....................................................................................89 TOPSALESREPS Procedure............................................................................................................90 Java Method: topSalesReps()......................................................................................................90 Creating the Procedure: TOPSALESREPS.................................................................................
List of Figures 1-1 7-1 Different Applications Calling the Same SPJ................................................................................18 CALL Statement Elements............................................................................................................
List of Tables 3-1 7-1 8 Mapping of Java Data Types to SQL Data Types..........................................................................24 Input Parameter Argument Types................................................................................................
List of Examples A-1 A-2 A-3 A-4 A-5 A-6 A-7 A-8 A-9 A-10 A-11 A-12 A-13 A-14 A-15 A-16 Sales.java—The Sales Class...........................................................................................................65 lowerPrice() Method......................................................................................................................69 numDailyOrders() Method...........................................................................................................
About This Document This manual describes how to develop, deploy, and manage stored procedures in Java (SPJs) on the Neoview Data Warehousing Platform for Business Intelligence. Intended Audience This manual is intended for application programmers who are writing and compiling Java code for stored procedures and for database administrators who are deploying and managing stored procedures in Java (SPJs) on the Neoview platform.
Appendix A: Sample SPJs Provides examples of SPJ methods that demonstrate business logic in a Neoview database. Appendix B: Sample Database Describes the sample database on which the Sample SPJs operate. Notation Conventions General Syntax Notation This list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required.
horizontally, enclosed in a pair of braces and separated by vertical lines. For example: INTERVAL { start-field TO end-field } { single-field } INTERVAL { start-field TO end-field | Vertical Line | single-field } A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces.
[ESCAPE esc-char-expression] Related Documentation This manual is part of the HP Neoview customer library. Neoview Customer Library The manuals in the Neoview customer library are listed here for your convenience.
Neoview Workload Management Information about using Neoview Workload Management Services (WMS) to Services Guide manage workload and resources on a Neoview data warehousing platform.
HP Encourages Your Comments HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs. Send any errors found, suggestions for improvement, or compliments to: docsfeedback@hp.com Include the document title, part number, and any comment, error found, or suggestion for improvement you have concerning this document.
1 Introduction This chapter introduces stored procedures in Java (SPJs) in a Neoview database and covers these topics: • • • “What Is an SPJ?” “Benefits of SPJs” (page 17) “How Do I Use SPJs in a Neoview Database?” (page 19) What Is an SPJ? A stored procedure is a type of user-defined routine (UDR) that operates within a database server and typically performs SQL operations on a database. The database server contains information about the stored procedure and controls its execution.
Java Methods Callable From Neoview SQL With support for SPJs, Java methods are callable from any client application that communicates with the Neoview platform. For example, you can invoke the same SPJ method from JDBC client applications and ODBC client applications. By using Neoview SQL to invoke Java methods, you can extend the functionality of a Neoview database and share business logic among different applications.
Using the Java language to implement stored procedures increases development productivity. Given the popularity of the Java language, you can leverage the existing skill set of Java programmers to develop SPJs. The portability of the Java language enables you to write and compile Java class files for SPJs once and deploy them anywhere. Portability Because SPJ methods are written in Java, and SPJs conform to the ANSI SQL standard, SPJs are portable across different database servers.
c. Package the SPJ class file in a JAR file: jar cvf Payroll.jar Payroll.class If the SPJ class refers to other classes, package the other classes in the same JAR file as the SPJ class: jar cvf Payroll.jar Payroll.class other.class For details, see Chapter 3 (page 23). 3. 4. 5. Upload the SPJ JAR file from the client workstation to the Neoview platform. For details, see Chapter 4 (page 33). As the schema owner, create the SPJ in the Neoview database. For details, see Chapter 5 (page 37).
2 Getting Started Before you can start using SPJs on the Neoview platform, verify that you have the required software installed on the client workstation and the Neoview platform. Required Software on the Neoview Platform To use SPJs on the Neoview platform, you must have at least Neoview Release 2.2 (N02.02) or a later version of the database software on the Neoview platform. The Neoview Release 2.
Recommend Software on the Client Workstation Neoview Command Interface (NCI) NCI is a command-line interface in which you can run SQL statements, such as GRANT EXECUTE and CALL statements, interactively or from script files. Neoview Release 2.2 or a later version of NCI (or Neoview Script, as it was called in Neoview Release 2.2) supports SPJs. To install NCI on a client workstation, see the README for the HP Neoview Command Interface (NCI).
3 Developing SPJ Methods Before creating, or registering, an SPJ in a Neoview database, you must write and compile the Java method to be used as the body of the SPJ. The manual refers to those Java methods as SPJ methods.
Table 3-1 Mapping of Java Data Types to SQL Data Types Java Data Type Maps to SQL Data Type... java.lang.String CHAR[ACTER] CHAR[ACTER] VARYING VARCHAR PIC[TURE] X1 NCHAR NCHAR VARYING NATIONAL CHAR[ACTER] NATIONAL CHAR[ACTER] VARYING java.sql.Date DATE java.sql.Time TIME java.sql.Timestamp TIMESTAMP java.math.BigDecimal NUMERIC (including NUMERIC with a precision greater than eighteen)2 DEC[IMAL]2 PIC[TURE] S93 short SMALLINT2 int or java.lang.Integer4 INT[EGER]2 long or java.lang.
This subsection covers these topics related to output parameters: • • “Using Arrays for Output Parameters” (page 25) “Type Mapping of Output Parameters” (page 25) Using Arrays for Output Parameters You must use arrays for the output parameters of a Java method because of how Java handles the arguments of a method. Java supports arguments that are passed by value to a method and does not support arguments that are passed by reference.
parameters in the signature, Neoview SQL prevents you from creating an SPJ using that Java method. This example shows the declaration of an SPJ method, orderSummary(), which returns a maximum of two result sets: public static void orderSummary(java.lang.String onOrAfter, long[] numOrders, java.sql.ResultSet[] orders, java.sql.ResultSet[] detail) This code fragment shows how the orderSummary() method returns one of its result sets by executing a SELECT statement and assigning the java.sql.
For information about processing result sets in different calling applications, see: • • • “Returning Result Sets in NCI” (page 55) “Returning Result Sets in an ODBC Client Application” (page 56) “Returning Result Sets in a JDBC Client Application” (page 58) Using the main() Method You can use the main() method of a Java class file as an SPJ method. The main() method is different from other Java methods because it accepts input values in an array of java.lang.
Accessing a Neoview Database SPJ methods that access a Neoview database must be from a Java class that uses JDBC method calls. Follow these guidelines when writing an SPJ method that accesses a Neoview database: • • • • “Use of java.sql.Connection Objects” (page 28) “Using JDBC Method Calls” (page 29) “Referring to Database Objects in an SPJ Method” (page 30) “Exception Handling” (page 31) Use of java.sql.
• maxPoolSize=0 • minPoolSize=1 • initialPoolSize=1 To change these settings, use the properties parameter of the DriverManager.getConnection() method as shown below: java.util.Properties props = new Properties(); props.setProperty("maxPoolSize", "10"); props.setProperty("minPoolSize", "5"); props.setProperty("initialPoolSize", "5"); Connection conn = DriverManager.
Referring to Database Objects in an SPJ Method In an SPJ method, you can refer to SQL database objects by specifying two-part ANSI names that include the schema and object name. For more information about database object names, see the Neoview SQL Reference Manual. Neoview SQL propagates the name of the schema where the SPJ is registered to the SPJ environment.
Exception Handling For SPJ methods that access a Neoview database, no special code is necessary for handling exceptions. If an SQL operation fails inside an SPJ, the error message associated with the failure is returned to the application that issues the CALL statement. Handling Java Exceptions If an SPJ method returns an uncaught Java exception or an uncaught chain of java.sql.SQLException objects, Neoview SQL converts each Java exception object into an SQL error condition, and the CALL statement fails.
1. Compile the Java source file into Java bytecode by using the Java programming language compiler, javac: javac Payroll.java 2. Put the SPJ class file and all associated class files into a Java archive (JAR) file: jar cvf Payroll.jar Payroll.class A manifest file is not needed for the JAR file.
4 Deploying SPJ JAR Files on the Neoview Platform After developing and packaging the SPJ code into JAR files, you must move the JAR files from the client workstation to the Neoview platform. To upload the SPJ JAR files to the Neoview platform, use the HP Database Manager (HPDM).
NOTE: Note that if you selected the role folder, the Procedure Code File Tool shows all of the JAR files for the Neoview role that you used to connect to the current system. The role is your default role unless you specified a different role in the system definition. 8. Open the JAR file folder to display the class files within it, and click one of the class files to display the method names and signatures. 9.
2. 3. In the navigation tree pane, right-click a schema name, and select Procedure Code File Tool. The Procedure Code File Tool window appears. In the left pane of the Procedure Code Files Tool, open the folder for your Neoview role or user ID to display the JAR files within it. NOTE: If you selected the folder for your role, note that the Procedure Code File Tool shows all of the JAR files for the Neoview role that you used to connect to the current system.
7. 36 When you are done, click File > Exit or the Close button ( ) to close the Procedure Code File Tool dialog box and return to the HPDM main window.
5 Creating SPJs in the Neoview Database After uploading the SPJ JAR files to the Neoview platform, you can create the procedures in the Neoview database. To create or drop a procedure from the Neoview database, use the HP Database Manager (HPDM).
NOTE: The left pane of the Procedure Code File Browser shows folders for your Neoview role and private user ID. If you select the role folder, the Procedure Code File Browser shows all of the JAR files for the Neoview role that you used to connect to the current system. The role is your default role unless you specified a different role in the system definition. If an SPJ JAR file does not exist on the Neoview platform, you can upload it. See “Upload a JAR File” (page 33). 6.
Use the Create Procedure Dialog Box This table shows reference information for the Create Procedure dialog box: Group Box Control or Field Action Name Name Enter a name for the new procedure. The name must be unique and must not exist for any procedure, table, or view in the same schema. The procedure name is not case-sensitive. Neoview SQL automatically qualifies the procedure name with the name of the schema in which you are creating the procedure.
NOTE: There can be more than one java.sql.ResultSet[] parameter, but they must all be at the end of the method signature. • Have these parameter types: Parameter Type or Class Type character “java.lang.String” “java.lang.String[]” numeric “java.lang.Integer” “java.lang.Integer[]” “java.lang.Long” “java.lang.Long[]” “java.lang.Float” “java.lang.Float[]” “java.lang.Double” “java.lang.Double[]” “java.math.BigDecimal” “java.math.BigDecimal[]” date/timestamp “java.sql.Date” “java.sql.Date[]” “java.sql.
all objects, members of your role could drop procedures. For more information about the GRANT SCHEMA statement, see the Neoview SQL Reference Manual. To drop a procedure: 1. 2. 3. 4. 5. Under the navigation tree pane, click the Database area. Under My Systems, expand the tree for the Neoview platform containing the procedure until you can see the schema folder and the Procedures folder underneath it. Expand the Procedures folder.
In the right pane, these tabs appear: • • • • Attributes tab Parameters tab Usage tab Privileges tab Attributes Tab To display the procedure names and attributes: 1. 2. 3. Select a Schema in the navigation tree. Click the Procedures tab in the right pane and select a procedure name, or open the Procedures folder and select a procedure name in the tree. Click the Attributes tab in the right pane. In the right pane, these attributes are displayed: • Name: The name of the procedure.
• • Creation Time: Time and date when the procedure was created. Timestamp is in the format (yyyy-MM-dd HH:mm:ss.FFFFFF local time). Redefinition Time: Time and date when the procedure was last redefined. Timestamp is in the format (yyyy-MM-dd HH:mm:ss.FFFFFF local time). Parameters Tab To display the procedure parameter information: 1. 2. 3. Select a Schema in the navigation tree.
1. 2. 3. Select a Schema in the navigation tree. Click the Procedures tab in the right pane and select a procedure name, or open the Procedures folder and select a procedure name in the tree. Click the Privileges tab in the right pane. The Privileges tab displays a table showing the access privileges. • • • • • Grantee: The name of a role whose privilege is being described. Grantor: The name of the role that granted the privileges and the special SQL user names “PUBLIC” and “SYSTEM.
overwrite the original JAR file on the Neoview platform. For more information, see “Upload a JAR File” (page 33). If you upload a JAR file that contains an SPJ method that has a different class name, method name, or signature than the original SPJ method, you must drop the SPJ from the database before uploading the JAR file. After uploading the JAR file, re-create the SPJ in the database.
6 Granting Privileges for Executing SPJs Security for SPJs is implemented by schema ownership rules and by granting privileges to specified user roles. The schema in which an SPJ is registered is the unit of ownership. The role of the user who creates the schema is the owner of that schema and all objects associated with it.
GRANT EXECUTE ON sales.totalprice TO PUBLIC; After granting the EXECUTE privilege to PUBLIC, you cannot revoke the privilege from a subset of roles. You must revoke the privilege from PUBLIC and then revoke the privilege from specific roles. Granting Privileges on Referenced Database Objects If the SPJ operates on a database object, the roles that invoke the SPJ must have the appropriate privileges on that database object.
REVOKE GRANT OPTION FOR EXECUTE ON PROCEDURE persnl.adjustsalary FROM "ROLE.PAYROLLA"; The SPJ owner (or creator) can also revoke the EXECUTE privilege from any user with this privilege and from any dependent privileges by using the CASCADE option. In this example, the SPJ owner (or creator) revokes the EXECUTE privilege from the ROLE.PAYROLLA role and from the regional department managers to whom the ROLE.PAYROLLA role granted privileges: REVOKE GRANT OPTION FOR EXECUTE ON PROCEDURE persnl.
ON PROCEDURE sales.monthlyorders FROM PUBLIC; REVOKE SELECT ON TABLE sales.orders FROM PUBLIC; ?SECTION RevokePersnlProcs REVOKE GRANT OPTION FOR EXECUTE ON PROCEDURE persnl.adjustsalary FROM "ROLE.PAYROLLA", "ROLE.PAYROLLB" CASCADE; REVOKE EXECUTE ON PROCEDURE persnl.adjustsalary FROM "ROLE.PAYROLLA", "ROLE.PAYROLLB" CASCADE; REVOKE SELECT, UPDATE(salary) ON TABLE persnl.employee FROM "ROLE.PAYROLLA", "ROLE.
7 Executing SPJs This chapter describes how to execute SPJs by using the CALL statement and assumes that you have already registered the SPJs in the Neoview database and that you have granted privileges to execute the SPJs to the appropriate roles. For information, see Chapter 5 (page 37) and Chapter 6 (page 47).
back any changes made to the database at the end of the CALL statement execution. However, when AUTOCOMMIT is OFF, the current transaction remains active until the end of the client session or until you explicitly commit or roll back the transaction.
CALL sales.lowerprice(); If the SPJ has one IN parameter, one OUT parameter, and two result sets, you must list the IN and OUT parameters but not the result sets in the argument list: CALL sales.ordersummary('01-01-2001', ?); Data Conversion of Parameter Arguments Neoview SQL performs an implicit data conversion when the data type of a parameter argument is compatible with but does not match the formal data type of the stored procedure.
OUT and INOUT parameter arguments must be dynamic parameters in a client application (for example, ?) or named or unnamed parameters in NCI (for example, ?param or ?).
Using Unnamed Parameters In an NCI session, invoke the SPJ named TOTALPRICE by preparing and executing a CALL statement. The INOUT parameter accepts a value that is set by the USING clause of the EXECUTE statement and returns the total price: SQL>prepare stmt1 from call sales.totalprice(50, 'nextday',?); --- SQL command prepared. SQL>execute stmt1 using 2.
Order/Num ---------- Part/Num -------- Unit/Price ------------ Qty/Ord ---------- Part Description ------------------ 100210 100210 100210 100210 100250 100250 . . . 2001 2403 244 5100 6500 6301 1100.00 620.00 3500.00 150.00 95.00 245.00 3 6 3 10 10 15 GRAPHIC PRINTER,M1 DAISY PRINTER,T2 PC GOLD, 30 MB MONITOR BW, TYPE 1 DISK CONTROLLER GRAPHIC CARD, HR --- 70 row(s) selected. --- SQL operation complete. For other result set examples, see Appendix A (page 65).
NOTE: The Neoview ODBC API does not currently support interleaved result set processing, where more than one returned result set can be open at a time. /* Allocate a statement handle */ SQLHSTMT s; RETCODE rc = SQLAllocHandle(SQL_HANDLE_STMT, myConnection, &s); /* Prepare a CALL */ char *stmtText = "{call sales.
Returning Result Sets in a JDBC Client Application This example shows serial result set processing in a JDBC client application where the result sets are processed in order and one at a time after the CALL statement executes. The java.sql.Statement.getMoreResults() method closes the current result set and moves processing to the next available result set. // Prepare a CALL statement java.sql.CallableStatement s = myConnection.prepareCall ( "{call sales.
} } Calling an SPJ in a Trigger A trigger is a mechanism in the database that enables the database engine to perform certain actions when a specified event occurs. SPJs are useful as triggered actions because they can help you encapsulate and enforce rules in the database. For more information about the benefits of using SPJs, see “Benefits of SPJs” (page 17).
8 Performance and Troubleshooting This chapter describes how to improve and monitor the performance of SPJs on the Neoview platform and provides guidelines for troubleshooting common problems: • • • “Troubleshooting Common Problems” “Performance Tip” “Displaying an Execution Plan of a CALL Statement” (page 62) Troubleshooting Common Problems To resolve problems that occur when you register or execute an SPJ, follow these guidelines: • • • • • • • • • • • Note the SQLCODE or SQLSTATE value of the e
Performance Tip To ensure the optimal performance of SPJs on the Neoview platform, avoid nesting CALL statements in an SPJ method, which wastes resources and might diminish performance. For more information, see “Nested Java Method Invocations” (page 27). Displaying an Execution Plan of a CALL Statement An execution plan reveals how a CALL statement was optimized. You can display all or part of the execution plan for a CALL statement by using the EXPLAIN statement or function.
signature .............. language ............... max_result_sets ........ input_values ........... (Ljava/math/BigDecimal;D[Ljava/math/BigDecimal;)V Java 0 cast(202), cast(cast((cast(5.5) / cast(10)))) --- SQL operation complete. SQL> The EXPLAIN statement generates and displays all the columns of the result table of the EXPLAIN function. For the syntax of the EXPLAIN statement, see the Neoview SQL Reference Manual.
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 Neoview database. For information about the sample database, see Appendix B (page 101).
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 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 “DAILYORDERS Procedure” (page 71).
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); price[0] = subtotal.add(charges); } // See the “TOTALPRICE Procedure” (page 75).
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 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.
" AND " ORDER BY o.order_date >= CAST(? AS DATE) d.ordernum " + "; java.sql.PreparedStatement ps3 = conn.prepareStatement(s); ps3.setString(1, onOrAfter); detail[0] = ps3.executeQuery(); } // See the “ORDERSUMMARY Procedure” (page 81). } See the following sections for more information about each SPJ method. 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.
Parameters: None Attributes: • Dynamic result sets: 0 • Accesses Database: selected Calling the Procedure: LOWERPRICE To invoke the LOWERPRICE procedure in NCI: SQL>call 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 sales.parts p LEFT OUTER JOIN sales.odetail o ON p.
DAILYORDERS Procedure The DAILYORDERS procedure accepts a date and returns the number of orders on that date to an output parameter. Java Method: numDailyOrders() Example A-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 sales.orders " + "WHERE order_date = ?"); getNumOrders.
Calling the Procedure: DAILYORDERS To invoke the DAILYORDERS procedure in NCI: SQL>call sales.dailyorders(date '2003-03-19', ?); The DAILYORDERS procedure determines the total number of orders on a specified date and returns this output in NCI: NUMBER ----------2 --- SQL operation complete. On March 19, 2003, there were two orders.
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() Example A-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.
Calling the Procedure: MONTHLYORDERS To invoke the MONTHLYORDERS procedure in NCI: SQL>call sales.monthlyorders(3,?); The MONTHLYORDERS procedure determines the total number of orders during a specified month and returns this output in NCI: ORDERNUM ----------4 --- SQL operation complete. In March, there were four orders.
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() Example A-5 totalPrice() Method public static void totalPrice(BigDecimal qtyOrdered, String shippingSpeed, BigDecimal[] price) throws SQLException { BigDecimal shipcharge = new BigDecimal(0); if (shippingSpeed.
Parameters: • First SQL parameter: — Name: qty — Direction: IN — SQL Data Type: NUMERIC ◦ Precision: 18 ◦ Scale: 0 — Java Data Type: java.math.BigDecimal • Second SQL parameter: — Name: rate — Direction: IN — SQL Data Type: VARCHAR ◦ Length: 10 ◦ Upshift: not selected ◦ Character set: ISO88591 — Java Data Type: java.lang.String • Third SQL parameter: — Name: price — Direction: INOUT — SQL Data Type: NUMERIC ◦ Precision: 18 ◦ Scale: 2 — Java Data Type: java.math.
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 where 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
// 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 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.
Parameters: • First SQL parameter: — Name: partnum — Direction: IN — SQL Data Type: INTEGER — Java Data Type: int • Second SQL parameter: — Name: partdesc — Direction: OUT — SQL Data Type: CHARACTER ◦ Length: 18 ◦ Varying: not selected ◦ Upshift: not selected ◦ Character set: ISO88591 — Java Data Type: java.lang.String[] • Third SQL parameter: — Name: price — Direction: OUT — SQL Data Type: NUMERIC ◦ Precision: 8 ◦ Scale: 2 — Java Data Type: java.math.
--- 2 row(s) selected. EMPNUM -----220 227 FIRST_NAME --------------JOHN XAVIER LAST_NAME DEPTNUM JOBCODE SALARY -------------------- ------- ------- ---------HUGHES 3200 300 33000.10 SEDLEMEYER 3300 300 30000.00 --- 2 row(s) selected. --- SQL operation complete.
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, order date, total dollar amount, number of parts ordered, and the name of the sales representative. • A result set that contains details about each order.
java.sql.PreparedStatement ps3 = conn.prepareStatement(s); ps3.setString(1, onOrAfter); detail[0] = ps3.executeQuery(); } Creating the Procedure: ORDERSUMMARY To create this procedure in the SALES schema, upload the Sales.jar file to the Neoview platform, navigate to the SALES schema in HPDM, and then enter or select these values in the Create Procedure dialog box of HPDM. For more information, see the HP Database Manager (HPDM) User Guide. Name: ordersummary Code: Sales.
Procedures in the PERSNL Schema The Payroll class contains these SPJ methods, which are useful for managing personnel data: • “ADJUSTSALARY Procedure” (page 85) • “EMPLOYEEJOB Procedure” (page 87) • “PROJECTTEAM Procedure” (page 89) • “TOPSALESREPS Procedure” (page 90) Those methods are registered as stored procedures in the PERSNL schema. Example A-8 shows the code of the Payroll.java source file. Example A-8 Payroll.java—The Payroll Class import java.sql.*; import java.math.
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 persnl.employee E, persnl.dept D, persnl.project P " + " WHERE P.projcode = ? " + " AND P.empnum = E.empnum " + " AND E.deptnum = D.deptnum "); getMembers.setInt(1, projectCode); } members[0] = getMembers.
ADJUSTSALARY Procedure The ADJUSTSALARY procedure accepts an employee number and a percentage value and updates the employee’s salary in the database based on this value. This method also returns the updated salary to an output parameter. Java Method: adjustSalary() Example A-9 adjustSalary() Method public static void adjustSalary(BigDecimal empNum, double percent, BigDecimal[] newSalary) throws SQLException { Connection conn = DriverManager.
Parameters: • First SQL parameter: — Name: empnum — Direction: IN — SQL Data Type: NUMERIC ◦ Precision: 4 ◦ Scale: 0 — Java Data Type: java.math.BigDecimal • Second SQL parameter: — Name: percent — Direction: IN — SQL Data Type: FLOAT ◦ Bits of precision: 52 — Java Data Type: double • Third SQL parameter: — Name: newsalary — Direction: OUT — SQL Data Type: NUMERIC ◦ Precision: 8 ◦ Scale: 2 — Java Data Type: java.math.
EMPLOYEEJOB Procedure The EMPLOYEEJOB procedure accepts an employee number and returns a job code or null value to an output parameter. Java Method: employeeJob() Example A-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 persnl.employee " + "WHERE empnum = ?"); getJobcode.
The EMPLOYEEJOB procedure accepts the employee number 337 and returns this output in NCI: JOBCODE ----------900 --- SQL operation complete. The job code for employee number 337 is 900.
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() Example A-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.
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.
Calling the Procedure: TOPSALESREPS To invoke the TOPSALESREPS procedure in NCI: SQL>call persnl.topsalesreps(1); The TOPSALESREPS procedure returns this information about the top five sales representatives during the first fiscal quarter: EMPNUM -----227 231 222 226 220 FIRST_NAME --------------XAVIER HERB MARTIN HEIDI JOHN LAST_NAME TOTAL -------------------- -------------------SEDLEMEYER 172460.00 ALBERT 67025.00 SCHAEFFER 52000.00 WEIGL 28985.00 HUGHES 22625.00 --- 5 row(s) selected.
Procedures in the INVENT Schema The Inventory class contains these SPJ methods, which are useful for tracking parts and suppliers: • “SUPPLIERINFO Procedure” (page 94) • “SUPPLYNUMBERS Procedure” (page 97) • “PARTLOCS Procedure” (page 99) Those methods are registered as stored procedures in the INVENT schema. Example A-13 shows the code of the Inventory.java source file. Example A-13 Inventory.java—The Inventory Class import java.sql.*; import java.math.
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 invent.partloc L " + " WHERE L.partnum = ? " + " AND L.qty_on_hand = ? " + " ORDER BY L.partnum "); getLocationsExact.setInt(1, partNum); getLocationsExact.
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() Example A-14 supplierInfo() Method public static void supplierInfo(BigDecimal suppNum, String[] suppName, String[] streetAddr, String[] cityName, String[] stateName, String[] postCode) throws SQLException { Connection conn = DriverManager.
Parameters: • First SQL parameter: — Name: empnum — Direction: IN — SQL Data Type: NUMERIC ◦ Precision: 4 ◦ Scale: 0 — Java Data Type: java.math.BigDecimal • Second SQL parameter: — Name: suppname — Direction: OUT — SQL Data Type: CHARACTER ◦ Length: 18 ◦ Varying: not selected ◦ Upshift: not selected ◦ Character set: ISO88591 — Java Data Type: java.lang.
Calling the Procedure: SUPPLIERINFO To invoke the SUPPLIERINFO procedure in NCI: SQL>call invent.supplierinfo(25,?,?,?,?,?); The SUPPLIERINFO procedure accepts the supplier number 25 and returns this output in NCI: 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 The SUPPLYNUMBERS procedure returns the average, minimum, and maximum quantities of available parts in inventory to separate output parameters. Java Method: supplyQuantities() Example A-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.
Calling the Procedure: SUPPLYNUMBERS To invoke the SUPPLYNUMBERS procedure in NCI: SQL>call invent.supplynumbers(?,?,?); The SUPPLYNUMBERS procedure returns this output in NCI: 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 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() Example A-16 partLocations() Method public static void partLocations(int partNum, int quantity, ResultSet exactly[], ResultSet moreThan[]) throws SQLException { Connection conn = DriverManager.
Calling the Procedure: PARTLOCS To invoke the PARTLOCS procedure in NCI: SQL>call 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.
B Sample Database This appendix presents the Sample Database schemas and tables on which the SPJs in this manual operate: • “PERSNL Schema” (page 101) — “JOB Table” (page 101) — “EMPLOYEE Table” (page 101) — “DEPT Table” (page 103) — “PROJECT Table” (page 105) • “SALES Schema” (page 108) — “CUSTOMER Table” (page 108) — “ORDERS Table” (page 109) — “ODETAIL Table” (page 110) — “PARTS Table” (page 112) • “INVENT Schema” (page 113) — “SUPPLIER Table” (page 113) — “PARTSUPP Table” (page 114) — “PARTLOC Table” (p
,first_name CHARACTER (15) DEFAULT ' ' NOT NULL ,last_name CHARACTER (20) DEFAULT ' ' NOT NULL ,deptnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL ,jobcode NUMERIC (4) UNSIGNED DEFAULT NULL ,salary NUMERIC (8, 2) UNSIGNED DEFAULT NULL ,PRIMARY KEY (empnum) ); ALTER TABLE persnl.employee ADD CONSTRAINT empnum_constrnt CHECK (empnum BETWEEN 0001 AND 9999); CREATE INDEX xempname ON employee ( last_name ,first_name ); CREATE INDEX xempdept ON employee ( deptnum ); CREATE VIEW persnl.
( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( 203,'KATHRYN' ,'HALL' ,4000, 400, 96000.00 205,'GINNY' ,'FOSTER' ,3300, 900, 30000.00 206,'DAVE' ,'FISHER' ,3200, 900, 25000.00 207,'MARK' ,'FOLEY' ,4000, 420, 33000.00 208,'SUE' ,'CRAMER' ,1000, 900, 19000.00 209,'SUSAN' ,'CHAPMAN' ,1500, 900, 17000.00 210,'RICHARD' ,'BARTON' ,1000, 500, 29000.00 211,'JIMMY' ,'SCHNEIDER' ,1500, 600, 26000.00 212,'JONATHAN','MITCHELL' ,1500, 600, 32000.
,location VARCHAR (18) DEFAULT ' ' NOT NULL ,PRIMARY KEY (deptnum) ); CREATE INDEX xdeptmgr ON dept ( manager ); CREATE INDEX xdeptrpt ON dept ( rptdept ); ALTER TABLE persnl.dept ADD CONSTRAINT mgrnum_constrnt CHECK (manager BETWEEN 0000 AND 9999); ALTER TABLE persnl.dept ADD CONSTRAINT deptnum_constrnt CHECK (deptnum IN ( 1000 ,1500 ,2000 ,2500 ,3000 ,3100 ,3200 ,3300 ,3500 ,4000 ,4100 ,9000 )); CREATE VIEW persnl.
( 9000,'CORPORATE' , 1,9000,'CHICAGO' ); UPDATE STATISTICS FOR TABLE persnl.dept ON EVERY COLUMN; PROJECT Table CREATE TABLE persnl.project ( projcode NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL ,empnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL ,projdesc VARCHAR (18) DEFAULT ' ' NOT NULL ,start_date DATE DEFAULT DATE '2002-07-01' NOT NULL ,ship_timestamp TIMESTAMP DEFAULT TIMESTAMP '2002-08-01:12:00:00.
( 2000,215,'ROSS PRODUCTS' ,DATE '2002-06-10' ,TIMESTAMP '2002-07-21:08:30:00.0000' ,INTERVAL '30' DAY ), ( 2000,65,'ROSS PRODUCTS' ,DATE '2002-06-10' ,TIMESTAMP '2002-07-21:08:30:00.0000' ,INTERVAL '30' DAY ), ( 2500,65,'MONTANA TOOLS' ,DATE '2002-10-10' ,TIMESTAMP '2002-12-21:09:00:00.0000' ,INTERVAL '60' DAY ), ( 2500,207,'MONTANA TOOLS' ,DATE '2002-10-10' ,TIMESTAMP '2002-12-21:09:00:00.0000' ,INTERVAL '60' DAY ), ( 2500,232,'MONTANA TOOLS' ,DATE '2002-10-10' ,TIMESTAMP '2002-12-21:09:00:00.
( 4000,29,'THE WORKS' ,DATE '2002-09-21' ,TIMESTAMP '2002-10-21:10:15:00.0000' ,INTERVAL '30' DAY ), ( 4000,231,'THE WORKS' ,DATE '2002-09-21' ,TIMESTAMP '2002-10-21:10:15:00.0000' ,INTERVAL '30' DAY ), ( 4000,228,'THE WORKS' ,DATE '2002-09-21' ,TIMESTAMP '2002-10-21:10:15:00.0000' ,INTERVAL '30' DAY ), ( 4000,223,'THE WORKS' ,DATE '2002-09-21' ,TIMESTAMP '2002-10-21:10:15:00.0000' ,INTERVAL '30' DAY ), ( 4000,568,'THE WORKS' ,DATE '2002-09-21' ,TIMESTAMP '2002-10-21:10:15:00.
SALES Schema The SALES schema stores customer and sales data. CUSTOMER Table CREATE TABLE sales.customer ( custnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL ,custname CHARACTER (18) NO DEFAULT NOT NULL ,street CHARACTER (22) NO DEFAULT NOT NULL ,city CHARACTER (14) NO DEFAULT NOT NULL ,state CHARACTER (12) DEFAULT ' ' NOT NULL ,postcode CHARACTER (10) NO DEFAULT NOT NULL ,credit CHARACTER (2) DEFAULT 'C1' NOT NULL ,PRIMARY KEY (custnum) ); INSERT INTO sales.
(5635,'ROYAL CHEMICALS' ,'45 NEW BROAD STREET' ,'LONDON' ,'ENGLAND' ,'EC2M 1NH','B2' ), (7654,'MOTOR DISTRIBUTING','2345 FIRST STREET' ,'CHICAGO' ,'ILLINOIS' ,'60610' ,'E4' ), (7777,'SLEEPWELL HOTELS' ,'9000 PETERS AVENUE' ,'DALLAS' ,'TEXAS' ,'75244' ,'B1' ), (9000,'bunknought inn' ,'4738 ralph street' ,'bayonne' ,'new jersey' ,'09520' ,'c1' ), (9010,'hotel oregon' ,'333 portland ave.' ,'medford' ,'oregon' ,'97444' ,'c2' ), (9033,'art supplies, inc.','22 sweet st.' ,'pittsburgh','penna.
,o.custnum FROM persnl.employee e ,sales.orders o ,sales.customer c WHERE e.empnum = o.salesrep AND o.custnum = C.custnum; CREATE INDEX xcustnam ON customer ( custname ); CREATE VIEW sales.custlist AS SELECT custnum ,custname ,street ,city ,state ,postcode FROM sales.customer; INSERT INTO VALUES ( ( ( ( ( ( ( ( ( ( ( ( ( sales.
VALUES ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( 100210, 244, 100210,2001, 100210,2403, 100210,5100, 100250, 244, 100250,5103, 100250,6301, 100250,6500, 101220, 255, 101220,5103, 101220,7102, 101220,7301, 200300, 244, 200300,2001, 200300,2002, 200320,5504, 200320,6201, 200320,6301, 200320,6400, 200490,3210, 200490,5505, 300350, 244, 300350,5100, 300350,5110, 300350,6301, 300350,6400, 300380, 244, 300380,2402, 300380,240
( ( ( ( ( ( ( 800660,6201, 800660,6301, 800660,6400, 800660,6401, 800660,6500, 800660,7102, 800660,7301, 195.00, 235.00, 525.00, 700.00, 95.00, 275.00, 425.00, 6 24 30 36 22 6 12 ), ), ), ), ), ), ); UPDATE STATISTICS FOR TABLE sales.odetail ON EVERY COLUMN; PARTS Table CREATE TABLE sales.
(7102,'SMART MODEM, 1200' , (7301,'SMART MODEM, 2400' , 275.00, 2200 ), 425.00, 2332 ); UPDATE STATISTICS FOR TABLE sales.parts ON EVERY COLUMN; INVENT Schema The INVENT schema stored inventory data. SUPPLIER Table CREATE TABLE invent.
( 20,'Macadam''S PC''s' ,'106 River Road' ,'New Orleans' ,'Louisiana' ,'67890' ), ( 25,'Schroeder''s Ltd' ,'212 Strasse Blvd West' ,'Hamburg' ,'Rhode Island','22222' ), ( 30,'O''Donnell''s Drives','729 West Palm Beach ,'San Antonio' ,'Texas' ,'78344' ), ( 35,'Mac''Murphys PC''s' ,'93323 Alemeda' ,'Menlo Park' ,'California' ,'94025' ), ' ( 36,'MAC''MURPHYS PCB''s' ,'93323 Alemeda Suite B' ,'Menlo Park' ,'California' ,'94025' ), ( 90,'laser jets inc' ,'levittown' ,'penna.
,partprice ,qtyreceived ) AS SELECT x.partnum ,partdesc ,x.suppnum ,suppname ,partcost ,qty_received FROM invent.partsupp x ,sales.parts p ,invent.supplier s WHERE x.partnum = p.partnum AND x.suppnum = s.suppnum; CREATE VIEW invent.view207n ( partnumber ,partdescrpt ,suppnumber ,supplrname ,partprice ,qtyreceived ) AS SELECT x.partnum ,p.partdesc ,s.suppnum ,s.suppname ,x.partcost ,x.qty_received FROM invent.supplier s LEFT JOIN invent.partsupp x ON s.suppnum = x.suppnum LEFT JOIN sales.parts p ON x.
( 244, ( 244, ( 255, ( 255, (2001, (2001, (2002, (2002, (2003, (2003, (2003, (2402, (2403, (2405, (2405, (3103, (3103, (3201, (3205, (3210, (3210, (4102, (4102, (4102, (5100, (5100, (5100, (5101, (5101, (5103, (5103, (5110, (5110, (5504, (5504, (5504, (5505, (6201, (6301, (6400, (6401, (6401, (6500, (6500, (6603, (7102, (7301, 1, 2, 1, 3, 1, 2, 1, 6, 1, 2, 10, 1, 1, 1, 6, 1, 15, 1, 1, 6, 15, 6, 8, 15, 6, 8, 15, 8, 15, 8, 15, 1, 2, 2, 6, 15, 15, 1, 1, 1, 2, 3, 2, 3, 2, 10, 1, 2400.00, 2200.00, 3300.
,PRIMARY KEY ); INSERT INTO invent.
Index A Accesses Database, 38 ADJUSTSALARY procedure creating, 85 invoking, 86 adjustSalary() method code for, 29, 85 description of, 85 example of, 19 Altering SPJs, 44 Application classes, accessible to an SPJ, 31 Arguments, CALL statement input types arithmetic expression, 53 concatenation operation, 53 dynamic parameter, 53 literal, 53 scalar subquery, 53 SQL function, 53 list, 52 output types dynamic parameter, 54 named parameter, 54 unnamed parameter, 54 Array, output parameter, 25 Attributes tab, pro
I IN parameters, 53 INOUT parameters, 53 INVENT schema description of, 113 PARTLOC table, 116 PARTLOCS procedure, 99 PARTSUPP table, 114 SUPPLIER table, 113 SUPPLIERINFO procedure, 94 SUPPLYNUMBERS procedure, 97 Inventory class, 92 Invoking SPJs description of, 51 examples of, 65 JDBC client application, 57 NCI, 54 ODBC client application, 56 privileges, 47 restrictions, 51 transaction behavior, 51 ISO88591, 38 J JAR file deleting, 35 downloading, 34 renaming, 34 uploading, 33 Java classes accessible to an
Payroll class, 83 Performance tip, 62 PERSNL schema ADJUSTSALARY procedure, 85 DEPT table, 103 description of, 101 EMPLOYEE table, 101 EMPLOYEEJOB procedure, 87 JOB table, 101 PROJECT table, 105 PROJECTTEAM procedure, 89 TOPSALESREPS procedure, 90 Privileges displaying for procedures, 43 for creating SPJs, 37 for invoking SPJs, 47 granting, 47 on referenced database objects, 48 revoking, 48 Privileges tab, procedures, 43 Procedure Code File Tool deleting a JAR, 35 downloading a JAR, 34 renaming a JAR, 34 up
T throw statement, 31 TOPSALESREPS procedure creating, 90 invoking, 91 topSalesReps() method code for, 90 description of, 90 TOTALPRICE procedure creating, 75 invoking, 76 totalPrice() method code for, 75 description of, 75 Transaction behavior, 51 Triggers, 51, 59 Troubleshooting guidelines, 61 Types data types, 57 U UCS2, 38 Unnamed parameter, 55 Usage tab, procedures, 43 Usage, displaying for procedures, 43 User-defined exceptions, 31 V VERSION command, example of, 21 W WITH GRANT OPTION privilege, 47
