chomping isql and date conversion

Today a colleague was working on retrieving data from a Windows SQL Server database from within linux. He needed a bash script that would execute a query against the database and dump the results in comma delimited format.

The SQL query he wrote was rather long and so was broken up into new lines and indented to ease editing and readability. This, however, caused a snag in isql.

isql is a commandline tool that ships with linux ODBC that can be used to connect to an ODBC DSN and execute sql commands as well as execute batch files in unattended mode.

isql parses batch files ( essentially SQL scripts ) in such a way as that each line is treated as a single command. It does not recognise the GO statement or any other command terminator.

Our workaround was to chomp the script prior to piping it into isql. So here we go:

Here is an example script.

#--- mySqlScript.sql ---

SELECT   TOP 100 *
FROM     myTable t
WHERE    t.created_at between '2009-01-01' and '2009-12-31'
         AND t.enabled = 1

As you can tell the script is formatted in a nice and legible way which would break in isql batchmode.

The fix is rather simple but also elegant because we can keep the script in it's original format and remove the newlines ( chomp ) just before it is sent to isql.

Here is the commandline that does just that.

[dave@fedora junk]$ cat mySqlScript.sql | perl -ne 'chomp; print unless /^$/'
SELECT   TOP 100 *FROM     myTable tWHERE    t.created_at between '2009-01-01' and '2009-12-31'         AND t.enabled = 1[dave@fedora junk]$ 

So the final step is to pipe all that into an isql command.

[dave@fedora junk]$ cat mySqlScript.sql | perl -ne 'chomp; print unless /^$/' | isql -b -d, my_dsn userName passW0rd

Now the above script failed on my computer because (a) I do not have linux odbc installed at home and (b) I do not have a dsn called my_dsn to connect to.
The chomping, however, does work!