Calling a stored procedure from a Sql server MySQL database or any other database in groovy is simple. First we need a datasource which spring could provide for us. Do the necessary datasource mapping in the resources.xml ( spring folder) in your grails folder &/Or datasources.groovy.
In service, use the datasource to call a store procedure as below example
import groovy.sql.Sql
class TestService{
def dataSource // using the datasource we define in the spring's resources.xml
<strong>/**Calling Procedure with in-parameters only- use sql.query**/</strong>
def abcList = { params ->
Sql sql = new Sql(dataSource)
def clientkey = (SecurityUtils.getSecureClientKey(params) !=
null)?SecurityUtils.getSecureClientKey(params):1
//calling proc that returns resultset
sql.query("{call svr_RetrieveClientPreferences(?)}",[clientkey]) { rs ->
if (rs != null) {
int i = 0
while (rs.next()) {
/ *all functionality ...... */
}
}
}
}
<strong>/**Calling Procedure with in & Out parameters - use sql.call **/</strong>
def abcCalc = { params ->
Sql sql = new Sql(dataSource)
guid = params.guide
userKey = params.user
//calling proc that returns out parameters
sql.call("{call gdx_ReadSessionData(?,?,?,?,?,?)}",
[guid, userKey, Sql.out(Sql.INTEGER.type),
Sql.INTEGER, Sql.VARCHAR, Sql.INTEGER])
{ date,userKeyCode,msgCode,retCode -> /* The out parameters will be returned in the same order it passed to procedure */
userKey = userKeyCode
println(" return code" + retCode)
}
}
}
Note-
in-Out Param can be passed as Sql.out(Sql.<DATATYPE>.type) OR Sql.<DATATYPE> .
See more for DATATYPE here – http://groovy.codehaus.org/api/groovy/sql/Sql.html
LIMITATION -
Some issues with groovy sql; where we want to use out parameters & resultset both as a return of proc execution.
In this case we have to use the jdbc way (callable stmt) where stored procedure that return ResultSet and OUT parameter.
