Unexpected java.sql.SQLException: Operation not allowed after ResultSet closed when querying tables

[Marco Mistroni]

Hi all
  i am a SQUERYL noob, and i am finding SQL exception while querying   squeryl tables
I have a scala standalope application with the following Adapter configured

trait DBConnector {
  Class.forName("com.mysql.jdbc.Driver");
    SessionFactory.concreteFactory = Some(() =>
      Session.create(
        java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/cameldb",
          "zzzzz", "xxxx"),
        new MySQLInnoDBAdapter()))
 
}

and the following class with these two methods

class SquerylPersistenceService extends PersistenceService with DBConnector{
    import SharesSchema._

    def findAllShares2 = {
      transaction  {
        println("Begin..")
        val res = from(SharesSchema.shares)(share => select(share))// replace with Iterator. we dont
                                                                         // need to consume everythingUsers
        println("end")
        res.map(s => s.copy())
      }
    }

   
    def findAllShares: Seq[Share] = {
      transaction  {
        println("Begin")
        val res = from(SharesSchema.shares)(share => select(share)).iterator// replace with Iterator. we dont
                                                                         // need to consume everythingUsers
        println("end")
        res.toSeq
      }

This component will be called to find out all Shares in the database
Now,whenever i call findAllShares2, e.g.

val res = service.findAllShares2
res.foreach(println)

everything is fine  (i am guessing because i am doing a copy)

However, whenever i run e,g this

val res = serivce.findAllShares
println(res.size)

i get this exception

java.sql.SQLException: Operation not allowed after ResultSet closed
  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:959)
  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:862)
  at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:743)
  at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6320)
  at org.squeryl.dsl.AbstractQuery$$anon$1._next(AbstractQuery.scala:214)
  at org.squeryl.dsl.AbstractQuery$$anon$1.hasNext(A

But i suppose my case is a general usecase for querying data from Squeryl. So what am i doing wrong?
Am i using the wrong Adapter>?  am i missing some configurations in the adapter?
How shall i write my query methods?

kind regarsd
 marco

[Boris Chazalet]

Your problem is probably the .iterator and toSeq. I am guessing it makes the whole thing 'lazy' and will only try to fetch elements inside your println which is outside your transaction block.

Try to call `toList` rather:

  def findAllShares: Seq[Share] = {
      transaction  {
        println("Begin")
        val res = from(SharesSchema.shares)(share => select(share))

        println("end")
        res.toList
      }

[Marco Mistroni]

Many thanks Boris, that fixed it

i temporarily 'bypassed' the issue by doing a res.map(s => s.copy()).toSeq    but i think my temp solution is just a needless hack

kind regards

 Marco

--
You received this message because you are subscribed to a topic in the Google Groups "Squeryl" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/squeryl/bZs2G96FR9w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to squeryl+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.