ZConnection.isValid does not close PreparedStatement when the underlying connection is valid

[karthik3791]

Based on current snippet of ZConnection.isValid : -

  def isValid(): ZIO[Scope, ZSQLException, Boolean] = {
    for {
      closed    <- access(_.isClosed)
      statement <- access(_.prepareStatement("SELECT 1"))
      isAlive   <- ZIO.succeed(!closed && statement != null)
    } yield isAlive
  }.refineOrDie { case e: SQLException =>
    ZSQLException(e)
  }

The PreparedStatement created from SQL statement : "SELECT 1" is not closed by the connection when it is valid.

When used with IBM DB2 database, this results in following exception
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH204 0X5359534C564C3031, DRIVER=3.64.106

More details are at : -
https://www.ibm.com/support/pages/how-many-concurrently-running-statements-allowed-db2-java-application-and-how-increase-it

This is exhausting the number of concurrent PreparedStatement instances allowed on DB end.

Possible Fix ?

Should we consider closing the PreparedStatement as below ?

  def isValid(): ZIO[Any, ZSQLException, Boolean] = ZIO.scoped {
    for {
      closed    <- access(_.isClosed)
      statement <- access(_.prepareStatement("SELECT 1"))
      _ <- if(statement !=null) ZIO.addFinalizer(ZIO.attempt(statement.close).ignoreLogged) else ZIO.unit
      isAlive   <- ZIO.succeed(!closed && statement != null)
    } yield isAlive
  }.refineOrDie { case e: SQLException =>
    ZSQLException(e)
  }