Accessing SQLServer using groovy
*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:
- 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.
- host on which SQLServer resides is reachable from the unix host and that there are no firewall issues.
- All queries have one bind variable, which is satisfied by the argument to option -v
- USAGE: groovy queryMSSQL.groovy -h -s sqlserverhost [-P port] -u userid -p password -v value -t textfile queryfile [queryfile]
- Option / arguments info:
- -P port - denotes the port where SQLServer is listening
- -u userid* -* denotes userid (on SQLServer)
- -p password - denotes password for the userid on SQLServer
- -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.
- -t textfile* *- The name of text file where output would go
- queryfile - A file containing query
Appendix A - Installing the Microsoft JDBC driver on unix
These notes are based on instruction provided in http://support.microsoft.com/kb/313100.
- Download SQL Server 2000 Driver for JDBC Service Pack 3. This is done by getting the file mssqlserver.tar from Microsoft site:
- Upload the tar file mssqlserver.tar to $HOME/download (choose a suitable directory).
- Extract the files from mssqlserver.tar using tar xvf mssqlserver.tar
- Make a directory where the JDBC driver will be installed (say $HOME/mssqljdbcsp3) using mkdir $HOME/mssqljdbcsp3
- Change to $HOME/download and run ./install.ksh
- 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
- Set the CLASSPATH variable in the startup file (.login or .profile) to include the following jar files:
In Bourne/Korn shell CLASSPATH can be appended to using:
Results of your search request can come from various sources: the Groovy website itself,
the JIRA issues, the API documentation, as well as a few other interesting Groovy-related blogs.