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

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