Using Answer sets with Java Stored Procedures and the Teradata Plug-in for Eclipse

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.

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.

Run SQL

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.

Enter Data

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.

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.

Source Page

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.

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

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.

Run JXSP

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.

Save Source

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.

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

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.

java.lang.NullPointerException at com.teradata.datatools.jxsp.JXSPJavaReflectionUtil.filterTableCols(JXSPJavaReflectionUtil.java:1133) at com.teradata.datatools.jxsp.JXSPJavaReflectionUtil.getMethodProps(JXSPJavaReflectionUtil.java:991) at com.teradata.datatools.jxsp.JXSPJavaReflectionUtil.getMethodProperties(JXSPJavaReflectionUtil.java:1170) at com.teradata.datatools.jxsp.JXSPJavaReflectionUtil.updateAttributesFromJavaSource(JXSPJavaReflectionUtil.java:269) at com.teradata.datatools.jxsp.editors.JXSPMultiPageEditor.updateAttributesFromSource(JXSPMultiPageEditor.java:568) at com.teradata.datatools.jxsp.editors.JXSPMultiPageEditor$6.run(JXSPMultiPageEditor.java:771) at org.eclipse.swt.widgets.RunnableLock.run(RunnableLock.java:35) at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Synchronizer.java:135) at org.eclipse.swt.widgets.Display.runAsyncMessages(Display.java:4140) at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3757) at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:2701) at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:2665) at org.eclipse.ui.internal.Workbench.access$4(Workbench.java:2499) at org.eclipse.ui.internal.Workbench$7.run(Workbench.java:679) at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332) at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:668) at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:149) at org.eclipse.ui.internal.ide.application.IDEApplication.start(IDEApplication.java:123) at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196) at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110) at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79) at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:344) at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:179) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:622) at org.eclipse.equinox.launcher.Main.basicRun(Main.java:577) at org.eclipse.equinox.launcher.Main.run(Main.java:1410)

when I try to save it mentions that the name is a constructor and wants me to remove the "static void" which then says can't be used as a stored procedure. What am I missing? Not very fluent with Java, just trying to learn a few things.

Re: Using Answer sets with Java Stored Procedures and the Teradata Plug-in for Eclipse

Assuming the bug is in the plug-in and if so is there a workaround? Such as creating the input value at the beginning instead of doing in from the code itself? Also, what is the general availability of the next release?

Re: Using Answer sets with Java Stored Procedures and the Teradata Plug-in for Eclipse

Creating the input value at the beginning using the Java Stored Procedure Wizard instead of doing in from the code itself is a good work around. The problem seems to be intermittent so you might try it again to see if it works. I have made a bug fix to prevent the problem. It will be available first quarter of 2013.