JDBC improvements and convenience method suggestions

[Vladimir Sitnikov]

Lukas> So Ctrl+C / Ctrl+V to the new method, right?

setObject(int, Object) is known to be extremely hard to implement for null
values, and alternating datatypes is a known source of performance issues
and/or database crashes.
I would try to refrain from adding one more problem source of that kind
into JDBC API.
Something like con.execute(String, JdbcValue... args) would be great if
there was a way to make that reasonably sane to read and write.

Lukas>Perhaps it is an option after all to come back to your suggestion of
passing NULL sentinels. E.g. java.sql.SQLType.nullValue();

How would you use that?
con.execute("select id, name from orders where type=? and name?",
type!=null ? type : JDBCType.NUMERIC.nullValue(), name != null ? name :
JDBCType.VARCHAR.nullValue())
?
Or
con.execute("select id, name from orders where type=? and name?",
JDBCType.NUMERIC.ofNullable(type), JDBCType.VARCHAR.ofNullable(orderName))

In my opinion neither of the above look great :-/
Note: Object[].. vararg would leave a trap for those who forget to use null
sentinel. JdbcValue... vararg would force everybody to use the sentinels,
however the call would not be that nice anymore (and it would add
wrapper/indirection for each and every parameter).

Yet more alternative is to wait till Java has nullable vs non-nullable
types, so the argument can be made NotNullableObject...params.

Lukas>given that ADBA is going down that route, avoiding the many
type-specific overloads and encouraging an Object-accepting way of setting
bind values...

Just to clarify: I'm somewhat fine with absence of setInt kind of methods.
Object null is an issue.

Lukas>That seems to be an implementation detail. Implementations that
cannot handle the new method can still throw the
SQLFeatureNotSupportedException.

The thing with current SQLOutput is it provides absolutely no way to create
instances of struct/array or other vendor-defined types.
It would be nice if SQLOutput could isolate client code (implementation of
SQLData) from having a reference to SQL Connection.

In other words, throwing SQLFeatureNotSupportedException makes SQLOutput
unusable for structs/arrays, thus it makes very little sense to use
SQLOutput then.

Lukas> I'm sure they can get it right as well, though. After all, a JDBC
ResultSet is also a stream and it also gives access to the Connection.

Note: ResultSet have to buffer the data for similar reasons. However
methods like getBinaryStream explicitly declare that one must read all the
data in a stream prior to getting the value of any other column.

Lukas>All I'm saying is that creating arrays through JDBC API in PostgreSQL
is useless because the driver cannot serialise types and nested types, so
jOOQ directly does things like

I beg your pardon, but we discuss JDBC spec here, not PostgreSQL and not
jOOQ.
Just to recap:
* You suggest improvement to SQLOutput/SQLInput to make it usable for
struct/array types.
* I agree, and I add a note that current createArrayOf is ill-defined for
databases that use named types for arrays (e.g. for Oracle). Technically
speaking PostgreSQL supports both named and unnamed array types yet current
createArrayOf leaves no room for named array types.

Both items are relevant from my point of view. For instance it might make
sense to create SqlTypeFactory interface or something like that for
creating structs/arrays.

I find that discussion on "PostgreSQL JDBC not supporting arrays yet" is
irrelevant here.

Vladimir