Thread: how to set a PreparedStatement column of XML type in 8.3?
I'm using the 8.3dev-602 JDBC driver, trying to set the value of an "xml" type column in an 8.3 database. The driver keeps throwing exceptions like ERROR: column "xml" is of type xml but expression is of type character varying I've tried a variety of different ways, including pstmt.setString, pstmt.setObject, and pstmt.setObject passing in Types.OTHER. What is the correct way to set a statement parameter value for an xml column? Thanks, m@
> I'm using the 8.3dev-602 JDBC driver, trying to set the value of an "xml" > type column in an 8.3 database. The driver keeps throwing exceptions like > > ERROR: column "xml" is of type xml but expression is of type character > varying > > I've tried a variety of different ways, including pstmt.setString, > pstmt.setObject, and pstmt.setObject passing in Types.OTHER. > > What is the correct way to set a statement parameter value for an xml > column? > > Thanks, > m@ I am able to work around the issue by setting a PGObject value with the type set to "xml" and the value my XML String. Is this the way the driver is expected to work, or should it map Strings to the xml type automatically in 8.3? -- m@
On Thu, 13 Dec 2007, Matt Magoffin wrote: > I am able to work around the issue by setting a PGObject value with the > type set to "xml" and the value my XML String. Is this the way the driver > is expected to work, or should it map Strings to the xml type > automatically in 8.3? > Expected? Should? The driver should implment SQLXML, but since that hasn't happened yet, it's expected that the only way to deal with it is via Strings. You can get around your original complaint via the stringtype=unspecified URL parameter so you don't have to go through PGobject, but long term we should use SQLXML. Kris Jurka
> Expected? Should? The driver should implment SQLXML, but since that > hasn't happened yet, it's expected that the only way to deal with it is > via Strings. You can get around your original complaint via the > stringtype=unspecified URL parameter so you don't have to go through > PGobject, but long term we should use SQLXML. > > Kris Jurka It was hardly a complaint, just a question about how one is expected to handle the xml type via JDBC in the current version of the driver. Thank you for the tip on stringtype=unspecified, that is another option for me. -- m@
On Thu, 13 Dec 2007, Matt Magoffin wrote: >> Expected? Should? > > It was hardly a complaint, just a question about how one is expected to > handle the xml type via JDBC in the current version of the driver. That may have came out a little wrong. I just was trying to point out that expected and should have different meanings depending on what you're asking. As a user of the JDBC API you should expect the driver to correctly map the xml type to SQLXML. As a user of the current pg jdbc driver code you would not expect it to return SQLXML because it hasn't been implemented, so it's not that you just need to tweak something to get it to work. Sorry if it came out in a negative way. Kris Jurka
I'm sure I do not have the experience or knowledge to understand the interaction between the PostgreSQL JDBC driver and the database. With that said the issue though that Matt has brought up, this type mapping concept, in the PostgreSQL JDBC driver for prepare statements parameters is fine as long as the java.sql.preparedstatement standard supports that type. Unfortunately the PostgreSQL database has some extended data types that are not defined by the Java JDBC PreparedStatement and when one of those types are not set appropriately the PostgreSQL JDBC throws the error Matt has described. This then seems to force the developer to either create a class for each of these non-supported data types or import the type from the org.postgresql driver. In may case neither of these solutions is viable without extending my code. I would like to maintain a generic interface. Why can't we just pass a string or object, setString or setObject, and have it accepted without a data type check in the PostgreSQL JDBC driver. Yes if the database can not convert the data to the type then throw a error. Other JDBC drivers allow an Integer type for example to set the value through either setString() or setInt(). The JDBC driver doesn't seem to care. The check/conversion seems to take place at the database level dana. >I'm using the 8.3dev-602 JDBC driver, trying to set the value of an "xml" > type column in an 8.3 database. The driver keeps throwing exceptions like > > ERROR: column "xml" is of type xml but expression is of type character > varying > > I've tried a variety of different ways, including pstmt.setString, > pstmt.setObject, and pstmt.setObject passing in Types.OTHER. > > What is the correct way to set a statement parameter value for an xml > column? > > Thanks, > m@ > I am able to work around the issue by setting a PGObject value with the > type set to "xml" and the value my XML String. Is this the way the driver > is expected to work, or should it map Strings to the xml type > automatically in 8.3? -- m@
On Thu, 13 Dec 2007, dmp wrote: > Why can't we just pass a string or object, setString or setObject, > and have it accepted without a data type check in the PostgreSQL JDBC driver. That's what the aforementioned stringtype=unspecified URL parameter does. The downside of this is that sometimes you want the server to know what types you're dealing with, for example overloaded functions. Kris Jurka
>> It was hardly a complaint, just a question about how one is expected to >> handle the xml type via JDBC in the current version of the driver. > > That may have came out a little wrong. I just was trying to point out > that expected and should have different meanings depending on what you're > asking. As a user of the JDBC API you should expect the driver to > correctly map the xml type to SQLXML. As a user of the current pg jdbc > driver code you would not expect it to return SQLXML because it hasn't > been implemented, so it's not that you just need to tweak something to get > it to work. Sorry if it came out in a negative way. Yes, that makes sense, thanks. I suppose I was "expecting" the driver for 8.3 to simply handle a Java String <-> Postgres xml type automatically, simply because the xml type in Postgres is sort of a glorified text type (at the moment)... and as a user of Postgres databases that store XML data as simple text columns, was investigating what it would be like to switch those over to the xml type in 8.3. Do you think it's worth considering having the 8.3 driver do this automatic conversion of String <-> xml, so that users don't have to use PGObject or set the stringtype=unspecified connection parameter? As a user, it definitely has benefits for ease of use, but I understand there might be good reasons not to do this. -- m@
On Fri, 14 Dec 2007, Matt Magoffin wrote: > Do you think it's worth considering having the 8.3 driver do this > automatic conversion of String <-> xml, so that users don't have to use > PGObject or set the stringtype=unspecified connection parameter? The driver can't really do that. At the time you say setString, the driver doesn't know that the real target type is xml, so the driver's only options are to set the parameter type to varchar (String) or unknown (the unspecified URL parameter). Kris Jurka
Exactly my point. dana. > On Fri, 14 Dec 2007, Matt Magoffin wrote: > >> Do you think it's worth considering having the 8.3 driver do this >> automatic conversion of String <-> xml, so that users don't have to use >> PGObject or set the stringtype=unspecified connection parameter? > > > The driver can't really do that. At the time you say setString, the > driver doesn't know that the real target type is xml, so the driver's > only options are to set the parameter type to varchar (String) or > unknown (the unspecified URL parameter). > > Kris Jurka
>> Do you think it's worth considering having the 8.3 driver do this >> automatic conversion of String <-> xml, so that users don't have to use >> PGObject or set the stringtype=unspecified connection parameter? > > The driver can't really do that. At the time you say setString, the > driver doesn't know that the real target type is xml, so the driver's only > options are to set the parameter type to varchar (String) or unknown (the > unspecified URL parameter). > > Kris Jurka What about with calls to setObject(int, Object, int) when a String is passed in and the type set to Types.OTHER? Would it be possible then... say if Object is a String, to treat it as if the stringtype=unspecified parameter was set? I apologize if these are obvious questions, I'm just bouncing ideas around. -- m@
On Sat, 15 Dec 2007, Matt Magoffin wrote: > What about with calls to setObject(int, Object, int) when a String is > passed in and the type set to Types.OTHER? Would it be possible then... > say if Object is a String, to treat it as if the stringtype=unspecified > parameter was set? Sounds like a good idea to me. Right now it bails out with the following, so it's not like we'd be breaking anything that's working... org.postgresql.util.PSQLException: Cannot cast an instance of java.lang.String to type Types.OTHER Kris Jurka
On Sat, 15 Dec 2007, Matt Magoffin wrote: > What about with calls to setObject(int, Object, int) when a String is > passed in and the type set to Types.OTHER? Would it be possible then... > say if Object is a String, to treat it as if the stringtype=unspecified > parameter was set? I've implemented this for the 8.3 driver in CVS. It takes Object.toString() of anything given with Types.OTHER, so it doesn't necessarily need to be in String form already. I've uploaded some jars here if you want to give it a test. http://ejurka.com/pgsql/jars/mm/ Kris Jurka
>> What about with calls to setObject(int, Object, int) when a String is >> passed in and the type set to Types.OTHER? Would it be possible then... >> say if Object is a String, to treat it as if the stringtype=unspecified >> parameter was set? > > I've implemented this for the 8.3 driver in CVS. It takes > Object.toString() of anything given with Types.OTHER, so it doesn't > necessarily need to be in String form already. > > I've uploaded some jars here if you want to give it a test. > > http://ejurka.com/pgsql/jars/mm/ Yep, that did the trick for me. Thanks! -- m@