2

Doobie

Without much experience in either Scala or Doobie I am trying to select data from a DB2 database. Following query works fine and prints as expected 5 employees.

import doobie.imports._, scalaz.effect.IO

object ScalaDoobieSelect extends App {
    val urlPrefix = "jdbc:db2:"
    val schema = "SCHEMA"
    val obdcName = "ODBC"
    val url = urlPrefix + obdcName + ":" +
              "currentSchema=" + schema + ";" +
              "currentFunctionPath=" + schema + ";"
    val driver = "com.ibm.db2.jcc.DB2Driver"
    val username = "username"
    val password = "password"

    implicit val han = LogHandler.jdkLogHandler       // (ii)

    val xa = DriverManagerTransactor[IO](
        driver, url, username, password
    )

    case class User(id: String, name: String)

    def find(): ConnectionIO[List[User]] =
        sql"SELECT ID, NAME FROM EMPLOYEE FETCH FIRST 10 ROWS ONLY"
            .query[User]
            .process
            .take(5)                                  // (i)
            .list

    find()
        .transact(xa)
        .unsafePerformIO
        .foreach(e => println("ID = %s, NAME = %s".format(e.id, e.name)))
}

Issue

When I want to read all selected rows and remove take(5), so I have .process.list instead of .process.take(5).list, I get following error. (i)

com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][3.64.133] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

I am wondering what take(5) changes that it does not return an error. To get more information about the invalid operation, I have tried to enable logging. (ii) Unfortunately, logging is not supported for streaming. How can I get more information about what operation causes this error?

Plain JDBC

Below, in my opinion equivalent, plain JDBC query works as expected and returns all 10 rows.

import java.sql.{Connection,DriverManager}

object ScalaJdbcConnectSelect extends App {
    val urlPrefix = "jdbc:db2:"
    val schema = "SCHEMA"
    val obdcName = "ODBC"
    val url = urlPrefix + obdcName + ":" +
              "currentSchema=" + schema + ";" +
              "currentFunctionPath=" + schema + ";"
    val driver = "com.ibm.db2.jcc.DB2Driver"
    val username = "username"
    val password = "password"

    var connection:Connection = _
    try {
        Class.forName(driver)
        connection = DriverManager.getConnection(url, username, password)
        val statement = connection.createStatement
        val rs = statement.executeQuery(
            "SELECT ID, NAME FROM EMPLOYEE FETCH FIRST 10 ROWS ONLY"
        )
        while (rs.next) {
            val id = rs.getString("ID")
            val name = rs.getString("NAME")
            println("ID = %s, NAME = %s".format(id,name))
        }
    } catch {
        case e: Exception => e.printStackTrace
    }
    connection.close
}

Environment

As can be seen in the error message, I am using db2jcc.jar version 3.64.133. DB2 is used in version 11.

Valy Dia
  • 2,781
  • 2
  • 12
  • 32
conspicillatus
  • 195
  • 3
  • 11
  • Looks like the driver doesn't support streaming (like almost all SQL drivers...) – JVS Jul 12 '17 at 12:38
  • According to IBM this driver appears to [support streaming](https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/java/src/tpc/imjcc_r0053795.html). Further I have added all properties from [this answer](https://stackoverflow.com/a/22760068/5498201), i still get the same result. – conspicillatus Jul 12 '17 at 13:39
  • I think you should be using getClob for streaming, instead of getString – JVS Jul 13 '17 at 19:31
  • The example where I am using `getText` works perfectly fine. `getClob` is for large character objects, I am selecting fixed length strings. Streaming in Doobie is not the same as in above article. In Doobie it means using a `scalaz.stream.Process` and for IBM it is related to LOB retrieval. So I guess that's the wrong track. – conspicillatus Jul 15 '17 at 14:46

0 Answers0