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.

7 thoughts on “Using Microsoft SQL in Ant

  1. I Love this task – in particular I find it very useful for testing – as it allows me blow away data in test tables and recreate the data before running some unit tests!

    Like

  2. How does your connection url look like for a MSSQL2000 server? I always get an error “Server has to be MS SQL Server 2000 or later” though I am using a MSSQL 2000.

    Like

  3. How does your connection url look like for a MSSQL2000 server? I always get an error “Server has to be MS SQL Server 2000 or later” though I am using a MSSQL 2000.

    Like

Leave a Reply to Phillip Gagnon Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s