Creating a Simple Java Stored Procedure using the Teradata Plug-in for Eclipse

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

Creating a Simple Java Stored Procedure using the Teradata Plug-in for Eclipse

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

10 REPLIES
Teradata Employee

Re: Creating a Simple Java Stored Procedure using the Teradata Plug-in for Eclipse

This is a great article -- thank you!
Teradata Employee

Re: Creating a Simple Java Stored Procedure using the Teradata Plug-in for Eclipse

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.

Enthusiast

Re: Creating a Simple Java Stored Procedure using the Teradata Plug-in for Eclipse

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.
Teradata Employee

Re: Creating a Simple Java Stored Procedure using the Teradata Plug-in for Eclipse

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
Enthusiast

Re: Creating a Simple Java Stored Procedure using the Teradata Plug-in for Eclipse

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

Re: Creating a Simple Java Stored Procedure using the Teradata Plug-in for Eclipse

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.
Teradata Employee

Re: Creating a Simple Java Stored Procedure using the Teradata Plug-in for Eclipse

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
Teradata Employee

Re: Creating a Simple Java Stored Procedure using the Teradata Plug-in for Eclipse

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!
Enthusiast

Re: Creating a Simple Java Stored Procedure using the Teradata Plug-in for Eclipse

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