Re: setObject on PGInterval throws "Unknown Type null"

Jean-Pierre Pelletier wrote:

> if myPgInterval != null
> Originally code: myPreparedStatement.setObject(i, myPGInterval)
> It throws SQLException "Unknown type null"

That's a bug; the PGInterval(String) constructor is broken. As a
workaround try this:

PGInterval value = new PGInterval();
value.setValue("1 day");

I will fix this in CVS shortly. Thanks for the report!

> We tried: myPreparedStatement.setObject(i,myPGInterval,Types.OTHER)
> It throws "setNull(i, Types.OTHER) is not supported; use
> setObject(i,null, Types.OTHER) instead"
>
> How should setObject be coded with a PGInterval ?

For extension types, you need to jump through some hoops to identify the
actual postgresql type in use, since a simple Types value isn't sufficient.

Try this to set a null:

myPreparedStatement.setObject(i, new PGInterval());

(the default ctor constructs an interval-typed "null" value, similar to
"null::interval" in plain SQL)

I'll try to cram that into a more useful exception message..

> With JDBC Build 309, setObject requires an SQL Type when object == null,
> this makes setObject(i, object) useless.

Hardly useless; it works in every non-null case.

> Why do JDBC requires an SQL Type when the same statement can be
> processed by psql
> without the SQL type specified?

The short answer: because JDBC is strongly typed while psql knows
nothing at all about parameter types.

There was extended discussion about this on the list at the time. See:

http://archives.postgresql.org/pgsql-jdbc/2004-10/msg00059.php

I have an outstanding query with the JDBC expert group about clarifying
this case. The JDBC API goes to some trouble to provide type information
for every parameter even when nulls are involved (see, for example,
setNull). setObject(i,null) seems like an oversight.

> Is it supported by other DBMS?

I don't know -- is it? I can think of one (Clustra, subsequently bought
by Sun) that would break horribly if you tried something like this.

-O