A Java External Stored Procedure (JXSP) is a program written in Java that is kept and executed with in a database server. Java stored procedures are implemented as external stored procedures. This means the source is compiled externally to the DBS and kept in Java Archive (JAR) files. These JAR files are installed into the database using stored procedures in the SQLJ database. Once a JAR file is installed the JXSP can be defined to use the Java class and methods within the JAR file. The JXSP are executed via a protected mode server separate from the database process.

A JXSP Provides an advantage over Java applications that access the database directly in that a JXSP can take advantage of the existing database access control security. A JXSP minimizes the overhead of network latency because it is operating closer to the data. Also Java stored procedures can perform faster than Java applications running in an application server because they are running inside their own dedicated Java Virtual Machine (JVM).

This article will show how the Teradata Plug-in for Eclipse facilitates the process of creating and installing the Java parts and DDL for a JXSP using the JXSP Wizard and Multi-page Editor.

Prerequisite for this Article
If you have not work through the guide Getting Started with Teradata Plug-in for Eclipse, do so now before you continue.


Using the JXSP Wizard
In this example, you will be creating a JXSP which takes a full name and returns a last name from the full name. You will use the JXSP Wizard to implement this JXSP.

Create a Java Project
You will need to create a Java project to store the Java source of the JXSP. Go to the top of the Eclipse IDE and select the pull down menu Window ->Perspective->Other... ->Java (Default). Then go to the package explorer in Eclipse and right click New->Java Project. Enter “teradata_jxsp” for the Java project name. Then select the "Next" button.


 

 Remove “src” Folder
In this example, the “src” folder is going to be deleted and the “src/java” folder will be added. This is done to give the Java project structure. Later in following articles different source folders will be added such as “src/config” and “src/test”.

Now remove your default source folder by selecting “src” and then picking the "Remove source folder" option.


Add “src/java” Folder
Now select the “Create a new source folder” option and enter “src/java” in for the folder name and select “Finish”.


Now hit “Finish” again in the New Project Wizard.

Launch JXSP Wizard
Go to the top of the Eclipse IDE and select the pull down menu Window ->Perspective->Other…->Database Development. Select the database you created a profile for in the Data Source Explorer. Right click on the database node and select connect. Open the tree in the Explorer and select the “Stored Procedures” node for the schema in which you wish to create your JXSP. Right click on the “Stored Procedures” node and select Teradata->Create Java Stored procedure.
 

 

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

 

JXSP Class Definition
The next page is where the Java stored procedure class is defined. Enter the source folder “terdata_jxsp/src/java”, package name “jxsp” and the class name “LastName”. It is the best practice not to use the default package name. Once this is done, hit the “Next” button.


Define JXSP Method
This page allows the user to define the method for the JXSP. SQL data types are used to define the parameter types in this panel. The SQL data types are mapped to specific Java types as shown in Appendix A.

Now enter the method name “getLastName”. Next go to the "New" button for the parameter list and then add the parameter name “name”. Pick the data type VARCHAR. Enter in the size of 256 and hit the "Apply" button. Now hit the new button again and enter the parameter name “lastName”. Select the data type of VARCHAR and enter the size of 256. Next go to the "Parameter Mode" list and select the "OUT" option. Once this is done, select the "Next" button.
 

Set DDL Options
Now hit the "Next" button until you get to the “DDL Options” Wizard page. This Wizard page allows the user to specify the procedure name and “SQL Data Access” type in the DDL for a JXSP. The SQL data access type specifies how the JXSP is going to execute SQL.

Now select the option "No SQL". This option means you will not be executing any SQL from your JXSP. Now hit the "Finish" button and the JXSP Multi-Page Editor will be launched.


Using the JXSP Multi-Page Editor
The JXSP Multi-Page Editor brings up the contents of the JXSP properties defined in the JXSP Wizard. The Multi-Page editor allows the user to edit and deploy a JXSP.


 

Java Stored Procedure Source
Go to the source page of the editor. You will see the generated Java code for the stored procedure. In this case, it is up to you to enter the content of the JXSP.


Change Source
Edit the Java source on this page entering the code shown below to get the last name from a full name.
 

Then 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, you will see the JAR file and JAR ID for the JXSP. 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. Select the “Run SQL” button. This will install your JXSP on the database server.


Run Java Stored Procedure
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 menu item "Refresh". You will now see the getLastName procedure. Select the procedure 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 “John Smith” and hit the “OK” button.
 

Results
Your results of running your JXSP will end up in the bottom of the Eclipse IDE. Select the "Parameters" tab and see the results of the execution of your JXSP.
 

Conclusion
This article has shown how Teradata Plug-in for Eclipse facilitates the process of creating and installing the Java parts and DDL for a JXSP using the JXSP Wizard and Multi-page Editor. The next article in this series will show how a user can create an Ant script to automate the build process so the user can deploy a JAR and install a DDL for a JXSP outside of Eclipse.


Appendix A
SQL Data Types Mapping
The following table defines how data is mapped between SQL and Java. The SQL data type is converted to/from the corresponding Java data type based on the type of parameter mapping. The XSP defaults to simple mapping. The user may specify object mapping via the External Name clause.
 

SQL data type

Java data type

Simple map (Java primitive symbol)

Object map

CHARACTER

N/A

Java.lang.String

VARCHAR

 N/A

Java.lang.String

NUMERIC

 N/A

Java.math.BigDecimal

DECIMAL

N/A

Java.math.BigDecimal

BIGINT

 long(J)

Java.lang.Long

SMALLINT

short (S)

Java.lang.Short

INTEGER

int (I)

Java.lang.Integer

REAL

double (D)*

Java.lang .Double

FLOAT

double (D)*

Java.lang .Double

DOUBLE PRECISION

double (D)

Java.lang .Double

BYTE

 byte[] ([B)

N/A

VARBYTE

 byte[] ([B)

N/A

DATE

 N/A

Java.sql.Date

TIME

N/A

Java.sql.Time*

TIMESTAMP

N/A

Java.sql.Timestamp

BYTEINT

byte (B)

Java.lang.Byte

CLOB

 N/A

Java.sql.Clob

BLOB

N/A

Java.sql.Blob

VARGRAPHIC

 N/A

N/A

GRAPHIC

 N/A

N/A

INTERVAL

N/A

Java.lang.String

PERIOD**

N/A

N/A

UDT**

N/A

N/A

 

Discussion
JA185041 1 comment Joined 03/08
29 Oct 2009

This is a great article -- thank you!

js185064 48 comments Joined 03/09
28 May 2010

Did you know you can import existing Java Stored Procedures into the JXSP Multi-Page Editor with the JXSP Wizard using the Teradata Plug-in for Eclipse? When you use the import feature, your JXSP must have a Java Package. Do not use the default package when importing.

ND_Siouxfan 5 comments Joined 05/09
06 Jul 2010

I've got the plug-in installed and am browsing. However, when I try to create a java stored proc the error log gives an error "unhandled event loop exception". I'm running the Helios release.

js185064 48 comments Joined 03/09
06 Jul 2010

Try the following:

Make sure you have installed the current release of Teradata Plug-in for Eclipse
http://downloads.teradata.com/download/tools/teradata-plug-in-for-eclipse-13-02

Install the Java EE version of Eclipse (This already includes all required Plug-ins except for the Teradata Plug-in for Eclipse)
http://www.eclipse.org/downloads/packages/eclipse-ide-java-ee-developers/heliosr

Create a Teradata Project instead of a Java Project for your Java Stored procedure

ND_Siouxfan 5 comments Joined 05/09
08 Jul 2010

I didn't get that to work.... but I reverted back to 3.5.2 and everything works great! Thanks!

fhdaniels 7 comments Joined 05/09
22 Sep 2010

I tried to follow the procedure you outlined, managed to get the jar installed in the database, but when i tried to run the sql to create the stored procedure i got an error message: A java method in the specified jar which matches that in the external name clause was not found.

js185064 48 comments Joined 03/09
22 Sep 2010

Make sure you have compiled your Java code with JDK 1.5. The Teradata project in Teradata Plug-in for Eclipse will take care of this for you. (See article below)
http://developer.teradata.com/tools/articles/create-a-teradata-project-using-the-teradata-plug-in-for-eclipse

fhdaniels 7 comments Joined 05/09
22 Sep 2010

ok it is working now, thanks. i had to upgrade the plug in from 13.1 to 13.2, and then I also had to change the JDK from 1.6 to 1.5. After that everything worked my and I got getLastName up and running on TD13 VMware!

vkbagare 6 comments Joined 02/10
19 Jun 2012

Do I have to downgrade to 1.5 from 1.7 java version for me to compile the procedure

Best,
Vinay Bagare

js185064 48 comments Joined 03/09
19 Jun 2012

Any Teradata Database 14.0 or higher supports JDK 1.6 for Java Stored Procedures. Any Teradata Database below 14.0 uses JDK 1.5 for Java Stored Procedures. If you use a Teradata project, your compiler compliance level will be set to JDK 1.5 automatically. If you use a Java Project, you will have to change the compliance level your self. This can be done by bringing up the properties dialog for your Java Project.

*(Databases below version 12.00 do not support Java Stored Procedures)

You must sign in to leave a comment.