Groovy | Connect to SQL Server and execute stored procedure

To connect to Microsoft SQL Server from Groovy script, and execute stored procedure, simply follow steps outlined below

1) Download JDBC driver Jar file from https://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx.
    Add jar when running groovy script with -cp parameter:

1 2	#!/bin/bash groovy -cp "./lib/sqljdbc41.jar" myScript.groovy

    OR if running compiled JVM code (output from groovyc), use java's cp param

1 2	#!/bin/bash java -cp "./lib/sqljdbc41.jar;myScript.jar" com.example.MainClas

   Note: Examples above assume you have unpacked JDBC driver jar to 'lib' directory

2) Create Groovy's SQL object by using instance method, and passing JDBC connection string to it:

1 2 3 4 5 6 7 8 9 10 11 12 13

def configuration = [ 'dbInstance' : 'localhost', 'dbPort' : 1433, 'dbName' : 'myDatabase', 'dbUser' : 'sa', 'dbPass' : 'sa123' ] def sql = Sql.newInstance( "jdbc:sqlserver://${configuration.dbInstance}:${configuration.dbPort};" + "databaseName=" + configuration.dbName, configuration.dbUser, configuration.dbPass, 'com.microsoft.sqlserver.jdbc.SQLServerDriver')

   Note : All configuration params - host, port, username and pass are placed in 'configuration' map

3) Executing stored procedure is as easy as calling 'call' method on Sql object. This method accepts following parameters:

1. String representing method execution and taking following format
   {? = call SP_NAME(?,?...)}
   Little clarification for question marks:
1. First question mark represents SP's return value (0 for OK)
2. Every following question mark inside brackets presents parameter, either input or output one
2. List for SP params:
1. For input parameters use their values
2. For output parameters specify their type, one of the groovy.sql.OutParameter constants from groovy.sql.Sql class (e.g. groovy.sql.INTEGER)
3. Closure executed upon SP execution. First parameter passed to closure is return value of stored procedure (usually 0 for OK). Every following parameter matches respecting OUT parameter in same order as declared in procedure itself.

Below is example code for both creating Sql object and executing stored procedure, asuming that stored procedure multiplies two integers (first and second parameter), and assigns result of multiplication to third (output) parameter

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

def configuration = [ 'dbInstance' : 'localhost', 'dbPort' : 1433, 'dbName' : 'myDatabase', 'dbUser' : 'sa', 'dbPass' : 'sa123' ] def sql = Sql.newInstance( "jdbc:sqlserver://${configuration.dbInstance}:${configuration.dbPort};" + "databaseName=" + configuration.dbName, configuration.dbUser, configuration.dbPass, 'com.microsoft.sqlserver.jdbc.SQLServerDriver') sql.call('{? = call MULTIPLY(?,?,?)}',[ Sql.INTEGER, 5,5 ,Sql.CHAR],{ spResult, multiplicationResult -> if(spResult == 0){ assert multiplicationResult == 25 }else { throw new RuntimeException("SP MULTIPLY FAILED WITH ERR CODE:${spResult}") } })