Capital Area Flex User's Group Reminder

Just a quick reminder that we are holding the postponed Capital Area Flex User's Group(CAPFug) meeting from last week tonight, Wednesday, December 12th, onsite and broadcasting live via Adobe Connect!

Theo Rushin will be talking about "Working with the SQL Lite Database in Flex on AIR" and Samer Sadek, AboutWeb Software Architect, will present on "Using ColdFusion with AIR".

The first presentation will start at 6:30pm here at the AboutWeb Office and as always, free pizza, sodas and snacks are provided during the social break between sessions.

For live broadcast: http://adobechats.adobe.acrobat.com/capfug1207

Capital Area Flex User's Group(CAPFug)
AboutWeb Office
6177 Executive Blvd
Rockville, MD 20852
(301) 468-9246

ANT Builds and MS SQL - Run .sql scripts in your build.

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
<!-- directory where db scritps reside -->
<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
<target name="sql.getLogin">
<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.

Red-Gate Software: SQL Prompt Tool

Last week I discovered this really neat application from Red-Gate Software, makers of some really nice MSSQL tools such as "SQL Compare" and "SQL Data Compare". They have released a free third party add-on that adds Intellisense® style auto-completion to Microsoft SQL Server editors.

This tool runs on the background and works with Microsoft Query Analyzer, SQL Server 2005 Management Studio, Visual Studio 2005, Visual Studio .NET 2003, SQL Server 2000 Enterprise Manager, UltraEdit32

Features include :

  • Table/View name completion
  • Column name completion
  • Stored procedure name completion
  • USE completion
  • JOIN/JOIN ON completion
  • Auto-uppercasing of keywords
  • Auto-popup after keywords

You can get more info and download it from Red-Gate Software's Site

BlogCFC was created by Raymond Camden. This blog is running version 5.003. Powered by ColdFusion Server v8,0,1,195765.