sintaxis - procedimientos almacenados en postgresql pdf

Cómo llamar a un procedimiento almacenado y obtener un valor de retorno en Slick(usando Scala)

Question

Intento llamar a un procedimiento almacenado desde Slick 3.0 (en Play Framework). He revisado una y otra vez la documentación, pero desafortunadamente los documentos simples de SQL en Typesafe nunca muestran llamar a un procedimiento almacenado.

Lo que parece bastante sencillo es causar un mensaje de error de Scala típicamente oscuro:

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...?
}

El código anterior causa este error de compilación:

[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]                                   ^

Si utilizo un enunciado de llamada puramente codificado ( ${i.xyz} todas las referencias de ${i.xyz} , puedo hacer que se compile ... pero luego, recibo un error de tiempo de ejecución informando que las Update statements should not return a result set.

Eso me llevó a cambiar la declaración a una llamada sql regular:

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

Pero eso tampoco lleva a ninguna parte, produciendo un error de compilación:

[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]                                              ^

Encontré (mientras navegaba a través de las API de Slick) un prepareCall en la sesión, pero de nuevo ... no hay documentación sobre cómo usar esta cosa.

Todos y cada uno de los consejos serían muy apreciados. Esto se ha convertido en un gran bloqueador para mí, ya que realmente necesitamos obtener una llamada activa a nuestros procedimientos almacenados de Postgres. Gracias.

Answer 1

Bueno, después de mucha investigación y revisión de documentación conflictiva, encontré la respuesta. Desafortunadamente, no era el que estaba buscando:

Para las funciones de la base de datos que devuelven tablas completas o procedimientos almacenados, utilice consultas SQL simples. Los procedimientos almacenados que devuelven múltiples conjuntos de resultados actualmente no son compatibles.

En pocas palabras, Slick no admite funciones almacenadas o procedimientos listos para usar, por lo que tenemos que escribir el nuestro.

La respuesta es salir de Slick agarrando el objeto de sesión, y luego usar JDBC estándar para administrar la llamada de procedimiento. Para aquellos que están familiarizados con JDBC, no es una alegría ... pero, afortunadamente, con Scala podemos hacer algunos trucos muy agradables con la coincidencia de patrones que hacen que el trabajo sea más fácil.

El primer paso para mí fue armar una API externa limpia. Esto es lo que terminó pareciéndose a:

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()

Aquí hay un tutorial rápido: Creé un contenedor simple para modelar una llamada a procedimiento almacenado. GPProcedureParameterSet puede contener una lista de instancias GPIn, GPOut o GPInOut. Cada uno de estos mapas tiene un valor de tipo JDBC. El contenedor se ve así:

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(?, ?, ?, ?, ?, ?, ?)}")
}

Para completar, incluyo la enumeración GPProcedure para que pueda poner todo junto.

Todo esto se entrega a mi función execute() . Es grande y desagradable, huele a anticuado JDBC, y estoy seguro de que voy a mejorar el Scala bastante. Literalmente terminé esto a las 3 de la madrugada ... pero funciona, y funciona muy bien. Tenga en cuenta que esta función particular execute() devuelve una List contiene todos los parámetros OUT ... Tendré que escribir una función executeQuery() para manejar un procedimiento que devuelva un resultSet . (La diferencia es trivial: simplemente escribe un bucle que agarra un resultSet.next y lo resultSet.next todo en una List o cualquier otra estructura que desee).

Aquí está la gran desagradable función Scala <-> JDBC mapping execute() :

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