http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/spj_guide/src/asciidoc/_chapters/develop_spjs.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/develop_spjs.adoc b/docs/spj_guide/src/asciidoc/_chapters/develop_spjs.adoc new file mode 100644 index 0000000..6e9cba9 --- /dev/null +++ b/docs/spj_guide/src/asciidoc/_chapters/develop_spjs.adoc @@ -0,0 +1,899 @@ +//// +/** + *@@@ START COPYRIGHT @@@ + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * @@@ END COPYRIGHT @@@ + */ +//// + +[[develop-spj-methods]] += Develop SPJ Methods + +Before creating, or registering, an SPJ in the 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_. + +This chapter requires a familiarity with writing and compiling Java +programs and covers these topics: + +* <<guidelines-for-writing-spj-methods, Guidelines for Writing SPJ Methods>> +* <<accessing-a-trafodion-database, Accessing a Trafodion Database>> +* <<handling-java-exceptions,Handling Java Exceptions>> +* <<compiling-and-packaging-java-classes,Compiling and Packaging Java Classes>> + +[[guidelines-for-writing-spj-methods]] +== Guidelines for Writing SPJ Methods + +Follow the guidelines for these topics when you write SPJ methods to be +used as SPJs in the database: + +* <<signature of the java method, Signature of the Java Method>> +* <<returning-output-values-from-the-java-method, Returning Output Values From the Java Method>> +* <<returning-stored-procedure-result-sets, Returning Stored Procedure Result Sets>> +* <<using-the-main-method, Using the main() Method>> +* <<null-input-and-output, Null Input and Output>> +* <<static-java-variables, Static Java Variables>> +* <<nested-java-method-invocations, Nested Java Method Invocations>> + +[[signature-of-the-java-method]] +=== Signature of the Java Method + +A Java method that you use as an SPJ must have this general signature: + +[source, java] +---- +public static void myMethodName ( java-parameter-list ) +---- + +[[public-access-and-static-modifiers]] +==== Public Access and Static Modifiers + +The Java method must be defined as public and static. If a method is +private or protected, the database engine is unable to find the Java +method when you try to register the SPJ and returns an error. The Java +method must be defined as static so that the method can be invoked +without having to instantiate its class. + +[[void-return-type]] +==== Void Return Type + +The return type of the Java method must be `void`. The method must not +return a value directly to the caller. + +[[java-parameters]] +==== Java Parameters + +Except for result sets, which are described in +<<returning-stored-procedure-result-sets, Returning Stored Procedure Result Sets>>, +the parameter types in the Java signature must correspond to the SQL +parameters of the stored procedure that you are planning to create. For +type mappings, see the table below. + +[[table-1]] +.Mapping of Java Data Types to SQL Data Types + +[cols="40%,60%",options="header",] +|=== +| Java Data Type | Maps to SQL Data Type. . . +| `java.lang.String` | +CHAR[ACTER] + +CHAR[ACTER] VARYING + +VARCHAR + +PIC[TURE] X^1^ + +NCHAR + +NCHAR VARYING + +NATIONAL CHAR[ACTER] + +NATIONAL CHAR[ACTER] VARYING +| `java.sql.Date` | DATE +| `java.sql.Time` | TIME +| `java.sql.Timestamp` | TIMESTAMP +| `java.math.BigDecimal` | +NUMERIC (including NUMERIC with a precision greater than eighteen)^2^ + +DEC[IMAL]^2^ + +PIC[TURE] S9^3^ +| `short` | SMALLINT^2^ +| `int or java.lang.Integer`^4^ | INT[EGER]^2^ +| `long or java.lang.Long`^4^ | LARGEINT2 +| `double or java.lang.Double`^4^ | FLOAT +| `float or java.lang.Float`^4^ | REAL +| `double or java.lang.Double`^4^ | DOUBLE PRECISION +| `java.sql.ResultSet[]` | None +|=== + +1. The Trafodion database stores `PIC X` as a `CHAR` data type. + +2. Numeric data types of SQL parameters must be `SIGNED`, which is the +default in the Trafodion database. + +3. The Trafodion database stores `PIC S9` as a `DECIMAL` or `NUMERIC` data type. + +4. Choose a Java wrapper class if you plan to pass null values as +arguments to or from the method. See +<<null-input-and-output, Null Input and Output>> + +Output parameters in the Java signature must be arrays (for example, +`int[]` or `String[]`) that accept only one value in the first element of +the array at index 0. For more information, see +<<returning-output-values-from-the-java-method, Returning Output Values From the Java Method>>. + +[[returning-output-values-from-the-java-method]] +=== Returning Output Values From the Java Method + +The Java method can return data to the calling application in the form +of output parameters or result sets. + +_Output parameters_ in the Java signature are parameter arrays that +accept only one value in the first element of the array at index 0. The +array objects have a fixed size of one element. + +IMPORTANT: You cannot return more than one value to an output parameter of an SPJ. +Any attempt to return more than one value to an output parameter results in a Java exception, +`ArrayIndexOutOfBounds`. + +_Result sets_ in the Java signature are one-element `java.sql.ResultSet[]` +arrays that contain ResultSet objects that have multiple rows of data. +For more information about result sets, see +<<returning-stored-procedure-result-sets, Returning Stored Procedure Result Sets>> + +This subsection covers these topics related to output parameters: + +* <<using-arrays-for-output-parameters, Using Arrays for Output Parameters>> +* <<type-mapping-of-output-parameters, Type Mapping of Output Parameters>> + +[[using-arrays-for-output-parameters]] +==== 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. Therefore, arrays must be +used for output parameters in a Java method. + +IMPORTANT: 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, `ArrayIndexOutOfBounds`. + +For each output parameter, specify the Java type followed by empty +square brackets (`[]`) to indicate that the type is an array. For example, +specify an int type as `int[]` for an output parameter in the Java +signature. + +To return multiple values from a Java method, use an output parameter +for each returned value. For example, the `supplierInfo()` method returns +a supplier's name, address, city, state, and post code, each as a single +string in an output parameter: + +The `supplyQuantities()` method returns an average quantity, a minimum +quantity, and a maximum quantity to separate output parameters of the +integer type: + +[source, java] +---- +public static void supplyQuantities( int[] avgQty + , int[] minQty + , int[] maxQty + ) +{ + +... + +throws SQLException +---- + +For more information about the SPJ examples, see +<<sample-spjs, Appendix A: Sample SPJs>>. + +[[type-mapping-of-output-parameters]] +==== Type Mapping of Output Parameters + +When writing an SPJ method, consider how the output of the SPJ is +used in the calling application. For output parameters, the Java data +type of the SPJ method must map to an SQL data type. See +<<table-1, Table 1>>. + +The SQL data type must then map to a compatible data type in the calling +application. For the client application programming interfaces (APIs) that +support SPJs and for cross-references to the appropriate manuals for type +mappings between Trafodion SQL and each API, see +<<execute-spjs, Execute SPJs>> below. + +[[returning-stored-procedure-result-sets]] +=== Returning Stored Procedure Result Sets + +The Trafodion database engine supports SPJs that return stored procedure +result sets. A stored procedure result set is a cursor that is left open +after the SPJ method executes (that is, after the CALL statement +executes successfully). After the CALL statement executes successfully, +the calling application can issue requests to open and then retrieve +multiple rows of data from the returned result sets. + +An SPJ method returns an ordered collection of result sets to the +calling application by executing SELECT statements and placing each +returned ResultSet object into a one-element Java array of type +`java.sql.ResultSet[]`. The `java.sql.ResultSet[]` array is part of the Java +method's signature and is recognized by the database engine as a +container for a single stored procedure result set. + +Place the `java.sql.ResultSet[]` parameters after the other Java +parameters, if any, in the Java signature. If you do not place the +`java.sql.ResultSet[]` parameters after the other +parameters in the signature, the database engine prevents you from +creating an SPJ using that Java method. This example shows the +declaration of an SPJ method, `orderSummary()`, which returns a maximum of +two result sets: + +[source, java] +---- +public static void orderSummary( java.lang.String onOrAfter + , long[] numOrders + , java.sql.ResultSet[] orders + , java.sql.ResultSet[] detail + ) +---- + +This code fragment shows how the `orderSummary()` method returns one of +its result sets by executing a SELECT statement and assigning the +`java.sql.ResultSet` object to a `java.sql.ResultSet[]` output array: + +[source,java] +---- +// 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 demo.sales.orders o, demo.sales.odetail d " + + " WHERE o.ordernum = d.ordernum " + + " AND o.order_date >= CAST(? AS DATE) " + + " GROUP BY o.ordernum ) amounts, " + + " demo.sales.orders orders, demo.persnl.employee emps " + + "WHERE amounts.ordernum = orders.ordernum " + + " AND orders.salesrep = emps.empnum " + + "ORDER BY orders.ordernum " + ; + +java.sql.PreparedStatement ps2 = conn.prepareStatement(s) ; +ps2.setString( 1, onOrAfter ) ; + +// Assign the returned result set object to the first element of a +// java.sql.ResultSet[] output array +orders[0] = ps2.executeQuery() ; +---- + +For the entire example, see +<<ordersummary-procedure, ORDERSUMMARY Procedure>>. + +IMPORTANT: In an SPJ method that returns result sets, do not explicitly close +the default connection or the statement object. The database engine closes the +connection used to return result sets after it finishes processing the result +sets. If you close the connection on which the result sets are being returned, +those result sets will be lost, and the calling application will not be able +to process them. + +An SPJ method can return result sets that contain any data types, except +large object (LOB) data. An SPJ method can return a holdable or +updatable cursor as a result set. However, Trafodion SQL does not expose +those attributes in the calling application. An SPJ method can return a +ResultSet object that is a stored procedure result set acquired from a +nested CALL statement executed by the SPJ method. However, you are +discouraged from nesting CALL statements in SPJ methods. For more +information, see <<nested-java-method-invocations, Nested Java Method Invocations>>. + +If an SPJ method returns multiple ResultSet objects, the database engine +sorts the collection of valid result sets in chronological order +according to when the underlying SQL statements were executed. If the +number of result sets exceeds the declared maximum for the SPJ, only the +first set of result sets up to the maximum number are returned. The +database engine discards the other result sets and returns a warning to +the calling application. + +When an SPJ method returns a ResultSet object through a +`java.sql.ResultSet[]` parameter, Trafodion SQL exposes the underlying rows +of data as an SQL cursor in the calling application. + +If a returned result set is a scrollable cursor, all underlying rows are +included in the result set and are available to the calling application. +If a returned result set is not scrollable, only those rows not +processed by the SPJ method are included in the result set and are +available to the calling application. If an SPJ method returns multiple +occurrences of the same ResultSet object, the database engine ignores +all but one occurrence and makes the underlying rows available to the +calling application as a single result set. + +For information about processing result sets in different calling +applications, see: + +* <<returning-result-sets-in-trafci, Returning Result Sets in trafci>> +* <<returning-result-sets-in-an-odbc-client-application, Returning Result Sets in an ODBC Client Application>> +* <<returning-result-sets-in-a-jdbc-client-application, Returning Result Sets in a JDBC Client Application>> + +[[using-the-main-method]] +=== 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: + +[source,java] +---- +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 <<create-spjs, Create SPJs>>. + +[[null-input-and-output]] +=== Null Input and Output + +You can pass a `null` value as input to or output from an SPJ method, +provided that the Java data type of the parameter supports nulls. Java +primitive data types do not support nulls. However, Java wrapper classes +that correspond to primitive data types do support nulls. If a null is +input or output for a parameter that does not support nulls, the +database engine raises an error condition. + +To anticipate null input or output for your SPJ, use Java wrapper +classes instead of primitive data types in the method signature. + +For example, this Java method uses a Java primitive data type in its +signature where no null values are expected: + +[source, java] +---- +public static void employeeJob( int empNum, Integer[] jobCode ) +---- + +This Java method also uses a Java wrapper class in its signature to +anticipate a possible returned null value: + +[source, java] +---- +public static void employeeJob( int empNum, Integer[] jobCode ) +---- + +[[static-java-variables]] +=== Static Java Variables + +To ensure that your SPJ method is portable, you should avoid using +static variables in the method. The database engine does not ensure the +scope and persistence of static Java variables. + +[[nested-java-method-invocations]] +=== Nested Java Method Invocations + +An SPJ that invokes another SPJ by issuing a CALL statement causes +additional system resources to be used. If you want an SPJ method to +call another SPJ method, consider invoking the other Java method +directly through Java instead of using a CALL statement. The other Java +method should be packaged in the same JAR file as the SPJ method. For +more information, see +<<compiling-and-packaging-java-classes, Compiling and Packaging Java Classes>>. + +[[accessing-a-trafodion-database]] +== Accessing a Trafodion Database + +SPJ methods that access a Trafodion database must be from a Java class +that uses JDBC method calls. Follow these guidelines when writing an SPJ +method that accesses a Trafodion database: + +* <<use-of-java.sql.connection-objects, Use of java.sql.Connection Objects>> +* <<using-jdbc-method-calls, Using JDBC Method Calls>> +* <<referring-to-database-objects-in-an-spj-method, Referring to Database Objects in an SPJ Method>> +* <<using-the-session_user-or-current_user-function-in-an-spj-method, Using the SESSION_USER or CURRENT_USER Function in an SPJ Method>> +* <<exception-handling, Exception Handling>> + +[[use-of-java.sql.connection-objects]] +=== Use of java.sql.Connection Objects + +The Trafodion database engine supports a default connection in an SPJ +execution environment, which has a data source URL of +`"jdbc:default:connection"`. For example: + +[source, java] +---- +Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; +---- + +`java.sql.Connection` objects that use the `"jdbc:default:connection"` URL +are portable to the Trafodion platform from other database management +systems (DBMSs). + +[[closing-default-connections]] +==== Closing Default Connections + +The Trafodion database engine 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. + +IMPORTANT: If an SPJ method returns result sets, you should not explicitly +close the default connection. The database engine closes the connection used +to return result sets after it finishes processing the result sets. If an SPJ +method closes the connection on which the result sets are being returned, those +result sets will be lost, and the calling application will not be able to +process them. The JVM does not return an error or warning when the +connection is closed. + +A default connection that is acquired when an SPJ method executes does +not necessarily remain open for future invocations of the SPJ method. +Therefore, do not store default connections in static variables for +future use. + +[[default-connection-url]] +==== Default Connection URL + +The default connection URL, `"jdbc:default:connection"`, is invalid when +the Java method is invoked outside the DBMS, such as when you execute +the Java method in a client application. To write an SPJ method that +operates in a DBMS, in a client application, or both, without having to +change and recompile the code, use the `sqlj.defaultconnection` system +property: + +[source, java] +---- +String s = System.property( "sqlj.defaultconnection" ) ; +if ( s == null ) +{ + s = other-url ; +} + +Connection c = DriverManager.getConnection( s ) ; +---- + +The value of `sqlj.defaultconnection` is `"jdbc:default:connection"` in a +DBMS and `null` outside a DBMS. + +[[connection-pooling]] +==== Connection Pooling + +Connection pooling, where a cache of database connections is assigned to +a client session and reused, is enabled by default in the SPJ +environment. The SPJ environment sets the initial connection pool size +to `1`, but it does not limit the number of connections an SPJ method can +make. + +The SPJ environment also sets the minimum connection pool size to 1 so that +there is always at least one connection available in the pool. The +default settings in the SPJ environment are: + +* `maxPoolSize=0` +* `minPoolSize=1` +* `initialPoolSize=1` + +To change these settings, use the properties parameter of the +`DriverManager.getConnection()` method as shown below: + +[source, java] +---- +java.util.Properties props = new Properties() ; + +props.setProperty( "maxPoolSize", "10" ) ; +props.setProperty( "minPoolSize", "5" ) ; +props.setProperty( "initialPoolSize", "5" ) ; + +Connection conn = + DriverManager.getConnection( "jdbc:default:connection", props ) ; +---- + +[[using-jdbc-method-calls]] +=== Using JDBC Method Calls + +The Trafodion platform uses a JDBC Type-4 driver internally to execute +the SQL statements inside an SPJ method. To enable an SPJ to perform SQL +operations on a Trafodion database, use JDBC method calls in the SPJ +method. The JDBC method calls must be supported by the JDBC Type-4 +driver on the Trafodion platform. + +For example, if you want the SPJ method to operate on a Trafodion database, +use the JDBC API that is supported by Trafodion. + +NOTE: You do not have to explicitly load the JDBC driver before +establishing a connection to the Trafodion database. The database engine +automatically loads the JDBC driver when the SPJ is called. + +Here is an example of an SPJ method, `adjustSalary()`, that uses JDBC +method calls to adjust an employee's salary in the EMPLOYEE table: + +[source, java] +---- +public class Payroll +{ + public static void adjustSalary( BigDecimal empNum + , double percent + , BigDecimal[] newSalary + ) throws SQLException + { + Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; + + PreparedStatement setSalary = + conn.prepareStatement( "UPDATE demo.persnl.employee " + + "SET salary = salary * (1 + (? / 100)) " + + "WHERE empnum = ?" + ) ; + + PreparedStatement getSalary = + conn.prepareStatement( "SELECT salary " + + "FROM demo.persnl.employee " + + "WHERE empnum = ?" + ) ; + + setSalary.setDouble( 1, percent ) ; + setSalary.setBigDecimal( 2, empNum ) ; + setSalary.executeUpdate() ; + + getSalary.setBigDecimal( 1, empNum ) ; + ResultSet rs = getSalary.executeQuery() ; + rs.next() ; + + newSalary[0] = rs.getBigDecimal( 1 ) ; + + rs.close(); + conn.close(); + } +} +---- + +For other examples of SPJ methods, see <<sample-spjs, Appendix A: Sample SPJs>>. + +[[referring-to-database-objects-in-an-spj-method]] +=== Referring to Database Objects in an SPJ Method + +In an SPJ method, you can refer to SQL database objects by specifying +three-part ANSI names that include the catalog, schema, and object name. +For more information about database object names, see the +http://trafodion.incubator.apache.org/docs/sql_reference/index.hmtl[Trafodion SQL Reference Manual]. + +The database engine propagates the names of the catalog and schema where +the SPJ is registered to the SPJ environment. By default, database +connections created in the SPJ method are associated with that catalog +and schema, meaning that unqualified database objects with one-part or +two-part names in the SPJ method are qualified with the same catalog +and/or schema name as the SPJ. For example, this SPJ method, which is +registered as an SPJ in the DEMO.SALES schema, refers to the unqualified +database object, ORDERS: + +[source, java] +---- +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 orders " + + "WHERE order_date = ?" + ) ; + + getNumOrders.setDate( 1, date ) ; + + ResultSet rs = getNumOrders.executeQuery() ; + 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 catalog and schema as the SPJ, DEMO.SALES. + +The default behavior takes effect only when `getConnection()` does not +contain catalog and schema properties. Catalog and schema property +values in `getConnection()` have higher precedence over the default +behavior. To override the default schema name and associate a database +connection with a different schema, specify the schema property during +connection creation. For example, `getConnection()` in this SPJ method +specifies the schema, SALES2, which overrides the default schema, SALES: + +[source, java] +---- +public static void numDailyOrders( Date date + , int[] numOrders + ) throws SQLException +{ + Properties prop = new Properties() ; + prop.setProperty( "schema", "SALES2" ) ; + + Connection conn = + DriverManager.getConnection( "jdbc:default:connection", prop) ; + + PreparedStatement getNumOrders = + conn.prepareStatement( "SELECT COUNT( order_date ) " + + "FROM orders " + + "WHERE order_date = ?" + ) ; + + getNumOrders.setDate( 1, date ) ; + + ResultSet rs = getNumOrders.executeQuery() ; + 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 catalog or schema name and might make SPJ +methods less portable across systems. + +[[using-the-session_user-or-current_user-function-in-an-spj-method]] +=== Using the SESSION_USER or CURRENT_USER Function in an SPJ Method + +SESSION_USER is an SQL function that returns the name of the +authenticated database user who started the session and invoked the +function, and CURRENT_USER (or USER) is an SQL function that returns the +name of the database user who is authorized to invoke the function. If +you plan to use the SESSION_USER or CURRENT_USER (or USER) function in +an SPJ method, you should be aware of differences in their behavior +depending on how extenal security is defined for the stored procedure. + +Suppose that you write this Java method, which uses the CURRENT_USER +function to return the name of the database user who is authorized to +invoke the function: + +[source, java] +---- +public static void getUser( ResultSet [] rs ) throws SQLException +{ + Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; + + Statement stmt = conn.createStatement() ; + + rs[0] = + stmt.executeQuery( "SELECT CURRENT_USER FROM (VALUES(1)) X(A) ; " ) ; +} +---- + +If this method is used in a stored procedure with external security +defined as _invoker_, the CURRENT_USER function returns the name of +the database user who is authorized to invoke the function, which +happens to be the authenticated database user who started the session +and called the stored procedure. + +For example, suppose that DB USERADMINUSER creates a stored procedure +named GETINVOKER using the `getUser()` method and sets the external +security to invoker. If a database user named PAULLOW1, who has the +EXECUTE privilege on the stored procedure, calls GETINVOKER, the procedure +returns his name: + +``` +Welcome to Apache Trafodion Command Interface +Copyright (c) 2015 Apache Software Foundation + +User Name:PAULLOW1 Password: + +Connected to Data Source: TDM_Default_DataSource + +SQL> CALL demo.persnl.getinvoker() ; + +(EXPR) +-------------------------------------------------------------------------------- +PAULLOW1 + +--- 1 row(s) selected. + +--- SQL operation complete. +``` + +If the method is used in a stored procedure with external security +defined as _definer_, the CURRENT_USER function returns the name of +the database user who is authorized to invoke the function, which +happens to be the user who created the stored procedure (that is, the +definer of the stored procedure). When a stored procedure's external +security is set to definer, any user who has the execute privilege on +the stored procedure can call the procedure using the privileges of the +user who created the stored procedure. + +For example, suppose that DB USERADMINUSER creates a stored procedure +named GETDEFINER using the `getUser(`) method and sets the external +security to definer. If the database user named PAULLOW1, who has the +EXECUTE privilege on the stored procedure, calls GETDEFINER, the procedure +returns the name of the stored procedures's creator, DB USERADMINUSER, +whose privileges PAULLOW1 is using to call the procedure: + +``` +SQL> SHOW USER + +USER PAULLOW1 (NONE) + +SQL> CALL demo.persnl.getdefiner() ; + +(EXPR) +-------------------------------------------------------------------------------- +DB USERADMINUSER + +--- 1 row(s) selected. + +--- SQL operation complete. +``` + +Suppose that you write this Java method, which uses the SESSION_USER +function to return the name of the authenticated database user who +started the session and invoked the function: + +[source, java] +---- +public static void getSessionUser( ResultSet [] rs ) throws SQLException +{ + Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; + + Statement stmt = conn.createStatement() ; + + rs[0] = stmt.executeQuery( "SELECT SESSION_USER FROM (VALUES(1) ) X(A) ; " ) ; +} +---- + +The SESSION_USER function returns the name of the authenticated database +user who started the session and invoked the function, regardless of the +external security setting of the stored procedure. + +For example, suppose that DB USERADMINUSER creates a stored procedure named +GETSESSIONUSER using the `getSessionUser()` method and sets the external +security to definer. If the database user named PAULLOW1, who has the EXECUTE +privilege on the stored procedure, calls GETSESSIONUSER, the procedure +returns his name because he is the authenticated user who started the +session and invoked the function: + +``` +SQL> SHOW USER + +USER PAULLOW1 (NONE) + +SQL> CALL demo.persnl.getsessionuser() ; + +(EXPR) +-------------------------------------------------------------------------------- +PAULLOW1 + +--- 1 row(s) selected. + +--- SQL operation complete. +``` + +For more information about external security, see +<<understand-external-security, Understand External Security>>. + +[[exception-handling]] +=== Exception Handling + +For SPJ methods that access a Trafodion database, no special code is +necessary for handling exceptions. If an SQL operation fails inside an +SPJ, the error message associated with the failure is returned to the +application that issues the CALL statement. + +[[handling-java-exceptions]] +== Handling Java Exceptions + +If an SPJ method returns an uncaught Java exception or an uncaught chain +of `java.sql.SQLException` objects, the database engine converts each Java +exception object into an SQL error condition, and the CALL statement +fails. Each SQL error condition contains the message text associated +with one Java exception object. + +If an SPJ method catches and handles exceptions itself, those exceptions +do not affect SQL processing. + +[[user-defined-exceptions]] +=== User-Defined Exceptions + +The SQLSTATE values 38001 to 38999 are reserved for you to define your +own error conditions that SPJ methods can return. By coding your SPJ +method to throw a `java.sql.SQLException` object, you cause the CALL +statement to fail with a specific user-defined SQLSTATE value and your +own error message text. + +If you define the SQLSTATE to be outside the range of 38001 to 38999, +the database engine 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: + +[source, java] +---- +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. " + + "Retry the CALL statement using a number " + + "from 1 to 12 to represent the month." + , "38001" + ) ; + } + + .... +} +---- + +For more information about the numMonthlyOrders() method, see the +<<monthlyorders-procedure, MONTHLYORDERS Procedure>>. + +For information about specific SQL errors, see the +http://trafodion.incubator.apache.org/docs/messages_guide/index.html[Trafodion Messages Manual], which lists +the SQLCODE, SQLSTATE, message text, and cause-effect-recovery information for all SQL errors. + +[[compiling-and-packaging-java-classes]] +== Compiling and Packaging Java Classes + +On the Trafodion database, the class files of SPJ methods must be +packaged in Java archive (JAR) files. After writing an SPJ method, +compile the Java source file of the SPJ method into Java bytecode and +package the Java bytecode in a JAR file. A Java method that you register +as an SPJ 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. Compile the Java source file into Java bytecode by using the Java +programming language compiler, `javac`: ++ +``` +javac Payroll.java +``` + +2. Put the SPJ class file and all associated class files into a Java +archive (JAR) file: ++ +``` +jar cvf Payroll.jar Payroll.class +``` ++ +A manifest file is not needed for the JAR file. + +
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/spj_guide/src/asciidoc/_chapters/execute_spjs.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/execute_spjs.adoc b/docs/spj_guide/src/asciidoc/_chapters/execute_spjs.adoc new file mode 100644 index 0000000..a2557c9 --- /dev/null +++ b/docs/spj_guide/src/asciidoc/_chapters/execute_spjs.adoc @@ -0,0 +1,730 @@ +//// +/** + *@@@ START COPYRIGHT @@@ + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * @@@ END COPYRIGHT @@@ + */ +//// + +[[execute-spjs]] += Execute SPJs + +This chapter describes how to execute SPJs by using the CALL statement +and assumes that you have already registered the SPJs in the Trafodion +database and that you have granted privileges to execute the SPJs to the +appropriate database users. For information, see +<<deploy-spj-jar-files, Deploy SPJ JAR Files>> +and <<create-spjs, Create SPJs>>. + +This chapter covers these topics: + +* <<required-privileges-for-calling-an-spj, Required Privileges for Calling an SPJ>> +* <<transaction-behavior, Transaction Behavior>> +* <<multithreading, Multithreading>> +* <<using-the-call-statement, Using the CALL Statement>> +* <<calling-spjs-in-trafci, Calling SPJs in trafci>> +* <<calling-spjs-from-an-odbc-client-application, Calling SPJs From an ODBC Client Application>> +* <<calling-spjs-from-a-jdbc-client-application, Calling SPJs From a JDBC Client Application>> +* <<calling-an-spj-in-a-trigger, Calling an SPJ in a Trigger>> + +The CALL statement invokes an SPJ in the database. You can issue a CALL +statement from any of these applications or interfaces supported by the +Trafodion platform: + +* trafci command-line interface or script file +* JDBC Type-4 client applications +* ODBC client applications + +You can use a CALL statement as a stand-alone SQL statement in +applications or in command-line interfaces, such as trafci. You can also +use a CALL statement in a trigger but not inside a compound statement or +with rowsets. The SPJ that you use in a trigger must not have any OUT or +INOUT parameters or return any result sets. + +== Required Privileges for Calling an SPJ + +To execute the CALL statement, you must have the EXECUTE privilege on +the procedure. For more information, see +<<grant-privileges, Grant Privileges>>. + +== Transaction Behavior + +The stored procedure's transaction attribute determines whether it +inherits the transaction from the calling application (TRANSACTION +REQUIRED) or whether it runs without inheriting the calling +application's transaction (NO TRANSACTION REQUIRED). The transaction +attribute is set during the creation of the stored procedure. For more +information, see the Transaction Required attribute in +<<create-a-procedure, Create a Procedure>>. + +Typically, you want the stored procedure to inherit the transaction +from the calling application. See <<transaction-required, Transaction Required>>. +However, if the SPJ method does not access the database or if you want the +stored procedure to manage its own transactions, you should set the stored +procedure's transaction attribute to NO TRANSACTION REQUIRED. +See <<no-transaction-required>>. + +=== Transaction Required + +If you want the SPJ method to inherit the transaction from the calling +application, set the stored procedure's transaction attribute to +TRANSACTION REQUIRED (the default setting) when creating the stored +procedure. For more information, see the Transaction Required attribute +in link:#_bookmark116["Create a] +link:#_bookmark116[Procedure" (page 37)]. When a stored procedure's +transaction attribute is TRANSACTION REQUIRED, a CALL statement +automatically initiates a transaction if there is no active transaction. + +==== Using Transaction Control Statements or Methods + +If you select Yes for the Transaction Required attribute when creating a +stored procedure, you should not use transaction control statements (or +equivalent JDBC transaction methods) in the SPJ method. Transaction +control statements include COMMIT WORK and ROLLBACK WORK, and the +equivalent JDBC transaction methods are Connection.commit() and +Connection.rollback(). If you try to use transaction control statements +or methods in an SPJ method when the stored procedure's transaction +attribute is set to TRANSACTION REQUIRED, the transaction control +statements or methods in the SPJ method are ignored, and the Java +virtual machine (JVM) does not report any errors or warnings. When the +stored procedure's transaction attribute is set to TRANSACTION REQUIRED, +you should rely on the transaction control statements or methods in the +application that calls the stored procedure and allow the calling +application to manage the transactions. + +===== Committing or Rolling Back a Transaction + +If you do not use transaction control statements in the calling +application, the transaction initiated by the CALL statement might not +automatically commit or roll back changes to the database. When +AUTOCOMMIT is ON (the default setting), the database engine +automatically commits or rolls back any changes made to the database at +the end of the CALL statement execution. However, when AUTOCOMMIT is +OFF, the current transaction remains active until the end of the client +session or until you explicitly commit or roll back the transaction. + +To ensure an atomic unit of work when calling an SPJ, use the COMMIT +WORK statement in the calling application to commit the transaction when +the CALL statement succeeds, and use the ROLLBACK WORK statement to roll +back the transaction when the CALL statement fails. For more information +about transaction management, see the __Trafodion SQL Reference Manual__. + +=== No Transaction Required + +In some cases, you might not want the SPJ method to inherit the +transaction from the calling application. Instead, you might want the +stored procedure to manage its own transactions or to run without a +transaction. Not inheriting the calling application's transaction is +useful in these cases: + +* The stored procedure performs several long-running operations, such as +multiple DDL or table maintenance operations, on the database. In this +case, you might want to commit those operations periodically from within +the SPJ method to avoid locking tables for a long time. +* The stored procedure performs certain SQL operations that must run +without an active transaction. For example, INSERT, UPDATE, and DELETE +statements with the WITH NO ROLLBACK option are rejected when a +transaction is already active, as is the case when a stored procedure +inherits a transaction from the calling application. The PURGEDATA +utility is also rejected when a transaction is already active. +* The stored procedure does not access the database. (For an example, +see the link:#_bookmark250["TOTALPRICE] link:#_bookmark250[Procedure" +(page 73)].) In this case, the stored procedure does not need to inherit +the transaction from the calling application. By setting the stored +procedure's transaction attribute to NO TRANSACTION REQUIRED, you can +avoid the overhead of the calling application's transaction being +propagated to the stored procedure. + +In these cases, you should set the stored procedure's transaction +attribute to NO TRANSACTION REQUIRED when creating the stored procedure. +For more information, see the Transaction Required attribute in +link:#_bookmark116["Create a Procedure" (page 37)]. + +If you select No for the Transaction Required attribute when creating a +stored procedure and if the SPJ method creates a JDBC default +connection, that connection will have autocommit enabled by default. You +can either use the autocommit transactions or disable autocommit +(conn.setAutoCommit(false);) and use the JDBC transaction methods, +Connection.commit() and Connection.rollback(), to commit or roll back +work where needed. + +== Multithreading + +The Trafodion database engine 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. + +== Using the CALL Statement + +To invoke a stored procedure, specify the name of the stored procedure +and its arguments in a CALL statement, as shown in +link:#_bookmark167[Figure 2]: + +image:media/image8.png[image]Figure 2 CALL Statement Elements + +For the syntax of the CALL statement, see the __Trafodion SQL Reference +Manual__. + +=== Specifying the Name of the SPJ + +In the CALL statement, specify the name of an SPJ that you have already +created in the database. Qualify the procedure name with the same +catalog and schema that you specified when you registered the SPJ. For +example: + +``` +CALL demo.persnl.adjustsalary(202, 5.5, ?); +``` + +Or, for example: + +``` +SET SCHEMA demo.persnl; + +CALL adjustsalary(202, 5.5, ?); +``` + +If you do not fully qualify the procedure name, the database engine +qualifies the procedure according to the catalog and schema of the +current session. + +=== Listing the Parameter Arguments of the SPJ + +Each argument that you list in the CALL statement must correspond to an +SQL parameter of the SPJ. A result set in the Java signature of the SPJ +method does not correspond to an SQL parameter. Do not specify result +sets in the argument list. + +For example, if you registered the stored procedure with three SQL +parameters (two IN parameters and one OUT parameter), you must list +three formal parameter arguments, separated by commas, in the CALL +statement: + +``` +CALL demo.persnl.adjustsalary(202, 5, ?); +``` + +If the SPJ does not accept arguments, you must specify empty +parentheses, as shown below: + +``` +CALL demo.sales.lowerprice(); +``` + +If the SPJ has one IN parameter, one OUT parameter, and two result sets, +you must list the IN and OUT parameters but not the result sets in the +argument list: + +``` +CALL demo.sales.ordersummary('01-01-2011', ?); +``` + +==== Data Conversion of Parameter Arguments + +The database engine performs an implicit data conversion when the data +type of a parameter argument is compatible with but does not match the +formal data type of the stored procedure. For stored 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. +The SQL value expression can evaluate to NULL provided that the +underlying Java parameter supports null values. For more information, +see link:#_bookmark58["Null Input and Output" (page 22)]. + +For an IN parameter argument, use one of these SQL value expressions in +link:#_bookmark175[Table 2]: + +Table 2 Input Parameter Argument Types + +Type of Argument Examples + +Literal + +CALL adjustsalary(**202**, 5.5, ?); + +CALL dailyorders(**DATE '2011-03-19**', ?); CALL totalprice(23, +*'nextday'* , ?param); + +SQL function (including CASE and CAST expressions) + +CALL dailyorders(**CURRENT_DATE**, ?); + +Arithmetic expression CALL adjustsalary(202, **?percent * 0.25**, :OUT +newsalary); + +Concatenation operation + +CALL totalprice(23, **'next' || 'day'**, ?param); + +Scalar subquery + +Dynamic parameter + +CALL totalprice(**(SELECT qty_ordered** + +*FROM odetail* + +**WHERE ordernum = 100210 AND partnum = 5100)**, + +'nextday', ?param); + +CALL adjustsalary(**?**, ?, ?); + +CALL adjustsalary(**?param1**, ?param2, ?param3); + +For more information about SQL value expressions, see the __Trafodion SQL +Reference Manual__. + +Because an INOUT parameter passes a single value to and accepts a single +value from an SPJ, you can specify only dynamic parameters for INOUT +parameter arguments in a CALL statement. + +==== Output Parameter Arguments + +Except for result sets, an SPJ returns values in OUT and INOUT +parameters. Each OUT or INOUT parameter accepts only one value from an +SPJ. Any attempt to return more than one value to an output parameter +results in a Java exception. See link:#_bookmark48["Returning Output +Values From the Java Method"] link:#_bookmark48[(page 19)]. + +OUT and INOUT parameter arguments must be dynamic parameters in a client +application (for example, ?) or named or unnamed parameters in trafci +(for example, ?param or ?). + +For information about how to call SPJs in different applications, see: + +* link:#_bookmark188["Calling SPJs in trafci" (page 53)] +* link:#_bookmark196["Calling SPJs From an ODBC Client Application" +(page 54)] +* link:#_bookmark200["Calling SPJs From a JDBC Client Application" (page +56)] + +==== 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 to 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. For information about how to process result sets in +different applications, see: + +* link:#_bookmark194["Returning Result Sets in trafci" (page 54)] +* link:#_bookmark198["Returning Result Sets in an ODBC Client +Application" (page 55)] +* link:#_bookmark204["Returning Result Sets in a JDBC Client +Application" (page 56)] + +== Calling SPJs in trafci + +In trafci, you can invoke an SPJ by issuing a CALL statement directly or +by preparing and executing a CALL statement. + +Use named or unnamed parameters anywhere in the argument list of an SPJ +invoked in trafci. A named parameter is set by the SET PARAM command, and +an unnamed parameter is set by the USING clause of the EXECUTE +statement. + +You must use a parameter for an OUT or INOUT parameter argument. trafci +displays all output parameter values and result sets after you issue the +CALL statement. The procedure call changes the value of a named +parameter that you use as an OUT or INOUT parameter. + +For more information about named and unnamed parameters, see the _HP +Database Command_ __Interface (trafci) Guide__. + +=== Using Named Parameters + +In an trafci session, invoke the SPJ named TOTALPRICE, which has two IN +parameters and one INOUT parameter. This SPJ accepts the quantity, +shipping speed, and price of an item, calculates the total price, +including tax and shipping charges, and returns the total price. For +more information, see the link:#_bookmark250["TOTALPRICE Procedure" +(page 73)]. + +Set the input value for the INOUT parameter by entering a SET PARAM +command before calling the SPJ: + +``` +SQL>**set param ?p 10;** + +SQL>**call demo.sales.totalprice(23, 'standard', ?p);** +``` + +The CALL statement returns the total price of the item: + +``` +p +-------------------- +253.97 + +--- SQL operation complete. +``` + +The value of the named parameter, ?p, changes from 10 to the returned +value, 253.97: + +``` +SQL>**show param** + +p 253.97 +``` + +=== Using Unnamed Parameters + +In an trafci session, invoke the SPJ named TOTALPRICE by preparing and +executing a CALL statement. The INOUT parameter accepts a value that is +set by the USING clause of the EXECUTE statement and returns the total +price: + +``` +SQL>**prepare stmt1 from call demo.sales.totalprice(50, 'nextday',?);** + +--- SQL command prepared. + +SQL>**execute stmt1 using 2.25;** +``` + +The output of the prepared CALL statement is: + +``` +PRICE +-------------------- +136.77 + +--- SQL operation complete. +``` + +In an trafci 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 demo.sales.totalprice(?,?,?);** + +--- SQL command prepared. + +SQL>**execute stmt2 using 3, 'economy', 16.99;** +``` + +The output of the prepared CALL statement is: + +``` +PRICE +-------------------- + +57.13 + +--- SQL operation complete. +``` + +=== Returning Result Sets in trafci + +If a CALL statement returns result sets, trafci displays column headings +and data for each returned result set in the same format as SELECT +statements. For example, this CALL statement returns an output parameter +for the number of orders and two result sets in the trafci session: + +``` +SQL>**call demo.sales.ordersummary('01-01-2011', ?);** + +NUM_ORDERS +-------------------- +13 + +ORDERNUM NUM_PARTS AMOUNT ORDER_DATE LAST_NAME +-------- -------------------- -------------------- ---------- -------------------- +HUGHES HUGHES SCHNABL + +... + +--- 13 row(s) selected. + +ORDERNUM PARTNUM UNIT_PRICE QTY_ORDERED PARTDESC + +-------- ------- ---------- ----------- ------------------ 100210 244 +3500.00 3 PC GOLD, 30 MB + +100210 2001 1100.00 3 GRAPHIC PRINTER,M1 + +100210 2403 620.00 6 DAISY PRINTER,T2 + +... ... ... ... ... + +--- 70 row(s) selected. + +--- SQL operation complete. +``` + +For other result set examples, see link:#_bookmark221[Appendix A (page +62)]. + +== 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. If you specify a literal for an INOUT parameter, the driver +discards the output value. + +For OUT parameters, you can use only a parameter marker (?). You must +bind all parameter markers with the SQLBindParameter function before you +can execute the CALL statement. + +In this example, a CALL statement is executed from an ODBC client +application: + +``` +/* Declare variables. */ +SQLHSTMT hstmt; SQL_NUMERIC_STRUCT salary; SDWORD cbParam = SQL_NTS; + +/* Bind the parameter markers. */ + +SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, 4, 0, 202, 0, &cbParam); + +SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, 0, 0, 5.5, 0, &cbParam); + +SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_NUMERIC, SQL_NUMERIC, 8, 2, &salary, 0, &cbParam); + +/* Execute the CALL statement. */ + +SQLExecDirect(hstmt, "{call demo.persnl.adjustsalary(?,?,?)}", SQL_NTS); +``` + +For more information about ODBC client applications, see the __Neoview +ODBC Drivers Manual__. + +=== Returning Result Sets in an ODBC Client Application + +This example shows how an ODBC client application processes the result +sets returned by a CALL statement. The SQLMoreResults() function closes +the current result set and moves processing to the next available result +set. + +NOTE: The HP ODBC API does not currently support interleaved result set +processing, where more than one returned result set can be open at a +time. + +``` +/* Allocate a statement handle */ +SQLHSTMT s; + +RETCODE rc = SQLAllocHandle(SQL_HANDLE_STMT, myConnection, &s); + +/* Prepare a CALL */ +char *stmtText = "{call demo.sales.ordersummary('01-01-2011', ?)}"; +rc = SQLPrepare(s, (SQLCHAR *) stmtText, strlen(stmtText)); + +/* Bind the output parameter */ +_int64 num_orders = 0; 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. */ +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 HP JDBC Type 4 driver requires +that you put the CALL statement in an escape clause: + +``` +{call _procedure-name_ ([__parameter__[{, __parameter__}...]])} +``` + +Set input values for IN and INOUT parameters by using the set__type__() +methods of the CallableStatement interface. + +Retrieve output values from OUT and INOUT parameters by using the +get__type__() methods of the CallableStatement interface. + +If the parameter mode is OUT or INOUT, you must register the parameter +as an output parameter by using the registerOutParameter() method of the +CallableStatement interface before executing the CALL statement. + +In this example, a CALL statement is executed from a JDBC client application: + +``` +CallableStatement stmt = + +con.prepareCall("{call demo.persnl.adjustsalary(?,?,?)}"); + +stmt.setBigDecimal(1,202); // x = 202 stmt.setDouble(2,5.5); // y = 5.5 +stmt.registerOutParameter(3, java.sql.Types.NUMERIC); + +stmt.execute(); + +BigDecimal z = stmt.getBigDecimal(3); // Retrieve the value of the +// OUT parameter +``` +For more information about the HP JDBC Type 4 driver and mappings of SQL +to JDBC data types, see the __Neoview JDBC Type 4 Driver Programmer's +Reference__. + +=== Returning Result Sets in a JDBC Client Application + +This example shows serial result set processing in a JDBC client +application where the result sets are processed in order and one at a +time after the CALL statement executes. The +java.sql.Statement.getMoreResults() method closes the current result set +and moves processing to the next available result set. + +``` +// Prepare a CALL statement + +java.sql.CallableStatement s = myConnection.prepareCall ( "{call +demo.sales.ordersummary('01-01-2011', ?)}" ); + +// Register an output parameter s.registerOutParameter(1, +java.sql.Types.BIGINT); + +// 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.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. + +``` +// Prepare a CALL statement + +java.sql.CallableStatement s = myConnection.prepareCall ( "{call +demo.sales.ordersummary('01-01-2011', ?)}" ); + +// Register an output parameter s.registerOutParameter(1, +java.sql.Types.BIGINT); + +// Execute the CALL s.execute(); + +// Open the FIRST result set java.sql.ResultSet firstRS = +s.getResultSet(); + +// Open the SECOND result set but do not close the FIRST +s.getMoreResults(java.sql.Statement.KEEP_CURRENT_RESULT); +java.sql.ResultSet secondRS = s.getResultSet(); + +// The outer loop processes each row of the FIRST result set while +(firstRS.next()) + +{ + +// Process a row from the FIRST result set +// The inner loop processes some number of rows from the SECOND +// result set. The number depends on data extracted from the +// current row of the FIRST result set. + +for (int i = 0; i < NUM_ROWS_TO_PROCESS; i++) + +{ + +// Process a row from the SECOND result set secondRS.next(); + +} + +} +``` + +== Calling an SPJ in a Trigger + +A trigger is a mechanism in the database that enables the database +engine to perform certain actions when a specified event occurs. SPJs +are useful as triggered actions because they can help you encapsulate +and enforce rules in the database. For more information about the +benefits of using SPJs, see link:#_bookmark11["Benefits of SPJs" (page +11)]. + +Trafodion SQL supports a CALL statement in a trigger provided that the +SPJ in the CALL statement does not have any OUT or INOUT parameters or +return any result sets. For more information, see +link:#_bookmark48["Returning Output Values From the Java Method" (page +19)], link:#_bookmark183["Output Parameter Arguments"] +link:#_bookmark183[(page 52)], or link:#_bookmark54["Returning Stored +Procedure Result Sets" (page 20)]. + +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 demo.sales.setsalesprice AFTER UPDATE OF qty_on_hand +ON demo.invent.partloc FOR EACH STATEMENT +REFERENCING NEW as newqty +WHEN ( SUM(newqty.qty_on_hand) > 500 ) CALL demo.sales.lowerprice(); +``` + +For information about the CREATE TRIGGER syntax, see the __Trafodion SQL +Reference Manual__. + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/spj_guide/src/asciidoc/_chapters/get_started.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/get_started.adoc b/docs/spj_guide/src/asciidoc/_chapters/get_started.adoc new file mode 100644 index 0000000..647ed54 --- /dev/null +++ b/docs/spj_guide/src/asciidoc/_chapters/get_started.adoc @@ -0,0 +1,121 @@ +//// +/** + *@@@ START COPYRIGHT @@@ + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * @@@ END COPYRIGHT @@@ + */ +//// + +[[get-started]] += Get Started + +Before you can start using SPJs on the Trafodion platform, verify that +you have the required software installed on the client workstation. The +Trafodion platform is delivered to you ready to use and preconfigured +with the software required to support SPJs. + +[[required-client-software]] +== Required Client Software + +[[java-development-kit]] +=== Java Development Kit + +To develop Java methods to be used as SPJs, you must have a Java +Development Kit (JDK) installed on the client workstation. To download a +JDK, go to +http://www.oracle.com/technetwork/java/index.html + +The version of the JDK that you download and use on the client +workstation should be the same as or lower than the Java version running +on the Trafodion database. To check the Java version that is running in +the Trafodion database, use one of these approaches: + +* Run `sqvers` in the `RUN_SCRIPT` stored procedure from `trafci`: ++ +``` +SQL> CALL demo.hp_sp.run_script( 'sqvers', '', '', ? ) ; + +OUTRESULT +------------------------------------------------------------ +Requesting user: DB ROOT +Current time: 2013-05-22 04:44:16.00 +Location of script: /opt/hp/sqtest4/M9V29713/export/bin64 +Results are stored in table: "sqvers@2013-05-22@04:44:16.00" +Command executed: sqvers + +RESULTS +---------------------------------- + +MY_SQROOT=/opt/hp/sqtest4/M9V29713 +who@host=sqtest4@n013 +JAVA_HOME=/usr/java/jdk1.7.0_09 + +--- 3 row(s) selected. + +--- SQL operation complete. + +SQL> +``` ++ +In this example, the returned `JAVA_HOME` indicates that the Trafodion +database supports Java SE 7 or JDK 7 (1.7.0), or earlier versions of the +JDK. + +* Launch `trafci` on the Trafodion platform, and run the `LOCALHOST` or `LH java -version` command. +(To use the on-platform trafci client, see the +http://trafodion.apache.org/docs/command_interface/index.html[Trafodion Command Interface Guide.) +For example: ++ +``` +SQL> lh java -version + +java version "1.6.0_06" +Java(TM) SE Runtime Environment (build 1.6.0_06-b02) +Java HotSpot(TM) Client VM (build 10.0-b22, mixed mode) + +SQL> +``` ++ +In this example, the returned Java version indicates that the Trafodion +instance supports Java SE 6 or JDK 6 (1.6.0), or earlier versions of the +JDK. + +NOTE: If you plan to install the Trafodion JDBC Type-4 Driver on the client +workstation, you must have JDK 6 (1.6.0) or higher installed on the +client workstation. + +[[recommended-client-software]] +== Recommended Client Software + +[[trafodion-command-interface-trafci]] +=== Trafodion Command Interface (trafci) + +`trafci` is a command-line interface in which you can run SQL statements, +such as GRANT PROCEDURE and CALL statements, interactively or from +script files. To install `trafci` on a client workstation, see the +http://trafodion.apache.org/docs/client_install/index.html[Trafodion Client Installation Guide]. + +[[hp-jdbc-type-4-driver]] +=== HP JDBC Type 4 Driver + +If you plan to use `trafci`, you must have a compatible version of the Trafodion +JDBC Type-4 Driver installed on the client workstation. + +To install the JDBC Type-4 driver on the client workstation, see the +http://trafodion.apache.org/docs/client_install/index.html[Trafodion Client Installation Guide]. + + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/spj_guide/src/asciidoc/_chapters/grant_privileges.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/grant_privileges.adoc b/docs/spj_guide/src/asciidoc/_chapters/grant_privileges.adoc new file mode 100644 index 0000000..9ff668c --- /dev/null +++ b/docs/spj_guide/src/asciidoc/_chapters/grant_privileges.adoc @@ -0,0 +1,312 @@ +//// +/** + *@@@ START COPYRIGHT @@@ + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * @@@ END COPYRIGHT @@@ + */ +//// + +[[grant-privileges]] +== Grant Privileges + +Security for SPJs is implemented by schema ownership rules and by +granting privileges to specified database users and roles. + +The schema in which an SPJ is registered is the unit of ownership. The +database user who creates the schema is the owner of that schema and all +objects associated with it. In a Trafodion database, the schema owner +automatically has these privileges: + +* Ability to create and drop SPJs in the schema. You can create SPJs in +the schema provided that you also have the CREATE_PROCEDURE privilege +for the SQL_OPERATIONS component. For more information, see the +component privileges in the _HP Database Manager (HPDM) User Guide_ or +the online help in HPDM +* EXECUTE and WITH GRANT OPTION privileges on the SPJs in the schema + +To create or drop an SPJ in a schema, you must be the schema owner or +have the appropriate create or drop privileges for the schema. For more +information, see link:#_bookmark114["Required Privileges for Creating] +link:#_bookmark114[or Dropping an SPJ" (page 37)]. To invoke an SPJ, you +must have the EXECUTE privilege on the SPJ. The EXECUTE privilege allows +a user to invoke an SPJ by issuing a CALL statement. The WITH GRANT +OPTION privilege allows a user to grant the EXECUTE and WITH GRANT +OPTION privileges to other users and roles. For more information, see: + +* link:#_bookmark141["Granting Execute Privileges on an SPJ" (page 45)] +* link:#_bookmark143["Granting Privileges on Referenced Database +Objects" (page 46)] +* link:#_bookmark145["Revoking Execute Privileges on an SPJ" (page 47)] +* link:#_bookmark147["Using Script Files to Grant and Revoke Privileges" +(page 47)] + +To display the current ownership and privileges, see +link:#bookmark130["Display Procedures and Their Properties"] +link:#bookmark130[(page 42)]. + +NOTE: You can also grant or revoke the EXECUTE privilege on an SPJ by +using the Grant/Revoke Privileges Tool in HPDM. For more information, +see the _HP Database Manager (HPDM) User Guide_ or the online help in +HPDM. + +== Granting Execute Privileges on an SPJ + +Use the GRANT PROCEDURE or GRANT statement to assign the EXECUTE and +WITH GRANT OPTION privileges on an SPJ to specific database users and +roles. In a GRANT statement, specify ALL PRIVILEGES to grant the EXECUTE +privilege on an SPJ. For the syntax of the GRANT PROCEDURE and GRANT +statements, see the __Trafodion SQL Reference Manual__. + +If you own the SPJ, you can grant the EXECUTE and WITH GRANT OPTION +privileges on the SPJ to any database user or role. If you are not the +owner of the SPJ, you must have been granted the EXECUTE and WITH GRANT +OPTION privileges on the SPJ to grant privileges to other database users +and roles, or you must be associated with a role that has the EXECUTE +and WITH GRANT OPTION privileges on the SPJ. + +As the owner of an SPJ, you can selectively grant the EXECUTE and WITH +GRANT OPTION privileges to specified database users and roles. For some +SPJs, particularly ones that handle sensitive information or modify +data, you should grant the EXECUTE and WITH GRANT OPTION privileges to a +restricted group of users or roles. For example, the SPJ named +ADJUSTSALARY changes an employee's salary in the database. Therefore, +only specific users or roles should be allowed to invoke this SPJ. In +this example, the SPJ owner (or creator) grants the EXECUTE and WITH +GRANT OPTION privileges on ADJUSTSALARY to the Payroll directors. + +``` +GRANT EXECUTE + +ON PROCEDURE demo.persnl.adjustsalary + +TO "payrolldir1", "payrolldir2" WITH GRANT OPTION; +``` + +One of the Payroll directors grants the EXECUTE privilege on +ADJUSTSALARY to the regional department managers: + +``` +GRANT EXECUTE +ON PROCEDURE demo.persnl.adjustsalary TO "rgn1mgr", "rgn2mgr", "rgn3mgr" +WITH GRANT OPTION; +``` + +In some cases, all users of a database system might need to invoke an +SPJ. For example, the SPJ named TOTALPRICE calculates the total price of +an item, including tax and shipping charges. This SPJ does not handle +sensitive information or modify data and might be useful to customers or +anyone within the company. Therefore, the SPJ owner (or creator) grants +the EXECUTE privilege on TOTALPRICE to PUBLIC, meaning all present and +future database users and roles: + +``` +GRANT EXECUTE +ON PROCEDURE demo.sales.totalprice TO PUBLIC; +``` + +After granting the EXECUTE privilege to PUBLIC, you cannot revoke the +privilege from a subset of database users or roles. You must revoke the +privilege from PUBLIC and then grant the privilege to specific database +users and roles. + +== Granting Privileges on Referenced Database Objects + +If the SPJ operates on a database object and the SPJ has been created +with the external security specified as invoker (EXTERNAL SECURITY +INVOKER), the database users that invoke the SPJ must have the +appropriate privileges on that database object. If the SPJ has been +created with the external security specified as definer (EXTERNAL +SECURITY DEFINER), users other than the procedure's creator may invoke +the SPJ without needing privileges on the underlying database objects. +When the SPJ's external security is definer, users execute, or invoke, +the stored procedure using the privileges of the user who created the +stored procedure. The user who creates the stored procedure must have +the appropriate privileges on the underlying database objects. For more +information, see link:#_bookmark124["Understand External Security" (page +41)]. + +For example, users with the EXECUTE privilege on the SPJ named +ADJUSTSALARY, which is defined with EXTERNAL SECURITY INVOKER and which +selects data from and updates the EMPLOYEE table, must have the SELECT +and UPDATE privileges on that SQL table. The SPJ owner (or creator) +grants these access privileges to the Payroll directors: + +``` +GRANT SELECT, UPDATE (salary) +ON TABLE demo.persnl.employee +TO "payrolldir1", "payrolldir2" WITH GRANT OPTION; +``` + +One of the Payroll directors then grants these access privileges to the +regional department managers: + +``` +GRANT SELECT, UPDATE (salary) +ON TABLE demo.persnl.employee +TO "rgn1mgr", "rgn2mgr", "rgn3mgr"; +``` + +Users with the EXECUTE privilege on the SPJ named TOTALPRICE, which does +not access the database, are not required to have privileges on any +database tables because that SPJ does not access any database tables. + +The types of SQL statements in the underlying SPJ method, such as +SELECT, UPDATE, DELETE, and INSERT, indicate which types of privileges +are required for the referenced database objects. + +For the syntax of the GRANT statement, see the __Trafodion SQL Reference +Manual__. + +== Revoking Execute Privileges on an SPJ + +Use the REVOKE PROCEDURE or REVOKE statement to remove the EXECUTE or +WITH GRANT OPTION privilege on an SPJ from specific database users or +roles. In a REVOKE statement, specify ALL PRIVILEGES to revoke the +EXECUTE privilege on an SPJ. For the syntax of the REVOKE PROCEDURE and +REVOKE statements, see the __Trafodion SQL Reference Manual__. + +If you own the SPJ, you can revoke the EXECUTE and WITH GRANT OPTION +privileges on the SPJ from any database user or role to whom you granted +those privileges or, if you did not directly grant those privileges, on +behalf of the role that granted those privileges, provided that you were +granted that role. If you are not the owner of the SPJ, you must have +been granted the EXECUTE and WITH GRANT OPTION privileges on the SPJ to +revoke privileges from other database users or roles, and you can revoke +the privileges only from other users or roles to whom you have granted +those privileges or, if you did not directly grant those privileges, on +behalf of the role that granted those privileges, provided that you were +granted that role. For example, the payrolldir1 user can revoke the +EXECUTE privilege on ADJUSTSALARY from one or more regional department +managers to whom the payrolldir1 user granted those privileges. In this +example, the payrolldir1 user revokes the EXECUTE privilege from the +Region 2 department manager: + +``` +REVOKE EXECUTE +ON PROCEDURE demo.persnl.adjustsalary FROM "rgn2mgr"; +``` + +The payrolldir1 user cannot revoke the EXECUTE or WITH GRANT OPTION +privilege from the payrolldir2 user because it was the SPJ owner (or +creator) who granted those privileges. + +A user can revoke the WITH GRANT OPTION privilege on ADJUSTSALARY from +any user or role to whom the user granted this privilege. In this +example, the SPJ owner (or creator) revokes the WITH GRANT OPTION +privilege from the payrolldir1 user: + +``` +REVOKE GRANT OPTION FOR EXECUTE +ON PROCEDURE demo.persnl.adjustsalary FROM "payrolldir1"; +``` + +A user can also revoke the EXECUTE privilege from any user or role to +whom the user granted this privilege and from any dependent privileges +by using the CASCADE option. In this example, the SPJ owner (or creator) +revokes the EXECUTE privilege from the payrolldir1 user and from the +regional department managers to whom the payrolldir1 user granted +privileges: + +``` +REVOKE GRANT OPTION FOR EXECUTE +ON PROCEDURE demo.persnl.adjustsalary FROM "payrolldir1" +CASCADE; +``` + +For SPJs on which all users (that is, PUBLIC) have privileges, you can +revoke privileges from PUBLIC but not from one or more specific users or +roles. For example, this statement revokes the EXECUTE privilege on the +SPJ named TOTALPRICE from all users and roles (that is, PUBLIC): + +``` +REVOKE EXECUTE +ON PROCEDURE demo.sales.totalprice FROM PUBLIC; +``` + +== Using Script Files to Grant and Revoke Privileges + +Consider keeping your GRANT or REVOKE statements in script files. That +way, you can quickly and easily grant or revoke privileges to the SPJs, +as needed. + +=== Script File for Granting Privileges + +You can use another or the same script file to grant privileges on a +series of SPJs. For example, the script file, grantprocs.sql, contains a +series of GRANT PROCEDURE and GRANT statements: + +``` +?SECTION GrantSalesProcs + +GRANT EXECUTE +ON demo.sales.monthlyorders +TO PUBLIC; + +GRANT SELECT +ON TABLE demo.sales.orders TO PUBLIC; + +?SECTION GrantPersnlProcs + +GRANT EXECUTE +ON PROCEDURE demo.persnl.adjustsalary TO "payrolldir1", "payrolldir2" +WITH GRANT OPTION; + +GRANT SELECT, UPDATE(salary) +ON TABLE demo.persnl.employee +TO "payrolldir1", "payrolldir2" WITH GRANT OPTION; +``` + +To grant privileges on the SPJs, run the script file in the trafci +interface: + +``` +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 PROCEDURE and REVOKE statements: + +``` +?SECTION RevokeSalesProcs + +REVOKE EXECUTE +ON PROCEDURE demo.sales.monthlyorders FROM PUBLIC; + +REVOKE SELECT +ON TABLE demo.sales.orders FROM PUBLIC; + +?SECTION RevokePersnlProcs + +REVOKE EXECUTE +ON PROCEDURE demo.persnl.adjustsalary FROM "payrolldir1", "payrolldir2" +CASCADE; + +REVOKE SELECT, UPDATE(salary) +ON TABLE demo.persnl.employee +FROM "payrolldir1", "payrolldir2" CASCADE; +``` + +To revoke privileges on the SPJs, run the script file in the trafci +interface: + +``` +OBEY c:\revokeprocs.sql (RevokeSalesProcs) +``` + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/spj_guide/src/asciidoc/_chapters/introduction.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/introduction.adoc b/docs/spj_guide/src/asciidoc/_chapters/introduction.adoc new file mode 100644 index 0000000..eb8d690 --- /dev/null +++ b/docs/spj_guide/src/asciidoc/_chapters/introduction.adoc @@ -0,0 +1,255 @@ +//// +/** + *@@@ START COPYRIGHT @@@ + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * @@@ END COPYRIGHT @@@ + */ +//// + +[[introduction]] += Introduction + +This chapter introduces stored procedures in Java (SPJs) in a Trafodion +database and covers these topics: + +[[what-is-an-spj]] +== 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. A client application executes a +stored procedure by issuing an SQL CALL statement. Unlike a user-defined +function, which returns a value directly to the calling application, a +stored procedure returns each output value to a dynamic parameter in its +parameter list or returns a set of values to a result set array. + +The Trafodion database supports stored procedures written in the Java +programming language. The Trafodion implementation of stored procedures +complies mostly, unless otherwise specified, with SQL/JRT (Java Routines +and Types), which extends the ANSI SQL/Foundation standard. A stored +procedure in Java (SPJ) is a Java method contained in a Java archive +(JAR) file on the Trafodion platform, registered in the database, and +executed by the database engine when a client application issues a CALL +statement. + +The body of a stored procedure consists of a public, static Java method +that returns void. These Java methods, called _SPJ methods_, are +contained in classes within JAR files on the cluster hosting the +Trafodion database. + +An SPJ method must be registered as a stored procedure in the database before a +client application can execute it with a CALL statement. You upload the SPJ to +the cluster where the Trafodion datbase is running +and then you register the SPJ as a library object using the +http://trafodion.apache.org/docs/sql_reference/index.html#create_library_statement[CREATE LIBRARY] +statement. Next, you register the library object using the +http://trafodion.apache.org/docs/sql_reference/index.html#create_procedure_statement[CREATE PROCEDURE] +statement. + +[[benefits-of-spjs]] +== Benefits of SPJs + +SPJs provide an efficient and secure way to implement business logic in +the database. SPJs offer these advantages: + +* <<java-methods-callable-from-sql, Java Methods Callable From SQL>> +* <<common-packaging-technique,Common Packaging Technique>> +* <<security,Security>> +* <<increased-productivity,Increased Productivity>> +* <<portability,Portability>> + +[[java-methods-callable-from-sql]] +=== Java Methods Callable From SQL + +With support for SPJs, Java methods are callable from any client +application that connects to the Trafodion platform. For example, you can +invoke the same SPJ method from JDBC client applications and ODBC client +applications. By using the database engine to invoke Java methods, you +can extend the functionality of the database and share business logic +among different applications. + +For more information, see <<execute-spjs, Execute SPJs>>. + +[[common-packaging-technique]] +=== Common Packaging Technique + +Different applications can invoke the same SPJ to perform a common +business function. By encapsulating business logic in an SPJ, you can +maintain consistent database operations and avoid duplicating code in +applications. + +Applications that call SPJs are not required to know the structure of +the database tables that the SPJ methods access. The application does +not need to use any table or column names; it needs only the name of the +stored procedure in the CALL statement. If the table structure changes, +you might need to change the SPJ methods but not necessarily the CALL +statements within each application. + +[[security]] +=== Security + +By using SPJs, you can conceal sensitive business logic inside SPJ +methods instead of exposing it in client applications. You can also +grant privileges to execute an SPJ to specific users and restrict the +privileges of other users. For more information, see +<<grant-privileges, Grant Privileges>> + +[[increased-productivity]] +=== Increased Productivity + +Use SPJs to reduce the time and cost of developing and maintaining +client applications. By having several applications call the same SPJ, +you need only change the SPJ method once when business rules or table +structures change instead of changing every application that calls the +SPJ. + +Using the Java language to implement stored procedures increases +productivity. Given the popularity of the Java language, you can +leverage the existing skill set of Java programmers to develop SPJs. + +The portability of the Java language enables you to write and compile +Java class files for SPJs once and deploy them anywhere. + +[[portability]] +=== 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 Trafodion platform and register the methods +as stored procedures in a Trafodion database. You can also port client +applications that call SPJs in other databases to Trafodion SQL with +minimal changes to the CALL statements in the application. + +<<< +[[use-spjs]] +== Use SPJs + +To use SPJs in a Trafodion database: + +1. Verify that you have the required software installed on the client +workstation. See <<get-started, Get Started>>. + +2. Develop a Java method to be used as an SPJ: +.. Write a static Java method: ++ +[source, java] +---- +public class Payroll +{ + public static void adjustSalary( BigDecimal empNum + , double percent, BigDecimal[] newSalary + ) throws SQLException + { + Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; + + PreparedStatement setSalary = + conn.prepareStatement( "UPDATE demo.persnl.employee " + + "SET salary = salary * (1 + (? / 100)) " + + "WHERE empnum = ?" + ) ; + + PreparedStatement getSalary = + conn.prepareStatement( "SELECT salary " + + "FROM demo.persnl.employee " + + "WHERE empnum = ?" + ) ; + + setSalary.setDouble( 1, percent ) ; + setSalary.setBigDecimal( 2, empNum ) ; + setSalary.executeUpdate() ; + + getSalary.setBigDecimal( 1, empNum ) ; + ResultSet rs = getSalary.executeQuery() ; + rs.next() ; + + newSalary[0] = rs.getBigDecimal( 1 ) ; + + rs.close() ; + conn.close() ; + } +} +---- + +.. Compile the Java source file to produce a class file: ++ +``` +javac Payroll.java +``` + +.. Package the SPJ class file in a JAR file: ++ +``` +jar cvf Payroll.jar Payroll.class +``` ++ +If the SPJ class refers to other classes, package the other classes in +the same JAR file as the SPJ class: ++ +``` +jar cvf Payroll.jar Payroll.class other.class +``` ++ +For details, see <<develop-spj-methods, Develop SPJ Methods>>. + +3. Deploy the SPJ JAR file on the Trafodion platform by creating a +library object for the JAR file in one of the database schemas. For +details, see <<Deploy-spj-jar-files, Deploy SPJ JAR Files>>. + +4. As the schema owner, create the SPJ in the database. For details, +see <<create-spjs, Create SPJs>>. + +5. Grant privileges to database users for executing the SPJ and for +operating on the referenced database objects. For example, you can issue +GRANT statements in an trafci session, as shown below: ++ +[source,sql] +---- +GRANT EXECUTE +ON PROCEDURE demo.persnl.adjustsalary +TO "payrolldir1", "payrolldir2" +WITH GRANT OPTION ; + +GRANT SELECT, UPDATE (salary) +ON TABLE demo.persnl.employee +TO "payrolldir1", "payrolldir2" +WITH GRANT OPTION ; +---- ++ +For details, see <<grant-privileges, Grant Privileges>>. + +6. Execute an SPJ by using a CALL statement in a client application. +For example, you can issue a CALL statement in an trafci session, as +shown below, or in a JDBC or ODBC client application: ++ +``` +SQL> CALL demo.persnl.adjustsalary( 29, 2.5, ? ) ; + +NEWSALARY +------------ + 139400.00 + +--- SQL operation complete. +``` ++ +For details, see <<execute-spjs, Execute SPJs>>. + +7. Monitor the performance of SPJs and resolve common problems with +SPJs in the database. See <<performance-and-troubleshooting, Performance and Troubleshooting>>. +
