JDBC improvements and convenience method suggestions

[Vladimir Sitnikov]

>      "INSERT INTO t VALUES (?, ?, ?)")) {
>      s.set(1, 2, 3);
>I think that this should require that the provided parameters should
>.include **all** parameters

How should it handle nulls?
I'm fine if it throws in case null arguments are present, however it would
limit the use of the feature.
SQL deals with typed nulls, while Java has no way to tell if null is of
numeric or text type.

Consider { call procedure(?) } when there are two overloads (text and
numeric argument). The type of null enables to pick the proper overload.

Lukas>    // PreparedStatement
Lukas>    connection.executeUpdate("INSERT INTO t VALUES (?, ?, ?)", 1, 2,
3);

The same corner case with NULLS.
There should either be some standard-defined sentinel for typed null or NPE
for null values. Both variants do not sound like a solution to me.


Lukas> 6. Add SQLInput.getConnection() and SQLOutput.getConnection()

Makes sense at least for SQLOutput.
Current SQLOutput has writeArray(Array) method, however it is absolutely
not clear where to get the Array in the first place from.
The downside of having SQLOutput.getConnection is it would block
"streaming" implementation of SQLOutput.

I'm not sure what is the value of SQLInput.getConnection().


Note: Connection#createArrayOf(String elementTypeName, Object[] elements)
does not work for the databases that have NAMED types for arrays. For
instance, Oracle DB has explicit type names for "arrays".
On the other hand, PostgreSQL can't create named arrays (it is more like
Java).
Existing elementTypeName is confusing when client application tries to use
fully qualified type name.

For instance, the following works in PostgreSQL
CREATE SCHEMA baz;

CREATE TYPE baz.mood AS ENUM ('happy');
CREATE TYPE public."baz.mood" AS ENUM ('silly');

SELECT
    CAST('happy' AS baz.mood) AS schema_type,
    CAST('silly' AS "baz.mood") AS public_type

And the question is how one could create baz.mood[] array vs
public."baz.mood"[] one.
What should one pass to the elementTypeName?
something like createArrayOf(SQLType elementType, Object[] elements) might
be a good idea to have proper type qualifiers.
It looks like createNamedArrayOf(SQLType arrayType, Object[] elements)
could be useful when DB uses named arrays.

Vladimir