http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/asciidoc/_chapters/sample_spjs.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/sample_spjs.adoc b/docs/spj_guide/src/asciidoc/_chapters/sample_spjs.adoc index 758b156..1383718 100644 --- a/docs/spj_guide/src/asciidoc/_chapters/sample_spjs.adoc +++ b/docs/spj_guide/src/asciidoc/_chapters/sample_spjs.adoc @@ -85,36 +85,50 @@ include::{sourcedir}/lowerPrice.java[lowerPrice procedure source code] [[creating-the-procedure-lowerprice]] ==== Creating the Procedure: LOWERPRICE -Before creating the procedure, use HPDM to create a library named `SALES` -in the `DEMO.SALES` schema and select the `Sales.jar` file to upload to the +Before creating the procedure, create a library named `SALES` +in the `TRAFODION.SALES` schema and select the `Sales.jar` file to upload to the Trafodion platform for that library. For more information, see <<create-a-library, Create a Library>>. -After creating the library, navigate to the Procedures folder in the SALES schema, -launch the Create Procedure dialog box, and then enter or select these values. -For more information, see <<create-a-procedure, Create a Procedure>>. +After creating the library, use the values listed in the table below to define and execute the +CREATE PROCEDURE command. For more information, see <<create-a-procedure, Create a Procedure>>. [[table-3]] -.HPDM Create Procedure Settings: LOWERPRICE Procedure +.Create Procedure Settings: LOWERPRICE Procedure -[cols="15%,30%,55%", options="header"] +[cols="20%,85%",options="header"] |=== -| Group Box | Field or Option | Value or Setting -| *Name* | Procedure Name | `lowerprice` -| *Code* | Library | `DEMO.SALES.SALES` -| | Class Name | `Sales` -| | Method Name | `lowerPrice` -| *Parameters* | | None -| *Attributes* | Number of dynamic results sets | `0` -| | Access Database | `selected` -| | External Security | `Invoker` + +| Attribute | Definition +| *procedure-ref* | `trafodion.sales.lowerprice` +| *sql-parameter* | Not applicable. +| *external name* | `EXTERNAL NAME sales.lowerPrice` +| *library* | `LIBRARY trafodion.sales.sales` +| *external security* | `invoker`^1^ + + -*NOTE*: Depending on your security requirements, you can select Definer -instead. For more information, see -<<understand-external-security, Understand External Security>>. -| | Transaction Required | `Yes` +Choice depends on your security requirements, you can select `definer` instead. +For more information, see <<understand-external-security, Understand External Security>>. +| *language java* | `LANGUAGE JAVA` +| *parameter style java* | `PARAMETER STYLE JAVA` +| *_sql access_* | `MODIFIES SQL DATA` +| *dynamic result sets* | `DYNAMIC RESULT SETS 0`^1^ +| *_transaction_* | `TRANSACTION REQUIRED`^1^ +| *_determinism_* | `NOT DETERMINISTIC`^1^ +| *_isolation_* | `ISOLATE`^1^ |=== +^1^ Definition represents default value. Not included in the CREATE PROCEDURE example. + +[source, sql] +---- +CREATE PROCEDURE trafodion.sales.lowerprice( ) + EXTERNAL NAME 'sales.lowerPrice' + LIBRARY trafodion.sales.sales + LANGUAGE JAVA + PARAMETER STYLE JAVA + MODIFIES SQL DATA + ; +---- + [[calling-the-procedure-lowerprice]] ==== Calling the Procedure: LOWERPRICE @@ -126,7 +140,7 @@ To invoke the LOWERPRICE procedure in trafci: [source, sql] ---- -SQL> CALL demo.sales.lowerprice() ; +SQL> CALL trafodion.sales.lowerprice() ; --- SQL operation complete. ---- @@ -142,8 +156,8 @@ FROM ( SELECT p.partnum , SUM(qty_ordered) AS qtyOrdered , p.price - FROM demo.sales.parts p - LEFT OUTER JOIN demo.sales.odetail o ON p.partnum = o.partnum + FROM trafodion.sales.parts p + LEFT OUTER JOIN trafodion.sales.odetail o ON p.partnum = o.partnum GROUP BY p.partnum, p.price ) AS allparts WHERE qtyOrdered < 51 ; @@ -206,43 +220,53 @@ include::{sourcedir}/numDailyOrders.java[numDailyOrders procedure source code] [[creating-the-procedure-dailyorders]] ==== Creating the Procedure: DAILYORDERS -Before creating the procedure, use HPDM to create a library named `SALES` -in the `DEMO.SALES` schema and select the `Sales.jar` file to upload to the +Before creating the procedure, create a library named `SALES` +in the `TRAFODION.SALES` schema and select the `Sales.jar` file to upload to the Trafodion platform for that library. For more information, see <<create-a-library, Create a Library>>. -After creating the library, navigate to the Procedures folder in the SALES schema, launch -the Create Procedure dialog box, and then enter or select these values. -For more information, see <<create-a-procedure, Create a Procedure>> +After creating the library, use the values listed in the table below to define and execute the +CREATE PROCEDURE command. For more information, see <<create-a-procedure, Create a Procedure>>. [[table-4]] -.HPDM Create Procedure Settings: DAILYORDERS Procedure +.Create Procedure Settings: DAILYORDERS Procedure -[cols="15%,30%,55%", options="header"] +[cols="20%,85%",options="header"] |=== -| Group Box | Field or Option | Value or Setting -| *Name* | Procedure Name | dailyorders -| *Code* | Library | DEMO.SALES.SALES -| | Class Name | Sales -| | Method Name | numDailyOrders -3+<| *Parameters* -| _First_ | Parameter Name | date1 -| | SQL Data Type | DATE -| | Direction | IN -| | Java Data Type | Displays the signature of the Java method that you selected: `java.sql.Date` -| _Second_ | Parameter Name | number -| | SQL Data Type | SIGNED INTEGER -| | Direction | OUT -| | Java Data Type | Displays the signature of the Java method that you selected: `int[]` -| *Attributes* | Number of dynamic result sets | 0 -| | Accesses Database | selected -| | External security | Invoker + +| Attribute | Definition +| *procedure-ref* | `trafodion.sales.dailyorders` +| *sql-parameter-1* | `IN date1 DATE` +| *sql-parameter-2* | `OUT number SIGNED INTEGER` +| *external name* | `EXTERNAL NAME sales.numDailyOrders` +| *library* | `LIBRARY trafodion.sales.sales` +| *external security* | `invoker`^1^ + + -*NOTE:* Depending on your security requirements, you can select Definer -instead. For more information, see <<understand-external-security, Understand External Security>>. -| | Transaction required | Yes +Choice depends on your security requirements, you can select `definer` instead. +For more information, see <<understand-external-security, Understand External Security>>. +| *language java* | `LANGUAGE JAVA` +| *parameter style java* | `PARAMETER STYLE JAVA` +| *_sql access_* | `READS SQL DATA` +| *dynamic result sets* | `DYNAMIC RESULT SETS 0`^1^ +| *_transaction_* | `TRANSACTION REQUIRED`^1^ +| *_determinism_* | `NOT DETERMINISTIC`^1^ +| *_isolation_* | `ISOLATE`^1^ |=== +^1^ Definition represents default value. Not included in the CREATE PROCEDURE example. + +[source, sql] +---- +CREATE PROCEDURE trafodion.sales.dailyorders( IN date1 DATE + , OUT number SIGNED INTEGER + ) + EXTERNAL NAME 'sales.numDailyOrders' + LIBRARY trafodion.sales.sales + LANGUAGE JAVA + PARAMETER STYLE JAVA + READS SQL DATA + ; +---- + [[calling-the-procedure-dailyorders]] ==== Calling the Procedure: DAILYORDERS @@ -254,7 +278,7 @@ To invoke the DAILYORDERS procedure in trafci: [source, sql] ---- -SQL> CALL demo.sales.dailyorders( DATE '2011-03-19', ? ) ; +SQL> CALL trafodion.sales.dailyorders( DATE '2011-03-19', ? ) ; ---- The DAILYORDERS procedure determines the total number of orders on a @@ -291,43 +315,53 @@ include::{sourcedir}/numMonthlyOrders.java[numMonthlyOrders procedure source cod [[creating-the-procedure-monthlyorders]] ==== Creating the Procedure: MONTHLYORDERS -Before creating the procedure, use HPDM to create a library named SALES -in the `DEMO.SALES` schema and select the `Sales.jar` file to upload to the -Trafodion platform for that library. -For more information, see <<create-a-library, Create a Library>>. +Before creating the procedure, create a library named `SALES` +in the `TRAFODION.SALES` schema and select the `Sales.jar` file to upload to the +Trafodion platform for that library. For more information, see +<<create-a-library, Create a Library>>. -After creating the library, navigate to the Procedures folder in the SALES schema, -launch the Create Procedure dialog box, and then enter or select these values. -For more information, see <<create-a-procedure, Create a Procedure>>. +After creating the library, use the values listed in the table below to define and execute the +CREATE PROCEDURE command. For more information, see <<create-a-procedure, Create a Procedure>>. [[table-5]] -.HPDM Create Procedure Settings: MONTHLYORDERS Procedure +.Create Procedure Settings: MONTHLYORDERS Procedure -[cols="15%,30%,55%", options="header"] +[cols="20%,85%",options="header"] |=== -| Group Box | Field or Option | Value or Setting -| *Name* | Procedure Name | `monthlyorders` -| *Code* | Library | `DEMO.SALES.SALES` -| | Class Name | `Sales` -| | Method Name | `numMonthlyOrders` -3+<| *Parameters* -| _First_ | Parameter Name | `monthnum` -| | SQL Data Type | `SIGNED INTEGER` -| | Direction | `IN` -| | Java Data Type | Displays the signature of the Java method that you selected: `int` -| _Second_ | Parameter Name | `ordernum` -| | SQL Data Type | `SIGNED INTEGER` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `int[]` -| *Attributes* | Number of dynamic result sets | `0` -| | Acceses Database | `selected` -| | External Security | `Invoker` + +| Attribute | Definition +| *procedure-ref* | `trafodion.sales.monthlyorders` +| *sql-parameter-1* | `IN monthnum SIGNED INTEGER` +| *sql-parameter-2* | `OUT ordernum SIGNED INTEGER` +| *external name* | `EXTERNAL NAME sales.numMonthlyOrders` +| *library* | `LIBRARY trafodion.sales.sales` +| *external security* | `invoker`^1^ + + -*NOTE:* Depending on your security requirements, you can select `Definer` instead. -For more information, see <<understand-external-security, Understand External Security>>. -| | Transaction Required | `Yes` +Choice depends on your security requirements, you can select `definer` instead. +For more information, see <<understand-external-security, Understand External Security>>. +| *language java* | `LANGUAGE JAVA` +| *parameter style java* | `PARAMETER STYLE JAVA` +| *_sql access_* | `READS SQL DATA` +| *dynamic result sets* | `DYNAMIC RESULT SETS 0`^1^ +| *_transaction_* | `TRANSACTION REQUIRED`^1^ +| *_determinism_* | `NOT DETERMINISTIC`^1^ +| *_isolation_* | `ISOLATE`^1^ |=== +^1^ Definition represents default value. Not included in the CREATE PROCEDURE example. + +[source, sql] +---- +CREATE PROCEDURE trafodion.sales.monthlyorders( IN monthnum SIGNED INTEGER + , OUT ordernum SIGNED INTEGER + ) + EXTERNAL NAME 'sales.numMonthlyOrders' + LIBRARY trafodion.sales.sales + LANGUAGE JAVA + PARAMETER STYLE JAVA + READS SQL DATA + ; +---- + [[calling-the-procedure-monthlyorders]] ==== Calling the Procedure: MONTHLYORDERS @@ -339,7 +373,7 @@ To invoke the MONTHLYORDERS procedure in trafci: [source, sql] ---- -SQL> CALL demo.sales.monthlyorders( 3,? ) ; +SQL> CALL trafodion.sales.monthlyorders( 3,? ) ; ---- The MONTHLYORDERS procedure determines the total number of orders during @@ -376,56 +410,55 @@ include::{sourcedir}/totalPrice.java[totalPrice procedure source code] [[creating-the-procedure-totalprice]] ==== Creating the Procedure: TOTALPRICE -Before creating the procedure, use HPDM to create a library named SALES -in the `DEMO.SALES` schema and select the `Sales.jar` file to upload to the -Trafodion platform for that library. -For more information, see <<create-a-library, Create a Library>>. +Before creating the procedure, create a library named `SALES` +in the `TRAFODION.SALES` schema and select the `Sales.jar` file to upload to the +Trafodion platform for that library. For more information, see +<<create-a-library, Create a Library>>. -After creating the library, navigate to the Procedures folder in the SALES schema, -launch the Create Procedure dialog box, and then enter or select these values. -For more information, see <<create-a-procedure, Create a Procedure>>. +After creating the library, use the values listed in the table below to define and execute the +CREATE PROCEDURE command. For more information, see <<create-a-procedure, Create a Procedure>>. [[table-6]] -.HPDM Create Procedure Settings: TOTALPRICE Procedure +.Create Procedure Settings: TOTALPRICE Procedure -[cols="15%,30%,55%", options="header"] +[cols="20%,85%",options="header"] |=== -| Group Box | Field or Option | Value or Setting -| *Name* | Procedure Name | `totalprice` -| *Code* | Library | `DEMO.SALES.SALES` -| | Class Name | `Sales` -| | Method Name | `totalPrice` -3+<| *Parameters* -| _First_ | Parameter Name | `qty` -| | SQL Data Type | `SIGNED NUMERIC` + - + -- Precision: `18` + -- Scale: `0` -| | Direction | `IN` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.math.BigDecimal` -| _Second_ | Parameter Name | `rate` -| | SQL Data Type | `VARCHAR` + +| Attribute | Definition +| *procedure-ref* | `trafodion.sales.totalprice` +| *sql-parameter-1* | `IN gty NUMERIC(18)` +| *sql-parameter-2* | `IN speed VARCHAR(10)` +| *sql-parameter-3* | `INOUT price NUMERIC(18,2)` +| *external name* | `EXTERNAL NAME sales.totalPrice` +| *library* | `LIBRARY trafodion.sales.sales` +| *external security* | `invoker`^1^ + + -- Length: `10` + -- Upshift: `not selected` + -- Character set: `ISO88591` -| | Direction | `IN` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.lang.String` -| _Third_ | Parameter Name | `price` -| | SQL Data Type | `SIGNED NUMERIC` + - + -- Precision: `18` + -- Scale: `2` -| | Direction | `INOUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.math.BigDecimal[]` -| *Attributes* | Number of dynamic result sets | `0` -| | Accesses Database | `not selected` -| | External Security | `Invoker` + - + -*NOTE:* External security is disabled because the stored procedure does not access the database. -| | Transaction Required | No +Choice depends on your security requirements, you can select `definer` instead. +For more information, see <<understand-external-security, Understand External Security>>. +| *language java* | `LANGUAGE JAVA` +| *parameter style java* | `PARAMETER STYLE JAVA` +| *_sql access_* | `NO SQL` +| *dynamic result sets* | `DYNAMIC RESULT SETS 0`^1^ +| *_transaction_* | `TRANSACTION REQUIRED`^1^ +| *_determinism_* | `NOT DETERMINISTIC`^1^ +| *_isolation_* | `ISOLATE`^1^ |=== +^1^ Definition represents default value. Not included in the CREATE PROCEDURE example. + +[source, sql] +---- +CREATE PROCEDURE trafodion.sales.totalprice( IN gty NUMERIC(18) + , IN speed VARCHAR(10) + , INOUT price NUMERIC(18,2) + ) + EXTERNAL NAME 'sales.totalPrice' + LIBRARY trafodion.sales.sales + LANGUAGE JAVA + PARAMETER STYLE JAVA + NO SQL + ; +---- + [[calling-the-procedure-totalprice]] ==== Calling the Procedure: TOTALPRICE @@ -438,7 +471,7 @@ To invoke the TOTALPRICE procedure in trafci: [source, sql] ---- SQL> SET PARAM ?p 10 ; -SQL> CALL demo.sales.totalprice( 23, 'standard', ?p ) ; +SQL> CALL trafodion.sales.totalprice( 23, 'standard', ?p ) ; ---- The TOTALPRICE procedure calculates the total price of a purchase and @@ -481,59 +514,58 @@ include::{sourcedir}/partData.java[partData procedure source code] [[creating-the-procedure-partdata]] ==== Creating the Procedure: PARTDATA -Before creating the procedure, use HPDM to create a library named SALES -in the `DEMO.SALES` schema and select the `Sales.jar` file to upload to the -Trafodion platform for that library. -For more information, see <<create-a-library, Create a Library>>. +Before creating the procedure, create a library named `SALES` +in the `TRAFODION.SALES` schema and select the `Sales.jar` file to upload to the +Trafodion platform for that library. For more information, see +<<create-a-library, Create a Library>>. -After creating the library, navigate to the Procedures folder in the SALES schema, -launch the Create Procedure dialog box, and then enter or select these values. -For more information, see <<create-a-procedure, Create a Procedure>>. +After creating the library, use the values listed in the table below to define and execute the +CREATE PROCEDURE command. For more information, see <<create-a-procedure, Create a Procedure>>. [[table-7]] -.HPDM Create Procedure Settings: PARTDATA Procedure +.Create Procedure Settings: PARTDATA Procedure -[cols="15%,30%,55%", options="header"] +[cols="20%,85%",options="header"] |=== -| Group Box | Field or Option | Value or Setting -| *Name* | Procedure Name | `partdata` -| *Code* | Library | `DEMO.SALES.SALES` -| | Class Name | `Sales` -| | Method Name | `partData` -3+<| *Parameters* -| _First_ | Parameter Name | `partnum` -| | SQL Data Type | `SIGNED INTEGER` -| | Direction | `IN` -| | Java Data Type | Displays the signature of the Java method that you selected: `int` -| _Second_ | Parameter Name | `partdesc` -| | SQL Data Type | `CHARACTER` + - + -- Length: `18` + -- Varying: `not selected` + -- Upshift: `not selected` + -- Character set: `ISO88591` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.lang.String[]` -| _Third_ | Parameter Name | `price` -| | SQL Data Type | `SIGNED NUMERIC` + +| Attribute | Definition +| *procedure-ref* | `trafodion.sales.partdata` +| *sql-parameter-1* | `IN partnum SIGNED INTEGER` +| *sql-parameter-2* | `OUT partdesc CHARACTER(18)` +| *sql-parameter-3* | `INOUT price NUMERIC(18,2)` +| *sql-parameter-4* | `OUT qty_available SIGNED INTEGER` +| *external name* | `EXTERNAL NAME sales.partData` +| *library* | `LIBRARY trafodion.sales.sales` +| *external security* | `invoker`^1^ + + -- Precision: `8` + -- Scale: `2` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.math.BigDecimal[]` -| _Fourth_ | Parameter Name | `qty_available` -| | SQL Data Type | `SIGNED INTEGER` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `int[]` -| *Attributes* | Number of dynamic result sets | `4` -| | Acceses Database | `selected` -| | External Security | `Invoker` + - + -*NOTE:* Depending on your security requirements, you can select `Definer` instead. -For more information, see <<understand-external-security, Understand External Security>>. -| | Transaction Required | `Yes` +Choice depends on your security requirements, you can select `definer` instead. +For more information, see <<understand-external-security, Understand External Security>>. +| *language java* | `LANGUAGE JAVA` +| *parameter style java* | `PARAMETER STYLE JAVA` +| *_sql access_* | `READS SQL DATA` +| *dynamic result sets* | `DYNAMIC RESULT SETS 4` +| *_transaction_* | `TRANSACTION REQUIRED`^1^ +| *_determinism_* | `NOT DETERMINISTIC`^1^ +| *_isolation_* | `ISOLATE`^1^ |=== +^1^ Definition represents default value. Not included in the CREATE PROCEDURE example. + +[source, sql] +---- +CREATE PROCEDURE trafodion.sales.partdata( IN partnum SIGNED INTEGER + , OUT partdesc CHARACTER(18) + , INOUT price NUMERIC(18,2) + , OUT qty_available SIGNED INTEGER + ) + EXTERNAL NAME 'sales.partData' + LIBRARY trafodion.sales.sales + LANGUAGE JAVA + PARAMETER STYLE JAVA + DYNAMIC RESULT SETS 4 + READS SQL DATA + ; +---- + [[calling-the-procedure-partdata]] ==== Calling the Procedure: PARTDATA @@ -545,7 +577,7 @@ To invoke the PARTDATA procedure in trafci: [source, sql] ---- -SQL> CALL demo.sales.partdata(212,?,?,?) ; +SQL> CALL trafodion.sales.partdata(212,?,?,?) ; ---- The PARTDATA procedure returns this information about part 212: @@ -616,47 +648,54 @@ include::{sourcedir}/orderSummary.java[orderSummary procedure source code] [[creating-the-procedure-ordersummary]] ==== Creating the Procedure: ORDERSUMMARY -Before creating the procedure, use HPDM to create a library named SALES -in the `DEMO.SALES` schema and select the `Sales.jar` file to upload to the -Trafodion platform for that library. -For more information, see <<create-a-library, Create a Library>>. +Before creating the procedure, create a library named `SALES` +in the `TRAFODION.SALES` schema and select the `Sales.jar` file to upload to the +Trafodion platform for that library. For more information, see +<<create-a-library, Create a Library>>. -After creating the library, navigate to the Procedures folder in the SALES schema, -launch the Create Procedure dialog box, and then enter or select these values. -For more information, see <<create-a-procedure, Create a Procedure>>. +After creating the library, use the values listed in the table below to define and execute the +CREATE PROCEDURE command. For more information, see <<create-a-procedure, Create a Procedure>>. [[table-8]] -.HPDM Create Procedure Settings: ORDERSUMMARY Procedure +.Create Procedure Settings: ORDERSUMMARY Procedure -[cols="15%,30%,55%", options="header"] +[cols="20%,85%",options="header"] |=== -| Group Box | Field or Option | Value or Setting -| *Name* | Procedure Name | `ordersummary` -| *Code* | Library | `DEMO.SALES.SALES` -| | Class Name | `Sales` -| | Method Name | `orderSummary` -3+<| *Parameters* -| _First_ | Parameter Name | `on_or_after_date` -| | SQL Data Type | `VARCHAR` + - + -- Length: `20` -- Upshift: `not selected` -- Character set: `ISO88591` -| | Direction | `IN` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.lang.String` -| _Second_ | Parameter Name | `num_orders` -| | SQL Data Type | `SIGNED LARGEINT` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `long[]` -| *Attributes* | Number of dynamic result sets | `2` -| | Acceses Database | `selected` -| | External Security | `Invoker` + +| Attribute | Definition +| *procedure-ref* | `trafodion.sales.ordersummary` +| *sql-parameter-1* | `IN on_or_after_date VARCHAR(20)` +| *sql-parameter-2* | `OUT num_orders LARGEINT` +| *external name* | `EXTERNAL NAME sales.orderSummary` +| *library* | `LIBRARY trafodion.sales.sales` +| *external security* | `invoker`^1^ + + -*NOTE:* Depending on your security requirements, you can select `Definer` instead. -For more information, see <<understand-external-security, Understand External Security>>. -| | Transaction Required | `Yes` +Choice depends on your security requirements, you can select `definer` instead. +For more information, see <<understand-external-security, Understand External Security>>. +| *language java* | `LANGUAGE JAVA` +| *parameter style java* | `PARAMETER STYLE JAVA` +| *_sql access_* | `READS SQL DATA` +| *dynamic result sets* | `DYNAMIC RESULT SETS 2` +| *_transaction_* | `TRANSACTION REQUIRED`^1^ +| *_determinism_* | `NOT DETERMINISTIC`^1^ +| *_isolation_* | `ISOLATE`^1^ |=== +^1^ Definition represents default value. Not included in the CREATE PROCEDURE example. + +[source, sql] +---- +CREATE PROCEDURE trafodion.sales.ordersummary( IN on_or_after_date VARCHAR(20) + , OUT num_orders LARGEINT + ) + EXTERNAL NAME 'sales.orderSummary' + LIBRARY trafodion.sales.sales + LANGUAGE JAVA + PARAMETER STYLE JAVA + DYNAMIC RESULT SETS 2 + READS SQL DATA + ; +---- + [[calling-the-procedure-ordersummary]] ==== Calling the Procedure: ORDERSUMMARY @@ -668,7 +707,7 @@ To invoke the ORDERSUMMARY procedure in trafci: [source, sql] ---- -SQL> CALL demo.sales.ordersummary('01-01-2011', ?); +SQL> CALL trafodion.sales.ordersummary('01-01-2011', ?); ---- The ORDERSUMMARY procedure returns this information about the orders on @@ -729,7 +768,7 @@ See the following sections for more information about each SPJ method. === ADJUSTSALARY Procedure The ADJUSTSALARY procedure accepts an employee number and a percentage -vvalue and updates the employee's salary in the database based on that +value and updates the employee's salary in the database based on that percentage. This method also returns the updated salary to an output parameter. @@ -746,53 +785,55 @@ include::{sourcedir}/adjustSalary.java[adjustSalary procedure source code] [[creating-the-procedure-adjustsalary]] ==== Creating the Procedure: ADJUSTSALARY -Before creating the procedure, use HPDM to create a library named -`PAYROLL` in the `DEMO.PERSNL` schema and select the `Payroll.jar` file to -upload to the Trafodion platform for that library. -For more information, see <<create-a-library, Create a Library>>. +Before creating the procedure, create a library named `PAYROLL` +in the `TRAFODION.PERSNL` schema and select the `Payroll.jar` file to upload to the +Trafodion platform for that library. For more information, see +<<create-a-library, Create a Library>>. -After creating the library, navigate to the `Procedures` folder in the `PERSNL` schema, -launch the Create Procedure dialog box, and then enter or select these values. -For more information, see <<create-a-procedure, Create a Procedure>>. +After creating the library, use the values listed in the table below to define and execute the +CREATE PROCEDURE command. For more information, see <<create-a-procedure, Create a Procedure>>. [[table-9]] -.HPDM Create Procedure Settings: ADJUSTSALARY Procedure +.Create Procedure Settings: ADJUSTSALARY Procedure -[cols="15%,30%,55%", options="header"] +[cols="20%,85%",options="header"] |=== -| Group Box | Field or Option | Value or Setting -| *Name* | Procedure Name | `adjustsalary` -| *Code* | Library | `DEMO.PERSNL.PAYROLL` -| | Class Name | `Payroll` -| | Method Name | `adjustSalary` -3+<| *Parameters* -| _First_ | Parameter Name | `empnum` -| | SQL Data Type | `SIGNED NUMERIC` + - + -- Precision: `4` + -- Scale: `0` -| | Direction | `IN` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.math.BigDecimal` -| _Second_ | Parameter Name | `percent` -| | SQL Data Type | `FLOAT` -| | Direction | `IN` -| | Java Data Type | Displays the signature of the Java method that you selected: `double` -| _Third_ | Parameter Name | `newsalary` -| | SQL Data Type | `SIGNED NUMERIC` + - + -- Precision: `8` + -- Scale: `2` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.math.BigDecimal[]` -| *Attributes* | Number of dynamic result sets | `0` -| | Acceses Database | `selected` -| | External Security | `Invoker` + +| Attribute | Definition +| *procedure-ref* | `trafodion.persnl.adjustsalary` +| *sql-parameter-1* | `IN empnum NUMERIC(4)` +| *sql-parameter-2* | `IN percent FLOAT` +| *sql-parameter-3* | `OUT newsalary NUMERIC(8,2)` +| *external name* | `EXTERNAL NAME persnl.adjustSalary` +| *library* | `LIBRARY trafodion.persnl.payroll` +| *external security* | `invoker`^1^ + + -*NOTE:* Depending on your security requirements, you can select `Definer` instead. -For more information, see <<understand-external-security, Understand External Security>>. -| | Transaction Required | `Yes` +Choice depends on your security requirements, you can select `definer` instead. +For more information, see <<understand-external-security, Understand External Security>>. +| *language java* | `LANGUAGE JAVA` +| *parameter style java* | `PARAMETER STYLE JAVA` +| *_sql access_* | `MODIFIES SQL DATA` +| *dynamic result sets* | `DYNAMIC RESULT SETS 0`^1^ +| *_transaction_* | `TRANSACTION REQUIRED`^1^ +| *_determinism_* | `NOT DETERMINISTIC`^1^ +| *_isolation_* | `ISOLATE`^1^ |=== +^1^ Definition represents default value. Not included in the CREATE PROCEDURE example. + +[source, sql] +---- +CREATE PROCEDURE trafodion.persnl.adjustsalary( IN empnum NUMERIC(4) + , IN percent FLOAT + , OUT newsalary NUMERIC(8,2) + ) + EXTERNAL NAME 'persnl.adjustSalary' + LIBRARY trafodion.persnl.payroll + LANGUAGE JAVA + PARAMETER STYLE JAVA + MODIFIES SQL DATA + ; +---- + [[calling-the-procedure-adjustsalary]] ==== Calling the Procedure: ADJUSTSALARY @@ -804,7 +845,7 @@ To invoke the ADJUSTSALARY procedure in trafci: [source, sql] ---- -SQL> CALL demo.persnl.adjustsalary(29, 2.5, ?) ; +SQL> CALL trafodion.persnl.adjustsalary(29, 2.5, ?) ; ---- The ADJUSTSALARY procedure updates the salary of employee number 29 by @@ -841,46 +882,53 @@ include::{sourcedir}/employeeJob.java[employeeJob procedure source code] [[creating-the-procedure-employeejob]] ==== Creating the Procedure: EMPLOYEEJOB -Before creating the procedure, use HPDM to create a library named -`PAYROLL` in the `DEMO.PERSNL` schema and select the `Payroll.jar` file to -upload to the Trafodion platform for that library. -For more information, see <<create-a-library, Create a Library>>. +Before creating the procedure, create a library named `PAYROLL` +in the `TRAFODION.PERSNL` schema and select the `Payroll.jar` file to upload to the +Trafodion platform for that library. For more information, see +<<create-a-library, Create a Library>>. -After creating the library, navigate to the `Procedures` folder in the `PERSNL` schema, -launch the Create Procedure dialog box, and then enter or select these values. -For more information, see <<create-a-procedure, Create a Procedure>>. +After creating the library, use these values listed in the table below to define and execute the +CREATE PROCEDURE command. For more information, see <<create-a-procedure, Create a Procedure>>. [[table-10]] -.HPDM Create Procedure Settings: EMPLOYEEJOB Procedure +.Create Procedure Settings: EMPLOYEEJOB Procedure -[cols="15%,30%,55%", options="header"] +[cols="20%,85%",options="header"] |=== -| Group Box | Field or Option | Value or Setting -| *Name* | Procedure Name | `employeejob` -| *Code* | Library | `DEMO.PERSNL.PAYROLL` -| | Class Name | `Payroll` -| | Method Name | `employeeJob` -3+<| *Parameters* -| _First_ | Parameter Name | `empnum` -| | SQL Data Type | `SIGNED NUMERIC` + +| Attribute | Definition +| *procedure-ref* | `trafodion.persnl.employeejob` +| *sql-parameter-1* | `IN empnum NUMERIC(4)` +| *sql-parameter-2* | `OUT jobcode INT` +| *external name* | `EXTERNAL NAME persnl.employeeJob (int, java.lang.Integer[])` +| *library* | `LIBRARY trafodion.persnl.payroll` +| *external security* | `invoker`^1^ + + -- Precision: `4` + -- Scale: `0` -| | Direction | `IN` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.math.BigDecimal` -| _Second_ | Parameter Name | `jobcode` -| | SQL Data Type | `SIGNED INTEGER` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.lang.Integer[]` -| *Attributes* | Number of dynamic result sets | `0` -| | Acceses Database | `selected` -| | External Security | `Invoker` + - + -*NOTE:* Depending on your security requirements, you can select `Definer` instead. -For more information, see <<understand-external-security, Understand External Security>>. -| | Transaction Required | `Yes` +Choice depends on your security requirements, you can select `definer` instead. +For more information, see <<understand-external-security, Understand External Security>>. +| *language java* | `LANGUAGE JAVA` +| *parameter style java* | `PARAMETER STYLE JAVA` +| *_sql access_* | `READS SQL DATA` +| *dynamic result sets* | `DYNAMIC RESULT SETS 0`^1^ +| *_transaction_* | `TRANSACTION REQUIRED`^1^ +| *_determinism_* | `NOT DETERMINISTIC`^1^ +| *_isolation_* | `ISOLATE`^1^ |=== +^1^ Definition represents default value. Not included in the CREATE PROCEDURE example. + +[source, sql] +---- +CREATE PROCEDURE trafodion.persnl.adjustsalary( IN empnum NUMERIC(4) + , OUT jobcode INT + ) + EXTERNAL NAME 'persnl.employeeJob (int, java.lang.Integer[])' + LIBRARY trafodion.persnl.payroll + LANGUAGE JAVA + PARAMETER STYLE JAVA + READS SQL DATA + ; +---- + [[calling-the-procedure-employeejob]] ==== Calling the Procedure: EMPLOYEEJOB @@ -892,7 +940,7 @@ To invoke the EMPLOYEEJOB procedure in trafci: [source, sql] ---- -SQL> CALL demo.persnl.employeejob(337, ?) ; +SQL> CALL trafodion.persnl.employeejob(337, ?) ; ---- The EMPLOYEEJOB procedure accepts the employee number 337 and returns @@ -929,39 +977,53 @@ include::{sourcedir}/projectTeam.java[projectTeam procedure source code] [[creating-the-procedure-projectteam]] ==== Creating the Procedure: PROJECTTEAM -Before creating the procedure, use HPDM to create a library named -`PAYROLL` in the `DEMO.PERSNL` schema and select the `Payroll.jar` file to -upload to the Trafodion platform for that library. -For more information, see <<create-a-library, Create a Library>>. +Before creating the procedure, create a library named `PAYROLL` +in the `TRAFODION.PERSNL` schema and select the `Payroll.jar` file to upload to the +Trafodion platform for that library. For more information, see +<<create-a-library, Create a Library>>. -After creating the library, navigate to the `Procedures` folder in the `PERSNL` schema, -launch the Create Procedure dialog box, and then enter or select these values. -For more information, see <<create-a-procedure, Create a Procedure>>. +After creating the library, use the values listed in the table below to define and execute the +CREATE PROCEDURE command. For more information, see <<create-a-procedure, Create a Procedure>>. [[table-11]] -.HPDM Create Procedure Settings: PROJECTTEAM Procedure +.Create Procedure Settings: PROJECTTEAM Procedure -[cols="15%,30%,55%", options="header"] +[cols="20%,85%",options="header"] |=== -| Group Box | Field or Option | Value or Setting -| *Name* | Procedure Name | `projectteam` -| *Code* | Library | `DEMO.PERSNL.PAYROLL` -| | Class Name | `Payroll` -| | Method Name | `projectTeam` -3+<| *Parameters* -| _First_ | Parameter Name | `projectcode` -| | SQL Data Type | `SIGNED INTEGER` -| | Direction | `IN` -| | Java Data Type | Displays the signature of the Java method that you selected: `int` -| *Attributes* | Number of dynamic result sets | `1` -| | Acceses Database | `selected` -| | External Security | `Invoker` + +| Attribute | Definition +| *procedure-ref* | `trafodion.persnl.projectteam` +| *sql-parameter-1* | `IN projectcode INTEGER` +| *external name* | `EXTERNAL NAME persnl.projectTeam` +| *library* | `LIBRARY trafodion.persnl.payroll` +| *external security* | `invoker`^1^ + + -*NOTE:* Depending on your security requirements, you can select `Definer` instead. -For more information, see <<understand-external-security, Understand External Security>>. -| | Transaction Required | `Yes` +Choice depends on your security requirements, you can select `definer` instead. +For more information, see <<understand-external-security, Understand External Security>>. +| *language java* | `LANGUAGE JAVA` +| *parameter style java* | `PARAMETER STYLE JAVA` +| *_sql access_* | `READS SQL DATA` +| *dynamic result sets* | `DYNAMIC RESULT SETS 1` +| *_transaction_* | `TRANSACTION REQUIRED`^1^ +| *_determinism_* | `NOT DETERMINISTIC`^1^ +| *_isolation_* | `ISOLATE`^1^ |=== +^1^ Definition represents default value. Not included in the CREATE PROCEDURE example. + +[source, sql] +---- +CREATE PROCEDURE trafodion.persnl.projectteam( IN projectcode INTEGER + , OUT jobcode INT + ) + EXTERNAL NAME 'persnl.projectTeam' + LIBRARY trafodion.persnl.payroll + LANGUAGE JAVA + PARAMETER STYLE JAVA + DYNAMIC RESULT SETS 1 + READS SQL DATA + ; +---- + [[calling-the-procedure-projectteam]] ==== Calling the Procedure: PROJECTTEAM @@ -973,7 +1035,7 @@ To invoke the PROJECTTEAM procedure in trafci: [source, sql] ---- -SQL> CALL demo.persnl.projectteam( 5000 ) ; +SQL> CALL trafodion.persnl.projectteam( 5000 ) ; ---- The PROJECTTEAM procedure returns this information about the employees @@ -1014,39 +1076,52 @@ include::{sourcedir}/topSalesReps.java[topSalesReps procedure source code] [[creating-the-procedure-topsalesreps]] ==== Creating the Procedure: TOPSALESREPS -Before creating the procedure, use HPDM to create a library named -`PAYROLL` in the `DEMO.PERSNL` schema and select the `Payroll.jar` file to -upload to the Trafodion platform for that library. -For more information, see <<create-a-library, Create a Library>>. +Before creating the procedure, create a library named `PAYROLL` +in the `TRAFODION.PERSNL` schema and select the `Payroll.jar` file to upload to the +Trafodion platform for that library. For more information, see +<<create-a-library, Create a Library>>. -After creating the library, navigate to the `Procedures` folder in the `PERSNL` schema, -launch the Create Procedure dialog box, and then enter or select these values. -For more information, see <<create-a-procedure, Create a Procedure>>. +After creating the library, use the values listed in the table below to define and execute the +CREATE PROCEDURE command. For more information, see <<create-a-procedure, Create a Procedure>>. [[table-12]] -.HPDM Create Procedure Settings: TOPSALESREPS Procedure +.Create Procedure Settings: TOPSALESREPS Procedure -[cols="15%,30%,55%", options="header"] +[cols="20%,85%",options="header"] |=== -| Group Box | Field or Option | Value or Setting -| *Name* | Procedure Name | `topsalesreps` -| *Code* | Library | `DEMO.PERSNL.PAYROLL` -| | Class Name | `Payroll` -| | Method Name | `topSalesReps` -3+<| *Parameters* -| _First_ | Parameter Name | `whichquarter` -| | SQL Data Type | `SIGNED INTEGER` -| | Direction | `IN` -| | Java Data Type | Displays the signature of the Java method that you selected: `int` -| *Attributes* | Number of dynamic result sets | `1` -| | Acceses Database | `selected` -| | External Security | `Invoker` + +| Attribute | Definition +| *procedure-ref* | `trafodion.persnl.topsalesreps` +| *sql-parameter-1* | `IN whichquarter INTEGER` +| *external name* | `EXTERNAL NAME persnl.topSalesReps` +| *library* | `LIBRARY trafodion.persnl.payroll` +| *external security* | `invoker`^1^ + + -*NOTE:* Depending on your security requirements, you can select `Definer` instead. -For more information, see <<understand-external-security, Understand External Security>>. -| | Transaction Required | `Yes` +Choice depends on your security requirements, you can select `definer` instead. +For more information, see <<understand-external-security, Understand External Security>>. +| *language java* | `LANGUAGE JAVA` +| *parameter style java* | `PARAMETER STYLE JAVA` +| *_sql access_* | `READS SQL DATA` +| *dynamic result sets* | `DYNAMIC RESULT SETS 1` +| *_transaction_* | `TRANSACTION REQUIRED`^1^ +| *_determinism_* | `NOT DETERMINISTIC`^1^ +| *_isolation_* | `ISOLATE`^1^ |=== +^1^ Definition represents default value. Not included in the CREATE PROCEDURE example. + +[source, sql] +---- +CREATE PROCEDURE trafodion.persnl.topsalesreps( IN whichquarter INTEGER + ) + EXTERNAL NAME 'persnl.topSalesReps' + LIBRARY trafodion.persnl.payroll + LANGUAGE JAVA + PARAMETER STYLE JAVA + DYNAMIC RESULT SETS 1 + READS SQL DATA + ; +---- + [[calling-the-procedure-topsalesreps]] ==== Calling the Procedure: TOPSALESREPS @@ -1058,7 +1133,7 @@ To invoke the TOPSALESREPS procedure in trafci: [source, sql] ---- -SQL> CALL demo.persnl.topsalesreps( 1 ) ; +SQL> CALL trafodion.persnl.topsalesreps( 1 ) ; ---- The TOPSALESREPS procedure returns this information about the top five @@ -1123,87 +1198,61 @@ include::{sourcedir}/supplierInfo.java[supplierInfo procedure source code] [[creating-the-procedure-supplierinfo]] ==== Creating the Procedure: SUPPLIERINFO -Before creating the procedure, use HPDM to create a library named -`INVENTORY` in the `DEMO.INVENT` schema and select the `Inventory.jar` file to -upload to the Trafodion platform for that library. -For more information, see <<create-a-library, Create a Library>>. +Before creating the procedure, create a library named `INVENTORY` +in the `TRAFODION.INVENT` schema and select the `Inventory.jar` file to upload to the +Trafodion platform for that library. For more information, see +<<create-a-library, Create a Library>>. -After creating the library, navigate to the Procedures folder in the `INVENT` schema, -launch the Create Procedure dialog box, and then enter or select these values. -For more information, see <<create-a-procedure, Create a Procedure>>. +After creating the library, use the values listed in the table below to define and execute the +CREATE PROCEDURE command. For more information, see <<create-a-procedure, Create a Procedure>>. [[table-13]] -.HPDM Create Procedure Settings: SUPPLIERINFO Procedure +.Create Procedure Settings: SUPPLIERINFO Procedure -[cols="15%,30%,55%", options="header"] +[cols="20%,85%",options="header"] |=== -| Group Box | Field or Option | Value or Setting -| *Name* | Procedure Name | `supplierinfo` -| *Code* | Library | `DEMO.INVENT.INVENTORY` -| | Class Name | `Inventory` -| | Method Name | `supplierInfo` -3+<| *Parameters* -| _First_ | Parameter Name | `empnum` -| | SQL Data Type | `SIGNED NUMERIC` + +| Attribute | Definition +| *procedure-ref* | `trafodion.invent.supplierinfo` +| *sql-parameter-1* | `IN empnum NUMERIC(4)` +| *sql-parameter-2* | `IN suppnum CHARACTER(18)` +| *sql-parameter-3* | `OUT address CHARACTER(22)` +| *sql-parameter-4* | `OUT city CHARACTER(14)` +| *sql-parameter-5* | `OUT state CHARACTER(12)` +| *sql-parameter-6* | `OUT zipcode CHARACTER(10)` +| *external name* | `EXTERNAL NAME persnl.supplierInfo` +| *library* | `LIBRARY trafodion.invent.inventory` +| *external security* | `invoker`^1^ + + -- Precision: `4` + -- Scale: `0` -| | Direction | `IN` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.math.BigDecimal` -| _Second_ | Parameter Name | `suppname` -| | SQL Data Type | `CHARACTER` + - + -- Length: `18` + -- Varying: `not selected` + -- Upshift: `not selected` + -- Character set: `ISO88591` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.lang.String[]` -| _Third_ | Parameter Name | `address` -| | SQL Data Type | `CHARACTER` + - + -- Length: `22` + -- Varying: `not selected` + -- Upshift: `not selected` + -- Character set: `ISO88591` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.lang.String[]` -| _Fourth_ | Parameter Name | `city` -| | SQL Data Type | `CHARACTER` + - + -- Length: `14` + -- Varying: `not selected` + -- Upshift: `not selected` + -- Character set: `ISO88591` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.lang.String[]` -| _Fifth_ | Parameter Name | `state` -| | SQL Data Type | `CHARACTER` + - + -- Length: `12` + -- Varying: `not selected` + -- Upshift: `not selected` + -- Character set: `ISO88591` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.lang.String[]` -| _Sixth_ | Parameter Name | `zipcode` -| | SQL Data Type | `CHARACTER` + - + -- Length: `10` + -- Varying: `not selected` + -- Upshift: `not selected` + -- Character set: `ISO88591` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `java.lang.String[]` -| *Attributes* | Number of dynamic result sets | `0` -| | Acceses Database | `selected` -| | External Security | `Invoker` + - + -*NOTE:* Depending on your security requirements, you can select `Definer` instead. -For more information, see <<understand-external-security, Understand External Security>>. -| | Transaction Required | `Yes` +Choice depends on your security requirements, you can select `definer` instead. +For more information, see <<understand-external-security, Understand External Security>>. +| *language java* | `LANGUAGE JAVA` +| *parameter style java* | `PARAMETER STYLE JAVA` +| *_sql access_* | `READS SQL DATA` +| *dynamic result sets* | `DYNAMIC RESULT SETS 0`^1^ +| *_transaction_* | `TRANSACTION REQUIRED`^1^ +| *_determinism_* | `NOT DETERMINISTIC`^1^ +| *_isolation_* | `ISOLATE`^1^ |=== +^1^ Definition represents default value. Not included in the CREATE PROCEDURE example. + +[source, sql] +---- +CREATE PROCEDURE trafodion.invent.supplierinfo( IN empnum NUMERIC(4) + , IN suppnum CHARACTER(18) + , OUT address CHARACTER(22) + , OUT city CHARACTER(14) + , OUT state CHARACTER(12) + , OUT zipcode CHARACTER(10) + ) + EXTERNAL NAME 'persnl.supplierInfo' + LIBRARY trafodion.invent.inventory + LANGUAGE JAVA + PARAMETER STYLE JAVA + READS SQL DATA + ; +---- + [[calling-the-procedure-supplierinfo]] ==== Calling the Procedure: SUPPLIERINFO @@ -1215,7 +1264,7 @@ To invoke the SUPPLIERINFO procedure in trafci: [source, sql] ---- -SQL> CALL demo.invent.supplierinfo( 25,?,?,?,?,? ) ; +SQL> CALL trafodion.invent.supplierinfo( 25,?,?,?,?,? ) ; ---- The SUPPLIERINFO procedure accepts the supplier number 25 and returns @@ -1251,47 +1300,55 @@ include::{sourcedir}/supplyQuantities.java[supplyQuantities procedure source cod [[creating-the-procedure-supplyquantities]] ==== Creating the Procedure: SUPPLYQUANTITIES -Before creating the procedure, use HPDM to create a library named -`INVENTORY` in the `DEMO.INVENT` schema and select the `Inventory.jar` file to -upload to the Trafodion platform for that library. -For more information, see <<create-a-library, Create a Library>>. +Before creating the procedure, create a library named `INVENTORY` +in the `TRAFODION.INVENT` schema and select the `Inventory.jar` file to upload to the +Trafodion platform for that library. For more information, see +<<create-a-library, Create a Library>>. -After creating the library, navigate to the Procedures folder in the `INVENT` schema, -launch the Create Procedure dialog box, and then enter or select these values. -For more information, see <<create-a-procedure, Create a Procedure>>. +After creating the library, use the values listed in the table below to define and execute the +CREATE PROCEDURE command. For more information, see <<create-a-procedure, Create a Procedure>>. [[table-14]] -.HPDM Create Procedure Settings: SUPPLYQUANTITIES Procedure +.Create Procedure Settings: SUPPLYQUANTITIES Procedure -[cols="15%,30%,55%", options="header"] +[cols="20%,85%",options="header"] |=== -| Group Box | Field or Option | Value or Setting -| *Name* | Procedure Name | `supplyquantities` -| *Code* | Library | `DEMO.INVENT.INVENTORY` -| | Class Name | `Inventory` -| | Method Name | `supplyQuantities` -3+<| *Parameters* -| _First_ | Parameter Name | `avrg` -| | SQL Data Type | `SIGNED INTEGER` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `int` -| _Second_ | Parameter Name | `minm` -| | SQL Data Type | `SIGNED INTEGER` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `int` -| _Third_ | Parameter Name | `maxm` -| | SQL Data Type | `SIGNED INTEGER` -| | Direction | `OUT` -| | Java Data Type | Displays the signature of the Java method that you selected: `int` -| *Attributes* | Number of dynamic result sets | `0` -| | Acceses Database | `selected` -| | External Security | `Invoker` + +| Attribute | Definition +| *procedure-ref* | `trafodion.invent.supplyquantities` +| *sql-parameter-1* | `OUT avrg INT` +| *sql-parameter-2* | `OUT minm INT` +| *sql-parameter-3* | `OUT maxm INT` +| *external name* | `EXTERNAL NAME persnl.supplyQuantities` +| *library* | `LIBRARY trafodion.invent.inventory` +| *external security* | `invoker`^1^ + + -*NOTE:* Depending on your security requirements, you can select `Definer` instead. -For more information, see <<understand-external-security, Understand External Security>>. -| | Transaction Required | `Yes` +Choice depends on your security requirements, you can select `definer` instead. +For more information, see <<understand-external-security, Understand External Security>>. +| *language java* | `LANGUAGE JAVA` +| *parameter style java* | `PARAMETER STYLE JAVA` +| *_sql access_* | `READS SQL DATA` +| *dynamic result sets* | `DYNAMIC RESULT SETS 0`^1^ +| *_transaction_* | `TRANSACTION REQUIRED`^1^ +| *_determinism_* | `NOT DETERMINISTIC`^1^ +| *_isolation_* | `ISOLATE`^1^ |=== +^1^ Definition represents default value. Not included in the CREATE PROCEDURE example. + +[source, sql] +---- +CREATE PROCEDURE trafodion.invent.supplyquantities( OUT avrg INT + , OUT minm INT + , OUT maxm INT + ) + EXTERNAL NAME 'persnl.supplyQuantities' + LIBRARY trafodion.invent.inventory + LANGUAGE JAVA + PARAMETER STYLE JAVA + READS SQL DATA + ; +---- + [[calling-the-procedure-supplyquantities]] ==== Calling the Procedure: SUPPLYQUANTITIES @@ -1303,7 +1360,7 @@ To invoke the SUPPLYQUANTITIES procedure in trafci: [source, sql] ---- -SQL> CALL demo.invent.supplynumbers( ?,?,? ) ; +SQL> CALL trafodion.invent.supplynumbers( ?,?,? ) ; ---- The SUPPLYQUANTITIES procedure returns this output in trafci: @@ -1341,42 +1398,55 @@ include::{sourcedir}/partLocations.java[partLocations procedure source code] [[creating-the-procedure-partlocs]] ==== Creating the Procedure: PARTLOCS -Before creating the procedure, use HPDM to create a library named -`INVENTORY` in the `DEMO.INVENT` schema and select the `Inventory.jar` file to -upload to the Trafodion platform for that library. -For more information, see <<create-a-library, Create a Library>>. +Before creating the procedure, create a library named `INVENTORY` +in the `TRAFODION.INVENT` schema and select the `Inventory.jar` file to upload to the +Trafodion platform for that library. For more information, see +<<create-a-library, Create a Library>>. -After creating the library, navigate to the Procedures folder in the `INVENT` schema, -launch the Create Procedure dialog box, and then enter or select these values. -For more information, see <<create-a-procedure, Create a Procedure>>. +After creating the library, use the values listed in the table below to define and execute the +CREATE PROCEDURE command. For more information, see <<create-a-procedure, Create a Procedure>>. [[table-15]] -.HPDM Create Procedure Settings: PARTLOCS Procedure +.Create Procedure Settings: PARTLOCS Procedure -[cols="15%,30%,55%", options="header"] +[cols="20%,85%",options="header"] |=== -| Group Box | Field or Option | Value or Setting -| *Name* | Procedure Name | `partlocs` -| *Code* | Library | `DEMO.INVENT.INVENTORY` -| | Class Name | `Inventory` -| | Method Name | `partLocs` -3+<| *Parameters* -| _First_ | Parameter Name | `partnum` -| | SQL Data Type | `SIGNED INTEGER` -| | Direction | `IN` -| | Java Data Type | Displays the signature of the Java method that you selected: `int` -| _Second_ | Parameter Name | `qty` -| | SQL Data Type | `SIGNED INTEGER` -| | Direction | `IN` -| *Attributes* | Number of dynamic result sets | `2` -| | Acceses Database | `selected` -| | External Security | `Invoker` + +| Attribute | Definition +| *procedure-ref* | `trafodion.invent.partlocs` +| *sql-parameter-1* | `IN partnum INT` +| *sql-parameter-2* | `IN qty INT` +| *external name* | `EXTERNAL NAME persnl.partLocations` +| *library* | `LIBRARY trafodion.invent.inventory` +| *external security* | `invoker`^1^ + + -*NOTE:* Depending on your security requirements, you can select `Definer` instead. -For more information, see <<understand-external-security, Understand External Security>>. -| | Transaction Required | `Yes` +Choice depends on your security requirements, you can select `definer` instead. +For more information, see <<understand-external-security, Understand External Security>>. +| *language java* | `LANGUAGE JAVA` +| *parameter style java* | `PARAMETER STYLE JAVA` +| *_sql access_* | `READS SQL DATA` +| *dynamic result sets* | `DYNAMIC RESULT SETS 2` +| *_transaction_* | `TRANSACTION REQUIRED`^1^ +| *_determinism_* | `NOT DETERMINISTIC`^1^ +| *_isolation_* | `ISOLATE`^1^ |=== +^1^ Definition represents default value. Not included in the CREATE PROCEDURE example. + +[source, sql] +---- +CREATE PROCEDURE trafodion.invent.partlocs( IN partnum INT + , IN qty INT + , OUT maxm INT + ) + EXTERNAL NAME 'persnl.partLocations' + LIBRARY trafodion.invent.inventory + LANGUAGE JAVA + PARAMETER STYLE JAVA + DYNAMIC RESULT SETS 2 + READS SQL DATA + ; +---- + [[calling-the-procedure-partlocs]] ==== Calling the Procedure: PARTLOCS @@ -1388,7 +1458,7 @@ To invoke the PARTLOCS procedure in trafci: [source, sql] ---- -SQL> CALL demo.invent.partlocs( 212, 18 ) ; +SQL> CALL trafodion.invent.partlocs( 212, 18 ) ; ---- The PARTLOCS procedure accepts the part number 212 and returns a set of
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/Inventory.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/Inventory.java b/docs/spj_guide/src/resources/source/Inventory.java index becfbee..76c5359 100644 --- a/docs/spj_guide/src/resources/source/Inventory.java +++ b/docs/spj_guide/src/resources/source/Inventory.java @@ -23,7 +23,7 @@ public class Inventory PreparedStatement getSupplier = conn.prepareStatement( "SELECT suppname, street, city, " + " state, postcode " - + "FROM demo.invent.supplier " + + "FROM trafodion.invent.supplier " + "WHERE suppnum = ?" ) ; @@ -60,7 +60,7 @@ public class Inventory conn.prepareStatement( "SELECT AVG(qty_on_hand), " + " MIN(qty_on_hand), " + " MAX(qty_on_hand) " - + "FROM demo.invent.partloc" + + "FROM trafodion.invent.partloc" ) ; ResultSet rs = getQty.executeQuery() ; @@ -93,7 +93,7 @@ public class Inventory PreparedStatement getLocationsExact = conn.prepareStatement( "SELECT L.loc_code, L.partnum, L.qty_on_hand " - + "FROM demo.invent.partloc L " + + "FROM trafodion.invent.partloc L " + "WHERE L.partnum = ? " + " AND L.qty_on_hand = ? " + " ORDER BY L.partnum " @@ -104,7 +104,7 @@ public class Inventory PreparedStatement getLocationsMoreThan = conn.prepareStatement( "SELECT L.loc_code, L.partnum, L.qty_on_hand " - + "FROM demo.invent.partloc L " + + "FROM trafodion.invent.partloc L " + "WHERE L.partnum = ? " + " AND L.qty_on_hand > ? " + "ORDER BY L.partnum " http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/Payroll.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/Payroll.java b/docs/spj_guide/src/resources/source/Payroll.java index 7a85254..89c28a5 100644 --- a/docs/spj_guide/src/resources/source/Payroll.java +++ b/docs/spj_guide/src/resources/source/Payroll.java @@ -19,14 +19,14 @@ public class Payroll DriverManager.getConnection( "jdbc:default:connection" ) ; PreparedStatement setSalary = - conn.prepareStatement( "UPDATE demo.persnl.employee " + conn.prepareStatement( "UPDATE trafodion.persnl.employee " + "SET salary = salary * (1 + (? / 100)) " + "WHERE empnum = ?" ) ; PreparedStatement getSalary = conn.prepareStatement( "SELECT salary " - + "FROM demo.persnl.employee " + + "FROM trafodion.persnl.employee " + "WHERE empnum = ?" ) ; @@ -59,7 +59,7 @@ public class Payroll PreparedStatement getJobcode = conn.prepareStatement( "SELECT jobcode " - + "FROM demo.persnl.employee " + + "FROM trafodion.persnl.employee " + "WHERE empnum = ?" ) ; @@ -93,7 +93,7 @@ public class Payroll PreparedStatement getMembers = conn.prepareStatement( "SELECT E.empnum, E.first_name, E.last_name, D.location " - + "FROM demo.persnl.employee E, demo.persnl.dept D, demo.persnl.project P " + + "FROM trafodion.persnl.employee E, trafodion.persnl.dept D, trafodion.persnl.project P " + "WHERE P.projcode = ? " + " AND P.empnum = E.empnum " + " AND E.deptnum = D.deptnum " @@ -132,10 +132,10 @@ public class Payroll PreparedStatement getTopReps = conn.prepareStatement( "SELECT [first 5] e.empnum, e.first_name, " + "e.last_name, totals.total " - + "FROM demo.persnl.employee e, " + + "FROM trafodion.persnl.employee e, " + " ( SELECT o.salesrep, " + " SUM( od.unit_price * od.qty_ordered ) as total " - + " FROM demo.sales.orders o, demo.sales.odetail od " + + " FROM trafodion.sales.orders o, trafodion.sales.odetail od " + " WHERE o.ordernum = od.ordernum " + " AND QUARTER( o.order_date ) = ? " + " GROUP BY o.salesrep " http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/Sales.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/Sales.java b/docs/spj_guide/src/resources/source/Sales.java index e66fbe1..eba4fe2 100644 --- a/docs/spj_guide/src/resources/source/Sales.java +++ b/docs/spj_guide/src/resources/source/Sales.java @@ -17,14 +17,14 @@ public class Sales PreparedStatement getParts = conn.prepareStatement( "SELECT p.partnum, " + "SUM(qty_ordered) AS qtyOrdered " - + "FROM demo.sales.parts p " - + "LEFT JOIN demo.sales.odetail o " + + "FROM trafodion.sales.parts p " + + "LEFT JOIN trafodion.sales.odetail o " + "ON p.partnum = o.partnum " + "GROUP BY p.partnum" ) ; PreparedStatement updateParts = - conn.prepareStatement( "UPDATE demo.sales.parts " + conn.prepareStatement( "UPDATE trafodion.sales.parts " + "SET price = price * 0.9 " + "WHERE partnum = ?" ) ; @@ -61,7 +61,7 @@ public class Sales PreparedStatement getNumOrders = conn.prepareStatement( "SELECT COUNT(order_date) " - + "FROM demo.sales.orders " + + "FROM trafodion.sales.orders " + "WHERE order_date = ?" ) ; @@ -102,7 +102,7 @@ public class Sales PreparedStatement getNumOrders = conn.prepareStatement( "SELECT COUNT( month( order_date ) ) " - + "FROM demo.sales.orders " + + "FROM trafodion.sales.orders " + "WHERE month( order_date ) = ?" ) ; @@ -194,7 +194,7 @@ public class Sales // Retrieve detail about this part into the output parameters PreparedStatement getPartInfo = conn.prepareStatement( "SELECT P.partdesc, P.price, P.qty_available " - + "FROM demo.sales.parts P " + + "FROM trafodion.sales.parts P " + "WHERE partnum = ? " ) ; @@ -214,9 +214,9 @@ public class Sales // quantity of this part that was ordered. PreparedStatement getOrders = conn.prepareStatement( "SELECT O.*, QTY.QTY_ORDERED " - + "FROM demo.sales.orders O " + + "FROM trafodion.sales.orders O " + " , ( select ordernum, sum(qty_ordered) as QTY_ORDERED " - + " from demo.sales.odetail " + + " from trafodion.sales.odetail " + " where partnum = ? " + " group by ordernum ) QTY " + "WHERE O.ordernum = QTY.ordernum " @@ -232,7 +232,7 @@ public class Sales // have on hand. PreparedStatement getLocations = conn.prepareStatement( "SELECT * " - + "FROM demo.invent.partloc " + + "FROM trafodion.invent.partloc " + " WHERE partnum = ? " ) ; @@ -243,7 +243,7 @@ public class Sales // suppliers who supply this part. PreparedStatement getSuppliers = conn.prepareStatement( "SELECT * " - + "FROM demo.invent.partsupp " + + "FROM trafodion.invent.partsupp " + "WHERE partnum = ? " ) ; @@ -254,10 +254,10 @@ public class Sales // sales reps that have sold this part. PreparedStatement getReps = conn.prepareStatement( "SELECT * " - + "FROM demo.persnl.employee " + + "FROM trafodion.persnl.employee " + "WHERE empnum in ( SELECT O.salesrep " - + " FROM demo.sales.orders O, " - + " demo.sales.odetail D " + + " FROM trafodion.sales.orders O, " + + " trafodion.sales.odetail D " + " D.partnum = ? " + " O.ordernum = D.ordernum ) " + "ORDER BY empnum " @@ -295,7 +295,7 @@ public class Sales DriverManager.getConnection( "jdbc:default:connection" ) ; // Get the number of orders on or after this date - s = "SELECT COUNT(ordernum) FROM demo.sales.orders " + s = "SELECT COUNT(ordernum) FROM trafodion.sales.orders " + "WHERE order_date >= CAST(? AS DATE) " ; @@ -314,13 +314,13 @@ public class Sales + " 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 " + + " FROM trafodion.sales.orders o, trafodion.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 " + + " , trafodion.sales.orders orders " + + " , trafodion.persnl.employee emps " + "WHERE amounts.ordernum = orders.ordernum " + " AND orders.salesrep = emps.empnum " + "ORDER BY orders.ordernum " @@ -332,7 +332,7 @@ public class Sales // Open a result set for order detail rows s = "SELECT d.*, p.partdesc " - + "FROM demo.sales.odetail d, demo.sales.parts p, demo.sales.orders O " + + "FROM trafodion.sales.odetail d, trafodion.sales.parts p, trafodion.sales.orders O " + "WHERE d.partnum = p.partnum AND d.ordernum = o.ordernum " + " AND o.order_date >= CAST(? AS DATE) " + "ORDER BY d.ordernum " http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/adjustSalary.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/adjustSalary.java b/docs/spj_guide/src/resources/source/adjustSalary.java index 70ee558..cc2b095 100644 --- a/docs/spj_guide/src/resources/source/adjustSalary.java +++ b/docs/spj_guide/src/resources/source/adjustSalary.java @@ -14,14 +14,14 @@ public static void adjustSalary( BigDecimal empNum DriverManager.getConnection( "jdbc:default:connection" ) ; PreparedStatement setSalary = - conn.prepareStatement( "UPDATE demo.persnl.employee " + conn.prepareStatement( "UPDATE trafodion.persnl.employee " + "SET salary = salary * (1 + (? / 100)) " + "WHERE empnum = ?" ) ; PreparedStatement getSalary = conn.prepareStatement( "SELECT salary " - + "FROM demo.persnl.employee " + + "FROM trafodion.persnl.employee " + "WHERE empnum = ?" ) ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/employeeJob.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/employeeJob.java b/docs/spj_guide/src/resources/source/employeeJob.java index 99196bd..ce5cdab 100644 --- a/docs/spj_guide/src/resources/source/employeeJob.java +++ b/docs/spj_guide/src/resources/source/employeeJob.java @@ -12,7 +12,7 @@ public static void employeeJob( int empNum PreparedStatement getJobcode = conn.prepareStatement( "SELECT jobcode " - + "FROM demo.persnl.employee " + + "FROM trafodion.persnl.employee " + "WHERE empnum = ?" ) ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/invent_partloc_table.sql ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/invent_partloc_table.sql b/docs/spj_guide/src/resources/source/invent_partloc_table.sql index 03ecebb..6268f7f 100644 --- a/docs/spj_guide/src/resources/source/invent_partloc_table.sql +++ b/docs/spj_guide/src/resources/source/invent_partloc_table.sql @@ -1,56 +1,56 @@ -CREATE TABLE demo.invent.partloc +CREATE TABLE trafodion.invent.partloc ( loc_code CHARACTER (3) NO DEFAULT NOT NULL , partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL , qty_on_hand NUMERIC (7) DEFAULT 0 NOT NULL -, PRIMARY KEY (loc_code,partnum) +, PRIMARY KEY ( loc_code, partnum ) ) ; -INSERT INTO demo.invent.partloc VALUES - ('A10', 2001, 800 ) -, ('A21', 255, 21 ) -, ('A34', 6201, 0 ) -, ('A35', 6301, 0 ) -, ('A36', 6400, 34 ) -, ('A66', 6603, 300 ) -, ('A67', 6401, 454 ) -, ('A78', 244, 43 ) -, ('A78', 5505, 100 ) -, ('A87', 212, 18 ) -, ('A88', 2403, 735 ) -, ('A88', 5504, 30 ) -, ('A94', 3205, 200 ) -, ('A98', 5110, 510 ) -, ('G11', 2002, 20 ) -, ('G34', 6201, 106 ) -, ('G35', 6301, 331 ) -, ('G36', 6400, 1034 ) -, ('G43', 5100, 77 ) -, ('G45', 4102, 69 ) -, ('G65', 3201, 36 ) -, ('G68', 6500, 1132 ) -, ('G76', 2405, 42 ) -, ('G76', 7301, 32 ) -, ('G78', 5505, 0 ) -, ('G87', 212, 20 ) -, ('G87', 3103, 0 ) -, ('G87', 3210, 44 ) -, ('G87', 2402, 0 ) -, ('G88', 2403, 32 ) -, ('G88', 5504, 0 ) -, ('G89', 5101, 86 ) -, ('G94', 3205, 59 ) -, ('G98', 5103, 28 ) -, ('G98', 5110, 136 ) -, ('P10', 2001, 0 ) -, ('P12', 2002, 200 ) -, ('P12', 2003, 0 ) -, ('P15', 2003, 200 ) -, ('P66', 6603, 40 ) -, ('P67', 6401, 54 ) -, ('P68', 6500, 0 ) -, ('P76', 7102, 200 ) -, ('P78', 244, 23 ) -, ('P87', 3103, 300 ) +INSERT INTO trafodion.invent.partloc VALUES + ( 'A10', 2001, 800 ) +, ( 'A21', 255, 21 ) +, ( 'A34', 6201, 0 ) +, ( 'A35', 6301, 0 ) +, ( 'A36', 6400, 34 ) +, ( 'A66', 6603, 300 ) +, ( 'A67', 6401, 454 ) +, ( 'A78', 244, 43 ) +, ( 'A78', 5505, 100 ) +, ( 'A87', 212, 18 ) +, ( 'A88', 2403, 735 ) +, ( 'A88', 5504, 30 ) +, ( 'A94', 3205, 200 ) +, ( 'A98', 5110, 510 ) +, ( 'G11', 2002, 20 ) +, ( 'G34', 6201, 106 ) +, ( 'G35', 6301, 331 ) +, ( 'G36', 6400, 1034 ) +, ( 'G43', 5100, 77 ) +, ( 'G45', 4102, 69 ) +, ( 'G65', 3201, 36 ) +, ( 'G68', 6500, 1132 ) +, ( 'G76', 2405, 42 ) +, ( 'G76', 7301, 32 ) +, ( 'G78', 5505, 0 ) +, ( 'G87', 212, 20 ) +, ( 'G87', 3103, 0 ) +, ( 'G87', 3210, 44 ) +, ( 'G87', 2402, 0 ) +, ( 'G88', 2403, 32 ) +, ( 'G88', 5504, 0 ) +, ( 'G89', 5101, 86 ) +, ( 'G94', 3205, 59 ) +, ( 'G98', 5103, 28 ) +, ( 'G98', 5110, 136 ) +, ( 'P10', 2001, 0 ) +, ( 'P12', 2002, 200 ) +, ( 'P12', 2003, 0 ) +, ( 'P15', 2003, 200 ) +, ( 'P66', 6603, 40 ) +, ( 'P67', 6401, 54 ) +, ( 'P68', 6500, 0 ) +, ( 'P76', 7102, 200 ) +, ( 'P78', 244, 23 ) +, ( 'P87', 3103, 300 ) ; -UPDATE STATISTICS FOR TABLE demo.invent.partloc ON EVERY COLUMN ; +UPDATE STATISTICS FOR TABLE trafodion.invent.partloc ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/invent_partsupp_table.sql ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/invent_partsupp_table.sql b/docs/spj_guide/src/resources/source/invent_partsupp_table.sql index 5f388c1..e5d4e1d 100644 --- a/docs/spj_guide/src/resources/source/invent_partsupp_table.sql +++ b/docs/spj_guide/src/resources/source/invent_partsupp_table.sql @@ -1,16 +1,16 @@ -CREATE TABLE demo.invent.partsupp +CREATE TABLE trafodion.invent.partsupp ( partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL , suppnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL , partcost NUMERIC (8, 2) NO DEFAULT NOT NULL , qty_received NUMERIC (5) UNSIGNED DEFAULT 0 NOT NULL -, PRIMARY KEY (partnum,suppnum) +, PRIMARY KEY ( partnum, suppnum ) ) ; CREATE INDEX XSUPORD ON partsupp ( suppnum ) ; -CREATE VIEW demo.invent.view207 +CREATE VIEW trafodion.invent.view207 ( partnumber , partdescrpt , suppnumber @@ -26,14 +26,14 @@ AS SELECT , partcost , qty_received FROM - demo.invent.partsupp x -, demo.sales.parts p -, demo.invent.supplier s + trafodion.invent.partsupp x +, trafodion.sales.parts p +, trafodion.invent.supplier s WHERE x.partnum = p.partnum AND x.suppnum = s.suppnum ; -CREATE VIEW demo.invent.view207n +CREATE VIEW trafodion.invent.view207n ( partnumber , partdescrpt , suppnumber @@ -48,12 +48,12 @@ AS SELECT , s.suppname , x.partcost , x.qty_received -FROM demo.invent.supplier s -LEFT JOIN demo.invent.partsupp x ON s.suppnum = x.suppnum -LEFT JOIN demo.sales.parts p ON x.partnum = p.partnum +FROM trafodion.invent.supplier s +LEFT JOIN trafodion.invent.partsupp x ON s.suppnum = x.suppnum +LEFT JOIN trafodion.sales.parts p ON x.partnum = p.partnum ; -CREATE VIEW demo.invent.viewcust +CREATE VIEW trafodion.invent.viewcust ( custnumber , cusname , ordernum @@ -62,18 +62,18 @@ AS SELECT c.custnum , c.custname , o.ordernum -FROM demo.sales.customer c -LEFT JOIN demo.sales.orders o ON c.custnum = o.custnum +FROM trafodion.sales.customer c +LEFT JOIN trafodion.sales.orders o ON c.custnum = o.custnum ; -CREATE VIEW demo.invent.viewcs AS SELECT +CREATE VIEW trafodion.invent.viewcs AS SELECT custname -FROM demo.sales.customer +FROM trafodion.sales.customer UNION SELECT suppname -FROM demo.invent.supplier ; +FROM trafodion.invent.supplier ; -INSERT INTO demo.invent.partsupp VALUES +INSERT INTO trafodion.invent.partsupp VALUES ( 212, 1, 2000.00, 20 ) , ( 212, 3, 1900.00, 35 ) , ( 244, 1, 2400.00, 50 ) @@ -125,4 +125,4 @@ INSERT INTO demo.invent.partsupp VALUES , ( 7301, 1, 300.00, 32 ) ; -UPDATE STATISTICS FOR TABLE demo.invent.partsupp ON EVERY COLUMN ; +UPDATE STATISTICS FOR TABLE trafodion.invent.partsupp ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/invent_schema.sql ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/invent_schema.sql b/docs/spj_guide/src/resources/source/invent_schema.sql index 9074be8..69ec911 100644 --- a/docs/spj_guide/src/resources/source/invent_schema.sql +++ b/docs/spj_guide/src/resources/source/invent_schema.sql @@ -1,2 +1,2 @@ -CREATE SCHEMA DEMO.INVENT ; -SET SCHEMA DEMO.INVENT ; +CREATE SCHEMA trafodion.invent ; +SET SCHEMA trafodion.invent ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/invent_supplier_table.sql ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/invent_supplier_table.sql b/docs/spj_guide/src/resources/source/invent_supplier_table.sql index 0fc4da3..0391052 100644 --- a/docs/spj_guide/src/resources/source/invent_supplier_table.sql +++ b/docs/spj_guide/src/resources/source/invent_supplier_table.sql @@ -1,18 +1,18 @@ -CREATE TABLE demo.invent.supplier +CREATE TABLE trafodion.invent.supplier ( suppnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL , suppname CHARACTER (18) NO DEFAULT NOT NULL , street CHARACTER (22) NO DEFAULT NOT NULL , city CHARACTER (14) NO DEFAULT NOT NULL , state CHARACTER (12) NO DEFAULT NOT NULL , postcode CHARACTER (10) NO DEFAULT NOT NULL -, PRIMARY KEY (suppnum) +, PRIMARY KEY ( suppnum ) ) ; CREATE INDEX xsuppnam ON supplier ( suppname ) ; -INSERT INTO demo.invent.supplier VALUES +INSERT INTO trafodion.invent.supplier VALUES ( 1, 'NEW COMPUTERS INC', '1800 KING ST.', 'SAN FRANCISCO', 'CALIFORNIA', '94112' ) , ( 2, 'DATA TERMINAL INC', '2000 BAKER STREET', 'LAS VEGAS', 'NEVADA', '66134' ) , ( 3, 'HIGH DENSITY INC', '7600 EMERSON', 'NEW YORK', 'NEW YORK', '10230' ) @@ -32,4 +32,4 @@ INSERT INTO demo.invent.supplier VALUES , ( 186, '186 Disk Makers', '186 Dis Way', 'Dat Way', 'Wisconsin', '00186' ) ; -UPDATE STATISTICS FOR TABLE demo.invent.supplier ON EVERY COLUMN ; +UPDATE STATISTICS FOR TABLE trafodion.invent.supplier ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/lowerPrice.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/lowerPrice.java b/docs/spj_guide/src/resources/source/lowerPrice.java index fd25ecd..b49ff2f 100644 --- a/docs/spj_guide/src/resources/source/lowerPrice.java +++ b/docs/spj_guide/src/resources/source/lowerPrice.java @@ -12,14 +12,14 @@ public static void lowerPrice() throws SQLException PreparedStatement getParts = conn.prepareStatement( "SELECT p.partnum, " + "SUM(qty_ordered) AS qtyOrdered " - + "FROM demo.sales.parts p " - + "LEFT JOIN demo.sales.odetail o " + + "FROM trafodion.sales.parts p " + + "LEFT JOIN trafodion.sales.odetail o " + "ON p.partnum = o.partnum " + "GROUP BY p.partnum" ) ; PreparedStatement updateParts = - conn.prepareStatement( "UPDATE demo.sales.parts " + conn.prepareStatement( "UPDATE trafodion.sales.parts " + "SET price = price * 0.9 " + "WHERE partnum = ?" ) ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/numDailyOrders.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/numDailyOrders.java b/docs/spj_guide/src/resources/source/numDailyOrders.java index d7aab43..568d71f 100644 --- a/docs/spj_guide/src/resources/source/numDailyOrders.java +++ b/docs/spj_guide/src/resources/source/numDailyOrders.java @@ -12,7 +12,7 @@ public static void numDailyOrders( Date date PreparedStatement getNumOrders = conn.prepareStatement( "SELECT COUNT(order_date) " - + "FROM demo.sales.orders " + + "FROM trafodion.sales.orders " + "WHERE order_date = ?" ) ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/resources/source/numMonthlyOrders.java ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/resources/source/numMonthlyOrders.java b/docs/spj_guide/src/resources/source/numMonthlyOrders.java index 605f605..942ea01 100644 --- a/docs/spj_guide/src/resources/source/numMonthlyOrders.java +++ b/docs/spj_guide/src/resources/source/numMonthlyOrders.java @@ -23,7 +23,7 @@ public static void numMonthlyOrders( int month PreparedStatement getNumOrders = conn.prepareStatement( "SELECT COUNT( month( order_date ) ) " - + "FROM demo.sales.orders " + + "FROM trafodion.sales.orders " + "WHERE month( order_date ) = ?" ) ;
