Re: ResultSet.getClob() causing problems when used with JPA's @Lob - Mailing list pgsql-jdbc

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 |                                             |
------------------------+---------------------------------------------+