[JDBC] ResultSet.getClob() causing problems when used with JPA's @Lob
Andreas Joseph Krogh
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 <and...@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 | |
------------------------+---------------------------------------------+
--
Sent via pgsql-jdbc mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Radosław Smogura
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 <and...@officenet.no> Monday 07 February 2011 00:01:21
--
Andreas Joseph Krogh
> 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 <and...@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 | |
------------------------+---------------------------------------------+
--
Radosław Smogura
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 <and...@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.
--
Andreas Joseph Krogh
> 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 <and...@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 | |
------------------------+---------------------------------------------+
--
Oliver Jowett
> 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
Andreas Joseph Krogh
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? Under what circumstances would PG need an OID to map a *C*LOB? Are you suggesting to store CLOBs as bytea? Is anyone doing this?
> >> 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 with other DBs, like Oracle, which require this annotation in order to be able to store Strings which are over 4000 chars long. These properties are stored in Oracle as CLOB and in PG as VARCHAR.
--
Andreas Joseph Krogh <and...@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 | |
------------------------+---------------------------------------------+
--
Oliver Jowett
> 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 anyone doing this?
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 interoperability with other DBs, like Oracle, which require this annotation in order to be able to store Strings which are over 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
Andreas Joseph Krogh
> 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 anyone doing 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 any motivation for using a LO?
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).
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 <and...@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 | |
------------------------+---------------------------------------------+
--
Oliver Jowett
> 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
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
Oliver Jowett
> ...but the StringClobType is deprecated.
The obvious question here is "why?"
Oliver
Andreas Joseph Krogh
> Andreas Joseph Krogh wrote:
> > På tirsdag 08. februar 2011 kl 13:31:21 skrev du:
> >> Andreas Joseph Krogh wrote:
> >>
> >>> ...but the StringClobType is deprecated.
> >> The obvious question here is "why?"
> >
>
> My point about the "why?" is that there was presumably some reason why
> it is deprecated and investigating what that reason is may be enlightening.
>
> > If I get it right the justification for it being as it is is not to break old apps, in case they use OID for large character types of data and not varchar or text. I would be very surprised if any such applications still exist...
>
> Well, it's also arguably a more natural mapping of the CLOB type (go and
> read the javadoc at
> http://download.oracle.com/javase/6/docs/api/java/sql/Clob.html; for
> "logical pointer" read "OID" and you essentially have the postgres
> implementation right there)
>
> It's been this way for ages with no problems, it's a reasonable
> spec-compliant implementation, so you need a good argument for changing
> it now. Your argument doesn't seem to go beyond "it doesn't do this
> non-standard thing I want it to do" which is not very convincing to me.
To me the whole CLOB type seems to be superfluous and only exists to satisfy RDBMS-vendors with broken(...) implementations of varchar/text-types. CLOBs really shouldn't be necessary these days. PG can and does better by allowing arbitrary length of text stored in varchar/text-columns. Feel free to disagree.
> If you do think it's useful to change then you need to discuss it with
> Kris since he's basically looking after the driver solo these days (I
> unfortunately have no time to spend on the driver any more, and even
> this list discussion has eaten up all my JDBC time for about the next
> month). I anticipate he'll be worried about breaking compatibility so
> you'll need a good argument there.
I don't feel I'm in the position to push a new implementation of CLOB in the PG-JDBC-driver forward and don't want to waste anyones time any more on this issue.
But - having this discussion has shed some light on the issue and might be useful for archiving purpose if the CLOB issue some day resurrects.
--
Andreas Joseph Krogh <and...@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 | |
------------------------+---------------------------------------------+
--
Radosław Smogura
> 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.
Kris Jurka
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
Lew
> 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|
|_____|_____|
|===========|
Andreas Joseph Krogh
>
>
> 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 <and...@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 | |
------------------------+---------------------------------------------+
--
Oliver Jowett
> 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
Andreas Joseph Krogh
> 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.
Radosław Smogura
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 <and...@officenet.no> Wednesday 09 February 2011
09:04:05
--