Re: setObject on PGInterval throws "Unknown Type null" - Mailing list pgsql-jdbc

Thanks
your workaround with myPGInterval.setValue() works great

myPreparedStatement.setObject(i, new PGInterval()) also works to output an
SQL null
but it creates inconsistency.
With JDBC Build 309, there are now TWO DIFFERENT JAVA MAPPING OF AN SQL NULL
INTERVAL

   myPGInterval = (PGInterval) myResultSet.getObject(i)
   sets myPGInterval to null

   myPreparedStatement.setObject(i,myPGInterval)
   needs myPGInterval to be set to new PGInterval() instead of null

   so READING AN INTERVAL FROM A TABLE AND WRITING IT TO ANOTHER TABLE WOULD
NOT WORK
   unless null is converted to new PGInterval()

By contrast with a JDBC built-in type such as Integer, an SQL null can be
handled like this
   myInteger = (Integer) myResultSet.getObject(i)
   sets myPGInteger to null

   myPreparedStatement.setObject(i, myPGInteger, Types.INTEGER)
   needs myPGInteger to be null

It would be more consistent to output a SQL null interval with
myPreparedStatement.setObject(i, myPGInterval, SomeSpecializedType)
or even with
myPreparedStatement.setNull(i, SomeSpecializedType)

----- Original Message -----
From: "Oliver Jowett" <oliver@opencloud.com>
To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>
Cc: <pgsql-jdbc@postgresql.org>; "Jean-Pierre Pelletier"
<jppelletier@e-djuster.com>
Sent: Monday, January 24, 2005 3:17 PM
Subject: Re: [JDBC] 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org