Re: sql type reported for enum

done

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: rcohen(at)e1b(dot)org,
Cc: dmp <danap(at)ttc-cmc(dot)net>, PostgreSQL JDBC
<pgsql-jdbc(at)postgresql(dot)org>
Date: 08/22/2015 07:23 PM
Subject: Re: [JDBC] sql type reported for enum
Sent by: pgsql-jdbc-owner(at)postgresql(dot)org

Yes, this seems like a bug, please report it on github.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 21 August 2015 at 13:03, <rcohen(at)e1b(dot)org> wrote:
I'm willing to go with the idea that an enum is a set of
characters, but the jdbc driver is
not. So, if you attempt to setString(int index, String val) on a prepared
statement for a column
that is an enum, you will get an Exception because the passed in value
needs to be cast.
The problem boils down thus: if one queries the Connection
metadata it will tell you
that an enum column is of type VARCHAR; but if you believe this metadata
and treat the
column as if it were Types.VARCHAR when using PreparedStatments, you will
get an
exception.
So I personally don't care much whether the metadata reports enum
columns as
Types.VARCHAR, Types.OTHER, or Types.FLOAT_19_AND_A_HALF, but whatever
type
the metadata reports should work in a PreparedStatement. Right now it
isn't, and it has
become far, far more difficult to make work tools that examine columns
through the
Connection metadata.

As for DBUnit, it relies on the enum columns being reported by the
metadata as a
Types.OTHER.

My current solution to this mismatch between what the metadata
reports
and what PreparedStatements will accept is to set the connection property
"stringtype"
to "unspecified". It works, but I'm not wholly satisfied.

Ross

From: dmp <danap(at)ttc-cmc(dot)net>
To: rcohen(at)e1b(dot)org, PostgreSQL JDBC <pgsql-jdbc(at)postgresql(dot)org>,
Date: 08/19/2015 08:29 PM
Subject: Re: [JDBC] sql type reported for enum

rcohen(at)e1b(dot)org wrote:
> It breaks the jdbc meta-data; that is for an enum column
>
> columnsResultSet./getInt/("DATA_TYPE")
>
> now returns Types.VARCHAR.
>
> This breaks some of my code; it also breaks DBUnit's support for
postgres. Now
> I can code around this, but should the metadata be reporting that an
enum column
> is of type Types.VARCHAR? This seems like incorrect behavior.
>
> Yes, enums should probably be avoided, but that isn't an option for me
> right now.
>
> Ross

Seems an Enum is only a set of characters and the MySQL database does
define it
as such. So how does DBUnit handle that databases enum types?

Name Class
Type Type Name
enum_type java.lang.String 1
CHAR

danap.

--
BEGIN-ANTISPAM-VOTING-LINKS
------------------------------------------------------

Teach CanIt if this mail (ID 01P6Mt8fx) is spam:
Spam: about:blank
Not spam: about:blank
Forget vote: about:blank
------------------------------------------------------
END-ANTISPAM-VOTING-LINKS

Confidentiality Notice:
This electronic message and any attachments may contain confidential or
privileged information, and is intended only for the individual or entity
identified above as the addressee. If you are not the addressee (or the
employee or agent responsible to deliver it to the addressee), or if this
message has been addressed to you in error, you are hereby notified that
you may not copy, forward, disclose or use any part of this message or any
attachments. Please notify the sender immediately by return e-mail or
telephone and delete this message from your system.

Spam
Not spam
Forget previous vote

Confidentiality Notice:
This electronic message and any attachments may contain confidential or
privileged information, and is intended only for the individual or entity
identified above as the addressee. If you are not the addressee (or the
employee or agent responsible to deliver it to the addressee), or if this
message has been addressed to you in error, you are hereby notified that
you may not copy, forward, disclose or use any part of this message or any
attachments. Please notify the sender immediately by return e-mail or
telephone and delete this message from your system.