CODEWITHSUNDEEP
scalaslick
Dan Collins
Dan Collins

Reputation: 1018

Keeping database session open

I am trying to leverage TypeSafe's slick library to interface with a MySQL server. All the gettingstarted/tutorial examples use withSession{} where the framework will automatically create a session, execute the queries within the {}'s, then terminate the session at the end of the block.

My program is rather chatty, and I would like to maintain a persistent connection throughout the execution of the script. So far I have pieced together this code to explicitly create and close sessions.

val db = Database.forURL("jdbc:mysql://localhost/sandbox", user = "root", password="***", driver = "com.mysql.jdbc.Driver")
val s = db.createSession()
...
s.close()

Where I can execute queries in between. However, when I try to execute a command, such as

(Q.u + "insert into TEST (name) values('"+name+"')").execute

It crashes because it cannot find the implicit session. I don't completely understand the syntax of the execute definition in the documentation, but it seems like there might be an optional parameter to pass an explicit session. I've tried using .execute(s), but that spits out a warning that (s) doesn't do anything in a pure expession.

How do I explicitly specify a pre-existing session to run a query on?

Appended: Trial code for JAB's solution

class ActorMinion(name: String) extends Actor
{
    Database.forURL("jdbc:mysql://localhost/sandbox", user = "root", password="****", driver = "com.mysql.jdbc.Driver") withSession
    {
        def receive =
        {
            case Execute =>
            {
                (Q.u + "insert into TEST (name) values('"+name+"')").execute

                sender ! DoneExecuting(name,output,err.toString)
            }
        }
    }
}

Which returns compile error

[error] /home/ubuntu/helloworld/src/main/scala/hw.scala:41: missing parameter type for expanded function

[error] The argument types of an anonymous function must be fully known. (SLS 8.5)

[error] Expected type was: ?

[error] {

[error] ^

[error] one error found

Upvotes: 8

Views: 1468

Answers (2)

Dan Collins
Dan Collins

Reputation: 1018

I was able derive what I needed from this answer

//imports at top of file
//import Database.threadLocalSession <--this should be commented/removed
import scala.slick.session.Session // <-- this should be added
......
//These two lines in actor constructor
val db = Database.forURL("jdbc:mysql://localhost/sandbox", user = "root", password="****", driver = "com.mysql.jdbc.Driver")
implicit var session: Session = db.createSession()
......
session.close() //This line in actor destructor

Upvotes: 8

JAB
JAB

Reputation: 21079

Just enclose the relevant part of your script in withSession{}. Note that if you are keeping the session open for a while/are performing lots of database manipulation queries, you should also look into taking advantage of transactions.

And you should really be using prepared statements for inserts if the data has a potentially external source.

Upvotes: 2

Related Questions