Re: [JDBC] JPA + enum == Exception

On 02/08/2013 12:55 PM, Tom Lane wrote:
> AFAIK this is just business as usual with JDBC: setString() implies that
> the parameter is of a string type.
Well, it means that it's a type compatible with a java.lang.String .
JDBC doesn't say much about the database-side type.

It's my strong view that we should accept setString(...) for any
string-like type, like xml, json, domains of text, and so on - or at
least provide an easy, no-superuser-required way to tell Pg to do so.

I've pushed a testcase to:

https://github.com/ringerc/scrapcode/tree/master/testcases/postgresql/jdbc

that you can run if you want to see/experiment with the JDBC behaviour.
I'll attach a tgz of it to a follow-up mail.

Notably, you'll see that setObject(position, "value") is insufficient;
you need to explicitly invoke setObject with Types.OTHER.

> It'll fall over if the type actually
> required is anything but a string. (I'm no Java expert, but I seem to
> recall that using setObject instead is the standard workaround.)
Unfortunately, setObject(...) isn't a complete workaround; it doesn't
know about PostgreSQL-specific types, and setObject(1, "value") won't
work, you have to explicitly specify Types.OTHER, eg setObject(1,
"{\"key\" : \"value\"}", Types.OTHER);

More importantly, for many Java users there are layers on top of JDBC
that expect the database to be moderately lenient about accepting
java.lang.String arguments. The extreme strictness Pg imposes makes
things hard because most of these tools don't provide ways to punch
through the abstraction and specify database types - they haven't needed
to, because other DBs let you implicitly cast to/from things that look
like strings.

PgJDBC could work around this by treating setString(...) and the
two-argument setObject(...) as if they were setObject(..., Types.Other).
However, this would risk breaking queries that currently rely on the
explicit text type to resolve functions that would otherwise be
ambiguous. The same issue may apply to making conversions for text-like
types more lenient in the server, and it wouldn't help people who want to:

@Entity
public class MyJPAEntity {
//....

@Column
private String jsonField;

}

... in their JPA mappings backed by Hibernate/EclipseLink/etc. They
really need a session-level, DB-level or user-level (ie: GUC) way to say
"Let me cast implicitly between json/xml/etc and text".

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services