HP Neoview Guide to Stored Procedures in Java HP Part Number: 544807-001 Published: April 2008 Edition: HP Neoview Release 2.
© Copyright 2008 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.......................................................................................................13 Intended Audience................................................................................................................................13 New and Changed Information in This Edition...................................................................................13 Document Organization.......................................................................
Exception Handling.........................................................................................................................32 Handling Java Exceptions.....................................................................................................................32 User-Defined Exceptions.................................................................................................................32 Compiling and Packaging Java Classes...................................................
8 Performance and Troubleshooting..............................................................................73 Troubleshooting Common Problems....................................................................................................73 Performance Tip....................................................................................................................................73 Displaying an Execution Plan of a CALL Statement..................................................................
Calling the Procedure: SUPPLIERINFO..................................................................................103 SUPPLYNUMBERS Procedure......................................................................................................104 Java Method: supplyQuantities().............................................................................................104 Creating the Procedure: SUPPLYNUMBERS...........................................................................
List of Figures 1-1 7-1 Different Applications Calling the Same SPJ................................................................................20 CALL Statement Elements............................................................................................................
List of Tables 3-1 7-1 Mapping of Java Data Types to SQL Data Types..........................................................................26 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...........................................................................................................77 lowerPrice() Method......................................................................................................................78 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.
Chapter 6: Granting Privileges for Executing SPJs Chapter 7: Executing SPJs Chapter 8: Performance and Troubleshooting Appendix A: Sample SPJs Appendix B: Sample Database Explains how to grant and revoke privileges for executing SPJs in a Neoview database. Explains how to execute an SPJ on the Neoview platform by using the CALL statement. Describes how to improve and monitor the performance of SPJs and provides guidelines for troubleshooting common problems.
FROM { grantee[, grantee]...} A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines.
continuation line from items in a vertical list of selections. For example: match-value [NOT] LIKE pattern [ESCAPE esc-char-expression] Related Documentation This manual is part of the HP Neoview customer library.
• Reference Neoview SQL Reference Manual Reference information about the syntax of SQL statements, functions, and other SQL language elements supported by the Neoview database software. • Mapping Tables for Neoview Character Sets Provides links to the mapping tables used by the Neoview Character Sets product. Neoview Messages Manual Cause, effect, and recovery information for error messages. Connectivity Neoview JDBC Type 4 Driver API Reference information about the HP Neoview JDBC Type 4 Driver API.
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 19) “How Do I Use SPJs in a Neoview Database?” (page 21) 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.
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. With minimal changes to SPJ methods, you can port existing SPJ JAR files from another database server to a Neoview platform and register the methods as stored procedures in a Neoview database.
3. 4. 5. Upload the SPJ JAR file from the client workstation to the Neoview platform. For details, see Chapter 4 (page 35). As the schema owner, create the SPJ in the Neoview database. For details, see Chapter 5 (page 43). Grant privileges to roles for executing the SPJ and for operating on the referenced database objects. For example, you can issue GRANT statements in an NCI session, as shown below, or use the Grant/Revoke Privileges Tool in DB Admin: GRANT EXECUTE ON PROCEDURE persnl.
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 Neoview Release 2.2 (N02.02) or a later version of the database software on the Neoview platform. The Neoview Release 2.2 or later platform is delivered to you ready to use and preconfigured with the software required to support SPJs.
on the client workstation. For more information about installing the JRE plug-in and starting the DB Admin tool, see the Neoview Database Administrator's Guide. 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.
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 NUMERIC2 DEC[IMAL]2 PIC[TURE] S93 java.math.BigInteger NUMERIC with a precision greater than eighteen2 short SMALLINT2 int or java.lang.Integer4 INT[EGER]2 long or java.
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. As a result, Java primitive types can be passed only to a method, not out of a method. Because a Java array is an object, its reference is passed by value to a method, and changes to the array are visible to the caller of the method.
This code fragment shows how the orderSummary() method returns one of its result sets by executing a SELECT statement and assigning the java.sql.ResultSet object to a java.sql.ResultSet[] output array: // Open a result set for order num, order info rows java.lang.String 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 sales.orders o, sales.odetail d " + " WHERE o.ordernum = d.
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.String objects and does not return any values in its array parameter. For example, you can register this main() method as an SPJ: public static void main (java.lang.String [] args) { ...
Use of java.sql.Connection Objects Neoview SQL supports a default connection in an SPJ execution environment, which has a data source URL of "jdbc:default:connection". For example: Connection conn = DriverManager.getConnection("jdbc:default:connection"); java.sql.Connection objects that use the "jdbc:default:connection" URL are portable to the Neoview platform from other database management systems (DBMSs).
about the Neoview JDBC Type 4 Driver, see the Neoview JDBC Type 4 Driver Programmer’s Reference and the Neoview JDBC Type 4 Driver API Reference. NOTE: You do not have to explicitly load the JDBC driver before establishing a connection to the Neoview database. Neoview SQL automatically loads the JDBC driver when the SPJ is called.
rs.next(); numOrders[0] = rs.getInt(1); rs.close(); conn.close(); } In the SPJ environment, the ORDERS table is qualified by default with the same schema as the SPJ, SALES. The default behavior takes effect only when getConnection() does not contain schema properties. Schema property values in getConnection() have higher precedence over the default behavior.
If you define the SQLSTATE to be outside the range of 38001 to 38999, Neoview SQL raises SQLSTATE 39001, external routine invocation exception. This example uses the throw statement in the SPJ method named numMonthlyOrders() to raise a user-defined error condition when an invalid argument value is entered for the month: public static void numMonthlyOrders(int month, int[] numOrders) throws java.sql.SQLException { if ( month < 1 || month > 12 ) { throw new java.sql.SQLException ( "Invalid value for month.
4 Deploying SPJ JAR Files on the Neoview Platform This chapter describes how to move the SPJ code that you developed and packaged into JAR files from a client workstation to the Neoview platform.
3. 4. 5. 6. 36 Click Upload. If necessary, activate the Upload button by clicking your user folder (for example, DBA1) in the left pane. In the Upload dialog box, navigate to and select the SPJ JAR file on your client workstation: Click Open to upload the JAR file to the Neoview platform.
7. 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: The bottom of the dialog box displays the timestamp of the JAR file (not the class file within the JAR file). 8. When done, click Close to close the Procedure Code Files Tool dialog box and return to the DB Admin window.
Downloading SPJ JAR Files to a Client Workstation To download an SPJ JAR file from the Neoview platform to a client workstation, follow these instructions. When you download a JAR file to a client workstation, the original JAR file is retained on the Neoview platform. NOTE: 1. 2. You can download only one JAR file at a time. In DB Admin, click the Database tab.
7. 8. Click Save to download the JAR file to your client workstation. When done, click Close to close the Procedure Code Files Tool dialog box and return to the DB Admin window. Renaming an SPJ JAR Files To rename an SPJ JAR file on the Neoview platform, follow these instructions. CAUTION: Renaming a JAR file might leave a dangling stored procedure reference in the Neoview database. A dangling reference might cause errors when an application tries to execute the stored procedure.
TIP: You can also access the Procedure Code Files Tool by right-clicking any schema or Procedures folder or by clicking the Code Tool button on the Code page of the Create Procedure Wizard. The Procedure Code Files Tool dialog box launches. 3. 4. 5. 6. 7. 40 In the left pane of the Procedure Code Files Tool, open your user folder to display the JAR file within it. Click the JAR file that you want to rename: Click Rename. When prompted to rename the code file, click Yes.
The renamed JAR file appears in the left pane of the Procedure Code Files Tool dialog box. 8. When done, click Close to close the Procedure Code Files Tool dialog box and return to the DB Admin window. Deleting SPJ JAR Files From the Neoview Platform To delete one or more SPJ JAR files on the Neoview platform, follow these instructions. CAUTION: Deleting a JAR file might leave a dangling stored procedure reference in the Neoview database.
3. 4. 5. 6. In the left pane of the Procedure Code Files Tool, open your user folder to display the JAR files within it. Select one or more JAR files that you want to delete: Click Delete. When prompted to delete one or more code files, click Yes to continue. Otherwise, click No to end the operation. When you click Yes the selected JAR files are deleted from your user folder on the Neoview platform. 7.
5 Creating SPJs in a Neoview Database This chapter describes how to create SPJs in a Neoview database after you have moved the SPJ JAR files to the Neoview platform. To create or drop SPJs from a Neoview database, you must have the Neoview DB Admin tool.
The procedure name is not case-sensitive, must be unique, and must not exist for any procedure, table, or view in the same schema. Neoview SQL automatically qualifies the procedure name that you supply with the name of the schema in which you create the procedure. For example, if you enter monthlyorders as the procedure name, Neoview SQL stores the procedure as SALES.MONTHLYORDERS. 5.
6. Click Parameters and verify that the SQL parameters are mapped correctly to the Java parameters of the SPJ method: NOTE: The result set parameters (java.sql.ResultSet[]) in the Java signature do not have corresponding SQL parameters. a. To change an SQL parameter, such as the name of the parameter, the SQL data type, or the parameter mode (direction), select the parameter and click Edit.
b. In the Specify Parameter dialog box, enter a new name for the parameter, select a different SQL data type, if permitted, or select a different parameter mode (direction), if permitted. When changing the parameter name, note that the parameter name: • Is not case-sensitive • Must not be a reserved word • Must not contain spaces • Must begin with a letter, digit, or underscore When changing the SQL data type, select a data type that is appropriate for the parameter of the underlying Java method.
IMPORTANT: If you do not select the Accesses Database check box and your SPJ method performs SQL operations, Neoview SQL returns an error when trying to execute the procedure. 8. 9. Click Finish to create the procedure. When prompted to create the procedure in the schema, click Yes to continue or No to quit the operation.
7.
8. Click the Privileges tab to display the privileges assigned to the SPJ: To change the privileges, see “Using DB Admin to Grant Privileges on SPJs” (page 54) or “Using DB Admin to Revoke Privileges on SPJs” (page 57). Using NCI to Display SPJs in a Schema In the NCI command-line interface, use the SHOW PROCEDURES command to display the SPJs in a schema. For example, this SHOW PROCEDURES command displays a list of the procedures in the SALES schema: SQL>set schema sales; --- SQL operation complete.
2. 3. 4. 5. Open the Schemas folder, and navigate to the schema that contains the SPJ that you want to drop from the Neoview database. Open the schema folder, and then open the Procedures folder. Click the procedure that you want to drop and then right-click the procedure and then select Drop: When prompted to drop the procedure, click Yes to continue or No to quit the operation: The procedure is dropped from the Neoview database and no longer appears in the Procedures folder.
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.
or anyone within the company. Therefore, the SPJ owner (or creator) grants the EXECUTE privilege on TOTALPRICE to PUBLIC, meaning all present and future user roles: 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. To grant privileges by using DB Admin, see “Using DB Admin to Grant Privileges on SPJs” (page 54).
The ROLE.PAYROLLA role cannot revoke the EXECUTE or WITH GRANT OPTION privilege from the ROLE.PAYROLLB role because it was the SPJ owner (or creator) who granted those privileges. The SPJ owner (or creator) can revoke the WITH GRANT OPTION privilege on ADJUSTSALARY from any user with this privilege. In this example, the SPJ owner (or creator) revokes the WITH GRANT OPTION privilege from the ROLE.PAYROLLA role: REVOKE GRANT OPTION FOR EXECUTE ON PROCEDURE persnl.adjustsalary FROM "ROLE.
obey c:\grantprocs.sql (GrantSalesProcs) Script File for Revoking Privileges You can use another or the same script file to revoke privileges on a series of SPJs. For example, the script file, revokeprocs.sql, contains a series of REVOKE EXECUTE and REVOKE statements: ?SECTION RevokeSalesProcs REVOKE EXECUTE 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.
6. To launch the Grant/Revoke Privileges Tool, click the Grant/Revoke button in the right pane (or right-click the procedure name under the Procedures folder and select Grant/Revoke Privileges): 7. 8. 9. Select the Grant option. Select one role under Grantees to which you want to grant privileges on the procedure. Select the Execute check box for the Procedure Privileges, and select the With Grant Option check box if desired.
10. Click the Grant button: 11. When prompted to grant these privileges, click Yes to continue or No to quit the operation. 12. Before performing the next Grant operation, click Reset to clear the settings in the Grant/Revoke Privileges Tool dialog box. 13. Repeat steps 7 to 12 for each role to which you want to grant Execute or With Grant Option privileges. 14. In the left pane, select another procedure in the same schema, or navigate to a schema where another procedure is registered.
16. When prompted, click Yes to close the tool or No to keep the tool open. The right pane displays the new privileges on the stored procedure: Using DB Admin to Revoke Privileges on SPJs 1. 2. 3. 4. 5. In DB Admin, click the Database tab. Open the Schemas folder, and navigate to the schema that contains the SPJs. Open the schema folder, and then open the Procedures folder. Click the procedure name under the Procedures folder, or click the procedure name link in the right pane.
6. 7. 8. 9. 10. 11. 12. 58 Click Grant/Revoke to launch the Grant/Revoke Privileges Tool. Select the Revoke option. Select the Grant option for check box if you want to revoke the With Grant Option privilege. Select one role under Grantees from which you want to revoke privileges on the procedure. Select the Execute check box for the Procedure Privileges. Select the Cascade check box if you want to revoke any dependent privileges.
13. When prompted to revoke these privileges, click Yes to continue or No to quit the operation. 14. Before performing the next Revoke operation, click Reset to clear the settings in the Grant/Revoke Privileges Tool dialog box. 15. Repeat steps 7 to 14 for each role from which you want to revoke Execute or With Grant Option privileges. 16. In the left pane, select another procedure in the same schema, or navigate to a schema where another procedure is registered.
18. When prompted, click Yes to close the tool or No to keep the tool open. The right pane displays the existing privileges on the stored procedure: Showing Privileges on SPJs 1. 2. 3. 4. 60 In DB Admin, click the Database tab. Open the Schemas folder, and navigate to the schema that contains the SPJs. Open the schema folder, and then open the Procedures folder.
5. 6. To display the privileges for a procedure, click the procedure name under the Procedures folder, or click the procedure name link in the right pane. Click the Privileges tab to display the privileges assigned to the SPJ: To change the privileges, see “Using DB Admin to Grant Privileges on SPJs” (page 54) or “Using DB Admin to Revoke Privileges on SPJs” (page 57).
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 43) and Chapter 6 (page 51).
Multithreading Neoview SQL manages a single thread of execution within an SPJ environment, even if the application that issues a CALL statement is a multithreaded Java application. The CALL statements in a multithreaded application can execute in a nonblocking manner, but the SPJ methods underlying those CALL statements execute serially within a given SPJ environment.
procedure input values, the conversion is from the actual argument value to the formal parameter type. For stored procedure output values, the conversion is from the actual output value, which has the data type of the formal parameter, to the declared type of the dynamic parameter. Input Parameter Arguments To pass data to an IN or INOUT parameter of an SPJ, specify an SQL value expression that evaluates to a character, date-time, or numeric value.
Result Sets Result sets are an ordered set of open cursors that the SPJ method returns to the calling application in java.sql.ResultSet[] parameter arrays. The java.sql.ResultSet[] parameters do not correspond with SQL parameters, so you must not include them in the parameter argument list of a CALL statement. The calling application can retrieve multiple rows of data from the java.sql.ResultSet[] parameters.
SQL>execute stmt1 using 2.25; The output of the prepared CALL statement is: *** WARNING[11217] Java execution: Data overflow occurred while retrieving data at parameter position 3. Value is truncated. PRICE -------------------136.77 --- SQL operation complete. In an NCI session, invoke the SPJ named TOTALPRICE again by preparing and executing a CALL statement in which all three parameters accept values that are set by the USING clause of the EXECUTE statement.
100250 . . . 6301 245.00 15 GRAPHIC CARD, HR --- 70 row(s) selected. --- SQL operation complete. For other result set examples, see Appendix A (page 77). Calling SPJs From an ODBC Client Application You can execute a CALL statement in an ODBC client application. Microsoft ODBC requires that you put the CALL statement in an escape clause: {call procedure-name ([parameter][,[parameter]]...)} For IN or INOUT parameters, use a literal or a parameter marker (?).
SQLINTEGER indicator; rc = SQLBindParameter(s, 2, SQL_PARAM_OUTPUT, SQL_C_SBIGINT, SQL_BIGINT, 0, 0, &num_orders, 0, &indicator); /* Execute the CALL */ rc = SQLExecute(s); /* Process all returned result sets. The outer while loop repeats */ /* until there are no more result sets.
// Execute the CALL boolean rsAvailable = s.execute(); // Process all returned result sets. The outer while loop continues // until there are no more result sets. while (rsAvailable) { // The inner while loop processes each row of the current result set java.sql.ResultSet rs = s.getResultSet(); while (rs.next()) { // Process the row } rsAvailable = s.getMoreResults(); } This example shows how a JDBC client application can have more than one stored procedure result set open at a given time. The java.sql.
and “Output Parameter Arguments” (page 65). For more information about result sets, see “Returning Stored Procedure Result Sets” (page 27). This example creates a trigger that executes an SPJ named LOWERPRICE when the QTY_ON_HAND column of the PARTLOC table is updated and exceeds 500 parts: CREATE TRIGGER sales.setsalesprice AFTER UPDATE OF qty_on_hand ON invent.partloc FOR EACH STATEMENT REFERENCING NEW as newqty WHEN ( SUM(newqty.qty_on_hand) > 500 ) CALL sales.
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 73) 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 err
Using the EXPLAIN Statement Suppose that you want to display the execution plan for this CALL statement: CALL persnl.adjustsalary(202,5.5,?); Enter this EXPLAIN statement in an NCI session: SQL>prepare spj1 from call persnl.adjustsalary(202,5.5,?); --- SQL command prepared. SQL>explain spj1; ------------------------------------------------------------------ PLAN SUMMARY MODULE_NAME .............. DYNAMICALLY COMPILED STATEMENT_NAME ........... SPJ1 PLAN_ID .................. 212060628692228717 ROWS_OUT ..
Using the EXPLAIN Function You can also prepare the CALL statement and select specific columns from the result table of the EXPLAIN function, as shown: SQL>prepare spj1 from call persnl.adjustsalary(202,5.5,?); --- SQL command prepared.
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 109).
public static void orderSummary... { ... } } The following procedure sections show the code of 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. Java Method: lowerPrice() Example A-2 lowerPrice() Method public static void lowerPrice() throws SQLException { Connection conn = DriverManager.
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.partnum = o.partnum GROUP BY p.partnum, 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 DB Admin, and then enter or select these values in the Create Procedure Wizard of DB Admin. For instructions, see “Uploading SPJ JAR Files to the Neoview Platform” (page 35) and “Creating SPJs” (page 43).
Procedures in the PERSNL Schema The Payroll class contains these SPJ methods, which are useful for managing personnel data: • “ADJUSTSALARY Procedure” (page 93) • “EMPLOYEEJOB Procedure” (page 95) • “PROJECTTEAM Procedure” (page 97) • “TOPSALESREPS Procedure” (page 98) Those methods are registered as stored procedures in the PERSNL schema. Example A-8 shows part of the code of the Payroll.java source file. The rest of the code appears in the following procedure sections. Example A-8 Payroll.
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 101) • “SUPPLYNUMBERS Procedure” (page 104) • “PARTLOCS Procedure” (page 106) Those methods are registered as stored procedures in the INVENT schema. Example A-13 shows part of the code of the Inventory.java source file. The rest of the code appears in the following procedure sections. Example A-13 Inventory.java—The Inventory Class import java.
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 109) — “JOB Table” (page 109) — “EMPLOYEE Table” (page 109) — “DEPT Table” (page 111) — “PROJECT Table” (page 113) • “SALES Schema” (page 116) — “CUSTOMER Table” (page 116) — “ORDERS Table” (page 117) — “ODETAIL Table” (page 118) — “PARTS Table” (page 120) • “INVENT Schema” (page 121) — “SUPPLIER Table” (page 121) — “PARTSUPP Table” (page 122) — “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 ADJUSTSALARY procedure creating, 93 invoking, 94 adjustSalary() method code for, 31, 93 description of, 93 example of, 21 Altering SPJs, 50 Application classes, accessible to an SPJ, 33 Arguments, CALL statement input types arithmetic expression, 65 concatenation operation, 65 dynamic parameter, 65 literal, 65 scalar subquery, 65 SQL function, 65 list, 64 output types dynamic parameter, 65 named parameter, 65 unnamed parameter, 65 Array, output parameter, 27 B Bignum, 26 C CALL operator, 75 CALL s
INOUT parameters, 65 INVENT schema description of, 121 PARTLOC table, 124 PARTLOCS procedure, 106 PARTSUPP table, 122 SUPPLIER table, 121 SUPPLIERINFO procedure, 101 SUPPLYNUMBERS procedure, 104 Inventory class, 100 Invoking SPJs description of, 63 examples of, 77 JDBC client application, 69 NCI, 66 ODBC client application, 68 privileges, 51 restrictions, 63 transaction behavior, 63 ISO88591, 46 J JAR files deleting on the Neoview platform, 41 downloading to a client workstation, 38 renaming on the Neoview
ADJUSTSALARY procedure, 93 DEPT table, 111 description of, 109 EMPLOYEE table, 109 EMPLOYEEJOB procedure, 95 JOB table, 109 PROJECT table, 113 PROJECTTEAM procedure, 97 TOPSALESREPS procedure, 98 Privileges for creating SPJs, 43 for invoking SPJs, 51 granting, 51 on referenced database objects, 52 revoking, 52 showing in DB Admin, 60 Procedure Code Files Tool, 35 PROJECT table, 113 PROJECTTEAM procedure creating, 97 invoking, 97 projectTeam() method code for, 97 description of, 97 R registerOutParameter()
Troubleshooting guidelines, 73 Types data types, 69 U UCS2, 46 Unnamed parameter, 66 User-defined exceptions, 32 V VERSION command, example of, 23 W WITH GRANT OPTION privilege, 51 130 Index
