Yet another great ANT integration feature, executing .sql scripts. One of the things I like to do during development is keep a folder of all t-sql scripts needed to get my application up and running. These normally include creating tables, views, stored procs, and indexes as well as scripts that insert default records such as lookup values. I make sure the scripts can be run on a brand new installation as well as for updating an existing one (Listing 1.1).
Listing 1/ Script: Person Table /
/ Purpose: Creates Person Table /
/ Script Date: 02/07/2007 /
/ By: Phill Nacelli - Software Architect /
/************************************************************
-- if table does not exist create it.
IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = N'Person' AND type = 'U')
BEGIN
CREATE TABLE Person (
personID int IDENTITY (1, 1) NOT NULL ,
firstName varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
middleName varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
lastName varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
dateOfBirth datetime NULL ,
suffix varchar (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT PK_Person PRIMARY KEY CLUSTERED
(
personID
) ON PRIMARY
) ON PRIMARY
END
GO
-- insert new suffix column if does not exist
IF NOT EXISTS (SELECT sc.name FROM syscolumns sc INNER JOIN sysobjects so ON sc.id = so.id WHERE so.name = 'Person'
AND so.type = 'U' AND sc.name = 'suffix')
BEGIN
ALTER TABLE Person
ADD suffix varchar(30) NULL
END
-- insert new prefix column if does not exist
IF NOT EXISTS (SELECT sc.name FROM syscolumns sc INNER JOIN sysobjects so ON sc.id = so.id WHERE so.name = 'Person'
AND so.type = 'U' AND sc.name = 'prefix')
BEGIN
ALTER TABLE Person
ADD prefix varchar(30) NULL
END
However, running multiple t-sql scripts at a time is not a lot of fun, and that's when ANT comes in. It allows you to run .sql scripts using jdbc drivers directly into your database. Here's a step by step how:
1. Make sure you have your database's JDBC .jar file. These can be found either on the database manufacture's website or via online. Sun's Developer Network (SDN) has made this even easier by creating a JDBC search engine here.
2. In your build file, create the following properties to link to your database and .sql script files (Listing 1.2):
Listing 1.2 - build.xml<property name="sql.scriptPath" value="D:\\inetpub\\aLogix\\database" />
<!-- db server name -->
<property name="sql.serverName" value="MSSQL_DevBox" />
<!-- db name -->
<property name="sql.dbName" value="aLogix" />
<!-- jdbc url -->
<property name="sql.jdbcURL" value="jdbc:sqlserver://${sql.serverName};databasename=${sql.dbName}" />
<!-- jdbc path -->
<property name="sql.jdbcPath" value="C:\jdbcDrivers\microsoft-jdbc-2005\lib\sqljdbc.jar" />
<!-- jdbc classpath -->
<property name="sql.jdbcClasspath" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
3. Then simply create the target implementing the sql task tag to run your scripts. Here I have created two targets, the first runs all my sql scripts, the other prompts me which .sql file I want to run (Listing 1.3):
Listing 1.3 - build.xml<input message="Please enter sql server username:" addproperty="sql.username" />
<input message="Please enter sql server password:" addproperty="sql.password" />
</target>
<target name="sql.runAll" depends="sql.getLogin">
<sql driver="${sql.jdbcClasspath}" url="${sql.jdbcURL}" userid="${sql.username}" password="${sql.password}">
<classpath>
<pathelement path="${sql.jdbcPath}" />
</classpath>
<transaction src="${sql.scriptPath}\tables\Person.sql" />
<transaction src="${sql.scriptPath}\tables\Company.sql" />
<transaction src="${sql.scriptPath}\tables\Address.sql" />
<transaction src="${sql.scriptPath}\tables\Country.sql" />
<transaction src="${sql.scriptPath}\tables\StateProvince.sql" />
<transaction src="${sql.scriptPath}\lookupData\Country.sql" />
<transaction src="${sql.scriptPath}\lookupData\StateProvince.sql" />
</sql>
</target>
<target name="sql.runByFileName" depends="sql.getLogin">
<input message="Please enter sql script name to be executed:" addproperty="sql.filename" />
<sql driver="${sql.jdbcClasspath}" url="${sql.jdbcURL}" userid="${sql.username}" password="${sql.password}">
<classpath>
<pathelement path="${sql.jdbcClassPath}" />
</classpath>
<transaction src="${sql.scriptPath}\tables\${sql.filename}.sql" />
</sql>
</target>
That's it! Here's another simple way how ANT can make a developer's life a lot easier. Hopefully this example can be of some use on your toolbox as it has been for me.
Phill Nacelli has been architecting and developing software solutions for over 10 years. He has lead the engineering and development of multiple enterprise web based applications for the Federal Government, Commercial Software, and non-profit association/education market. He enjoys playing with the latest in programming techniques, frameworks and development tools. Phill is very active in the Adobe ColdFusion community as a member of the Adobe Community Professional Program, serving as the Capital Area ColdFusion User Group Manager, Adobe ColdFusion Customer Advisory Board. He's also involved in speaking at technical conferences, writing technical articles, user group presentations, community forums/chat rooms support and via this blog. He is currently the Senior Software Architect - Solutions Lead at 
Very timely post for me! Cheers
I tried your code using Eclipse/Ant against MS SQL 2005. The sample SQL script failed to run and errored due to syntax. The script ran fine in Query Analyzer. I modified the SQL scipt to take out the word Go and added ";" at end of each block of sql. Below is the modified /worked script
-- Script: Person Table /
-- Purpose: Creates Person Table /
-- Script Date: 02/07/2007 /
-- By: Phill Nacelli - Software Architect /
-- if table does not exist create it.
IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = N'Person' AND type = 'U')
BEGIN
CREATE TABLE [dbo].[Person](
[personID] [int] IDENTITY(1,1) NOT NULL,
[firstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[middleName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dateOfBirth] [datetime] NULL,
[suffix] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[prefix] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[personID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END;
-- insert new suffix column if does not exist
IF NOT EXISTS (SELECT sc.name FROM syscolumns sc INNER JOIN sysobjects so ON sc.id = so.id WHERE so.name = 'Person'
AND so.type = 'U' AND sc.name = 'suffix')
BEGIN
ALTER TABLE Person
ADD suffix varchar(30) NULL
END;
-- insert new prefix column if does not exist
IF NOT EXISTS (SELECT sc.name FROM syscolumns sc INNER JOIN sysobjects so ON sc.id = so.id WHERE so.name = 'Person'
AND so.type = 'U' AND sc.name = 'prefix')
BEGIN
ALTER TABLE Person
ADD prefix varchar(30) NULL
END
An alternative fix is to leave 'GO' in your SQL statements and add delimiter='GO' as an attribute in the ant sql task:
<pre>
<sql driver="com.SomeDriver" url="jdbc:someUrl" userid="someuser" password="somepassword" classpathref="myclasspath" delimiter="GO">
<transaction src="create_db.sql"/>
</sql>
</pre>
Regards,
Eliot
Do you know if it is at all possible to run Create Database commands within a ANT script?
(I'm using MS SQL 2000, and a MS JDBC driver)
Cant i add all the sql scripts in one and call the single sql script.
a sample would be
<property name="sql.file" location="${PLSQL_ROOT}/DropTables.sql"/>
src="${sql.file}"
Now DropTables.sql will have calls to other sql scripts like
@CreateTables
@RunMigration
This isnt working , as i want it to..is there a way to accomplish this ?
Thanks,
Sumit