get timestamp column from Oracle database produce exception

karlssor commented 1 year ago

Version

4.3.5, 4.3.8, 4.4.1

Context

I encountered an exception which looks suspicious while getting the value of an Oracle database timestamp column from a vertx Row.

row.getOffsetDateTime produce an exception on being unable to cast result to OffsetDateTime.

using a "simple" row.get(OffsetDateTime, column) produce a null, which is unexpected as the column is valued with "29-MAY-23 07.02.24.027212000 AM UTC". And getValue a reference to the oracle timestamp object

Do you have a reproducer?

    fun select() {
       val sql = "SELECT guide,updated FROM users WHERE type = ?"

        jdbcPool.withConnection {c -> 
            c.preparedQuery(sql).execute(Tuple.of("42")).onSuccess { rowset ->
                rowset.forEach { row ->
                    println("im guide ${row.getValue("GUIDE")}")
                    println("getvalue of updated ${row.getValue("UPDATED")}")
                    println("basic get of updated ${row.get(OffsetDateTime::class.java, "UPDATED")}")
                    println("getting updated")
                    val created = row.getOffsetDateTime("UPDATED")
                    println("done")
                }
            }
        }
    }

produce output

im guide Arthur
getvalue of updated oracle.sql.TIMESTAMPTZ@5c1e36cc
basic get of updated null
getting updated
TC 2023-05-29 00:02:26.004 [vert.x-eventloop-thread-4] ERROR io.vertx.core.impl.ContextBase - Unhandled exception
java.lang.ClassCastException: class java.lang.String cannot be cast to class java.time.OffsetDateTime (java.lang.String and java.time.OffsetDateTime are in module java.base of loader 'bootstrap')
    at io.vertx.sqlclient.Tuple.getOffsetDateTime(Tuple.java:572)
    at io.vertx.sqlclient.Row.getOffsetDateTime(Row.java:336)

Steps to reproduce

setup table with TIMESTAMP WITH TIME ZONE NOT NULL column
add row to table
get value of timestamp column

Extra

oracle.database.jdbc 21.5.0.0

karlssor commented 1 year ago

workaround/hack with custom decoder in resources/META-INF/services/io.vertx.ext.jdbc.spi.JDBCDecoder "works"

class WorkaroundOraTimestampDecoder : JDBCDecoderImpl() {
    override fun decodeSpecificVendorType(valueProvider: SQLValueProvider, descriptor: JDBCColumnDescriptor): Any {
        val value = valueProvider.apply(null)
        if (value is TIMESTAMPTZ) {
            return value.toOffsetDateTime()
        }
        return super.decodeSpecificVendorType(valueProvider, descriptor)
    }
}

tsegismont commented 1 year ago

In fact, this is not a bug. The Oracle driver returns null for JdbcType of TIMESTAMP WITH TIMEZONE columns.

You did the right thing by extending JDBCDecoderImpl and implementing decodeSpecificVendorType.

vert-x3 / vertx-jdbc-client