Ant, Groovy and the Database

When you are using Ant to cope with databases then you probably might feel the need to read Ant properties from the database with some lines of SQL. So Ant scripts can be controlled by values from the database. For instance one may query a version table to decide whether a deployment script can succeed or fail.

With Groovy and it’s SQL module it’s quite simple to do this. The following example shows how to query all parameters from Oracle’s NLS_DATABASE_PARAMETERS table and use them as properties within an Ant script.

1) Install the Groovy Ant Task to make Groovy scripts executable from Ant build files. Furthermore add Oracle’s JDBC driver to the classpath so Groovy can find it.

<path id="id.classpath">
  <fileset dir="${groovy.dir}/lib">
     <include name="*.jar"/>
  </fileset>
  <fileset dir="${jdbc.dir}/lib">
     <include name="ojdbc14.jar"/>
  </fileset>
</path>  

<taskdef name="groovy"
     classname="org.codehaus.groovy.ant.Groovy"
     classpathref="id.classpath"/>    

2) Initialise a JDBC connection object and add its reference to Ant’s project variable

<target name="init">
  <groovy>
    import groovy.sql.Sql
    def sql = Sql.newInstance("jdbc:oracle:thin:@host:1521:ora",
                   "system", "pass",
                   "oracle.jdbc.driver.OracleDriver")
    project.addReference("groovy.sqlinstance", sql)
  </groovy>
</target>

3) Write a macro that queries all rows from NLS_DATABASE_PARAMETERS (wich has a parameter and a value column) and add them as properties to the Ant project with a prefix nls

<macrodef name="read-nls-properties">
  <sequential>
  <groovy>
    def sql = project.getReference("groovy.sqlinstance")
    sql.eachRow("select * from nls_database_parameters") {
       project.setProperty("nls.${it.parameter}", it.value)
      }
  </groovy>
  </sequential>
</macrodef>

4) Execute this macro and display all the properties with the prefix nls

<target name="test-groovy" depends="init">
  <read-nls-properties/>
  <echoproperties prefix="nls"/>
</target>

5) The output of this Ant script will look like:

init:
   [groovy] statements executed successfully
test-groovy:
   [groovy] statements executed successfully
[echoproperties] #Ant properties
[echoproperties] #Fri Apr 07 15:53:29 CEST 2006
[echoproperties] nls.NLS_CALENDAR=GREGORIAN
[echoproperties] nls.NLS_TERRITORY=AMERICA
[echoproperties] nls.NLS_DUAL_CURRENCY=$
[echoproperties] nls.NLS_CHARACTERSET=WE8MSWIN1252
[echoproperties] nls.NLS_CURRENCY=$
[echoproperties] nls.NLS_DATE_LANGUAGE=AMERICAN
[echoproperties] nls.NLS_ISO_CURRENCY=AMERICA
[echoproperties] nls.NLS_TIME_TZ_FORMAT=HH.MI.SSXFF AM TZR
[echoproperties] nls.NLS_NUMERIC_CHARACTERS=.,
[echoproperties] nls.NLS_TIME_FORMAT=HH.MI.SSXFF AM
[echoproperties] nls.NLS_COMP=BINARY
[echoproperties] nls.NLS_SORT=BINARY
[echoproperties] nls.NLS_LANGUAGE=AMERICAN
[echoproperties] nls.NLS_LENGTH_SEMANTICS=BYTE
[echoproperties] nls.NLS_DATE_FORMAT=DD-MON-RR
[echoproperties] nls.NLS_NCHAR_CONV_EXCP=FALSE
[echoproperties] nls.NLS_NCHAR_CHARACTERSET=AL16UTF16
[echoproperties] nls.NLS_RDBMS_VERSION=10.1.0.4.0
BUILD SUCCESSFUL

Note that if you have Oracle and an Oracle client, you’ve got another option to accomplish those tasks with the help of Sql*Plus. You can use Sql*Plus (and the <sqlplus> task from Incanto) to write a properties file and afterwards load these properties within the Ant build script.