How to integrate the Scala Squeryl ORB with play 2.0 framework?

Question

I am trying to use Squeryl ORB with play 2.0 framework, but when calling DB.getConnection() during initialization I get:

BadPath: path parameter: Invalid path ' - could not find datasource for defaultdb': Token not allowed in path expression: '-' (you can double-quote this token if you really want it here)

The database configuration looks like this (conf/application.conf):

db.default.url="jdbc:postgresql://localhost/mydb?user=postgres&password=postgres"
db.default.driver=org.postgresql.Driver
db.default.jndiName=defaultdb

And the initializing:

object Global extends GlobalSettings {
  override def onStart(app: Application) {

    SessionFactory.externalTransactionManagementAdapter = Some(() => 
        Some(new Session(
          DB.getConnection("defaultdb", true),
          new PostgreSqlAdapter)))
    ...

Is this the right way to do it? Is it correct to use the db.default.jndiName config value as parameter value to DB.getConnection()?

Or should it be done like this?:

  SessionFactory.concreteFactory = Some(() =>
    Session.create(
      java.sql.DriverManager.getConnection("jdbc:postgresql://..."),
      new PostgreSqlAdapter))

This works, but then I am not able to use the squeryl query objects in the template for iteration, which I hoped would be possible with externalTransactionManagementAdapter.

Update:

I corrected to the following: DB.getConnection("default", true) and removed the db.default.jndiName config. With this I am able to get and use a connection, but the second time getConnection() is called, it throws SQLException: Timed out waiting for a free available connection.

Update 2:

I haven't managed to use externalTransactionManagementAdapter, but concreteFactory works well - as described below.

Can you be a bit more descriptive regarding what you're trying to accomplish? I'm not very familiar with Play or what a "template for iteration" would mean. The externalTransactionManagementAdapter is for situations where SQL transactions will be handled outside of Squeryl (not with transaction{} / inTransaction{} blocks). I can't tell from your question whether it fits your need. — Dave Whittaker, Mar 14 '12 at 15:57
If I understand "play" correctly, it should be able to handle the SQL transactions for me. In that case, I should be able to iterate over a squeryl query outside a transaction block, for instance when rendering html in a play template, right? The described error is probably more a play-issue than squeryl, though. — Roar Skullestad, Mar 14 '12 at 16:24

rssh · Accepted Answer · 2012-04-21T18:52:50.787

8

Next works for me:

import play.db.DB 
import play.api.Application 
import play.api.GlobalSettings 
import org.squeryl._ 
import org.squeryl.adapters._

....

object Global extends GlobalSettings
{

override def onStart(app:Application):Unit =
{
 SessionFactory.concreteFactory = Some(
      () => Session.create(DB.getDataSource().getConnection(),
                           dbAdapter)
 );
}

override def onStop(app:Application):Unit =
{
}

val dbAdapter = new PostgreSqlAdapter();

}

edited Apr 21 '12 at 18:52

answered Mar 14 '12 at 16:48

rssh

464
4
5

Thanks! I changed to use this code, but I still get `SQLException: Timed out...` as described in the update above. Are you using the connections in `transaction` blocks? Could you maybe give a simple example of a select and the context it's used in? – Roar Skullestad Mar 14 '12 at 21:04
This exception is throwed by BoneCP when all connections are acquired and none is released. The reason can vary from application error (not closing jdbc connections at all ; using transaction instead inTransaction and nesting one blocks more than pool size, loop with connection grabbing inside; etc) – rssh Mar 14 '12 at 22:20
2

So, better enable trace in log [minimal - application.log=TRACE in application.conf], more detailed -- enable trace specially for "com.jolbox.bonecp" logger ] and look for 'Check in connection' and Check out connection' messages in log. – rssh Mar 14 '12 at 22:22
For every http request (doing a single db-select) 17 connections were checked out and only one returned. The problem was my usage of `externalTransactionManagementAdapter` - and the fact that play had to be restarted manually when changing SessionFactory. So when using `concreteFactory` it does work, but it demands a `transaction` block around the view call when the squeryl query is used in the view. I had hoped that play would manage transactions for me by using `externalTransactionManagementAdapter`, but I guess I would still have to tell squeryl to "return" the connection somehow... – Roar Skullestad Mar 15 '12 at 12:58
2

I personally use concreteFactory, I would advise against using externalTransactionManagementAdapter with Play, as it is made for frameworks that manage the commmit for you (typically on a per web request mode, like Lift). Setting concreteFactory does mean that you need to explicitely declare transaction {} or inTransaction {}. Personally I prefer explicit transaction. – Max L. Apr 19 '12 at 18:38

How to integrate the Scala Squeryl ORB with play 2.0 framework?

Update:

Update 2:

1 Answers1

Linked