Thread: ResultSet.getClob() causing problems when used with JPA's @Lob
Hi.
I have a varchar-column in a table which maps to a field (of type String) in a
JPA-entity. When marking that String-property with the JPA @Lob-annotation,
using Hibernate as my JPA-provider, it stops working as expected when using
PostgreSQL (works as expected on Oracle and SQL Server). The problem is that
Hibernate, correctly, calls ResultSet.getClob() but PG internally calls
getLong(), which obviously won't work with varchar-types, resulting in:
Caused by: org.postgresql.util.PSQLException: Bad value for type long : Hei
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(AbstractJdbc2ResultSet.java:2796)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2ResultSet.java:2019)
at org.postgresql.jdbc4.Jdbc4ResultSet.getClob(Jdbc4ResultSet.java:43)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getClob(AbstractJdbc2ResultSet.java:384)
at
org.hibernate.type.descriptor.sql.ClobTypeDescriptor$4.doExtract(ClobTypeDescriptor.java:104)
After googling around I see this issue has come up before:
http://archives.postgresql.org/pgsql-jdbc/2010-02/msg00004.php
One is encurraged to use ResultSet.getString() instead. Hm, well - being at
Hibernate's mercy here, I don't really have that luxury. So, is PG's JDBC-
driver going to fix this flaw (IMNSHO it is a flaw) or is there consensus in the
PG-community that clobs are special and are not to be treated as
Strings/varchars?
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CTO
Public key: http://home.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment. |
Org.nr: NO 981 479 076 | |
| |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
Hi, PGSQL doesn't have CLOB type, instead JDBC uses LOB, but PGSQL doesn't have "clear" lob field type. It uses in table column with type OID, and supporting table for storing large objects. Look at bug submited by me http://opensource.atlassian.com/projects/hibernate/browse/HHH-4617 Should be fixed in 3.6.1 release. Kind regards, Radosław Smogura http://softperience.eu Andreas Joseph Krogh <andreak@officenet.no> Monday 07 February 2011 00:01:21 > Hi. > > I have a varchar-column in a table which maps to a field (of type String) > in a JPA-entity. When marking that String-property with the JPA > @Lob-annotation, using Hibernate as my JPA-provider, it stops working as > expected when using PostgreSQL (works as expected on Oracle and SQL > Server). The problem is that Hibernate, correctly, calls > ResultSet.getClob() but PG internally calls getLong(), which obviously > won't work with varchar-types, resulting in: > > Caused by: org.postgresql.util.PSQLException: Bad value for type long : Hei > at > org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(AbstractJdbc2ResultSet.j > ava:2796) at > org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2ResultSet. > java:2019) at > org.postgresql.jdbc4.Jdbc4ResultSet.getClob(Jdbc4ResultSet.java:43) at > org.postgresql.jdbc2.AbstractJdbc2ResultSet.getClob(AbstractJdbc2ResultSet. > java:384) at > org.hibernate.type.descriptor.sql.ClobTypeDescriptor$4.doExtract(ClobTypeDe > scriptor.java:104) > > After googling around I see this issue has come up before: > http://archives.postgresql.org/pgsql-jdbc/2010-02/msg00004.php > > One is encurraged to use ResultSet.getString() instead. Hm, well - being at > Hibernate's mercy here, I don't really have that luxury. So, is PG's JDBC- > driver going to fix this flaw (IMNSHO it is a flaw) or is there consensus > in the PG-community that clobs are special and are not to be treated as > Strings/varchars?
On 02/07/2011 09:11 AM, Radosław Smogura wrote: > Hi, > PGSQL doesn't have CLOB type, instead JDBC uses LOB, but PGSQL doesn't have > "clear" lob field type. It uses in table column with type OID, and supporting > table for storing large objects. > > Look at bug submited by me > http://opensource.atlassian.com/projects/hibernate/browse/HHH-4617 > Should be fixed in 3.6.1 release. I'm experiencing this with 3.6.1 so whatever that issue fixes, MaterializedClobType (which is the default type Hibernate uses when it sees @Lob on String-types) still uses ClobTypeDescriptor, which again has this code: protected X doExtract(ResultSet rs, String name, WrapperOptions options) throws SQLException { return javaTypeDescriptor.wrap( rs.getClob( name ), options ); } And it should be able to call rs.getClob(). It's not clear to me why the JDBC-driver for PG shouldn't just use rs.getString() internally for getClob() calls? Requiring special-handling in Hibernate for dealing with Strings by setting an obscure property just doesn't seem right. And I don't want to set a property to handle CLOB which might affect how I use BLOBs. -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / CTO Public key: http://home.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Rosenholmveien 25 | know how to do a thing and to watch | 1414 Trollåsen | somebody else doing it wrong, without | NORWAY | comment. | Org.nr: NO 981 479 076 | | | | Tlf: +47 24 15 38 90 | | Fax: +47 24 15 38 91 | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
PostgreSQL doesn't have field type CLOB, nor BLOB. If eveny it would be possible for driver to read CLOB from varchar, driver will be unable to set proper value when calling UPDATE or INSTERT. It is due to internal way how PSQL deals with those values. I know drivers supports custom casting, but casting from varchar to clob is implicite prohibted in JDBC. If you look at B-6 table x indicates possible casting. There is no x, nor X on varchar cross clob Kind regards, Radosław Smogura Andreas Joseph Krogh <andreak@officenet.no> Monday 07 February 2011 21:50:10 > On 02/07/2011 09:11 AM, Radosław Smogura wrote: > > Hi, > > PGSQL doesn't have CLOB type, instead JDBC uses LOB, but PGSQL doesn't > > have > > > "clear" lob field type. It uses in table column with type OID, and > > supporting > > > table for storing large objects. > > > > Look at bug submited by me > > http://opensource.atlassian.com/projects/hibernate/browse/HHH-4617 > > Should be fixed in 3.6.1 release. > > I'm experiencing this with 3.6.1 so whatever that issue fixes, > MaterializedClobType (which is the default type Hibernate uses when it > sees @Lob on String-types) still uses ClobTypeDescriptor, which again > has this code: > > protected X doExtract(ResultSet rs, String name, WrapperOptions options) > throws SQLException { > return javaTypeDescriptor.wrap( rs.getClob( name ), options ); > } > > And it should be able to call rs.getClob(). > > It's not clear to me why the JDBC-driver for PG shouldn't just use > rs.getString() internally for getClob() calls? Requiring > special-handling in Hibernate for dealing with Strings by setting an > obscure property just doesn't seem right. And I don't want to set a > property to handle CLOB which might affect how I use BLOBs.
On 02/07/2011 10:38 PM, Radosław Smogura wrote: > PostgreSQL doesn't have field type CLOB, nor BLOB. I know that. > If eveny it would be possible for driver to read CLOB from varchar, driver > will be unable to set proper value when calling UPDATE or INSTERT. It is due > to internal way how PSQL deals with those values. Isn't the whole concept of CLOB just a bi-product of old proprietary database-design where some RDBMS'es are unable to store arbitrary long varchars? What properties do CLOBs have that varchars don't, except being large? I don't understand why CLOBs can't be treated like text in the PG-driver as PG handles large Strings just fine in varchar/text-columns. If a DBA has for some reason defined a scale, say VARCHAR(10), on a column and tries to store a CLOB in it, well - his problem; He should get an Exception from PG. > I know drivers supports custom casting, but casting from varchar to clob is > implicite prohibted in JDBC. If you look at B-6 table x indicates possible > casting. There is no x, nor X on varchar cross clob Does it not make sense to you to be able to cast a *character large object* to a *variable length character*-type and visa versa? If the only argument is spec-complience I'm not getting it... -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / CTO Public key: http://home.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Rosenholmveien 25 | know how to do a thing and to watch | 1414 Trollåsen | somebody else doing it wrong, without | NORWAY | comment. | Org.nr: NO 981 479 076 | | | | Tlf: +47 24 15 38 90 | | Fax: +47 24 15 38 91 | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
On 08/02/11 12:25, Andreas Joseph Krogh wrote: > On 02/07/2011 10:38 PM, Radosław Smogura wrote: >> PostgreSQL doesn't have field type CLOB, nor BLOB. > > I know that. > >> If eveny it would be possible for driver to read CLOB from varchar, driver >> will be unable to set proper value when calling UPDATE or INSTERT. It is due >> to internal way how PSQL deals with those values. > > Isn't the whole concept of CLOB just a bi-product of old proprietary > database-design where some RDBMS'es are unable to store arbitrary long > varchars? What properties do CLOBs have that varchars don't, except > being large? I don't understand why CLOBs can't be treated like text in > the PG-driver as PG handles large Strings just fine in > varchar/text-columns. If a DBA has for some reason defined a scale, say > VARCHAR(10), on a column and tries to store a CLOB in it, well - his > problem; He should get an Exception from PG. The different property is that clobs are mapped to OIDs that reference an externally-stored LOB. So at the most basic "what do I get from the server?" level they're different to varchar - the column type is an OID, not varchar, so you have to send/receive OID values not strings and perform a separate step to manage the data referenced by the OID. They're mostly superceded by bytea/text, but they do still have some properties that mean they do not behave identically (e.g. they are essentially pass-by-reference, not pass-by-value; and you can modify parts of them in-place without passing around the whole value). >> I know drivers supports custom casting, but casting from varchar to clob is >> implicite prohibted in JDBC. If you look at B-6 table x indicates possible >> casting. There is no x, nor X on varchar cross clob > > Does it not make sense to you to be able to cast a *character large > object* to a *variable length character*-type and visa versa? > > If the only argument is spec-complience I'm not getting it... Conceivably, the driver could notice that a column is a varchar and expose it via a different implementation of the clob interface that doesn't try to interpret the value as an OID identifying an underlying LOB. But that's not currently done because it's not one of the required JDBC conversions (so if you expect that behavior from an arbitrary driver you're already on shaky ground) and there's been no requests for it before this that I can remember offhand. Also, the reverse conversion isn't going to work, as mentioned above, so I don't know how useful it'd be to you (the driver knows it got a varchar in a resultset, but in general it won't know that when you said PreparedStatement.setClob() you actually meant "please turn this into a varchar and insert that, instead of creating a LOB and inserting the OID as you usually would"). You could make it a big connection-wide toggle that said whether to interpret clobs as LOBs or varchars, I suppose, but that's rather a big hammer to fix what is arguably a problem in your persistence layer. (For example, why can't you tell Hibernate exactly the same thing - "please interpret my character LOBs as varchars" - which would be presumably be useful to more than just the postgresql driver?) The fundamental question here is "why are you trying to map a varchar to a Clob?" .. As you say, clobs are mostly redundant anyway. Given that your schema uses varchar, why don't you just use the varchar-style accessors? It's not unreasonable to expect your access method to match the underlying schema, surely. Oliver
På tirsdag 08. februar 2011 kl 01:53:23 skrev "Oliver Jowett" <oliver@opencloud.com>: > On 08/02/11 12:25, Andreas Joseph Krogh wrote: > > On 02/07/2011 10:38 PM, Radosław Smogura wrote: > >> PostgreSQL doesn't have field type CLOB, nor BLOB. > > > > I know that. > > > >> If eveny it would be possible for driver to read CLOB from varchar, driver > >> will be unable to set proper value when calling UPDATE or INSTERT. It is due > >> to internal way how PSQL deals with those values. > > > > Isn't the whole concept of CLOB just a bi-product of old proprietary > > database-design where some RDBMS'es are unable to store arbitrary long > > varchars? What properties do CLOBs have that varchars don't, except > > being large? I don't understand why CLOBs can't be treated like text in > > the PG-driver as PG handles large Strings just fine in > > varchar/text-columns. If a DBA has for some reason defined a scale, say > > VARCHAR(10), on a column and tries to store a CLOB in it, well - his > > problem; He should get an Exception from PG. > > The different property is that clobs are mapped to OIDs that reference > an externally-stored LOB. So at the most basic "what do I get from the > server?" level they're different to varchar - the column type is an OID, > not varchar, so you have to send/receive OID values not strings and > perform a separate step to manage the data referenced by the OID. > > They're mostly superceded by bytea/text, but they do still have some > properties that mean they do not behave identically (e.g. they are > essentially pass-by-reference, not pass-by-value; and you can modify > parts of them in-place without passing around the whole value). Firstly; My issue here only affects CLOBs, not BLOBs. Secondly; I'm not trying to be anal here, just trying to understand the arguments. Thirdly; Didn't we just agree on that PG doesn't have CLOBs? If PG doesn't have CLOBs, how can it map CLOBs to OIDs? Underwhat circumstances would PG need an OID to map a *C*LOB? Are you suggesting to store CLOBs as bytea? Is anyone doingthis? > >> I know drivers supports custom casting, but casting from varchar to clob is > >> implicite prohibted in JDBC. If you look at B-6 table x indicates possible > >> casting. There is no x, nor X on varchar cross clob > > > > Does it not make sense to you to be able to cast a *character large > > object* to a *variable length character*-type and visa versa? > > > > If the only argument is spec-complience I'm not getting it... > > Conceivably, the driver could notice that a column is a varchar and > expose it via a different implementation of the clob interface that > doesn't try to interpret the value as an OID identifying an underlying > LOB. But that's not currently done because it's not one of the required > JDBC conversions (so if you expect that behavior from an arbitrary > driver you're already on shaky ground) and there's been no requests for > it before this that I can remember offhand. Also, the reverse conversion > isn't going to work, as mentioned above, so I don't know how useful it'd > be to you (the driver knows it got a varchar in a resultset, but in > general it won't know that when you said PreparedStatement.setClob() you > actually meant "please turn this into a varchar and insert that, instead > of creating a LOB and inserting the OID as you usually would"). You > could make it a big connection-wide toggle that said whether to > interpret clobs as LOBs or varchars, I suppose, but that's rather a big > hammer to fix what is arguably a problem in your persistence layer. (For > example, why can't you tell Hibernate exactly the same thing - "please > interpret my character LOBs as varchars" - which would be presumably be > useful to more than just the postgresql driver?) All this stuff sounds like an implementation detail to me. The programmer using JDBC should, IMO, be able to use rs.setClob()to varchar/text-columns transparently as there's no reason to treat it differently than setString(). > The fundamental question here is "why are you trying to map a varchar to > a Clob?" .. As you say, clobs are mostly redundant anyway. Given that > your schema uses varchar, why don't you just use the varchar-style > accessors? It's not unreasonable to expect your access method to match > the underlying schema, surely. The reason I map the String-property as @Lob (which, by default, makes Hibernate use setClob()) is for interoperability withother DBs, like Oracle, which require this annotation in order to be able to store Strings which are over 4000 charslong. These properties are stored in Oracle as CLOB and in PG as VARCHAR. -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / CTO Public key: http://home.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Rosenholmveien 25 | know how to do a thing and to watch | 1414 Trollåsen | somebody else doing it wrong, without | NORWAY | comment. | Org.nr: NO 981 479 076 | | | | Tlf: +47 24 15 38 90 | | Fax: +47 24 15 38 91 | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
Andreas Joseph Krogh wrote: > Firstly; My issue here only affects CLOBs, not BLOBs. BLOB-vs-bytea is essentially the same issue as CLOB-vs-varchar, FWIW. If you tried to use the BLOB interface on a bytea column or vice versa, you'd see similar problems, because bytea is not a BLOB, regardless of the fact that they're both ways of storing large binary data. > Thirdly; Didn't we just agree on that PG doesn't have CLOBs? If PG doesn't have CLOBs, how can it map CLOBs to OIDs? Underwhat circumstances would PG need an OID to map a *C*LOB? Are you suggesting to store CLOBs as bytea? Is anyone doingthis? No, I don't think we agreed on that. The PG driver certainly does have something that is a CLOB - it is a column that contains an OID referencing a separate LO that contains character data. The server itself doesn't know about that mapping, but when you're working at the JDBC level, you do need to be aware of the mapping. (One way to look at it is to pretend that "clob" is spelled "oid" in your schema) > All this stuff sounds like an implementation detail to me. The programmer using JDBC should, IMO, be able to use rs.setClob()to varchar/text-columns transparently as there's no reason to treat it differently than setString(). Why do you assume this? JDBC's CLOB is not the same type as VARCHAR, and JDBC doesn't require that they're interchangeable. And, in fact, the postgresql driver's mapping of CLOB is not interchangeable with VARCHAR, which is the root of your problem.. >> The fundamental question here is "why are you trying to map a varchar to >> a Clob?" .. As you say, clobs are mostly redundant anyway. Given that >> your schema uses varchar, why don't you just use the varchar-style >> accessors? It's not unreasonable to expect your access method to match >> the underlying schema, surely. > > The reason I map the String-property as @Lob (which, by default, makes Hibernate use setClob()) is for interoperabilitywith other DBs, like Oracle, which require this annotation in order to be able to store Strings which areover 4000 chars long. These properties are stored in Oracle as CLOB and in PG as VARCHAR. Your problem here is that by using the JDBC driver's set/getClob() methods (indirectly via Hibernate), you are saying "I want to treat this type as a CLOB"; and a CLOB is mapped by the postgresql driver to the *oid* type, *not* the varchar type you are using in your schema. So you have a mismatch between your schema and how you are trying to access it. Is there some reason your schema can't use oid here, as the driver requires? When you're using something like Hibernate you have three layers involved: 1) your application layer, which is dealing in terms of Java objects 2) your persistence layer, which handles the mapping of the Java objects to a SQL schema, perhaps in a database-specific or schema-specific way (as customized by annotations etc) 3) the JDBC layer, which handles the database-specific parts of taking a SQL query expressed via the JDBC API and giving it to the database. The JDBC layer (3) really is quite a simple mapping. It does not have any knowledge of how you want to handle the data up at layer (1) - it just does specifically what is asked of it. When Hibernate calls, e.g., setClob() or getClob() it is explicitly asking the driver to interpret that parameter/column as a JDBC CLOB, which in the postgresql world means "a LO containing character data referenced by OID". If that interpretation doesn't match your schema, that's not a problem with the driver - it means that layer (2) doesn't have the right mapping set up. Putting knowledge in layer (3) about how to map your particular schema to the particular datatypes you want is really the wrong place for it - the JDBC API just doesn't provide a place to put that information. (TBH, I'd think the simplest solution would be to just teach Hibernate's postgresql dialect to map String-with-@Lob to the text type - the clob support in the driver is limited at best) Oliver
På tirsdag 08. februar 2011 kl 11:35:37 skrev du:
> Andreas Joseph Krogh wrote:
>
> > Firstly; My issue here only affects CLOBs, not BLOBs.
>
> BLOB-vs-bytea is essentially the same issue as CLOB-vs-varchar, FWIW. If
> you tried to use the BLOB interface on a bytea column or vice versa,
> you'd see similar problems, because bytea is not a BLOB, regardless of
> the fact that they're both ways of storing large binary data.
>
> > Thirdly; Didn't we just agree on that PG doesn't have CLOBs? If PG doesn't have CLOBs, how can it map CLOBs to
OIDs?Under what circumstances would PG need an OID to map a *C*LOB? Are you suggesting to store CLOBs as bytea? Is
anyonedoing this?
>
> No, I don't think we agreed on that.
I meant on the server-side.
> The PG driver certainly does have
> something that is a CLOB - it is a column that contains an OID
> referencing a separate LO that contains character data. The server
> itself doesn't know about that mapping, but when you're working at the
> JDBC level, you do need to be aware of the mapping.
>
> (One way to look at it is to pretend that "clob" is spelled "oid" in
> your schema)
And what benefits does having a LO referencing character-data gives you instead of just using varchar/text? I don't see
anymotivation for using a LO?
> > All this stuff sounds like an implementation detail to me. The programmer using JDBC should, IMO, be able to use
rs.setClob()to varchar/text-columns transparently as there's no reason to treat it differently than setString().
>
> Why do you assume this? JDBC's CLOB is not the same type as VARCHAR, and
> JDBC doesn't require that they're interchangeable.
>
> And, in fact, the postgresql driver's mapping of CLOB is not
> interchangeable with VARCHAR, which is the root of your problem..
>
> >> The fundamental question here is "why are you trying to map a varchar to
> >> a Clob?" .. As you say, clobs are mostly redundant anyway. Given that
> >> your schema uses varchar, why don't you just use the varchar-style
> >> accessors? It's not unreasonable to expect your access method to match
> >> the underlying schema, surely.
> >
> > The reason I map the String-property as @Lob (which, by default, makes Hibernate use setClob()) is for
interoperabilitywith other DBs, like Oracle, which require this annotation in order to be able to store Strings which
areover 4000 chars long. These properties are stored in Oracle as CLOB and in PG as VARCHAR.
>
> Your problem here is that by using the JDBC driver's set/getClob()
> methods (indirectly via Hibernate), you are saying "I want to treat this
> type as a CLOB"; and a CLOB is mapped by the postgresql driver to the
> *oid* type, *not* the varchar type you are using in your schema.
>
> So you have a mismatch between your schema and how you are trying to
> access it. Is there some reason your schema can't use oid here, as the
> driver requires?
>
> When you're using something like Hibernate you have three layers involved:
>
> 1) your application layer, which is dealing in terms of Java objects
> 2) your persistence layer, which handles the mapping of the Java objects
> to a SQL schema, perhaps in a database-specific or schema-specific way
> (as customized by annotations etc)
> 3) the JDBC layer, which handles the database-specific parts of taking a
> SQL query expressed via the JDBC API and giving it to the database.
>
> The JDBC layer (3) really is quite a simple mapping. It does not have
> any knowledge of how you want to handle the data up at layer (1) - it
> just does specifically what is asked of it. When Hibernate calls, e.g.,
> setClob() or getClob() it is explicitly asking the driver to interpret
> that parameter/column as a JDBC CLOB, which in the postgresql world
> means "a LO containing character data referenced by OID". If that
> interpretation doesn't match your schema, that's not a problem with the
> driver - it means that layer (2) doesn't have the right mapping set up.
> Putting knowledge in layer (3) about how to map your particular schema
> to the particular datatypes you want is really the wrong place for it -
> the JDBC API just doesn't provide a place to put that information.
>
> (TBH, I'd think the simplest solution would be to just teach Hibernate's
> postgresql dialect to map String-with-@Lob to the text type - the clob
> support in the driver is limited at best)
I hear over and over that PGs JDBC-driver wants to map CLOBs to OIDs but there really doesn't seem to be any good
reasonsfor it (at least not that I can see). Why would I want to map my data as OID instead of varchar when the data is
aJAVA-String? Is anyone using LO and the LO-api for storing large character-data? I think not. The @Lob annotation
(whichstarted this thread) is just there to help other DBs map the String-property correctly, I would love to get rid
ofit but need it to have my app work with Oracle. I also want other properties of varchar to work (LIKE-operator f.ex.,
whichOracle also support on CLOBs), and I don't know what the PG-LO type supports. Honestly; I don't see any reason to
useLOs at all, neither for BLOB (where I use bytea) or CLOBs (where I use varchar).
I get around this problem in Hibernate by using this combination:
@Column(name="my_name")
@Lob
@Type(type="org.hibernate.type.StringClobType")
...but the StringClobType is deprecated.
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CTO
Public key: http://home.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment. |
Org.nr: NO 981 479 076 | |
| |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
Andreas Joseph Krogh wrote: > I hear over and over that PGs JDBC-driver wants to map CLOBs to OIDs but there really doesn't seem to be any good reasonsfor it It's mostly there for historical reasons - IIRC it dates back to the early 7.x days when you *had* to use LOs to store large data, because there was no such thing as the "text" or "bytea" type. There's really not much reason to use it in new code these days, as I said. But there's also not much reason to arbitrarily break compatibility with existing code that assumes the current behavior, just for the sake of your particular application. I think that your efforts here might be more productively directed towards improving the Hibernate postgresql implementation so that it avoids using get/setClob() entirely. Oliver
Andreas Joseph Krogh wrote: > ...but the StringClobType is deprecated. The obvious question here is "why?" Oliver
Andreas Joseph Krogh <andreak@officenet.no> Tuesday 08 February 2011 12:34:19 > I hear over and over that PGs JDBC-driver wants to map CLOBs to OIDs but > there really doesn't seem to be any good reasons for it (at least not that > I can see). Why would I want to map my data as OID instead of varchar when > the data is a JAVA-String? Is anyone using LO and the LO-api for storing > large character-data? I think not. The @Lob annotation (which started this > thread) is just there to help other DBs map the String-property correctly, > I would love to get rid of it but need it to have my app work with Oracle. > I also want other properties of varchar to work (LIKE-operator f.ex., > which Oracle also support on CLOBs), and I don't know what the PG-LO type > supports. Honestly; I don't see any reason to use LOs at all, neither for > BLOB (where I use bytea) or CLOBs (where I use varchar). Actually there is good reason LOB are streamed. As it stands it's "large object", and need special treatment. This LO can be 1,2 or 16GB. If you will use bytea (it's impossible for such large objects) PGSQL sends all data in one message so, select * my_table_with_los will end with OutOfMemory. 2nd reason is that different communication is performed with LOBs, this is streaming mode, which is faster so you will not OOM your JVM. You need to understand that LO wasn't made to be something more then "fiele systems" in database for realy big files. CLOBS are more historical, in days of XMLs, but historical applications still exists and sitll many systemes use plain flat files to exchange data.
On Mon, 7 Feb 2011, Andreas Joseph Krogh wrote: > It's not clear to me why the JDBC-driver for PG shouldn't just use > rs.getString() internally for getClob() calls? Requiring > special-handling in Hibernate for dealing with Strings by setting an > obscure property just doesn't seem right. And I don't want to set a > property to handle CLOB which might affect how I use BLOBs. > The problem is that we have no idea what someone might want to do with a CLOB after they've fetched it. You're coming from the idea that all they want to do is read it and copy the data out to a String. From that perspective it's not hard to have a Clob wrapper around a String, but what happens when they say clob.setString(13, "data")? Are we going to try and figure out what row of what table that varchar column is coming from and do a partial update on it? Kris Jurka
Andreas Joseph Krogh wrote:
> I get around this problem in Hibernate by using this combination:
>
> @Column(name="my_name")
> @Lob
> @Type(type="org.hibernate.type.StringClobType")
>
> ...but the StringClobType is deprecated.
>
It really makes it so much easier to have a TEXT column mapped to a 'String'.
@Entity public class Foo
{
@Id
private String ident;
private String somethingBackedByTEXT;
...
}
FWIW, in a database like Oracle that does have CLOBs it's still a lot easier
with Hibernate to map CLOBs to 'String' than to 'Clob', as I learned from
experience. Even the Hibernate documentation recommends against mapping to
'Clob'.
--
Lew
Ceci n'est pas une fenêtre.
.___________.
|###] | [###|
|##/ | *\##|
|#/ * | \#|
|#----|----#|
|| | * ||
|o * | o|
|_____|_____|
|===========|
On 02/09/2011 01:20 AM, Kris Jurka wrote: > > > On Mon, 7 Feb 2011, Andreas Joseph Krogh wrote: > >> It's not clear to me why the JDBC-driver for PG shouldn't just use >> rs.getString() internally for getClob() calls? Requiring >> special-handling in Hibernate for dealing with Strings by setting an >> obscure property just doesn't seem right. And I don't want to set a >> property to handle CLOB which might affect how I use BLOBs. >> > > The problem is that we have no idea what someone might want to do with a > CLOB after they've fetched it. You're coming from the idea that all > they want to do is read it and copy the data out to a String. From that > perspective it's not hard to have a Clob wrapper around a String, but > what happens when they say clob.setString(13, "data")? Are we going to > try and figure out what row of what table that varchar column is coming > from and do a partial update on it? The javadoc for clob.setString(index, data) says: ---------------------------------------- Writes the given Java String to the CLOB value that this Clob object designates at the position pos. The string will overwrite the existing characters in the Clob object starting at the position pos. If the end of the Clob value is reached while writing the given string, then the length of the Clob value will be increased to accomodate the extra characters. Note: If the value specified for pos is greater then the length+1 of the CLOB value then the behavior is undefined. Some JDBC drivers may throw a SQLException while other drivers may support this operation. Parameters: pos - the position at which to start writing to the CLOB value that this Clob object represents; The first position is 1 str - the string to be written to the CLOB value that this Clob designates ---------------------------------------- So, I think clob.setString(index, data) should behave just like StringBuilder.insert(int offset, String str) except overwrite existing data instead of moving the characters above the position. -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / CTO Public key: http://home.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Rosenholmveien 25 | know how to do a thing and to watch | 1414 Trollåsen | somebody else doing it wrong, without | NORWAY | comment. | Org.nr: NO 981 479 076 | | | | Tlf: +47 24 15 38 90 | | Fax: +47 24 15 38 91 | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
Andreas Joseph Krogh wrote: > On 02/09/2011 01:20 AM, Kris Jurka wrote: >> The problem is that we have no idea what someone might want to do with a >> CLOB after they've fetched it. You're coming from the idea that all >> they want to do is read it and copy the data out to a String. From that >> perspective it's not hard to have a Clob wrapper around a String, but >> what happens when they say clob.setString(13, "data")? Are we going to >> try and figure out what row of what table that varchar column is coming >> from and do a partial update on it? > So, I think clob.setString(index, data) should behave just like > StringBuilder.insert(int offset, String str) except overwrite existing > data instead of moving the characters above the position. You really missed Kris' point here - the question is not "how should we modify the data?" but "how do we locate the data to modify in the first place?". Consider: SELECT somedatacolumn FROM sometable WHERE complex condition Now you have a resultset with one column. It's a varchar. The caller calls ResultSet.getClob().setString(). What does the driver do? Synthesize an UPDATE statement? How? With what parameters? Oliver
On 02/09/2011 04:23 AM, Lew wrote:
> Andreas Joseph Krogh wrote:
>> I get around this problem in Hibernate by using this combination:
>>
>> @Column(name="my_name")
>> @Lob
>> @Type(type="org.hibernate.type.StringClobType")
>>
>> ...but the StringClobType is deprecated.
>>
>
> It really makes it so much easier to have a TEXT column mapped to a
> 'String'.
>
> @Entity public class Foo
> {
> @Id
> private String ident;
> private String somethingBackedByTEXT;
> ...
> }
>
> FWIW, in a database like Oracle that does have CLOBs it's still a lot
> easier with Hibernate to map CLOBs to 'String' than to 'Clob', as I
> learned from experience. Even the Hibernate documentation recommends
> against mapping to 'Clob'.
Seems you missed the point
My mapping was like this, to a String-property
@Column(name="my_name")
@Lob
@Type(type="org.hibernate.type.StringClobType")
private String someLongDescription;
But I have to use @Lob in order to be able to persist this long String
in Oracle. With PG it works without the @Lob but then it's not portable.
The problem is that Hibernate uses ResultSet.setClob() regardless of the
DB-dialect when it sees @Lob on a String-type. This shouldn't be a
problem is the PG JDBC-driver implemented setClob as setString, but some
think that's not correct.
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CTO
Public key: http://home.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment. |
Org.nr: NO 981 479 076 | |
| |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
After think, It is good idea to have CLOBS for VARCHARS, but to have any chance: How do You create this CLOB? If your column will be null you are in trouble, as it will be impossible to call setClob(...) to set VARCHAR value. I mean everyting will work until you will set/change values only by retrivied Clob methods. You will be unable to set VARCHAR value with connection.createBlob, too You will need to ensure your VARCHAR will not be NULL, and your JPA will use Clob.getter/Setter. Andreas Joseph Krogh <andreak@officenet.no> Wednesday 09 February 2011 09:04:05 > On 02/09/2011 01:20 AM, Kris Jurka wrote: > > On Mon, 7 Feb 2011, Andreas Joseph Krogh wrote: > >> It's not clear to me why the JDBC-driver for PG shouldn't just use > >> rs.getString() internally for getClob() calls? Requiring > >> special-handling in Hibernate for dealing with Strings by setting an > >> obscure property just doesn't seem right. And I don't want to set a > >> property to handle CLOB which might affect how I use BLOBs. > > > > The problem is that we have no idea what someone might want to do with a > > CLOB after they've fetched it. You're coming from the idea that all > > they want to do is read it and copy the data out to a String. From that > > perspective it's not hard to have a Clob wrapper around a String, but > > what happens when they say clob.setString(13, "data")? Are we going to > > try and figure out what row of what table that varchar column is coming > > from and do a partial update on it? > > The javadoc for clob.setString(index, data) says: > ---------------------------------------- > Writes the given Java String to the CLOB value that this Clob object > designates at the position pos. The string will overwrite the existing > characters in the Clob object starting at the position pos. If the end > of the Clob value is reached while writing the given string, then the > length of the Clob value will be increased to accomodate the extra > characters. > > Note: If the value specified for pos is greater then the length+1 of the > CLOB value then the behavior is undefined. Some JDBC drivers may throw a > SQLException while other drivers may support this operation. > > Parameters: > pos - the position at which to start writing to the CLOB value that this > Clob object represents; The first position is 1 > str - the string to be written to the CLOB value that this Clob designates > ---------------------------------------- > > So, I think clob.setString(index, data) should behave just like > StringBuilder.insert(int offset, String str) except overwrite existing > data instead of moving the characters above the position.
Andreas Joseph Krogh schrieb am 07.02.2011 um 00:01: > I have a varchar-column in a table which maps to a field (of type String) in a > JPA-entity. When marking that String-property with the JPA @Lob-annotation, > using Hibernate as my JPA-provider, it stops working as expected when using > PostgreSQL (works as expected on Oracle and SQL Server). The problem is that > Hibernate, correctly, calls ResultSet.getClob() but PG internally calls > getLong(), which obviously won't work with varchar-types, resulting in: > > Caused by: org.postgresql.util.PSQLException: Bad value for type long : Hei > at > org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(AbstractJdbc2ResultSet.java:2796) > at > org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2ResultSet.java:2019) > at org.postgresql.jdbc4.Jdbc4ResultSet.getClob(Jdbc4ResultSet.java:43) > at > org.postgresql.jdbc2.AbstractJdbc2ResultSet.getClob(AbstractJdbc2ResultSet.java:384) > at > org.hibernate.type.descriptor.sql.ClobTypeDescriptor$4.doExtract(ClobTypeDescriptor.java:104) > > After googling around I see this issue has come up before: > http://archives.postgresql.org/pgsql-jdbc/2010-02/msg00004.php > > One is encurraged to use ResultSet.getString() instead. Hm, well - being at > Hibernate's mercy here, I don't really have that luxury. So, is PG's JDBC- > driver going to fix this flaw (IMNSHO it is a flaw) or is there consensus in the > PG-community that clobs are special and are not to be treated as > Strings/varchars? Is there anything new here? This still doesn't work with 9.4.1208 We are using Hibernate with an application that has to support Oracle and Postgres. For Oracle we have to use CLOB to store Strings longer then 4000 characters - which means we *have* to use CLOB. The Hibernate Entity is therefore annotated with @Lob and thus the whole thing fails with Postgres So we are caught between a rock and a hard place: change to String and lose the ability to work with Oracle (which we can't)or stick with @Lob and lose the possibility to also support Postgres. I think a connection parameter treatClobAsString=true would solve this problem - at least for us (and I think for most othersthat have this problem as well). Are there any plans for this? If not, is this something that would be accepted as a patch? Thomas
>Is there anything new here?
>This still doesn't work with 9.4.1208
>So we are caught between a rock and a hard place
I think so.
>This still doesn't work with 9.4.1208
Nothing new here yet.
>So we are caught between a rock and a hard place
I feel your pain.
Technically the problem is pgjdbc does not have "String -> Clob" conversion yet.
>Are there any plans for this?
I've not heard of those.
>If not, is this something that would be accepted as a patch?
I think so.
It should be possible to check the result column type and create java.sql.Clob instance that would just work off the resulting string.
Special thank would go for a patch that would add JPA -> pgjdbc kind of integration test to the regression suite.
Vladimir
Vladimir Sitnikov schrieb am 20.07.2016 um 14:37: >>Is there anything new here? >>This still doesn't work with 9.4.1208 > > Nothing new here yet. > >>So we are caught between a rock and a hard place > > I feel your pain. > > Technically the problem is pgjdbc does not have "String -> Clob" conversion yet. > >>Are there any plans for this? > > I've not heard of those. > >>If not, is this something that would be accepted as a patch? > > I think so. > It should be possible to check the result column type and create java.sql.Clob instance that would just work off the resultingstring. I don't think checking the column type is necessary (at least not for me). If this option is enabled, then all calls to getClob() are simply "re-routed" to getString().
1) AFAIK, Oracle allows to use getString/setString for clob columns when using Oracle JDBC driver 10.2 or higher. Basically you set SetBigStringTryClob=true connection option and that is it.
2)
Thomas>I don't think checking the column type is necessary (at least not for me).
Thomas>If this option is enabled, then all calls to getClob() are simply "re-routed" to getString().
Thomas>If this option is enabled, then all calls to getClob() are simply "re-routed" to getString().
I'm afraid you miss the point.
In postgresql world, there's "large object API": https://www.postgresql.org/docs/current/static/lo-interfaces.html
pgjdbc wraps that, so if you have a column of "oid" type, then you can access the value via getClob and pgjdbc would reroute the calls to large object API.
So getLong is not a mistake in getClob. getLong is here to retrieve the value of "large object oid" (i.e. pointer). That is why column type is crucial to tell if getClob is dealing with "large object API" or "just a string".
Vladimir
Andreas Joseph Krogh schrieb am 07.02.2011 um 00:01: > I have a varchar-column in a table which maps to a field (of type String) in a > JPA-entity. When marking that String-property with the JPA @Lob-annotation, > using Hibernate as my JPA-provider, it stops working as expected when using > PostgreSQL (works as expected on Oracle and SQL Server). The problem is that > Hibernate, correctly, calls ResultSet.getClob() but PG internally calls > getLong(), which obviously won't work with varchar-types, resulting in: > > Caused by: org.postgresql.util.PSQLException: Bad value for type long : Hei > at > org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(AbstractJdbc2ResultSet.java:2796) > at > org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2ResultSet.java:2019) > at org.postgresql.jdbc4.Jdbc4ResultSet.getClob(Jdbc4ResultSet.java:43) > at > org.postgresql.jdbc2.AbstractJdbc2ResultSet.getClob(AbstractJdbc2ResultSet.java:384) > at > org.hibernate.type.descriptor.sql.ClobTypeDescriptor$4.doExtract(ClobTypeDescriptor.java:104) > > After googling around I see this issue has come up before: > http://archives.postgresql.org/pgsql-jdbc/2010-02/msg00004.php > > One is encurraged to use ResultSet.getString() instead. Hm, well - being at > Hibernate's mercy here, I don't really have that luxury. So, is PG's JDBC- > driver going to fix this flaw (IMNSHO it is a flaw) or is there consensus in the > PG-community that clobs are special and are not to be treated as > Strings/varchars? Is there anything new here? This still doesn't work with 9.4.1208 We are using Hibernate with an application that has to support Oracle and Postgres. For Oracle we have to use CLOB to store Strings longer then 4000 characters - which means we *have* to use CLOB. The Hibernate Entity is therefore annotated with @Lob and thus the whole thing fails with Postgres So we are caught between a rock and a hard place: change to String and lose the ability to work with Oracle (which we can't)or stick with @Lob and lose the possibility to also support Postgres. I think a connection parameter treatClobAsString=true would solve this problem - at least for us (and I think for most othersthat have this problem as well). Are there any plans for this? If not, is this something that would be accepted as a patch? Thomas
Vladimir Sitnikov schrieb am 20.07.2016 um 15:52: > 2) > Thomas>I don't think checking the column type is necessary (at least not for me). > Thomas>If this option is enabled, then all calls to getClob() are simply "re-routed" to getString(). > > I'm afraid you miss the point. > > In postgresql world, there's "large object API": https://www.postgresql.org/docs/current/static/lo-interfaces.html > pgjdbc wraps that, so if you have a column of "oid" type, then you can access the value via getClob and pgjdbc would reroutethe calls to large object API. I am aware of the large object API but for the intended use case of "treatClobAsString=true" this would simply not supportstoring string values through large objects - and I doubt that any project that would actually use "treatClobAsString=true"would mix CLOBs as "large objects" and CLOBs as "text". > So getLong is not a mistake in getClob. getLong is here to retrieve the value of "large object oid" (i.e. pointer). > That is why column type is crucial to tell if getClob is dealing with "large object API" or "just a string". If we look at the use-case for this "override", I'm pretty sure there are (a lot) more people that could benefit from anunconditional getClob() == getString() then people that actually need the distinction because they store large text dataas "large objects" (oid columns): Thomas
>Is there anything new here?
>This still doesn't work with 9.4.1208
>So we are caught between a rock and a hard place
I think so.
>This still doesn't work with 9.4.1208
Nothing new here yet.
>So we are caught between a rock and a hard place
I feel your pain.
Technically the problem is pgjdbc does not have "String -> Clob" conversion yet.
>Are there any plans for this?
I've not heard of those.
>If not, is this something that would be accepted as a patch?
I think so.
It should be possible to check the result column type and create java.sql.Clob instance that would just work off the resulting string.
Special thank would go for a patch that would add JPA -> pgjdbc kind of integration test to the regression suite.
Vladimir
Thomas> treatClobAsString
I do not find adding such a property is justified.
I do not find adding such a property is justified.
Adding random properties complicates behavior, it makes support harder and it makes bugs more obscure.
Vladimir
Vladimir Sitnikov schrieb am 20.07.2016 um 14:37: >>Is there anything new here? >>This still doesn't work with 9.4.1208 > > Nothing new here yet. > >>So we are caught between a rock and a hard place > > I feel your pain. > > Technically the problem is pgjdbc does not have "String -> Clob" conversion yet. > >>Are there any plans for this? > > I've not heard of those. > >>If not, is this something that would be accepted as a patch? > > I think so. > It should be possible to check the result column type and create java.sql.Clob instance that would just work off the resultingstring. I don't think checking the column type is necessary (at least not for me). If this option is enabled, then all calls to getClob() are simply "re-routed" to getString().
1) AFAIK, Oracle allows to use getString/setString for clob columns when using Oracle JDBC driver 10.2 or higher. Basically you set SetBigStringTryClob=true connection option and that is it.
2)
Thomas>I don't think checking the column type is necessary (at least not for me).
Thomas>If this option is enabled, then all calls to getClob() are simply "re-routed" to getString().
Thomas>If this option is enabled, then all calls to getClob() are simply "re-routed" to getString().
I'm afraid you miss the point.
In postgresql world, there's "large object API": https://www.postgresql.org/docs/current/static/lo-interfaces.html
pgjdbc wraps that, so if you have a column of "oid" type, then you can access the value via getClob and pgjdbc would reroute the calls to large object API.
So getLong is not a mistake in getClob. getLong is here to retrieve the value of "large object oid" (i.e. pointer). That is why column type is crucial to tell if getClob is dealing with "large object API" or "just a string".
Vladimir
Vladimir Sitnikov schrieb am 20.07.2016 um 15:52: > 2) > Thomas>I don't think checking the column type is necessary (at least not for me). > Thomas>If this option is enabled, then all calls to getClob() are simply "re-routed" to getString(). > > I'm afraid you miss the point. > > In postgresql world, there's "large object API": https://www.postgresql.org/docs/current/static/lo-interfaces.html > pgjdbc wraps that, so if you have a column of "oid" type, then you can access the value via getClob and pgjdbc would reroutethe calls to large object API. I am aware of the large object API but for the intended use case of "treatClobAsString=true" this would simply not supportstoring string values through large objects - and I doubt that any project that would actually use "treatClobAsString=true"would mix CLOBs as "large objects" and CLOBs as "text". > So getLong is not a mistake in getClob. getLong is here to retrieve the value of "large object oid" (i.e. pointer). > That is why column type is crucial to tell if getClob is dealing with "large object API" or "just a string". If we look at the use-case for this "override", I'm pretty sure there are (a lot) more people that could benefit from anunconditional getClob() == getString() then people that actually need the distinction because they store large text dataas "large objects" (oid columns): Thomas
Thomas> treatClobAsString
I do not find adding such a property is justified.
I do not find adding such a property is justified.
Adding random properties complicates behavior, it makes support harder and it makes bugs more obscure.
Vladimir
Thomas Kellerer schrieb am 20.07.2016 um 13:51: >> I have a varchar-column in a table which maps to a field (of type String) in a >> JPA-entity. When marking that String-property with the JPA @Lob-annotation, >> using Hibernate as my JPA-provider, it stops working as expected when using >> PostgreSQL (works as expected on Oracle and SQL Server). The problem is that >> Hibernate, correctly, calls ResultSet.getClob() but PG internally calls >> getLong(), which obviously won't work with varchar-types, resulting in: >> >> >> After googling around I see this issue has come up before: >> http://archives.postgresql.org/pgsql-jdbc/2010-02/msg00004.php >> >> One is encurraged to use ResultSet.getString() instead. Hm, well - being at >> Hibernate's mercy here, I don't really have that luxury. So, is PG's JDBC- >> driver going to fix this flaw (IMNSHO it is a flaw) or is there consensus in the >> PG-community that clobs are special and are not to be treated as >> Strings/varchars? > > > Is there anything new here? > This still doesn't work with 9.4.1208 > > We are using Hibernate with an application that has to support Oracle and Postgres. > > For Oracle we have to use CLOB to store Strings longer then 4000 characters - which means we *have* to use CLOB. > The Hibernate Entity is therefore annotated with @Lob and thus the whole thing fails with Postgres > > So we are caught between a rock and a hard place: change to String and lose the ability to work with Oracle (which we can't) >or stick with @Lob and lose the possibility to also support Postgres. > Please find attached a patch that does the following: * An implementation of the java.sql.Clob interface based on a String * An implementation of the java.sql.Blob interface based on a byte[] *A modified PgResultSet that checks the column type in getClob() and getBlob() and returns the approriate Lob implementation if the column is a varchar/text or bytea column. The methods to set a stream are not implemented yet though. I did not extend AbstractBlobClob as that makes too many assumption that the underlying Lob is a "large object" in the database and I would wind up overwriting nearly all methods in there. I did however create an AbstractBasicLob to re-use the check for the correct position. The assertPosition() methods in there are a copy from AbstractBlobClob. If this is something that gets accepted, it might make sense to derive AbstractBlobClob from AbstractBasicLob and keep the assertions in a single place. I don't know if we also need support for creating empty Clobs based on Strings that can be filled later I think something similar is needed for PgPreparedStatement as well, but I did not find a way to check the type of a column the way it's done in PgResultSet. It seems that ParameterList.getTypeOIDs() would provide that information, but I don't know how to use that. If someone points me in the right direction I can add that as well. Regards Thomas
Attachment
Thomas,
Thanks for looking into this, however could you please file a github PR so the system tests the change against different PostgreSQL / Java versions?
Could you add a test case that explores ResultSet.getClob as well?
I think something similar is needed for PgPreparedStatement as well, but I did not find a way to check
the type of a column the way it's done in PgResultSet. It seems that ParameterList.getTypeOIDs()
would provide that information, but I don't know how to use that. If someone points me in the right
direction I can add that as well.
I'm not yet ready to answer that.
Vladimir
Thomas Kellerer schrieb am 20.07.2016 um 13:51: >> I have a varchar-column in a table which maps to a field (of type String) in a >> JPA-entity. When marking that String-property with the JPA @Lob-annotation, >> using Hibernate as my JPA-provider, it stops working as expected when using >> PostgreSQL (works as expected on Oracle and SQL Server). The problem is that >> Hibernate, correctly, calls ResultSet.getClob() but PG internally calls >> getLong(), which obviously won't work with varchar-types, resulting in: >> >> >> After googling around I see this issue has come up before: >> http://archives.postgresql.org/pgsql-jdbc/2010-02/msg00004.php >> >> One is encurraged to use ResultSet.getString() instead. Hm, well - being at >> Hibernate's mercy here, I don't really have that luxury. So, is PG's JDBC- >> driver going to fix this flaw (IMNSHO it is a flaw) or is there consensus in the >> PG-community that clobs are special and are not to be treated as >> Strings/varchars? > > > Is there anything new here? > This still doesn't work with 9.4.1208 > > We are using Hibernate with an application that has to support Oracle and Postgres. > > For Oracle we have to use CLOB to store Strings longer then 4000 characters - which means we *have* to use CLOB. > The Hibernate Entity is therefore annotated with @Lob and thus the whole thing fails with Postgres > > So we are caught between a rock and a hard place: change to String and lose the ability to work with Oracle (which we can't) >or stick with @Lob and lose the possibility to also support Postgres. > Please find attached a patch that does the following: * An implementation of the java.sql.Clob interface based on a String * An implementation of the java.sql.Blob interface based on a byte[] *A modified PgResultSet that checks the column type in getClob() and getBlob() and returns the approriate Lob implementation if the column is a varchar/text or bytea column. The methods to set a stream are not implemented yet though. I did not extend AbstractBlobClob as that makes too many assumption that the underlying Lob is a "large object" in the database and I would wind up overwriting nearly all methods in there. I did however create an AbstractBasicLob to re-use the check for the correct position. The assertPosition() methods in there are a copy from AbstractBlobClob. If this is something that gets accepted, it might make sense to derive AbstractBlobClob from AbstractBasicLob and keep the assertions in a single place. I don't know if we also need support for creating empty Clobs based on Strings that can be filled later I think something similar is needed for PgPreparedStatement as well, but I did not find a way to check the type of a column the way it's done in PgResultSet. It seems that ParameterList.getTypeOIDs() would provide that information, but I don't know how to use that. If someone points me in the right direction I can add that as well. Regards Thomas
Attachment
Thomas,
Thanks for looking into this, however could you please file a github PR so the system tests the change against different PostgreSQL / Java versions?
Could you add a test case that explores ResultSet.getClob as well?
I think something similar is needed for PgPreparedStatement as well, but I did not find a way to check
the type of a column the way it's done in PgResultSet. It seems that ParameterList.getTypeOIDs()
would provide that information, but I don't know how to use that. If someone points me in the right
direction I can add that as well.
I'm not yet ready to answer that.
Vladimir
Vladimir Sitnikov schrieb am 27.07.2016 um 21:58: > Thomas, > > Thanks for looking into this, however could you please file a github PR so the system tests the change against differentPostgreSQL / Java versions? Sorry, I don't have a github account, so I can't do that. > Could you add a test case that explores ResultSet.getClob as well? Yes, of course. Thomas
Vladimir Sitnikov schrieb am 27.07.2016 um 21:58: > Thomas, > > Thanks for looking into this, however could you please file a github PR so the system tests the change against differentPostgreSQL / Java versions? Sorry, I don't have a github account, so I can't do that. > Could you add a test case that explores ResultSet.getClob as well? Yes, of course. Thomas
> Thanks for looking into this, however could you please file a github PR so the system tests the change against different PostgreSQL / Java versions?
Sorry, I don't have a github account, so I can't do that.
I've created a PR with your changes. Tests work ok, however code style is not in line with the project.
Here's the test report: https://travis-ci.org/pgjdbc/pgjdbc/builds/147963868
Can you fix checkstyle errors as well?
FYI: you can run `mvn checkstyle:check` to check the style locally.
Vladimir
> Thanks for looking into this, however could you please file a github PR so the system tests the change against different PostgreSQL / Java versions?
Sorry, I don't have a github account, so I can't do that.
I've created a PR with your changes. Tests work ok, however code style is not in line with the project.
Here's the test report: https://travis-ci.org/pgjdbc/pgjdbc/builds/147963868
Can you fix checkstyle errors as well?
FYI: you can run `mvn checkstyle:check` to check the style locally.
Vladimir
Vladimir Sitnikov schrieb am 28.07.2016 um 15:57: > > Thanks for looking into this, however could you please file a github PR so the system tests the change against differentPostgreSQL / Java versions? > > > Sorry, I don't have a github account, so I can't do that. > > I've created a PR with your changes. Tests work ok, however code style is not in line with the project. > Here's the test report: https://travis-ci.org/pgjdbc/pgjdbc/builds/147963868 > > Can you fix checkstyle errors as well? > FYI: you can run `mvn checkstyle:check` to check the style locally. > > Vladimir Thanks. Here is a new patch with formatting fixed and a test for PgResultSet Thomas
Attachment
Vladimir Sitnikov schrieb am 27.07.2016 um 21:58: > > I think something similar is needed for PgPreparedStatement as well, but I did not find a way to check > the type of a column the way it's done in PgResultSet. It seems that ParameterList.getTypeOIDs() > would provide that information, but I don't know how to use that. If someone points me in the right > direction I can add that as well. > > > I'm not yet ready to answer that. > It would be nice if someone else could point me in the right direction, because I think that only a change to PreparedStatement would make that complete.
Thomas Kellerer schrieb am 28.07.2016 um 22:24:
> Vladimir Sitnikov schrieb am 27.07.2016 um 21:58:
>>
>> I think something similar is needed for PgPreparedStatement as well, but I did not find a way to check
>> the type of a column the way it's done in PgResultSet. It seems that ParameterList.getTypeOIDs()
>> would provide that information, but I don't know how to use that. If someone points me in the right
>> direction I can add that as well.
>>
>>
>> I'm not yet ready to answer that.
>>
>
> It would be nice if someone else could point me in the right direction, because I think
> that only a change to PreparedStatement would make that complete.
>
I wonder if something like this would be correct to detect the column type in PgPreparedStatement:
private boolean isBytea(int i) throws SQLException {
TypeInfo info = connection.getTypeInfo();
int columnOID = preparedParameters.getTypeOIDs()[i];
String pgType = info.getPGType(columnOID);
return "bytea".equals(pgType);
}
private boolean isVarchar(int i) throws SQLException {
TypeInfo info = connection.getTypeInfo();
int columnOID = preparedParameters.getTypeOIDs()[i];
String pgType = info.getPGType(columnOID);
return "text".equals(pgType) ||
"varchar".equals(pgType) ||
"character varying".equals(pgType) ||
"char".equals(pgType);
}
I chose to compare the Postgres Names rather than the java.sql.Type constants because it seems
that TypeInfoCache will send a SQL query to the backend (at least once) to get that mapping.
I am not sure if "character varying" will ever be needed. TypeInfoCache does not have that in its internal array
where oids are mapped to type names.
and then in the setClob() method:
if (isVarchar(i)) {
setString(i, x.getSubString(1, (int)x.length()));
return;
}
and for setBlob() the similar thing:
if (isBytea(i)) {
setBytes(i, x.getBytes(1, (int)x.length()));
return;
}
>It would be nice if someone else could point me in the right direction, because I think
that only a change to PreparedStatement would make that complete.
that only a change to PreparedStatement would make that complete.
Am I right your problem is "find a way to identify database type at PreparedStatement#setClob time"?
I'm afraid, there's no solution for that.
Technically speaking, java types are used to parse the SQL properly.
Suppose there's a function process(int4) and function process(text). When using prepareStatement("process(?)") at java side you can refer to both of those and the exact overload depends on the setXXX method.
For instance, if you call setString, then process(text) variation will be called.
Well, let's revert to our clob/text stuff.
I'm not quite sure we can easily figure out what is the proper data type for the bind.
I do not like the idea "having additional round trip to the database just to resolve desired bind types".
I do not like the idea of having a switch that binds setClob to text or lob database types either.
Vladimir
Vladimir Sitnikov schrieb am 28.07.2016 um 22:47: >> It would be nice if someone else could point me in the right direction, because I think >> that only a change to PreparedStatement would make that complete. > > Am I right your problem is "find a way to identify database type at PreparedStatement#setClob time"? > > I'm afraid, there's no solution for that. > > I'm not quite sure we can easily figure out what is the proper data type for the bind. That's really a pity, because the (very unusual) handling of "large objects" makes the Clob/Blob handling quite incompatiable with other JDBC implementations. > I do not like the idea "having additional round trip to the database just to resolve desired bind types". Me neither, that would be horrible. > I do not like the idea of having a switch that binds setClob to text or lob database types either. Well, in certain environments it would certainly make it easier to switch to Postgres. Thomas
>other JDBC implementations.
I've just looked into PgConnection.createClob, and it turns out the method is not implemented.
This means no one ever used that to pass strings into large objects or whatever thing.
Large object API works with bytes, not characters, so I think we can safely assume that PgPreparedStatement.setClob results into string datatype (that is it should be just redirected to setString).
This (plus the patch that enables to getClob for textual results) should solve the problem for the majority of pgjdbc users.
Thomas is that enough so you can give it a try?
That's really a pity, because the (very unusual) handling of "large objects" makes the Clob/Blob handling quite
incompatiable with other JDBC implementations.
The problem with Blob remains (I'm not sure if you have one): postgresql cannot automatically create a large object when it receives "bytea" bind.
For instance, we have pretty much similar problem with timestamp kind of types: setTimestamp() does not say much if "with time zone" or "without time zone" type should be used, so pgjdbc just sends a sting with "oid.UNKNOWN" in a hope that backend will figure out the proper timestamp type (luckily it can ignore time zone part when without time zone is required).
The same "unknown" approach does not work here since "large objects" are stored aside and a colum contains just a "oid" (64bit id).
That adds yet another case "bytea vs large object" to https://github.com/pgjdbc/pgjdbc/blob/master/backend_protocol_v4_wanted_features.md#binary-transfer-vs-exact-data-type
Vladimir
Vladimir Sitnikov schrieb am 28.07.2016 um 15:57: > > Thanks for looking into this, however could you please file a github PR so the system tests the change against differentPostgreSQL / Java versions? > > > Sorry, I don't have a github account, so I can't do that. > > I've created a PR with your changes. Tests work ok, however code style is not in line with the project. > Here's the test report: https://travis-ci.org/pgjdbc/pgjdbc/builds/147963868 > > Can you fix checkstyle errors as well? > FYI: you can run `mvn checkstyle:check` to check the style locally. > > Vladimir Thanks. Here is a new patch with formatting fixed and a test for PgResultSet Thomas
Attachment
Vladimir Sitnikov schrieb am 27.07.2016 um 21:58: > > I think something similar is needed for PgPreparedStatement as well, but I did not find a way to check > the type of a column the way it's done in PgResultSet. It seems that ParameterList.getTypeOIDs() > would provide that information, but I don't know how to use that. If someone points me in the right > direction I can add that as well. > > > I'm not yet ready to answer that. > It would be nice if someone else could point me in the right direction, because I think that only a change to PreparedStatement would make that complete.
Thomas Kellerer schrieb am 28.07.2016 um 22:24:
> Vladimir Sitnikov schrieb am 27.07.2016 um 21:58:
>>
>> I think something similar is needed for PgPreparedStatement as well, but I did not find a way to check
>> the type of a column the way it's done in PgResultSet. It seems that ParameterList.getTypeOIDs()
>> would provide that information, but I don't know how to use that. If someone points me in the right
>> direction I can add that as well.
>>
>>
>> I'm not yet ready to answer that.
>>
>
> It would be nice if someone else could point me in the right direction, because I think
> that only a change to PreparedStatement would make that complete.
>
I wonder if something like this would be correct to detect the column type in PgPreparedStatement:
private boolean isBytea(int i) throws SQLException {
TypeInfo info = connection.getTypeInfo();
int columnOID = preparedParameters.getTypeOIDs()[i];
String pgType = info.getPGType(columnOID);
return "bytea".equals(pgType);
}
private boolean isVarchar(int i) throws SQLException {
TypeInfo info = connection.getTypeInfo();
int columnOID = preparedParameters.getTypeOIDs()[i];
String pgType = info.getPGType(columnOID);
return "text".equals(pgType) ||
"varchar".equals(pgType) ||
"character varying".equals(pgType) ||
"char".equals(pgType);
}
I chose to compare the Postgres Names rather than the java.sql.Type constants because it seems
that TypeInfoCache will send a SQL query to the backend (at least once) to get that mapping.
I am not sure if "character varying" will ever be needed. TypeInfoCache does not have that in its internal array
where oids are mapped to type names.
and then in the setClob() method:
if (isVarchar(i)) {
setString(i, x.getSubString(1, (int)x.length()));
return;
}
and for setBlob() the similar thing:
if (isBytea(i)) {
setBytes(i, x.getBytes(1, (int)x.length()));
return;
}
>It would be nice if someone else could point me in the right direction, because I think
that only a change to PreparedStatement would make that complete.
that only a change to PreparedStatement would make that complete.
Am I right your problem is "find a way to identify database type at PreparedStatement#setClob time"?
I'm afraid, there's no solution for that.
Technically speaking, java types are used to parse the SQL properly.
Suppose there's a function process(int4) and function process(text). When using prepareStatement("process(?)") at java side you can refer to both of those and the exact overload depends on the setXXX method.
For instance, if you call setString, then process(text) variation will be called.
Well, let's revert to our clob/text stuff.
I'm not quite sure we can easily figure out what is the proper data type for the bind.
I do not like the idea "having additional round trip to the database just to resolve desired bind types".
I do not like the idea of having a switch that binds setClob to text or lob database types either.
Vladimir
Vladimir Sitnikov schrieb am 28.07.2016 um 22:47: >> It would be nice if someone else could point me in the right direction, because I think >> that only a change to PreparedStatement would make that complete. > > Am I right your problem is "find a way to identify database type at PreparedStatement#setClob time"? > > I'm afraid, there's no solution for that. > > I'm not quite sure we can easily figure out what is the proper data type for the bind. That's really a pity, because the (very unusual) handling of "large objects" makes the Clob/Blob handling quite incompatiable with other JDBC implementations. > I do not like the idea "having additional round trip to the database just to resolve desired bind types". Me neither, that would be horrible. > I do not like the idea of having a switch that binds setClob to text or lob database types either. Well, in certain environments it would certainly make it easier to switch to Postgres. Thomas
>other JDBC implementations.
I've just looked into PgConnection.createClob, and it turns out the method is not implemented.
This means no one ever used that to pass strings into large objects or whatever thing.
Large object API works with bytes, not characters, so I think we can safely assume that PgPreparedStatement.setClob results into string datatype (that is it should be just redirected to setString).
This (plus the patch that enables to getClob for textual results) should solve the problem for the majority of pgjdbc users.
Thomas is that enough so you can give it a try?
That's really a pity, because the (very unusual) handling of "large objects" makes the Clob/Blob handling quite
incompatiable with other JDBC implementations.
The problem with Blob remains (I'm not sure if you have one): postgresql cannot automatically create a large object when it receives "bytea" bind.
For instance, we have pretty much similar problem with timestamp kind of types: setTimestamp() does not say much if "with time zone" or "without time zone" type should be used, so pgjdbc just sends a sting with "oid.UNKNOWN" in a hope that backend will figure out the proper timestamp type (luckily it can ignore time zone part when without time zone is required).
The same "unknown" approach does not work here since "large objects" are stored aside and a colum contains just a "oid" (64bit id).
That adds yet another case "bytea vs large object" to https://github.com/pgjdbc/pgjdbc/blob/master/backend_protocol_v4_wanted_features.md#binary-transfer-vs-exact-data-type
Vladimir
Vladimir Sitnikov schrieb am 29.07.2016 um 00:20: >>other JDBC implementations. > > I've just looked into PgConnection.createClob, and it turns out the method is not implemented. > This means no one ever used that to pass strings into large objects or whatever thing. I noticed that as well. I wonder if we could use the PgStringClob and PgStringBlob I included with my patch for that purpose? I think the missing methods in those two classes shouldn't be that hard to implement. > Large object API works with bytes, not characters, so I think we can > safely assume that PgPreparedStatement.setClob results into string > datatype (that is it should be just redirected to setString). > > This (plus the patch that enables to getClob for textual results) should solve the problem for the majority of pgjdbc users. > > Thomas is that enough so you can give it a try? Redirecting setClob() to setString() unconditionally would help us in the current migration, yes. I can submit a patch for that, sounds easy enough. > That's really a pity, because the (very unusual) handling of "large objects" makes the Clob/Blob handling quite > incompatiable with other JDBC implementations. > > The problem with Blob remains (I'm not sure if you have one): > postgresql cannot automatically create a large object when it receives "bytea" bind. > > The same "unknown" approach does not work here since "large objects" are stored aside and a colum contains just a "oid"(64bit id). > > That adds yet another case "bytea vs large object" to > https://github.com/pgjdbc/pgjdbc/blob/master/backend_protocol_v4_wanted_features.md#binary-transfer-vs-exact-data-type I still think that having a connection property that switches between large objects and bytea for BLOBs makes sense. Thomas
Vladimir Sitnikov schrieb am 29.07.2016 um 00:20: >>other JDBC implementations. > > I've just looked into PgConnection.createClob, and it turns out the method is not implemented. > This means no one ever used that to pass strings into large objects or whatever thing. I noticed that as well. I wonder if we could use the PgStringClob and PgStringBlob I included with my patch for that purpose? I think the missing methods in those two classes shouldn't be that hard to implement. > Large object API works with bytes, not characters, so I think we can > safely assume that PgPreparedStatement.setClob results into string > datatype (that is it should be just redirected to setString). > > This (plus the patch that enables to getClob for textual results) should solve the problem for the majority of pgjdbc users. > > Thomas is that enough so you can give it a try? Redirecting setClob() to setString() unconditionally would help us in the current migration, yes. I can submit a patch for that, sounds easy enough. > That's really a pity, because the (very unusual) handling of "large objects" makes the Clob/Blob handling quite > incompatiable with other JDBC implementations. > > The problem with Blob remains (I'm not sure if you have one): > postgresql cannot automatically create a large object when it receives "bytea" bind. > > The same "unknown" approach does not work here since "large objects" are stored aside and a colum contains just a "oid"(64bit id). > > That adds yet another case "bytea vs large object" to > https://github.com/pgjdbc/pgjdbc/blob/master/backend_protocol_v4_wanted_features.md#binary-transfer-vs-exact-data-type I still think that having a connection property that switches between large objects and bytea for BLOBs makes sense. Thomas