JDBC improvements and convenience method suggestions

[Lukas Eder]

Am Fr., 13. Juli 2018 um 11:50 Uhr schrieb Vladimir Sitnikov <
sitnikov.vladimir at gmail.com>:

> 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.
>

Yes, you're right.


> 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.
>

So Ctrl+C / Ctrl+V to the new method, right? I mean, it's clear that my
suggestion is not maximally portable. Neither is setObject(). Yet, e.g. in
order to pass JSR-310 types, we have to use setObject() now.

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


> 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.
>

Yes, if you do it manually. But I reckon that when calling set(Object...),
there will be a single default behaviour. Which clearly needs more thought
as you've shown.


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

That is an interesting question, and I'm happy that you mention it, given
that ADBA is going down that route, avoiding the many type-specific
overloads and encouraging an Object-accepting way of setting bind values...


> 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.
>

This is no different from today, though, is it?


> 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.
>

That seems to be an implementation detail. Implementations that cannot
handle the new method can still throw the SQLFeatureNotSupportedException.
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.


> 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) ?
>

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 :

INSERT INTO t VALUES (?::my_schema.my_type)

... where the bind value is a string that looks something like
{"(1,2),(2,3)",5,true,,"{""(1,2)""}"}. With this approach, there's no
relying on JDBC API. If you're passing a simple String[] to setObject(),
this also works. But as soon as types are involved, you're on your own in
PostgreSQL.