SQL Ant Wizard

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

SQL Ant Wizard

Apache Ant allows the user to run a SQL task using JDBC. The Teradata SQL Ant Wizard allows you to wrap selected SQL Statements into an Ant build script from the Eclipse DTP SQL Editor.

The XML Ant build script generated from the Wizard runs the selected SQL statements inside or outside of Eclipse. The Ant build script will facilitate a consistent setup of test or base production environments. This creates a mechanism to run SQL reports and to integrate with build or schedule tools. 

Prerequisite for this Article

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

Create a Teradata Project

The SQL Ant Wizard requires you to create a Project in Eclipse. First, if you have not worked through the article Create a Teradata Project using the Teradata Plug-in for Eclipse, do so now before you continue. Now, select File->New->Other.. from the top menu bar in Eclipse. Then select the Teradata Wizards->Teradata Project from the pop-up dialog. Enter “AcctPrj” for the name of your new Project in the Teradata Project Wizard. Once this is done select the Finish button in the Wizard and a Teradata Project will be created.

Launch Wizard

The Wizard is launched by right clicking in the Eclipse DTP SQL Editor window. If no selection is made all of the SQL statements in the Editor will be wrapped in the Ant build file. If a selection is made, only the selected SQL will be included in the Ant build. If a partial SQL statement is selected, an error message dialog will come up and the Wizard will not be launched.

SQL Ant Wizard

The Wizard requires the folder and the file name for the Ant build. The default for the folder of the Ant build will be set to the project folder of the file being edited by the SQL Editor. The file name default is "build.xml". The xml suffix is added if it is missing in the name of the Ant build file. Also a target name must be defined for the selected SQL so it can be run from the Ant build. The file must not exist if the append option is not selected. If the append option is selected, the SQL target will be added to an existing SQL Ant Build file. The target name must be unique in the existing file. The SQL Ant build file will have a "RunSQL" target for all SQL tasks that have been appended to the file. If multiple SQL statements are selected, the user will have the option to execute each statement individually or as one. There are options to display the results to the console or to a text file or to a CSV file. The output files generated will be named the same as the corresponding targets and will have the suffix of txt or csv. The "On Error" option determines what will happen if an error occurs for the SQL target currently being added.

Parameters

If the selected SQL contains parameter substitution,"?", the Parameter panel will be the next Wizard page. This page will define the Parameter names and values. The parameter names will have default values. Also the parameter names can be changed or over written if named parameters are used. The parameter names and values will be saved to a properties file in the configuration directory of the Teradata Project and will be used in the Ant build script.

Generate "build.xml" File

Once the finish button is selected in the Wizard, the XML Ant build file is generated and launched in an editor. Also a corresponding SQL file is created to be run from the Ant build if the "In Line SQL" option is not selected. The SQL file will be named the same as the target with a suffix of "sql". If the "In Line SQL" option is selected, the SQL will be imbedded in the "build.xml" file.

Run SQL Ant Build

The SQL Ant Build can be run by using the following steps:

  •  Right click on the SQL Ant Build (build.xml) file and select Run As -> External Tools Configurations...
  •  Right Click Ant Build in the External Tools Configurations Dialog and select new
  •  Select "Refresh resources upon completion" option in the Refresh tab
  •  Enter "RunSQL" in the Arguments text box and select the Apply button in the External Tools Configurations Dialog
  •  Select the Run button in the External Tools Configurations Dialog.

The results for the SQL Ant Build will appear in the console of the Eclipse IDE.

 

CSV File

If the CSV file option was selected in the SQL Ant Wizard, a CSV file will be generated with results in the SQL directory of the Teradata Project.

Running Ant from the Command line

Once you have your Ant Build Script running in Eclipse, you can run it out side of Eclipse in the command prompt for Windows. You first have to install Ant on your computer. The installation down load and instructions are on the Apache Ant web site.

Once you installed Ant, bring up your command prompt in Windows and go to the Eclipse Project directory in which you created your Ant “build.xml” file  and enter “ant RunSQL” on your command line and hit enter.

You can now create a bat file to run your Ant Build script. You then can use a tool like the “Windows Task Scheduler” to run your Ant Build Script automatically every day.

The SQL Ant Build Wizard allows you to create scripts to run your SQL. It allows you to expand your scope beyond the usage of Eclipse and gives you an automated way to access your Teradata Database.

Appendix A

Sample Files

acct.sql

SELECT * FROM "ACM"."ACCT_CUST_VIEW"
WHERE "ACCT_DESC" = ?\AccDescrip;

buildParam.properties

#Parameter Properties
#Thu Apr 14 17:05:38 PDT 2011
SelectAcctType.AccDescrip='Visa'

build.xml

<?xml version="1.0"?>

<project name="AccPrj" basedir="." default="Usage">

<property name="name" value="AccPrj-SelectAcctType)"/>

<!-- Define the Usage ANT Target -->
<target name="Usage">
<echo message="RunSQL --> Run All SQL Targets"/>
<echo message="Usage --> Illustrate Usage of the build file"/>
<echo message=""/>
</target>

<!-- Define the property names for the AccPrj project -->

<property name="AccPrj.sql.dir" value="../AccPrj/src/sql"/>

<property name="AccPrj.config.dir" value="../AccPrj/src/config"/>
<!-- The property jdbclib.terajdbc4 is required to run this SQL Ant Build -->
<property name="jdbclib.terajdbc4" value="C:/test/eclipse-jee-helios-SR2-win32/eclipse/plugins/com.teradata.datatools.terajdbc_13.10.0.201104141519/terajdbc4.jar"/>

<!-- The property jdbclib.tdgssconfig is required to run this SQL Ant Build -->
<property name="jdbclib.tdgssconfig" value="C:/test/eclipse-jee-helios-SR2-win32/eclipse/plugins/com.teradata.datatools.terajdbc_13.10.0.201104141519/tdgssconfig.jar"/>

<!-- Include the jdbc.properties. This properties file is required to run this SQL Ant Build -->
<property file="${AccPrj.config.dir}/jdbc.properties"/>

<property file="${AccPrj.config.dir}/buildParam.properties"/>

<!-- Define the run-classpath used for running SQL. This classpath is required to run this SQL Ant Build -->
<path id="run-classpath">
<!-- Add the Development Path Libraries used by all applications -->
<pathelement location="${jdbclib.terajdbc4}"/>
<pathelement location="${jdbclib.tdgssconfig}"/>

</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.
This is required to run this SQL Ant Build -->
<target name="RefreshEclipse" if="eclipse.running">
<echo>"Refreshing Eclipse Workspace"</echo>
<eclipse.refreshLocal resource="" depth="infinite"/>
</target>

<!-- Define the SelectAcctType ANT Target -->
<target name="SelectAcctType">
<echo message="SQL Target SelectAcctType: ${jdbc.driverClassName} ${jdbc.url}"/>
<property name="myclasspath" refid="run-classpath"/>
<echo message="${myclasspath}" />
<sql driver="${jdbc.driverClassName}"
url="${jdbc.url}"
userid="${jdbc.username}"
password="${jdbc.password}"
onerror=""
delimiter=";"
expandProperties="true"
output="${AccPrj.sql.dir}/SelectAcctType.csv"
showtrailers="false"
print="yes">
<classpath refid="run-classpath"/>

SELECT *
FROM &quot;ACM&quot;.&quot;ACCT_CUST_VIEW&quot;
WHERE &quot;ACCT_DESC&quot; = ${SelectAcctType.AccDescrip};
commit;

</sql>

</target>

<!-- Do not edit this comment or below -->

<target name="RunSQL" depends="SelectAcctType">
<antcall target="RefreshEclipse"/>
</target>

</project>