*DISCLAIMER: *Use at your own risk. Author is not responsible for any damages resulting from direct or indirect use of the instructions here.

Accessing Microsoft SQLServer using Groovy

This is an example of how to access Microsoft SQL Server (2000) database using groovy to create reports on a unix box using a Microsoft JDBC driver. The instructions to install the JDBC driver itself are given in Appendix A. The script name is queryMSSQL.groovy. Assumptions are:

  1. -P port - denotes the port where SQLServer is listening
  2. -u userid* -* denotes userid (on SQLServer)
  3. -p password - denotes password for the userid on SQLServer
  4. -v value - value to satisfy bind variable (in a where clause eg. WHERE col = ...). If no ? is seen in queryfile, then no bind variables are involved. In this case the value passed should be none.
  5. -t textfile* *- The name of text file where output would go
  6. queryfile - A file containing query

import java.sql.Connection
import java.sql.DriverManager
import javax.sql.DataSource
import groovy.sql.Sql
def cli = new CliBuilder( usage: 'groovy queryMSSQL.groovy -h -s sqlserverhost [-P port] -u userid -p password -v value -t textfile queryfile [queryfile]...')
cli.h(longOpt:'help', 'usage information')
cli.s(argName:'servername', longOpt:'server', args:1, required:true, type:GString, 'sqlserverhost')
cli.P(argName:'port', longOpt:'port', args:1, required:false, type:GString, 'port')
cli.u(argName:'userid', longOpt:'userid', args:1, required:true, type:GString, 'userid')
cli.p(argName:'password', longOpt:'password', args:1, required:true, type:GString, 'password')
cli.v(argName:'value', longOpt:'value', args:1, required:true, type:GString, 'value')
cli.t(argName:'textfile', longOpt:'text', args:1, required:true, type:GString, 'text file')
def opt = cli.parse(args)
if (!opt) return
if (opt.h) cli.usage()
def port = 1433
if (opt.P)  port = opt.P // If the port was defined
def servername = opt.s
def userid = opt.u
def password = opt.p
def valuetobind = opt.v
def textfile = opt.t
def outFile
def outFileWriter
try {
    outFile = new File(textfile)
    outFile.write("");  // truncate if output file already exists
} catch (Exception e) {
    println "ERROR: Unable to open $textfile for writing";
    return;
}
driver = Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://$servername:$port", userid, password);

try {
    if  (args.length == 0)    {
        usage_error = "Error: Invalid number of arguments"
        usage_error = "\n$usage_error\nUSAGE: groovy queryMSSQL.groovy queryfile\n"
        throw new IllegalArgumentException(usage_error)
    }
    Sql sql = new Sql(conn)
// After options processing the remaining arguments are query files
// Go through the query files one at a time for execution
  for (queryfilename in opt.arguments()) {
        queryfile = new File(queryfilename)
        query = "" // initialize the query string
        param_count = 0      // Number of placeholders needed for parameters to query
        pattern = /\?/ // pattern to look for to find number of parameters
        // read the query from the query file (line by line) and build it
        queryfile.eachLine { it ->
            query += " " + it
        }
      // number of bind variables to satisfy is obtained by number of ? seen in the query
       query.eachMatch(pattern) { param_count++ }
        println '-.' * 40
        println "query is ${query}"

        println "Output is:"
        println '=' * 80
        def count = 0  // row count
        paramlist = []
        if (valuetobind != "none")
            1.upto(param_count) { paramlist << valuetobind }
        sql.eachRow(query, paramlist) { row ->
           count++; // increment number of rows seen so far
           //println "$count. ${row.name}" // print out the column name
           recstr = ""  // initialize the string that represents row
           meta = row.getMetaData() // get metadata about the row

           for (col in 0..<meta.columnCount) {
              // record is stored in a string called recstr
               if (recstr == "") {
                   recstr = row[col]
               }
               else {
                   recstr += "," + row[col]
               }
           }

           outFile.append(recstr + "\n")
        }
    }
    conn.close()
} catch(Exception e) {
    print e.toString()
}
finally {
}

Appendix A - Installing the Microsoft JDBC driver on unix

These notes are based on instruction provided in http://support.microsoft.com/kb/313100.

http://www.microsoft.com/downloads/details.aspx?FamilyID=07287B11-0502-461A-B138-2AA54BFDC03A&displaylang=en

  1. $HOME/lib/msbase.jar
  2. $HOME/lib/mssqlserver.jar
  3. $HOME/lib/msutil.jar

In Bourne/Korn shell CLASSPATH can be appended to using:

export CLASSPATH="$CLASSPATH:$HOME/lib/msbase.jar:$HOME/lib/mssqlserver.jar:HOME/lib/msutil.jar"