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

Andreas Joseph Krogh schrieb am 07.02.2011 um 00:01:
> 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?

Is there anything new here?
This still doesn't work with 9.4.1208

We are using Hibernate with an application that has to support Oracle and Postgres.

For Oracle we have to use CLOB to store Strings longer then 4000 characters - which means we *have* to use CLOB.
The Hibernate Entity is therefore annotated with @Lob and thus the whole thing fails with Postgres

So we are caught between a rock and a hard place: change to String and lose the ability to work with Oracle (which we can't) or stick with @Lob and lose the possibility to also support Postgres.

I think a connection parameter treatClobAsString=true would solve this problem - at least for us (and I think for most others that have this problem as well).

Are there any plans for this?

If not, is this something that would be accepted as a patch?

Thomas