3

I'm trying to call from Slick stored procedure which returns nothing but use read parameters as input and writable parameters as output. I combined (in Play application) SimpleDBIO tip found here, old tip for calling stored proc in Slick 2.x and legacy jsp code calling this procedure:

@javax.inject.Singleton
class UserDao @Inject()(dbp: models.DatabaseProvider) {

def lookupUser(u: User): Boolean = {
    // param 1 is output one, params 2,3 are input ones
    val callProc = """{BEGIN CALL GET_USR_INF(?, ?, ?); END;}"""
    println (s">>>>>> p1. user=${u.username}")
    SimpleDBIO[Unit] {
        implicit session => {
            println (s">>>>>> p2 ")
            val cs = session.connection.prepareCall(callProc)
            // val cs = session.connection.prepareStatement(callProc)
            println (s">>>>>> p2.1 ")
            cs.registerOutParameter(1, java.sql.Types.INTEGER);
            cs.setString(2, u.username);
            cs.setString(3, u.password);
            println (s">>>>>> p3")
            val result = cs.executeUpdate()
            println (s">>>>>> p4. result=${result}")
            val retStatus = cs.getInt(1)  // general result here
            println (s">>>>>> p5. retStatus=${retStatus}")
        }
    }
    println (s">>>>>> p-end ")

but it looks like code inside SimpleDBIO is not running at all, points p2-p5 are not printed and no jdbc calls are traced. How to do it correctly? Where are not much newbie-level examples of SimpleDBIO around. DatabaseProvider definition is below if it matter:

import javax.inject.{Inject, Provider, Singleton}
import com.typesafe.config.Config
import slick.jdbc.JdbcBackend.Database  //some imports are skipped for brevity
@Singleton
class DatabaseProvider @Inject() (config: Config) extends Provider[Database] {
  lazy val get = Database.forConfig("myapp.database", config)
}
Valent
  • 75
  • 6

1 Answers1

3

Well, looks like mentioned tip is not complete, one need to run declared SimpleDBIO somehow (do not laugh please, written for newbie like me). Instead of SimpleDBIO I found example for SimpleJdbcAction which is essentially the same. So working code is below: 

    val a = SimpleJdbcAction { ctx =>
        val cs = ctx.connection.prepareCall(callProc)
        cs.registerOutParameter(1, java.sql.Types.INTEGER);
        cs.setString(2, u.username);
        cs.setString(3, u.password);
        val result = cs.executeUpdate()
        retStatus = cs.getInt(1)
    }
    Await.result(dbp.get.run(a), Duration.Inf)
    println (s">>>>>> p-end, s=$retStatus")
Valent
  • 75
  • 6