You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by db...@apache.org on 2016/04/08 20:51:07 UTC
[08/14] incubator-trafodion git commit: Backup checkin
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 ) = ?"
) ;