HP Neoview Guide to Stored Procedures in Java HP Part Number: 544588-001 Published: August 2007 Edition: HP Neoview Release 2.
© Copyright 2007 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 A Sample SPJs....................................................................................................
Creating the Procedure: SUPPLYNUMBERS...........................................................................102 Calling the Procedure: SUPPLYNUMBERS.............................................................................103 PARTLOCS Procedure...................................................................................................................104 Java Method: partLocations()...................................................................................................
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...........................................................................................................75 lowerPrice() Method......................................................................................................................76 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.
UPPERCASE LETTERS Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: SELECT Italic Letters Italic letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required. For example: file-name Computer Type Computer type letters within text indicate case-sensitive keywords and reserved words. Type these items exactly as shown.
… Ellipsis An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. For example: ATTRIBUTE[S] attribute [, attribute]... {, sql-expression}... An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. For example: expression-n... Punctuation Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown.
Neoview Customer Library • Administration Neoview Database Administrator’s Guide Information about how to load and manage the Neoview database by using the Neoview DB Admin and other tools. Neoview DB Admin Online Help Context-sensitive help topics that describe how to use the HP Neoview DB Admin management interface. Neoview Guide to Stored Procedures in Java Information about how to use stored procedures that are written in Java within a Neoview database.
Publishing History Part Number Product Version Publication Date 544588-001 HP Neoview Release 2.2 August 2007 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: pubs.comments@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 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 a Neoview Script 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 platform is delivered to you ready to use and preconfigured with the software required to support SPJs.
Neoview JDBC Type 4 Driver If you plan to use Neoview Script, you must have the Neoview JDBC Type 4 Driver for Neoview Release 2.2 or later installed on the client workstation. To install the JDBC Type 4 driver on the client workstation, see the README for the HP Neoview JDBC Type 4 Driver.
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 DEC[IMAL] PIC[TURE] S92 short SMALLINT int or java.lang.Integer3 INT[EGER] long or java.lang.Long3 LARGEINT double or java.lang.Double3 FLOAT float or java.lang.
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. Therefore, arrays must be used for output parameters in a Java method. An output parameter accepts only one value in the first element of the array at index 0. Any attempt to return more than one value to an output parameter results in a Java exception.
// 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.ordernum " + " AND o.order_date >= CAST(? AS DATE) " + " GROUP BY o.ordernum ) amounts, " + " sales.orders orders, persnl.employee emps " + " WHERE amounts.ordernum = orders.ordernum " + " AND orders.
public static void main (java.lang.String [] args) { ... } When you register a main() method as an SPJ, you can specify zero or more SQL parameters, even though the underlying main() method has only one array parameter. All the SQL parameters of the SPJ must have the character string data type, CHAR or VARCHAR, and be declared with the IN mode. If you specify the optional Java signature, the signature must be (java.lang.String []). For more information about registering an SPJ, see Chapter 5 (page 43).
Those java.sql.Connection objects are portable to other database management systems (DBMSs). Neoview SQL controls default connections in the SPJ environment and closes default connections when they are no longer needed. Therefore, you do not need to use the close() method in an SPJ method to explicitly close a default connection when the connection is no longer needed. If an SPJ method returns result sets, do not explicitly close the default connection.
} } For other examples of SPJ methods, see Appendix A (page 75). The Neoview platform uses a JDBC Type 4 driver. In the SPJ method, use JDBC method calls that are supported by the JDBC Type 4 driver of the Neoview platform. For example, if you want the SPJ method to operate on a Neoview Release 2.2 database, use the JDBC API that is supported by Neoview Release 2.2.
rs.next(); numOrders[0] = rs.getInt(1); rs.close(); conn.close(); } Be aware that overriding the default values by using getConnection()requires you to hard-code the schema name and might make SPJ methods less portable across systems. 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.
might need to access, either directly or indirectly, other Java classes to operate properly. Those Java classes might include other application classes. To enable an SPJ method to refer to other application classes, put the application classes in the same JAR file as the SPJ class. All classes stored in the same JAR file as the SPJ class are accessible by default to the SPJ method. After writing the SPJ method: 1.
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 data types are mapped correctly to the Java data types of the SPJ method: NOTE: The result set parameters (java.sql.ResultSet[]) in the Java signature do not have corresponding SQL parameters. a. b. 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.
The parameter name is not case-sensitive, must not be a reserved word, must not contain spaces, and must begin with a letter, digit, or underscore. c. d. 7. Click Attributes. The Attributes page appears: • 46 Click OK to accept the changes, or click Cancel to quit the Specify Parameter dialog box. Repeat steps a to c for each parameter that you want to change. If your SPJ method returns result sets, enter the maximum number of result sets to be returned for Dynamic result sets.
• including 255. The actual number of result sets returned by the SPJ method can be less than or equal to this number. If your SPJ method performs any SQL operations, select the Accesses Database check box. 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.
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 Neoview Script to Display SPJs in a Schema In the Neoview Script command-line interface, use the SHOW PROCEDURES command to display the SPJs in a schema.
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 expression that evaluates to a character, date-time, or numeric value.
The calling application can retrieve multiple rows of data from the java.sql.ResultSet[] parameters.
136.77 --- SQL operation complete. In a Neoview Script 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. The INOUT parameter returns the total price: SQL>prepare stmt2 from call sales.totalprice(?,?,?); --- SQL command prepared. SQL>execute stmt2 using 3, 'economy', 16.
--- SQL operation complete. For other result set examples, see Appendix A (page 75). 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 (?). You cannot use an empty string as an IN or INOUT parameter in the argument list.
/* Process all returned result sets. The outer while loop repeats */ /* until there are no more result sets. */ while ((rc = SQLMoreResults(s)) != SQL_NO_DATA) { /* The inner while loop processes each row of the current result set */ while (SQL_SUCCEEDED(rc = SQLFetch(hStmt))) { /* Process the row */ } } Calling SPJs From a JDBC Client Application You can execute a CALL statement in a JDBC client application by using the JDBC CallableStatement interface.
{ // 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.Statement.getMoreResults(int) method uses its input argument to decide whether currently open result sets should remain open or be closed before the next result set is made available.
ON invent.partloc FOR EACH STATEMENT REFERENCING NEW as newqty WHEN ( SUM(newqty.qty_on_hand) > 500 ) CALL sales.lowerprice(); For information about the CREATE TRIGGER syntax, see the Neoview SQL Reference Manual.
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” 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 error messages and locate the information in the Neoview Messages
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 107).
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 Neoview Script: 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 Neoview Script: 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 Neoview Script: 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 Neoview Script: SQL>call sales.monthlyorders(3,?); The MONTHLYORDERS procedure determines the total number of orders during a specified month and returns this output in Neoview Script: 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 91) • “EMPLOYEEJOB Procedure” (page 93) • “PROJECTTEAM Procedure” (page 95) • “TOPSALESREPS Procedure” (page 96) 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 Neoview Script: 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 Neoview Script: 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 99) • “SUPPLYNUMBERS Procedure” (page 102) • “PARTLOCS Procedure” (page 104) 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 Neoview Script: SQL>call invent.supplierinfo(25,?,?,?,?,?); The SUPPLIERINFO procedure accepts the supplier number 25 and returns this output in Neoview Script: 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.
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 Neoview Script: SQL>call invent.supplynumbers(?,?,?); The SUPPLYNUMBERS procedure returns this output in Neoview Script: 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 Neoview Script: 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 107) — “JOB Table” (page 107) — “EMPLOYEE Table” (page 107) — “DEPT Table” (page 109) — “PROJECT Table” (page 111) • “SALES Schema” (page 114) — “CUSTOMER Table” (page 114) — “ORDERS Table” (page 115) — “ODETAIL Table” (page 116) — “PARTS Table” (page 118) • “INVENT Schema” (page 119) — “SUPPLIER Table” (page 119) — “PARTSUPP Table” (page 120) — “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.
); INSERT INTO invent.
Index A ADJUSTSALARY procedure creating, 91 invoking, 92 adjustSalary() method code for, 30, 91 description of, 91 example of, 21 Altering SPJs, 50 Application classes, accessible to an SPJ, 32 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, 26 C CALL statement argument list, 64 argum
Invoking SPJs description of, 63 examples of, 75 JDBC client application, 69 Neoview Script, 66 ODBC client application, 68 privileges, 51 restrictions, 63 transaction behavior, 63 required version, 23 Nesting SPJs, 29 Null values, input and output, 29 numDailyOrders() method code for, 78 description of, 78 numMonthlyOrders() method code for, 80 description of, 80 J O JAR files deleting on the Neoview platform, 41 downloading to a client workstation, 38 renaming on the Neoview platform, 39 uploading to
revoking, 52 showing in DB Admin, 60 Procedure Code Files Tool, 35 PROJECT table, 111 PROJECTTEAM procedure creating, 95 invoking, 95 projectTeam() method code for, 95 description of, 95 R registerOutParameter() method, 69 Required software, 23 Result sets coding in a Java method, 27 not specifying in a CALL statement, 65 returning in JDBC client applications, 69 returning in Neoview Script, 67 returning in ODBC client applications, 68 REVOKE EXECUTE statement, 52 REVOKE statement, 52 Rowsets, 63 S Sales
