I am trying to execute some stored procedures in groovy way. I am able to do it quite easily by using straight JDBC but this does not seem in the spirit of Grails.

I am trying to call the stored procedure as-

sql.query( "{call web_GetCityStateByZip(?,?,?,?,?)}",[params.postalcode, sql.out(java.sql.Types.VARCHAR), sql.out(java.sql.Types.VARCHAR), sql.out(java.sql.Types.INTEGER), sql.out(java.sql.Types.VARCHAR)]) { rs -> = rs.getString(2) params.state = rs.getString(3) }

i tried various ways like . I was trying to get output variable value after this .

Everytime error-

Message: Cannot register out parameter. Caused by: java.sql.SQLException: Cannot register out parameter. Class: SessionExpirationFilter

but this does not seem to work.

Can anyone point me in the right direction.

Thanks Sadhna

+1  A: 

This is still unanswered, so I did a bit of digging although I don't fully understand the problem. The following turned up from the Groovy source, perhaps it's of some help:

This line seems to be the origin of the exception:

This would seem to indicate that you have a Statement object implementing PreparedStatement, when you need the subinterface CallableStatement, which has the registerOutParameter() method which should be ultimately invoked.

Internet Friend

Thanks Internet Friend, If i write code like-

    Sql sql = new Sql(dataSource)
Connection conn
ResultSet rs
try {
     conn = sql.createConnection()
     CallableStatement callable = conn.prepareCall(
     "{call web_GetCityStateByZip(?,?,?,?,?)}")
         callable.execute() = callable.getString(2)
        params.state = callable.getString(3)

It working well in JDBC way. But i wanted to try it like the previous code using sql.query/

Any comments??

Thanks Sadhna


Hi, groovy way could be this code:

def getHours(java.sql.Date date, User user) throws CallProceduresServiceException { "Calling stored procedure for getting hours statistics."
    def procedure
 def hour
 try {
  def sql = Sql.newInstance(dataSource.url, user.username, user.password, dataSource.driverClassName)
  log.debug "Date(first param): '${date}'"

  procedure = "call ${dbPrefixName}.GK_WD_GET_SCHEDULED_TIME_SUM(?, ?, ?, ?)"
  log.debug "procedure: ${procedure}""{${procedure}}", [date, Sql.out(Sql.VARCHAR.getType()), Sql.out(Sql.VARCHAR.getType()), Sql.out(Sql.VARCHAR.getType())]) {
   hourInDay, hourInWeek, hourInMonth -> 
   log.debug "Hours in day: '${hourInDay}'"
      log.debug "Hours in week: '${hourInWeek}'"
      log.debug "Hours in month: '${hourInMonth}'"
   hour = new Hour(hourInDay, hourInWeek, hourInMonth)
  } "Procedure was executed."
 catch (SQLException e) {
  throw new CallProceduresServiceException("Executing sql procedure failed!"
    + "\nProcedure: ${procedure}", e)
 return hour 

In my app it works great.

Tomas Peterka