Instant Java Stored Procedures (Just Add Queries)

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Instant Java Stored Procedures (Just Add Queries)

In the last article, it was shown how a user can create a Java External Stored Procedure (JXSP) that reads SQL using answers sets with the Teradata Plug-in for Eclipse. This article 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.

The Java Bean Wrapper wizard allows the user to quickly generate a Java Bean class for a given SQL statement or stored procedure. The Java Bean Wrapper Wizard has an option to create a JXSP that calls the wrapped SQL from the Java Bean.

Prerequisite for this article

This article requires a Teradata Database version 13.0 or greater. Also if you have not work through the example shown in the last article in this series Using Answer sets with Java Stored Procedures and the Teradata Plug-in for Eclipse, do so now before you continue.

SQL Query

This article is going to use the same table and query used in the last example “select * from employee where empID = ?”. This query will use the same kind of parameterization JDBC uses, replacing actual values with question marks.

Create SQL File

Go to the Java Default view and select the project “terdata_jxsp” that has been created for the previous examples. Right click and select the menu item New->Other…. This will bring up the Select Wizard. In the Wizard tree, select SQL Development-> SQL File.

Now select the “Next” button. Enter “jxp_sql” in the “File name” text box. Now select the “Finish” button.

SQL Editor

The SQL Editor should appear in the Eclipse IDE. Go to the connection profile group and select “Terdata_13.x” from the “Type” combo box. Now select the profile name you created for the last example from the “Name” combo box. Go to the text area in the SQL editor and enter the query for this example “select * from employee where empID=?”.

Launch Java Bean Wrapper Wizard

Now select the SQL Query and right click. Select the menu item “Create a Java Bean…”. You should now see the "Class Specification" page for the Java Bean Wizard. Enter for your Source folder “teradata_jxp/src/java”. Enter “jxsp” for package name and enter “GetEmployeeById” for class name. Now select the toggle button “Call From a Java Stored Procedure”.

Generate Helper Classes

Select the "Next" button until you get to the “Generate Helper Classes” Wizard Page. This page uses the meta data from your query to generate helper classes for the Java Bean.

Select the generate button and the "Configure Parameters" dialog will pop up. Enter “1” in the value column of the dialog and select the “OK” button. Now select the "Next" button on the “Generate Helper Classes” Wizard Page.

Edit Classes

You will now be on the "Edit Classes" Wizard Page. Select “GetEmployeeId” in the “Classes” list. Now go to the table at the bottom of the page and replace the text “parameter1” with the text “id” in the “Member Name” column in the table. Once this is done, select the “Next” button.

Connection

You should be on the "Define Connection" Wizard page. The JXSP that is generated will use a default connection but the Java Bean Wrapper Bean is generic and it can also be called from the client. In this case, this is useful because it allows you to debug the JXSP on the client instead of the server. Select the “Use Driver Manager” radio button. Then also select the “By caller of execute method”radio button. Once this is done, select the “Finish” button. 

Launch JXSP Wizard.

Once the “Finish” button is selected for the Java Bean Wrapper Wizard, You will see a pop up dialog asking if you want to Launch the JXSP Wizard. The Java Bean Wrapper code has generated the code for the Bean. A JXSP method is created inside of the generated Class which calls the Bean it self. By selecting “Yes” for the popup dialog the JXSP Wizard imports the existing JXSP you just created. 

JXSP 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 "GetEmployeeByID". Once you have done this, select the “Next” button.

JXSP Connection

You will now see the “Connection” page for the JXSP Wizard. You will select the database server and schema for your JXSP

Select the “Change Connection” button. The “Connection Specification” dialog will pop up. Select the database server for the JXSP from the “Connection” combo box. Once this is done, select the “OK” button.

On the "Connection" Wizard page select the Schema you wish to install your JXSP from the combo box. Now select the “Next” button.

Existing JXSP

You will now see the “Existing Java External Stored Procedure” Wizard page. This page will show the Source file location of the Java Bean Wrapper class file you are importing into the JXSP Multi-Page Editor. Now select the “Finish” button.

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.

Source Page

Select the “Source” tab in the Multi-Page Editor. You will see the generated source for the Java Bean Wrapper. Scroll down to the “getEmployeeByIDProc” method in the Source page. This is your JXSP. It uses a default connection and creates an instance of the Java Bean Wrapper. It executes the Java Bean Wrapper and returns the result sets as answer sets.

Deploy JAR

On the “JAR Files” page, select the “Deploy” button. This will deploy the JAR for the JXSP on the database server.

Install DDL

Select the “Run SQL” button on the “SQL” page. This will install your JXSP on the database server.

Run Procedure

Go to the DTP tree in the Data Source Explorer and expand it to the schema in which you installed the JXSP. Select the Store Procedures tree node and right click. Now select the "Refresh" menu item. Select the procedure “getEmployeeByIDProc” 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.

Results

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.

Conclusion

This article has shown how a user can create a JXSP that reads SQL using the Java Bean Wrapper Wizard. The generated code from the Java Bean Wrapper Wizard gives the user a quick and easy way to create a JXSP.