Thread: 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
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"
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"
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)"
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"
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"
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.
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?
without the SQL type specified?
Why is a standard JDBC method setObject(i, object) rendered useless?
Is it supported by other DBMS?
Is it supported by other DBMS?
Thanks
Jean-Pierre Pelletier
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@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
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@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
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@opencloud.com> To: "Kris Jurka" <books@ejurka.com> Cc: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca>; <pgsql-jdbc@postgresql.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@opencloud.com> To: "Jean-Pierre Pelletier" <pelletier_32@sympatico.ca> Cc: "Kris Jurka" <books@ejurka.com>; <pgsql-jdbc@postgresql.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@postgresql.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
Fix for changing parameter types with server prepared statements (was Re: setObject on PGInterval throws "Unknown Type null")
From
Oliver Jowett
Date:
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
Attachment
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
Attachment
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
Attachment
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