Skip to end of metadata
Go to start of metadata


通过Groovy访问SQL Server数据库

本例关注如何在*nix环境下通过groovy调用MS JDBC驱动访问SQL Server 2000数据库并生成报表。附录A描述了如何安装JDBC驱动。脚本的名称是*queryMSSQL.groovy.*,并作如下假设:

  • 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:
  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

Appendix A - Installing the Microsoft JDBC driver on unix

These notes are based on instruction provided in

  •  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:
  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"


  • No labels