Automating a Build for Java Stored Procedures 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

Automating a Build for Java Stored Procedures using the Teradata Plug-in for Eclipse

In the last article, it was shown how Teradata Plug-in facilitates the process of creating and installing the Java parts and DDL for a Java External Stored Procedure (JXSP) using the JXSP Wizard and Multi-page Editor. In this article, it will be shown how a user can create an Ant script to automate the build process for a JXSP in the Teradata Plug-in for Eclipse.

The automated build that is created by Teradata Plug-in for Eclipse allows the user to deploy a JAR and install a DDL for a JXSP outside of Eclipse by using Ant. This will allow the user to have a command line install script. This will also give the user a way to incorporate a JXSP into a larger project.

Prerequisite for this article

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.

Bring up JXSP Multi-Page Editor

In the last article, the JXSP “getLastName” was created. If the JXSP Multi-Page Editor is not up for this JXSP, double click on the “LastName.jxsp” properties file in the Package Explorer from the Java Project "terdata_jxsp".

Now select the “SQL” tab in the JXSP Multi-Page Editor.

Create Automated Build

Once you are on the “SQL” tab, select the “Automate Build” button. Once this is done, the Automated Build Wizard will popup. Select the “Finish” button.

Automated Build Files

The following are files created for the automated build:

  • XXXXDrop.sql – Drops the installed JAR and JXSP
  • XXXXRemoveJars.sql – Drops the installed JARs if additional JARs were selected in the JXSP editors
  • XXXXInstall.sql - Install the JAR for JXSP
  • XXXXInstallJars.sql – Install the JARs for Alter path if additional JARs were selected in the JXSP editors
  • XXXXAlterPath.sql - Alter path for JXSP if additional JARs were selected in the JXSP editors
  • XXXXCreate.sql - Create JXSP DDL
  • jdbc.properties - Properties to run JDBC for an Ant build script
  • build.xml – Ant build script for JXSP

Note: XXXX is the JXSP class name to identify files uniquely. (See the files for the example shown in this article in Appendix A)

Run Ant Build

Eclipse comes with Ant but if you going to run Ant outside of Eclipse you will need to install it. You can download Ant from the Apache web site. Unzip the files to any directory. Then add the path to the Ant “bin” directory to your PATH environment variable.

The build XML file can be run from the project directory. The following is sample command line usage of the build XML with Ant:

ant BuildAll

This command drops any JXSP that is associated with the current build. Then this command will compile all class files, create a JAR and install any JAR, function or procedure associated with the automated build.

Run Ant Build from Eclipse

You can run an Ant script from Eclipse by using the following steps:

  • Select the build.xml script in the Package Explorer from the Java project “teradata_jxp”.
  • Right click on the build.xml and select Run As -> External Tools Configuration…
  • In the external tools dialog, select ant build and right click the “New” menu .
  • In the Arguments field, enter BuildAll and select the “Apply” button .
  • Now select the “Run” button .

Confirm Build

You can confirm your automated build was successful by selecting the console tab at the bottom of your Eclipse IDE. Maximize the “Console” tab and observe each step run by the Ant script. At the bottom of the console there should be a message that the build was successful.

Run JXSP

Once your JXSP is installed by using your “build.xml” file, 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 “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 a user can create an Ant script to automate a build so the user can deploy a JAR and install a DDL for a JXSP outside of Eclipse. The next article in this series will show how a user can create a JXSP that reads SQL using answers sets.

Appendix A

Sample Files

LastNameDrop.sql – Drops the installed JAR and JXSP 

DROP PROCEDURE  "guest"."getLastName";
COMMIT;
DATABASE "guest";
COMMIT;
CALL SQLJ.REMOVE_JAR('LastNameJarId',0);
COMMIT;

LastNameInstall.sql - Install the JAR for JXSP

DATABASE "guest";
COMMIT;
CALL SQLJ.INSTALL_JAR('cj!LastNameJarFile.jar', 'LastNameJarId', 0);
COMMIT;

LastNameCreate.sql - Create JXSP DDL

REPLACE PROCEDURE "guest"."getLastName" (
IN "name" VARCHAR(256),
OUT "lastName" VARCHAR(256))
LANGUAGE JAVA
NO SQL
PARAMETER STYLE JAVA
EXTERNAL NAME 'LastNameJarId:jxsp.LastName.getLastName(java.lang.String,java.lang.String[])';

jdbc.properties - Properties to run JDBC for an Ant build script

# Properties file with JDBC-related settings.

# JDBC-related settings.
jdbc.driverClassName=com.teradata.jdbc.TeraDriver
jdbc.url=jdbc:teradata://duck/CHARSET=UTF8,TMODE=ANSI
jdbc.username=guest
jdbc.password=please

build.xml – Ant build script for JXSP

<?xml version="1.0"?>

<project name="teradata_jxsp-getLastName" basedir="." default="Usage">

<property name="name" value="teradata_jxsp-getLastName)"/>

<!-- Define the Usage ANT Target -->
<target name="Usage">
<echo message=""/>
<echo message="${name} build file"/>
<echo message="--------------------------------------------"/>
<echo message=""/>
<echo message="Available targets are:"/>
<echo message=""/>
<echo message="Build --> Build the application"/>
<echo message="Clean --> Clean the workspace"/>
<echo message="Create-getLastName --> Create-getLastName"/>
<echo message="Drop-getLastName --> Drop-getLastName"/>
<echo message="Install-getLastName --> Install-getLastName"/>
<echo message="BuildAll --> Build All the components for getLastName JXSP"/>
<echo message="Usage --> Illustrate Usage of the build file"/>
<echo message=""/>
</target>

<!-- Define the property names for the teradata_jxsp project -->
<property name="teradata_jxsp.dir" value="../teradata_jxsp"/>
<property name="teradata_jxsp.src.dir" value="../teradata_jxsp/src/java/jxsp"/>
<property name="teradata_jxsp.build.dir" value="../teradata_jxsp/bin"/>
<property name="teradata_jxsp.sql.dir" value="../teradata_jxsp/sql"/>
<property name="teradata_jxsp.config.dir" value="../teradata_jxsp/src/config"/>
<property name="teradata_jxsp.jarbuild.dir" value="../teradata_jxsp"/>
<property name="jdbclib.terajdbc4" value="C:\TeraJDBC\terajdbc4.jar"/>
<property name="jdbclib.tdgssconfig" value="C:\TeraJDBC\tdgssconfig.jar"/>

<!-- Include the jdbc.properties -->
<property file="${teradata_jxsp.config.dir}/jdbc.properties"/>

<!-- Define the master-classpath for teradata_jxsp Java Compilation -->
<path id="master-classpath">
<pathelement location="${jdbclib.terajdbc4}"/>
<pathelement location="${jdbclib.tdgssconfig}"/>
<fileset dir="${teradata_jxsp.build.dir}">
<include name="*.jar"/>
</fileset>
<fileset dir="${teradata_jxsp.jarbuild.dir}">
<include name="*.jar"/>
</fileset>
</path>

<!-- Define the install-classpath used for installing JXSP JAR's -->
<path id="install-classpath">
<!-- Add the Development Path Libraries used by all applications -->
<pathelement location="${jdbclib.terajdbc4}"/>
<pathelement location="${jdbclib.tdgssconfig}"/>
<dirset dir="${teradata_jxsp.jarbuild.dir}/."/>
</path>

<!-- In the Eclipse Ant View Right Click on the desired build Target i.e. deploy
and Select Run As -> External Tools configuration
In the JRE Tab select the Runtime JRE: Run in the same JRE as the workspace -->
<target name="RefreshEclipse" if="eclipse.running">
<echo>"Refreshing Eclipse Workspace"</echo>
<eclipse.refreshLocal resource="" depth="infinite"/>
</target>

<!-- Define the Clean ANT Target -->
<target name="Clean" description="Clean output directories">

<!-- Clean teradata_jxsp build directory -->
<delete>
<fileset dir="${teradata_jxsp.build.dir}">
<include name="**/*.class"/>
<include name="**/*.jar"/>
</fileset>
<fileset dir="${teradata_jxsp.jarbuild.dir}">
<include name="**/LastNameJarFile.jar"/>
</fileset>
</delete>
<antcall target="RefreshEclipse"/>
</target>

<!-- Define the Build teradata_jxsp ANT Target -->
<target name="Build" depends="Clean">

<!-- Build the teradata_jxsp Project -->
<mkdir dir="${teradata_jxsp.build.dir}"/>
<javac destdir="${teradata_jxsp.build.dir}" source="1.5" target="1.5" debug="true"
deprecation="false" optimize="false" failonerror="true">
<src path="${teradata_jxsp.dir}"/>
<include name="src/java/jxsp/LastName.java"/>
<classpath refid="master-classpath"/>
</javac>

<!-- Create the teradata_jxsp.jar file -->
<jar destfile="${teradata_jxsp.jarbuild.dir}/LastNameJarFile.jar"
basedir="${teradata_jxsp.build.dir}"
includes="jxsp/LastName.class"
/>
<antcall target="RefreshEclipse"/>
</target>

<!-- Define the Install-getLastName ANT Target -->
<target name="Install-getLastName" depends="Build">
<echo message="Install Jar for JXSP: ${jdbc.driverClassName} ${jdbc.url}"/>
<property name="myclasspath" refid="install-classpath"/>
<echo message="${myclasspath}" />
<sql driver="${jdbc.driverClassName}"
url="${jdbc.url}"
userid="${jdbc.username}"
password="${jdbc.password}"
onerror="stop"
delimiter=";"
classpathref="install-classpath"
src="${teradata_jxsp.sql.dir}/LastNameInstall.sql">
</sql>
</target>

<!-- Define the Create-getLastName's ANT Target -->
<target name="Create-getLastName">
<property name="myclasspath" refid="install-classpath"/>
<echo message="${myclasspath}" />
<echo message="CREATE JXSP USING: ${jdbc.driverClassName} ${jdbc.url}"/>
<sql driver="${jdbc.driverClassName}"
url="${jdbc.url}"
userid="${jdbc.username}"
password="${jdbc.password}"
onerror="stop"
delimiter=";"
src="${teradata_jxsp.sql.dir}/LastNameCreate.sql">
<classpath refid="install-classpath"/>
</sql>
</target>

<!-- Define the Drop-getLastName's ANT Target -->
<target name="Drop-getLastName">
<echo message="Drop getLastName JXSP USING: ${jdbc.driverClassName} ${jdbc.url}"/>
<sql driver="${jdbc.driverClassName}"
url="${jdbc.url}"
userid="${jdbc.username}"
password="${jdbc.password}"
onerror=""
src="${teradata_jxsp.sql.dir}/LastNameDrop.sql">
<classpath refid="install-classpath"/>
</sql>
</target>

<!-- Define the BuildAll ANT Target -->
<target name="BuildAll" depends="Drop-getLastName, Install-getLastName, Create-getLastName">
</target>

</project>