How to call a stored procedure and get return value in Slick (using Scala)

Well, after much research and review of conflicting documentation, I found the answer. Unfortunately, it wasn't the one I was looking for:

For database functions that return complete tables or stored procedures please use Plain SQL Queries. Stored procedures that return multiple result sets are currently not supported.

Bottom line, Slick does not support stored functions or procedures out of the box, so we have to write our own.

The answer is to drop down out of Slick by grabbing the session object, and then use standard JDBC to manage the procedure call. For those of you familiar with JDBC, that's not a joy... but, fortunately, with Scala we can do some pretty nice tricks with pattern matching that make the job easier.

The first step for me was putting together a clean external API. This is what it ended up looking like:

val db = Database.forDataSource(DB.getDataSource)var response: Option[GPInviteResponse] = Nonedb.withSession {    implicit session => {        val parameters = GPProcedureParameterSet(            GPOut(Types.INTEGER) ::            GPIn(Option(i.token), Types.VARCHAR) ::            GPIn(recipientAccountId, Types.INTEGER) ::            GPIn(Option(contactType), Types.INTEGER) ::            GPIn(contactValue, Types.VARCHAR) ::            GPIn(None, Types.INTEGER) ::             GPIn(Option(requestType), Types.CHAR) ::            GPOut(Types.INTEGER) ::              Nil        )        val result = execute(session.conn, GPProcedure.SendInvitation, parameters)        val rc = result.head.asInstanceOf[Int]        Logger(s"FUNC return code: $rc")        response = rc match {            case 0 => Option(GPInviteResponse(true, None, None))            case _ => Option(GPInviteResponse(false, None, Option(GPError.errorForCode(rc))))        }    }}db.close()

Here's a quick walkthrough: I created a simple container to model a stored procedure call. The GPProcedureParameterSet can contain a list of GPIn, GPOut, or GPInOut instances. Each of these maps a value to a JDBC type. The container looks like this:

case class GPOut(parameterType: Int) extends GPProcedureParameterobject GPOutcase class GPIn(value: Option[Any], parameterType: Int) extends GPProcedureParameterobject GPIncase class GPInOut(value: Option[Any], parameterType: Int) extends GPProcedureParameterobject GPInOutcase class GPProcedureParameterSet(parameters: List[GPProcedureParameter])object GPProcedureParameterSetobject GPProcedure extends Enumeration {    type GPProcedure = Value    val SendInvitation = Value("{?=call app_glimpulse_invitation_pkg.n_send_invitation(?, ?, ?, ?, ?, ?, ?)}")}

For completeness I'm including the GPProcedure enumeration so you can put it all together.

All of this gets handed to my execute() function. It's big and nasty, smells like old-fashioned JDBC, and I'm sure I'll improve the Scala quite a bit. I literally finished this up at 3am last night... but it works, and it works really well. Note that this particular execute() function returns a List containing all of the OUT parameters... I'll have to write a separate executeQuery() function to handle a procedure that returns a resultSet. (The difference is trivial though: you just write a loop that grabs a resultSet.next and stuff it all into a List or whatever other structure you would like).

Here's the big nasty Scala<->JDBC mapping execute() function:

def execute(connection: Connection, procedure: GPProcedure, ps: GPProcedureParameterSet) = {    val cs = connection.prepareCall(procedure.toString)    var index = 0    for (parameter <- ps.parameters) {        index = index + 1        parameter match {            // Handle any IN (or INOUT) types: If the optional value is None, set it to NULL, otherwise, map it according to            // the actual object value and type encoding:            case p: GPOut => cs.registerOutParameter(index, p.parameterType)            case GPIn(None, t) => cs.setNull(index, t)            case GPIn(v: Some[_], Types.NUMERIC | Types.DECIMAL) => cs.setBigDecimal(index, v.get.asInstanceOf[java.math.BigDecimal])            case GPIn(v: Some[_], Types.BIGINT) => cs.setLong(index, v.get.asInstanceOf[Long])            case GPIn(v: Some[_], Types.INTEGER) => cs.setInt(index, v.get.asInstanceOf[Int])            case GPIn(v: Some[_], Types.VARCHAR | Types.LONGVARCHAR) => cs.setString(index, v.get.asInstanceOf[String])            case GPIn(v: Some[_], Types.CHAR) => cs.setString(index, v.get.asInstanceOf[String].head.toString)            case GPInOut(None, t) => cs.setNull(index, t)            // Now handle all of the OUT (or INOUT) parameters, these we just need to set the return value type:            case GPInOut(v: Some[_], Types.NUMERIC) => {                cs.setBigDecimal(index, v.get.asInstanceOf[java.math.BigDecimal]); cs.registerOutParameter(index, Types.NUMERIC)            }            case GPInOut(v: Some[_], Types.DECIMAL) => {                cs.setBigDecimal(index, v.get.asInstanceOf[java.math.BigDecimal]); cs.registerOutParameter(index, Types.DECIMAL)            }            case GPInOut(v: Some[_], Types.BIGINT) => {                cs.setLong(index, v.get.asInstanceOf[Long]); cs.registerOutParameter(index, Types.BIGINT)            }            case GPInOut(v: Some[_], Types.INTEGER) => {                cs.setInt(index, v.get.asInstanceOf[Int]); cs.registerOutParameter(index, Types.INTEGER)            }            case GPInOut(v: Some[_], Types.VARCHAR) => {                cs.setString(index, v.get.asInstanceOf[String]); cs.registerOutParameter(index, Types.VARCHAR)            }            case GPInOut(v: Some[_], Types.LONGVARCHAR) => {                cs.setString(index, v.get.asInstanceOf[String]); cs.registerOutParameter(index, Types.LONGVARCHAR)            }            case GPInOut(v: Some[_], Types.CHAR) => {                cs.setString(index, v.get.asInstanceOf[String].head.toString); cs.registerOutParameter(index, Types.CHAR)            }            case _ => { Logger(s"Failed to match GPProcedureParameter in executeFunction (IN): index $index (${parameter.toString})") }        }    }    cs.execute()    // Now, step through each of the parameters, and get the corresponding result from the execute statement. If there is    // no result for the specified column (index), we'll basically end up getting a "nothing" back, which we strip out.    index = 0    val results: List[Any] = for (parameter <- ps.parameters) yield {        index = index + 1        parameter match {            case GPOut(Types.NUMERIC) | GPOut(Types.DECIMAL) => cs.getBigDecimal(index)            case GPOut(Types.BIGINT) => cs.getLong(index)            case GPOut(Types.INTEGER) => cs.getInt(index)            case GPOut(Types.VARCHAR | Types.LONGVARCHAR | Types.CHAR) => cs.getString(index)            case GPInOut(v: Some[_], Types.NUMERIC | Types.DECIMAL) => cs.getInt(index)            case GPInOut(v: Some[_], Types.BIGINT) => cs.getLong(index)            case GPInOut(v: Some[_], Types.INTEGER) => cs.getInt(index)            case GPInOut(v: Some[_], Types.VARCHAR | Types.LONGVARCHAR | Types.CHAR) => cs.getString(index)            case _ => {                Logger(s"Failed to match GPProcedureParameter in executeFunction (OUT): index $index (${parameter.toString})")            }        }    }    cs.close()    // Return the function return parameters (there should always be one, the caller will get a List with as many return    // parameters as we receive):    results.filter(_ != (()))}