In the last article, it was shown how a user can create an ant script to automate a build so the user can deploy a JAR and install a DDL for a Java External Stored Procedure (JXSP) outside of Eclipse. This article will show how a user can create a JXSP that reads SQL using answers sets.
Answer sets are extended result sets returned by stored procedures. This is a new feature for JXSPs in the 13.0 version of the Teradata database.
If you have not work through the example shown in the last article Creating a Simple Java Stored Procedure using the Teradata Plug-in for Eclipse, do so now before you continue. Also It is required that you use a 13.0 Teradata database for the example in this article.
Create a Table
In this article, a table will need to be created for a schema because the JXSP will be accessing the database using a SQL select statement on a table.
Launch Table Creation Dialog
Go to the top of the Eclipse IDE and select the pull down menu Window ->Perspective->Other…->Database Development. Select the database profile you created in the example for the first article in the Data Source Explorer. Right click and select the menu item "Connect". Open the tree in the Explorer and select the “Table” tree node for the schema in which you wish to create your table. Right click on the “Table” tree node and select Teradata->Create Table.
Create Table Dialog
At this point, you should have the “Create Table” dialog up. Enter "Employee" in the table name field in the dialog. Now go to the “Column Name” field and enter "empID". Now select the “INTEGER” data type. Go to the “Value must be unique” toggle and select it. Hit the “Apply” button. Now hit the “New” button in the columns list. Enter "empName" in the Column name field. Select the VARCHAR data type and enter 30 for the size of the type option. Hit the “Apply” button. Now hit the “New” button in the columns list. Enter "empDept" in the Column name field. Select the VARCHAR data type and enter 30 for the size of the type option. Hit the “Apply” button. Now hit the “New” button in the columns list. Enter "empJob" in the Column name field. Select the VARCHAR data type and enter 300 for the size of the type option. Hit the “Apply” button one final time.
Now select the “SQL Preview” Tab in the Create Table Dialog and select the “Run SQL” button. This will create the table under the selected schema.
The new table now will require data. Go to the DTP tree in the Data Source Explorer and select the "Table" tree node “employee”. Right click the table node and the select Data->Edit menu item. The Edit data table will appear in the Eclipse dialog. Enter the data that appears in the image below. Once you are done right click and select the “Save” menu item.
Launch JXSP Wizard
Go to the DTP tree in the Data Source Explorer and select the “Stored Procedures” tree node for the schema in which you wish to create your JXSP. Right click on the “Stored Procedures” tree node and select the menu item Teradata->Create Java Stored procedure.
Java Stored Procedure Wizard
At this point the JXSP Wizard will come up. You will need to enter the container name "/terdata_jxsp" and the name of the JXSP properties file "GetEmployee". Once you have done this, select the “Next” button.
JXSP Class Definition
The next page is where the JXSP class is defined. Enter the source folder “terdata_jxsp/src/java”, package name “jxsp” and the class name “GetEmployee”. Once this is done hit the "Next" button.
Define JXSP Method
This page allows the user to define the method for the JXSP.
Enter the method name “getEmployee”. Once this is done hit the "Next" button.
Define Answer sets
This page allows the user to define answer sets used for a JXSP.
Select the “New” button on the “Answer Sets Parameters” Wizard page. The “New Answer set Parameter” Dialog will pop up. Enter “answer1” in the popup dialog text area.
Once you have done this, hit the “OK” button. The Answer Sets Parameters list will be updated with the new answer set parameter. JXSPs can return multiple result sets and it is possible to add multiple answer sets on this Wizard page.
Set DDL options
Now hit the “Next” button until you get to the DDL Options Wizard page. Select the option “Reads SQL Data”. This option means you will be executing read only SQL from your JXSP. Now hit the "Finish" button and the JXSP Multi-Page Editor will be launched.
JXSP Multi-Page Editor
The JXSP Multi-Page Editor will be brought up with the contents of the JXSP properties after the "Finish" button is selected in the JXSP Wizard. The first page of the JXSP Multi-Page Editor shows the class definition of the new JXSP.
Select the “Source” page tab of the Multi-Page Editor. In the "Source" page, you will see the answer set parameter defined in the wizard. Also you will see the JDBC connection in the generated source code.
The connection URL being used is "jdbc:default:connection". This creates a default connection that participates in the caller's session and the current transaction. No logoff occurs when the connection's close method is called since the default connection uses the same session as the caller. The default connection is only accessible from one thread, the thread that invoked the JXSP.
Edit Source Code
Now the code needs to be edited to access the database. A SQL query and the JDBC API calls must be added to the source so the JXSP can return answer sets. Add the java code shown below in your "Source" page in the editor.
Now go to the “JAR Files” page in the editor. A popup dialog will come up asking you if you want to save the source you just changed. Select the “Yes” button.
On the “JAR Files” page, select the “Deploy” button. This will deploy the JAR for the JXSP on the database server.
Go to the “SQL” page in the editor. Observe in the generated SQL the line of code “DYNAMIC RESULT SET 1”. This section of the SQL statement tells the JXSP to return a dynamic result set or an answer set.
Select the “Run SQL” button on the “SQL” page. This will install your JXSP on the database server.
Once your JXSP is installed, you can run it. Go to the DTP tree in the Data Source Explorer and select the “Store Procedures” tree node in which you launched the Wizard. Now right click and select the “Refresh” menu item. You will now see the “getEmployee” procedure. Select the procedure and right click. Now select the "Run" menu item. Your results of running your JXSP will end up in the bottom of the Eclipse IDE. Select the "Result1" tab and see the results of the execution of your JXSP. The answer set returns all of results of the JXSP.
Add a Parameter to the JXSP
Since the procedure name is getEmployee lets change the JXSP to only return a single result set based on employee ID. Go to the “Source” page of the Multi-Page Editor and change the Java source code and add a parameter called "empID" with the Java data type of int. Now add the where clause “where empID = ?” to the SQL query in the source code. Also add the JDBC API calls for parameterization using “empId”. An example of this is shown below.
Now go to the “Parameters” page of the Editor. A popup dialog will come up asking you if you want to save the source you just changed. Select the "Yes" button. You will see the parameter “empID” added to the list on the “Parameters” Page.
Now go to the “JARs File” Page and select the "Deploy" button to re-install your JAR. Then go to the “SQL” page and install your DDL by selecting the "Run SQL" button.
Run Modified Procedure
Go back to the DTP tree in the Data Source Explorer and select the "Store Procedures" tree node. Now right click and select the "Refresh" menu item. Select the procedure “getEmployee” and right click. Now select the "Run" menu item. A popup dialog will come up called "Configure parameters". Enter in the value column of the Dialog “100001” and hit the “OK” button.
Your results of running your JXSP will end up in the bottom of the Eclipse IDE. Select the "Result1" tab and see the results of the execution of your JXSP.
In this article, it was shown how a user can create a JXSP that reads SQL using answers sets with the Teradata Plug-in for Eclipse. The next article in this series will show how a JXSP can be automatically created that reads SQL. The only thing that is required is a SQL query from the user and the content of the JXSP is automatically generated.