Re: sql type reported for enum

From: rcohen(at)e1b(dot)org
To: dmp <danap(at)ttc-cmc(dot)net>
Cc: PostgreSQL JDBC <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: sql type reported for enum
Date: 2015-08-21 17:03:47
Message-ID: OF2BB55863.110B3185-ON85257EA8.004B9E79-85257EA8.005DBB27@e1b.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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:
https://milton1.wnyric.org/canit/b.php?i=01P6Mt8fx&m=1c6554c9d5ad&t=20150819&c=s

Not spam:
https://milton1.wnyric.org/canit/b.php?i=01P6Mt8fx&m=1c6554c9d5ad&t=20150819&c=n

Forget vote:
https://milton1.wnyric.org/canit/b.php?i=01P6Mt8fx&m=1c6554c9d5ad&t=20150819&c=f

------------------------------------------------------
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.

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Thomas Hill 2015-08-22 16:25:38 Functions returning SETs and CallableStatment Interface
Previous Message Mikko Tiihonen 2015-08-20 07:16:45 Re: best way to convert JSONB object from result