How can I read the output of the sql query in the ant property?

Question

I would like to submit 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 to the ant property, for example, service.pack.number ).

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

Answer 1

Although I would prefer not to create the file, I ended up going with the following solution:

The sql task is invoked 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 upload 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}" /> 

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

Answer 2

Perhaps an Ant exec task might be more useful? You can do offline work and get the result in the property through outputproperty . Unfortunately, you will have to run your SQL offline.

Alternatively, is it worth looking at the code for the Ant Sql task and changing it to accept outputproperty ? It sounds a little painful, but I think it can be a very simple modification if you cannot find anything more direct.

Answer 3

MySQL users - I had to modify the query as follows:

 SELECT CONCAT('mytable.id=', CAST(ID as CHAR)) from mytable 

Without the CONCAT function, I just returned the text "1" (representing my identifier) ​​in the properties file. In addition, CAST is required in the MySQL system, otherwise the concatenated field is returned as a BLOB.