Re: [JDBC] JPA + enum == Exception

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>> when attempting to use a prepared statement:
>
>>     ps = con.prepareStatement("insert into enumcast values (?)");
>>     ps.setString(1, "meh");
>>     ps.executeUpdate();
>
>> we get a
>
>> org.postgresql.util.PSQLException: ERROR: column "current_mood"
>> is of type mood but expression is of type character varying
>>   Hint: You will need to rewrite or cast the expression.
>
> AFAIK this is just business as usual with JDBC: setString() implies that
> the parameter is of a string type.  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.)

Right.  It is spelled out pretty specifically in the JDBC spec:

http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf

Table B-2 governs setting PreparedStatement parameters with
setString and similar methods, while tables B-4 and B-5 cover the
setObject methods.  It is clearly not unusual for other vendors to
extend the JDBC specification to make life easier for those writing
ORMs, etc.; but the behavior of the current PostgreSQL JDBC driver
is doing all that is required by the spec.

> Enums are not suffering any special hardship here, and I'd be against
> weakening the type system to give them a special pass.

This is not entirely unrelated to the discussions about allowing
broader use of automatic casting server-side.  It seems to me that
on one side of the argument is the idea that strict typing reduces
bugs and doesn't lead to problems with ambiguity, especially as
things change; and on the other side the argument is that where no
ambiguity exists we would make life easier for developers of
applications or access tools if we relexed things beyond what the
related specifications require, and that not doing so discourages
adoption.  I think that all the same arguments apply here with
equal force, on both sides of the issue.

The problem with this debate has always been that both sides are
completely right.  Those are always the toughest to resolve.  It
comes down to which evils we tolerate to garner which benefits.  It
seems that in such cases inertia tends to win.  I'm not so sure
that it should.  An ideal solution would find some way to address
the concerns of both sides, but so far that has eluded us when it
comes to the type system.

-Kevin