Re: [HACKERS] JPA + enum == Exception

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