Well, after much research and a review of the conflicting documentation, I found the answer. Unfortunately, this was not the one I was looking for:
For database functions that return full tables or are stored, please use Plain SQL Queries. Stored procedures that return multiple result sets are not currently supported.
On the bottom line, Slick does not support stored functions or procedures out of the box, so we must write our own.
The answer is to take it out of Slick by capturing the session object, and then use standard JDBC to control the procedure call. For those of you who are familiar with JDBC, it’s not a joy ... but, fortunately, with Scala we can do pretty nice pattern matching tricks to make things easier.
The first step for me was to build a clean external API. Here's what happened:
val db = Database.forDataSource(DB.getDataSource) var response: Option[GPInviteResponse] = None db.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 is a quick walkthrough. I created a simple container for simulating a stored procedure call. GPProcedureParameterSet may contain a list of instances of GPIn, GPOut, or GPInOut. Each of them displays a JDBC type value. The container is as follows:
case class GPOut(parameterType: Int) extends GPProcedureParameter object GPOut case class GPIn(value: Option[Any], parameterType: Int) extends GPProcedureParameter object GPIn case class GPInOut(value: Option[Any], parameterType: Int) extends GPProcedureParameter object GPInOut case class GPProcedureParameterSet(parameters: List[GPProcedureParameter]) object GPProcedureParameterSet object GPProcedure extends Enumeration { type GPProcedure = Value val SendInvitation = Value("{?=call app_glimpulse_invitation_pkg.n_send_invitation(?, ?, ?, ?, ?, ?, ?)}") }
For completeness, I include the GPProcedure enumeration so you can put it all together.
All this is passed to my execute() function. It's big and nasty, it smells like old-fashioned JDBC, and I'm sure I will improve Scala a bit. I literally finished it at 3am last night ... but it works, and it works very well. Note that this execute() function returns a List containing all OUT parameters ... I need to write a separate executeQuery() function to handle the procedure that returns resultSet . (The difference is trivial: you just write a loop that captures resultSet.next and it all contributes to List or any other structure you want).
Here's a big nasty Scala ↔ JDBC execute() mapping:
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(_ != (())) }