Re: ResultSet.getClob() causing problems when used with JPA's @Lob

På tirsdag 08. februar 2011 kl 01:53:23 skrev "Oliver Jowett" <oliver(at)opencloud(dot)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? 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 <andreak(at)officenet(dot)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 | |
------------------------+---------------------------------------------+