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. kola

    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!

    Reply
  2. Lars

    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.

    Reply
  3. Lars

    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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>