JDBC improvements and convenience method suggestions

[Vladimir Sitnikov]

Lukas>Note, I deliberately left out CallableStatements in general

Unfortunately, `CallableStatement extends PreparedStatement`, so `set`
would be inherited automatically.
There's a "fire and forget" kind of callable statements as well, so
set(Object..args) would be useful there as well.

Lukas>but this is already the case today, and it is really more of an
implementation problem than an API specification problem
Lukas> it should be totally possible to call
Lukas>    pstmt.setObject(1, null);

Please check javadoc for that method. There's an explicit advice there to
use `setNull` for maximal portability. Not all the databases support
untyped nulls.

Note: playing with `setNull(1, Types.VARCHAR)` vs `setNull(1,
Types.NUMERIC` could easily result in different child cursors in Oracle DB.
If the statement has N binds, you easily blow the database with 1<<N
distinct cursors for the same SQL text.

The problem with PreparedStatement.set(Object[] params) is it encourages
bad API being used.

There's a relevant problem with batch API: it is not clear what client
means if it submits a batch with alternating datatypes.
For instance, if the first row is submitted via setString(1, "42"); and the
subsequent one is submitted via setInt(1, 42);
It forces the driver to separate those rows into different sub-batches, and
it is really amazing when setObject(1, null) is used there.

If only parameter types were fixed at the prepare phase...

Lukas>PreparedStatement.set(Object[] params, int[] types)

It looks a bit odd: it lacks varargs, lacks visual correspondence between
param and its type.

Lukas>SELECT CAST(? AS INTEGER) FROM t;

Well, one of the options is to invent a DSL for typed parameter
placeholders.
For instance: SELECT {cast ? INT} FROM t
That could mean "the parameter is INT no matter which setter is used".

Vladimir>The downside of having SQLOutput.getConnection is it would block
"streaming" implementation of SQLOutput.

Lukas>Why would it? OracleSQLOutput (the only implementation I'm aware of)
has a property conn:OracleConnection, so this is a simple property access
in that implementation. The same is true for OracleJdbc2SQLInput

There's pgjdbc-ng as well:
https://github.com/impossibl/pgjdbc-ng/blob/fcfe2e8774eb8942c3cdfe3bba06fdca7842f300/src/main/java/com/impossibl/postgres/api/jdbc/PGSQLOutput.java#L42


Suppose SQLOutput is streaming (id int, contents byte[], name varchar)
right into the SocketOutputStream.
Suppose it has already sent id and contents, and it expects
writeString(String) call to finish the struct.
If one issues a method on a Connection that results in additional messages
being sent over the wire, then SQLOutput stream gets screwed.

For instance: PostgreSQL protocol cannot multiplex (it does not support
multiple parallel request streams over the same socket at the same time).

What I mean is Connection is a too powerful interface for SQLOutput kind of
case.

The same might happen when SQLInput is in the middle of processing.

Lukas>3. If you want to read vendor specific data types, like Oracle's
TIMESTAMPTZ

Should it be handled by SQLInput.readObject(TIMESTAMPTZ.class)?


Lukas> jOOQ just serialises all sorts of vendor-specific PostgreSQL types
as strings and reads them as well as strings. That's the only way I ever
got these things to work on PostgreSQL.

I know the issue is there, however could you please clarify what do you
mean by that?
Do you mean `createArrayOf` is not required at all since you can just use
Strings all over the place?
Do you mean "current createArrayOf is just fine since jOOQ does not use it
anyway"?

Do you just mean "you just don't know the way to pass qualified/unqualified
name to createArrayOf"? If so, what do you think of
createArrayOf(SQLTypeOrWhateverInterfaceThatPointsToATypeInTheDatabase,
Object[] args) ?

Vladimir