How to call a stored procedure and get return valu

Question

I'm trying to call a stored procedure from Slick 3.0 (in Play Framework). I've been over and over the documentation, but unfortunately the plain SQL docs at Typesafe never show calling a stored procedure.

What seems pretty straightforward is causing a typically obscure Scala error message:

val f = Try {
    val call: DBIO[Int] = sqlu"?=call app_glimpulse_invitation_pkg.n_send_invitation(${i.token}, ${i.recipientAccountId.getOrElse(None)}, ${i.email}, ${i.phoneNumber}, ${requestType})"

    val result: Future[Int] = db.run(call)

    val r = Await.result(result, Duration.Inf) // should only return one; use .seq.count(_.id != null)) to validate
    val z = result.value.get.get // should return the stored procedure return value...?
}

The above code causes this compiler error:

[error] /Users/zbeckman/Projects/Glimpulse/Server-2/project/glimpulse-server/app/controllers/GPInviteService/GPInviteService.scala:120: could not find implicit value for parameter e: slick.jdbc.SetParameter[Product with Serializable]
[error]             val call: DBIO[Int] = sqlu"?=call app_glimpulse_invitation_pkg.n_send_invitation(${i.token}, ${i.recipientAccountId.getOrElse(None)}, ${i.email}, ${i.phoneNumber}, ${requestType})"
[error]                                   ^

If I use a purely hard-coded call statement (remove all the ${i.xyz} references, I can get it to compile... but then, I get a runtime error reporting that Update statements should not return a result set.

That led me to changing the statement to a regular sql call:

val call: DBIO[Seq[(Int)]] = sql"call app_glimpulse_invitation_pkg.n_send_invitation('xyz', 1000, 1, '[e-mail]', NULL, 'I', ${out})".as[(Int)]
val result: Future[Int] = db.run(call)

But that also leads nowhere, yielding a compile error:

[error] /Users/zbeckman/Projects/Glimpulse/Server-2/project/glimpulse-server/app/controllers/GPInviteService/GPInviteService.scala:126: type mismatch;
[error]  found   : slick.driver.PostgresDriver.api.DBIO[Seq[Int]]
[error]     (which expands to)  slick.dbio.DBIOAction[Seq[Int],slick.dbio.NoStream,slick.dbio.Effect.All]
[error]  required: slick.dbio.DBIOAction[Int,slick.dbio.NoStream,Nothing]
[error]             val result: Future[Int] = db.run(call)
[error]                                              ^

I did find (while browsing through the Slick APIs) a prepareCall on the session, but again... no documentation on how to use this thing.

Any and all advice would be very deeply appreciated. This has become a huge blocker for me, as we really need to get a working call to our Postgres stored procedures. Thank you.

Answer 1

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] = 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'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 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'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(_ != (()))
}