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 responses so far ↓
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!2 Jaime Metcher
In this context it's worth a look at dbunit and associated ant task.3 Mike Henke
Lets say I want to do an insert, how would I pull back the new id in an echo message? Great post!4 Mike
Figured out my question. See http://henke.ws/machblog/index.cfm?event=showEntry&entryId=055A6924-188B-4E84-1534BBFD052A04CA5 Phillip Gagnon
Awesome post! FYI- That driver also works with MS2000. Just a heads up!6 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.7 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.Leave a Comment