How to call Stored Procedures and defined functions in MySQL with Slick 3.0

Question

I have defined in my db something like this

CREATE FUNCTION fun_totalInvestorsFor(issuer varchar(30)) RETURNS INT
NOT DETERMINISTIC
BEGIN
  RETURN (SELECT COUNT(DISTINCT LOYAL3_SHARED_HOLDER_ID) 
      FROM stocks_x_hldr
      WHERE STOCK_TICKER_SIMBOL = issuer AND
            QUANT_PURCHASES > QUANT_SALES);
END;

Now I have received an answer from Stefan Zeiger (Slick lead) redirecting me here: User defined functions in Slick

I have tried (having the following object in scope):

lazy val db = Database.forURL("jdbc:mysql://localhost:3306/mydb",
driver = "com.mysql.jdbc.Driver", user = "dev", password = "root")
val totalInvestorsFor = SimpleFunction.unary[String, Int]("fun_totalInvestorsFor")
totalInvestorsFor("APPLE") should be (23)

Result: Rep(slick.lifted.SimpleFunction$$anon$2@13fd2ccd fun_totalInvestorsFor, false) was not equal to 23

I have also tried while having an application.conf in src/main/resources like this:

tsql = {
  driver = "slick.driver.MySQLDriver$"
  db {
    connectionPool = disabled
    driver = "com.mysql.jdbc.Driver"
    url = "jdbc:mysql://localhost/mydb"
  }
}

Then in my code with @StaticDatabaseConfig("file:src/main/resources/application.conf#tsql")

tsql"select fun_totalInvestorsFor('APPLE')" should be (23)

Result: Error:(24, 9) Cannot load @StaticDatabaseConfig("file:src/main/resources/application.conf#tsql"): No configuration setting found for key 'tsql' tsql"select fun_totalInvestorsFor('APPLE')" should be (23) ^

I am also planning to call stored procedures that return one tuple of three values, via sql"call myProc(v1).as[(Int, Int, Int)]

Any ideas?

EDIT: When making sql""""SELECT COUNT(DISTINCT LOYAL3_SHARED_HOLDER_ID) FROM stocks_x_hldr WHERE STOCK_TICKER_SIMBOL = issuer AND QUANT_PURCHASES > QUANT_SALES""".as[(Int)] results in SqlStreamingAction[Vector[Int], Int, Effect] instead of the suggested DBIO[Int] (from what I infer) suggested by the documentation

Answer 1

I've been running into exactly the same problem for the past week. After some extensive research (see my post here, I'll be adding a complete description of what I've done as a solution), I decided it can't be done in Slick... not strictly speaking.

But, I'm resistant to adding pure JDBC or Anorm into our solution stack, so I did find an "acceptable" fix, IMHO.

The solution is to get the session object from Slick, and then use common JDBC to manage the stored function / stored procedure calls. At that point you can use any third party library that makes it easier... although in my case I wrote my own function to set up the call and return a result set.

val db = Database.forDataSource(DB.getDataSource)
var response: Option[GPInviteResponse] = None

db.withSession {
    implicit session => {
        // Set up your call here... (See my other post for a more detailed
        // answer with an example:
        // procedure is eg., "{?=call myfunction(?,?,?,?)}"
        val cs = session.conn.prepareCall(procedure.toString)
        // Set up your in and out parameters here
        // eg. cs.setLong(index, value)
        val result = cs.execute()
        val rc = result.head.asInstanceOf[Int]

        response = rc match {
            // Package up the response to the caller
        }
    }
}
db.close()

I know that's pretty terse, but as I said, see the other thread for a more complete posting. I'm putting it together right now and will post the answer shortly.