How can I read output of an sql query into an ant property?

Question

I would like to feed the result of a simple SQL query (something like: select SP_NUMBER from SERVICE_PACK) which I run inside my ant script (using the sql task) back into an ant property (e.g. service.pack.number).

The sql task can output to a file, but is there a more direct way?

Answer 1

Although I would have preferred not creating a file, I eventually went with the following solution:

The sql task is called as follows

<sql ... print="yes" output="temp.properties"
        expandProperties="true" showheaders="false" showtrailers="false" >
        <![CDATA[
        select 'current.sp.version=' || NAME from SERVICE_PACK;
        select 'current.major.version=' || NAME from VERSION;
        ]]>
</sql>

The generated properties file will contain:

current.sp.version=03

current.major.version=5

Then you just load the properties file and delete it:

<property file="temp.properties" />
<delete file="temp.properties" />

<echo message="Current service pack version: ${current.sp.version}" />
<echo message="Current major version: ${current.major.version}" />

This works, and everything is right there in the ant script (even if it's not the prettiest thing in the world!).

Answer 2

Perhaps the Ant exec task is more useful here ? You can execute a standalone and get the result in a property via outputproperty. You'll have to execute your SQL in some standalone fashion, unfortunately.

Alternatively is it worth looking at the code to the Ant Sql task and modify it to accept an outputproperty ? It sounds a bit of a pain, but I think it could well be a very simple modification if you can't find anything more direct.