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

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

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...0/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...0/msg00118.php

Oliver also mentions problems with anyelement/anyarray functions:

http://archives.postgresql.org/pgsql...0/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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"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@ejurka.com>
To: "Oliver Jowett" <oliver@opencloud.com>
Cc: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>;
<pgsql-jdbc@postgresql.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@postgresql.org so that your
> message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly