Using Microsoft SQL in Ant

I had to do a repetitive database task yet again the other day, and I stumbled onto the fact that Ant has full blown support for SQL. The only challenge is getting it to work with the Microsoft SQL we use here. It’s not exactly self evident, mostly because I’m not a Java programmer, so I figured I would share the information.

First download the Microsoft SQL JDBC driver from Microsoft:

Download SQL Server 2005 JDBC Driver 1.1

Once that’s done, execute it, and place the packaged contents somewhere on your computer.

Then fire up Eclipse. You’ll have to add the JDBC driver to the classpath for ANT:

• Go to “Window”
• Choose “Preferences”
• Expand “Ant”
• Choose “Runtime”
• Select “Ant Home Entries” This will cause buttons on right to be enabled
• Press “Add External Jar”
• Navigate to where you put the SQL JDBC driver
• Add sqljdbc.jar
• Hit Apply and hit “Ok”

Now that we have a SQL Driver on our machine, all that’s left to do is to write the Ant tasks that will use it.

<sql
driver=“com.microsoft.sqlserver.jdbc.SQLServerDriver”

url=“jdbc:sqlserver://${dbserver}:1433”

userid=“${dbusername}”
password=“${dbpassword}”

print=“TRUE”>

SELECT CURRENT_TIMESTAMP

</sql>

Obviously, you’ll want to use your application’s database password to do that. Also, I included the SQL directly, but you can also call a .sql file with statements in it.

There are a few gotcha’s:

• “Go” intermittently causes issues. Use a semicolon to separate statements
• Use [database name] doesn’t seem to work
• Putting Database passwords in an .xml file will expose them.
  • Rename build.xml to build.ant.
  • Make sure you don’t expose .ant files to your webserver.

What can you do with this? Well I have a couple of ideas:

• Delete rows created by tests.
• Run Consistency checkers
• In conjunction with an export script, synchronize schemas and stored procedures between two databases.

You may also want to look at the documentation for the SQL Ant Task.