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

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. 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-(at)Lob to the text type - the clob
support in the driver is limited at best)

Oliver