Re: setObject on PGInterval throws "Unknown Type null"

We upgraded to JDBC Build 309 on PostgreSQL 8.0 from
JDBC Build 307 on PostgreSQL 7.4 and setObject now fails
on PGInterval

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

We tried: myPreparedStatement.setObject(i, myPGInterval, Types.OTHER)
It throws "Unknown type null"

if myPgInterval == null
Original code: myPreparedStatement.setObject(i,myPGInterval)
It throws "setObject(i,null) is not supported. Instead, use setNull(i,type) or setObject(i,null,type)"

We tried: myPreparedStatement.setNull(i, Types.OTHER)
It throws "setNull(i, Types.OTHER) is not supported; use setObject(i,null, Types.OTHER) instead"

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 ?

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

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

Why is a standard JDBC method setObject(i, object) rendered useless?
Is it supported by other DBMS?

Thanks
Jean-Pierre Pelletier

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

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(at)opencloud(dot)com>
To: "Jean-Pierre Pelletier" <pelletier_32(at)sympatico(dot)ca>
Cc: <pgsql-jdbc(at)postgresql(dot)org>; "Jean-Pierre Pelletier"
<jppelletier(at)e-djuster(dot)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

Jean-Pierre Pelletier wrote:

> With JDBC Build 309, there are now TWO DIFFERENT JAVA MAPPING OF AN SQL
> NULL INTERVAL

Yes. No need to shout :)

> myPGInterval = (PGInterval) myResultSet.getObject(i)
> sets myPGInterval to null
>
> myPreparedStatement.setObject(i,myPGInterval)
> needs myPGInterval to be set to new PGInterval() instead of 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)

I considered using a new Types value, but there are a couple of problems:

- how do you allocate Types values that don't collide with future
specifications?
- the type registration interface would incompatibly change

Also, I don't see how that on its own fixes generic "table copy" code --
you still need to know you're dealing with an interval on that
particular column so you can pass the right Types value to setObject.
Perhaps it works this out via metadata. We could change the
table/resultset metadata to return the modified Types values, but that's
starting to be an invasive change that affects more than just users of
extension types.

Another approach would be to continue to use Types.OTHER, but return the
equivalent of "new PGInterval()" from getObject() when a NULL resultset
value is seen. But that leads to other problems: code that retrieves a
value via getObject() has to be aware that a non-null object might
actually mean a null column.

I'm not sure that either cure is better than the disease.

-O

On Wed, 26 Jan 2005, Oliver Jowett wrote:

> [ways to set null values for non-standard types]

I'm kind of leaning to removing the restriction that nulls must be
strongly typed. Compared to the current workarounds the idea that a
(very) few cases won't work isn't that bad. What I recall "? IS NULL"
won't work and "func(?)" could be ambiguous, but that doesn't stop you
from specifying a type for these cases. For non-null values we need the
strong typing to ensure that we don't send data in a different format than
the server expects, but this is not an issue with nulls.

Kris Jurka

"func(?)" will be rejected by the server at runtime if there is ambiguity,
but it could be coded as "func(cast(?) as sometype)" if ? can be null.

In the case of "? IS NULL", I don't understand how the type of the null
would
affect the evaluation of this boolean expression.

Jean-Pierre Pelletier

----- Original Message -----
From: "Kris Jurka" <books(at)ejurka(dot)com>
To: "Oliver Jowett" <oliver(at)opencloud(dot)com>
Cc: "Jean-Pierre Pelletier" <pelletier_32(at)sympatico(dot)ca>;
<pgsql-jdbc(at)postgresql(dot)org>
Sent: Tuesday, January 25, 2005 5:30 PM
Subject: Re: [JDBC] setObject on PGInterval throws "Unknown Type null"

>
>
> On Wed, 26 Jan 2005, Oliver Jowett wrote:
>
>> [ways to set null values for non-standard types]
>
> I'm kind of leaning to removing the restriction that nulls must be
> strongly typed. Compared to the current workarounds the idea that a
> (very) few cases won't work isn't that bad. What I recall "? IS NULL"
> won't work and "func(?)" could be ambiguous, but that doesn't stop you
> from specifying a type for these cases. For non-null values we need the
> strong typing to ensure that we don't send data in a different format than
> the server expects, but this is not an issue with nulls.
>
> Kris Jurka
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

On Tue, 25 Jan 2005, Jean-Pierre Pelletier wrote:

> "func(?)" will be rejected by the server at runtime if there is ambiguity,

Not always, sometimes it will resolve the ambiguity itself in a less than
expected manner:

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

> In the case of "? IS NULL", I don't understand how the type of the null
> would affect the evaluation of this boolean expression.
>

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

Oliver also mentions problems with anyelement/anyarray functions:

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

I am OK with all of these failings because they are easily solvable using
casts or strong typing, while the Types.OTHER case is not easily solvable.

Kris Jurka

Kris Jurka wrote:

> For non-null values we need the
> strong typing to ensure that we don't send data in a different format than
> the server expects, but this is not an issue with nulls.

What about when we prepare a statement with a null parameter, then later
use it with a non-null parameter? There is some protocol code needed
here to get the inferred types back.

We currently break in the case where parameter types change between
executions, but that's more easily fixable since we have all the
necessary information already available.

-O

On Wed, 26 Jan 2005, Oliver Jowett wrote:

> What about when we prepare a statement with a null parameter, then later
> use it with a non-null parameter? There is some protocol code needed
> here to get the inferred types back.
>
> We currently break in the case where parameter types change between
> executions, but that's more easily fixable since we have all the
> necessary information already available.
>

I'm not sure I'm willing to make that distinction, they seem like the same
thing to me. If we fixed the second case and found the solution to the
first intractable then you could make the case to require typed nulls, but
since the second case is broken that argument doesn't carry a lot of
weight with me.

Kris Jurka

Kris Jurka wrote:
>
> On Wed, 26 Jan 2005, Oliver Jowett wrote:
>
>
>>What about when we prepare a statement with a null parameter, then later
>>use it with a non-null parameter? There is some protocol code needed
>>here to get the inferred types back.
>>
>>We currently break in the case where parameter types change between
>>executions, but that's more easily fixable since we have all the
>>necessary information already available.
>>
>
>
> I'm not sure I'm willing to make that distinction, they seem like the same
> thing to me. If we fixed the second case and found the solution to the
> first intractable then you could make the case to require typed nulls, but
> since the second case is broken that argument doesn't carry a lot of
> weight with me.

It's not unfixable, it just means there is more work required to fix the
existing brokenness.

-O

On Wed, 26 Jan 2005, Oliver Jowett wrote:

> It's not unfixable, it just means there is more work required to fix the
> existing brokenness.
>

Actually I suppose it depends on how much you want to hold onto your
server prepared statement. In both cases you could state that any change
of datatype will cause a reparse. This would kill the usefulness if you
used untyped nulls intermixed with typed values, but it would be no worse
than never preparing at all.

Kris Jurka

With JDBC Build 309,
pstmt.setObject(i, x)
has to be replaced by

if (x == null) pstmt.setNull(i, Types.SomeType) else pstmt.setObject(i, x)
which may be simplified as

pstmt.setObject(i, x, Types.SomeType)

That's what I was referring to when I said that setObject(i, x) has been
rendered useless

The type information could be provided regardless of null by a method
specific to each class.
It could use new method names or be overloaded versions of setObject

for PGInterval, it could also hide SQL null => new PGInterval

public void setObject(int parameterIndex, PGInterval x)
throws SQLException
{
this.setObject(parameterIndex, x == null ? new PGInterval() : x);
}

These methods could also be available for jdbc buil-in types

public void setObject(int parameterIndex, Integer x)
throws SQLException
{
this.setObject(parameterIndex, x, Types.INTEGER);
}

public void setObject(int parameterIndex, String x)
throws SQLException
{
this.setObject(parameterIndex, x, Types.VARCHAR); // could also be
this.setString(parameterIndex, x);
}

I understand that this is not standard JDBC, but as you guys highlighted,
there is no provision
in the standard for supplying type with null values on extended types. Plus
it would provide some form
of backward compatibility for setObject(i,x)

Jean-Pierre Pelletier

----- Original Message -----
From: "Oliver Jowett" <oliver(at)opencloud(dot)com>
To: "Kris Jurka" <books(at)ejurka(dot)com>
Cc: "Jean-Pierre Pelletier" <pelletier_32(at)sympatico(dot)ca>;
<pgsql-jdbc(at)postgresql(dot)org>
Sent: Tuesday, January 25, 2005 6:33 PM
Subject: Re: [JDBC] setObject on PGInterval throws "Unknown Type null"

> Kris Jurka wrote:
>>
>> On Wed, 26 Jan 2005, Oliver Jowett wrote:
>>
>>
>>>What about when we prepare a statement with a null parameter, then later
>>>use it with a non-null parameter? There is some protocol code needed here
>>>to get the inferred types back.
>>>
>>>We currently break in the case where parameter types change between
>>>executions, but that's more easily fixable since we have all the
>>>necessary information already available.
>>>
>>
>>
>> I'm not sure I'm willing to make that distinction, they seem like the
>> same thing to me. If we fixed the second case and found the solution to
>> the first intractable then you could make the case to require typed
>> nulls, but since the second case is broken that argument doesn't carry a
>> lot of weight with me.
>
> It's not unfixable, it just means there is more work required to fix the
> existing brokenness.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Jean-Pierre Pelletier wrote:

> public void setObject(int parameterIndex, PGInterval x)
> throws SQLException

Overloading setObject() sounds very risky. How about this instead:

public void setInterval(int parameterIndex, PGInterval x)
throws SQLException

That said, this doesn't help the general case as we can't know the set
of extension types ahead of time.

> public void setObject(int parameterIndex, Integer x)
> throws SQLException

> public void setObject(int parameterIndex, String x)
> throws SQLException

I don't see the benefit of these methods; they're just a nonstandard way
of doing things that standard JDBC already lets you do (the second one
especially -- it's identical to PreparedStatement.setString())

-O

new method names such as setInterval would be fine with me,
as I don't mind changing my code.

It doesn't fix the general case, but at least if fixes PGInterval which is
an improvement.

The advantage of overloaded versions of setObject over new method names was
to
provide backward compatibility for setObject(i,x)
i.e. no need to recode setObject(i,x) to setObject(i,x,Types.someType)
because setObject(i,x) is now broken with null objects.

I don't know how important is backward compatibility, that's your call.

Jean-Pierre Pelletier

----- Original Message -----
From: "Oliver Jowett" <oliver(at)opencloud(dot)com>
To: "Jean-Pierre Pelletier" <pelletier_32(at)sympatico(dot)ca>
Cc: "Kris Jurka" <books(at)ejurka(dot)com>; <pgsql-jdbc(at)postgresql(dot)org>
Sent: Wednesday, January 26, 2005 2:47 PM
Subject: Re: [JDBC] setObject on PGInterval throws "Unknown Type null"

> Jean-Pierre Pelletier wrote:
>
>> public void setObject(int parameterIndex, PGInterval x)
>> throws SQLException
>
> Overloading setObject() sounds very risky. How about this instead:
>
> public void setInterval(int parameterIndex, PGInterval x)
> throws SQLException
>
> That said, this doesn't help the general case as we can't know the set of
> extension types ahead of time.
>
>> public void setObject(int parameterIndex, Integer x)
>> throws SQLException
>
>> public void setObject(int parameterIndex, String x)
>> throws SQLException
>
> I don't see the benefit of these methods; they're just a nonstandard way
> of doing things that standard JDBC already lets you do (the second one
> especially -- it's identical to PreparedStatement.setString())
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

Jean-Pierre Pelletier wrote:

> The advantage of overloaded versions of setObject over new method names
> was to
> provide backward compatibility for setObject(i,x)
> i.e. no need to recode setObject(i,x) to setObject(i,x,Types.someType)
> because setObject(i,x) is now broken with null objects.

How is this backwards-compatible? Without recompilation, adding extra
methods doesn't do anything. With recompilation, you need to add a cast
to PGStatement to see the new methods. If you're changing the code
anyway, why not just change to the typed setObject() variant?

-O

Oliver Jowett wrote:

> We currently break in the case where parameter types change between
> executions, but that's more easily fixable since we have all the
> necessary information already available.

I just fixed this in CVS, it wasn't as painful to do as I originally
thought. It should also work for the NULL-as-oid-0 case if we go back to
doing that -- any change in parameter type oids causes a reprepare.

-O

On Fri, 28 Jan 2005, Oliver Jowett wrote:

> I just fixed this in CVS, it wasn't as painful to do as I originally
> thought. It should also work for the NULL-as-oid-0 case if we go back to
> doing that -- any change in parameter type oids causes a reprepare.
>

The attached patch implements the Describe (Statement) protocol
message which allows us to do a number of things.

First it re-enables untyped nulls. When a statement with an untyped null
is executed (and we expect it to be reused) a describe statement is issued
to get the backend to resolve its type for us which is then fed back into
the ParameterList. This allows the following code to not require
re-parses for every execution:

PreparedStatement ps = conn.prepareStatemet("SELECT 1 + ? ");
ps.setObject(1, null);
ps.executeQuery();
ps.setInt(1, 1);
ps.executeQuery();
ps.setObject(1, null);
ps.executeQuery();
ps.setInt(1, 1);
ps.executeQuery();

A problem I came across is that it will actually require a reparse on the
second execution above because the prepared statements parameters are
cloned and stored during QueryExecutorImpl.sendParse which is before the
results of the describe statement message can be fed back into the system.
Any ideas on this would be appreciated.

Additionally a new QueryExecutor.QUERY_ flag has been added that indicates
we only want to describe the statement and not actually execute. This
uses the new Describe(Statement) support to implement
PreparedStatement.getMetaData() for an unexecuted statement and
PreparedStatement.getParameterMetaData().

Kris Jurka

On Sun, 30 Jan 2005, Kris Jurka wrote:

> The attached patch implements the Describe (Statement) protocol
> message which allows us to do a number of things.
>

Incremental diff for minor bug. If we are going to execute this statement
after a describe message the doneAfterRowDescNoData code will not be
called, so we need to increment the describeIndex ourselves.

Kris Jurka

On Sun, 30 Jan 2005, Kris Jurka wrote:
>
> The attached patch implements the Describe (Statement) protocol
> message which allows us to do a number of things.
>

Another incremental fix to make sure PSQLParameterMetaData gets built.

Kris Jurka

Kris Jurka wrote:

> A problem I came across is that it will actually require a reparse on the
> second execution above because the prepared statements parameters are
> cloned and stored during QueryExecutorImpl.sendParse which is before the
> results of the describe statement message can be fed back into the system.
> Any ideas on this would be appreciated.

Also store the statement itself in the describe queue, and update (in
the cloned array attached to the statement) any parameter OIDs that are
currently 0 when the ParameterDescription arrives?

-O

Kris Jurka wrote:
> On Fri, 28 Jan 2005, Oliver Jowett wrote:
>
>>I just fixed this in CVS, it wasn't as painful to do as I originally
>>thought. It should also work for the NULL-as-oid-0 case if we go back to
>>doing that -- any change in parameter type oids causes a reprepare.
>
> The attached patch implements the Describe (Statement) protocol
> message which allows us to do a number of things.

Looks good to me.

-O