Copy Page

You are not logged in. Any changes you make will be marked as anonymous. You may want to Log In if you already have an account. You can also Sign Up for a new account.

*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:
<ul>
	<li>The script takes are arguments one or more queryfiles and executes them against a Microsoft SQLServer 2000 database defined using options on the command line.</li>
	<li> host on which SQLServer resides is reachable from the unix host and that there are no firewall issues.</li>
	<li>All queries have one bind variable, which is satisfied by the argument to option -v</li>
	<li>USAGE: groovy queryMSSQL.groovy -h -s sqlserverhost [-P port] -u userid -p password -v value -t textfile queryfile [queryfile]</li>
	<li>Option / arguments info:</li>
</ul>

<ol>
	<li>-P port - denotes the port where SQLServer is listening</li>
	<li>-u userid* -* denotes userid (on SQLServer)</li>
	<li>-p password - denotes password for the userid on SQLServer</li>
	<li>-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.</li>
	<li>-t textfile* *- The name of text file where output would go</li>
	<li>queryfile - A file containing query
 
 </li>
</ol>

<table class="wysiwyg-macro" data-macro-name="code" data-macro-parameters="borderStyle=solid|title=queryMSSQL.groovy" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGU6Ym9yZGVyU3R5bGU9c29saWR8dGl0bGU9cXVlcnlNU1NRTC5ncm9vdnl9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
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 &lt;&lt; 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..&lt;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 {
}
</pre></td></tr></table>
Appendix A - Installing the Microsoft JDBC driver on unix

These notes are based on instruction provided in <a href="http://support.microsoft.com/kb/313100">http://support.microsoft.com/kb/313100</a>.
<ul>
	<li> Download SQL Server 2000 Driver for JDBC Service Pack 3. This is done by getting the file mssqlserver.tar from Microsoft site:</li>
</ul>

<a href="http://www.microsoft.com/downloads/details.aspx?FamilyID=07287B11-0502-461A-B138-2AA54BFDC03A&amp;displaylang=en">http://www.microsoft.com/downloads/details.aspx?FamilyID=07287B11-0502-461A-B138-2AA54BFDC03A&amp;displaylang=en</a>
<ul>
	<li>Upload the tar file mssqlserver.tar to $HOME/download (choose a suitable directory).</li>
	<li>Extract the files from mssqlserver.tar using tar xvf mssqlserver.tar</li>
	<li>Make a directory where the JDBC driver will be installed (say $HOME/mssqljdbcsp3) using mkdir $HOME/mssqljdbcsp3</li>
	<li>Change to $HOME/download and run ./install.ksh</li>
	<li>When prompted for the installation directory choose $HOME/mssqljdbcsp3. This results in the message:
 
SQL Server 2000 driver for JDBC is installed in the following location: $HOME/mssqljdbcsp3</li>
	<li>Set the CLASSPATH variable in the startup file (.login or .profile) to include the following jar files:</li>
</ul>

<ol>
	<li>$HOME/lib/msbase.jar</li>
	<li>$HOME/lib/mssqlserver.jar</li>
	<li>$HOME/lib/msutil.jar</li>
</ol>

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

export CLASSPATH=&quot;$CLASSPATH:$HOME/lib/msbase.jar:$HOME/lib/mssqlserver.jar:HOME/lib/msutil.jar&quot;