Thread: sql type reported for enum

I have some metadata utilities that query jdbc column metadata,
and I'm fairly certain that my enum colums, which used to be reported at
Type.OTHER, are now being reported as Type.VARCHAR.

That is,
        rs.getInt("DATA_TYPE")
returns Type.OTHER

The type names, however, seem to be correct.  Thus,
        rs.getString("TYPE_NAME")
returns the actual enum name.

Not only does my code now behave differently, but I also
see that 3rd party libraries (DBUnit) are also now missing
enum columns.

Thus, DBUnit's PostgresqlDataTypeFactory code for returning enums never
gets hits because the sqlType is never Types.OTHER:

        if (sqlType == Types.OTHER)
            if ("uuid".equals(sqlTypeName))
                return new UuidType();
            else if ("interval".equals(sqlTypeName))
                    return new IntervalType();
            else if ("inet".equals(sqlTypeName))
                return new InetType();
            else
            {
                // Finally check whether the user defined a custom datatype
                if(isEnumType(sqlTypeName))
                {
                    if(logger.isDebugEnabled())
                    return new GenericEnumType(sqlTypeName);
                }
            }


So did something change recently in the drivers?   I've recently upgraded my database from
9.1 to 9.4, along with my jdbc drivers.  

Thanks,
Ross

---

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.

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



From:        Dave Cramer <pg@fastcrypt.com>
To:        "Hudson, Derrick" <dhudson@redcom.com>,
Cc:        "rcohen@e1b.org" <rcohen@e1b.org>, "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>
Date:        08/19/2015 09:18 AM
Subject:        Re: [JDBC] sql type reported for enum
Sent by:        davecramer@gmail.com

---

So what does this break ?

FWIW, I avoid enum's for many reasons and use check constraints instead.

Dave Cramer

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

On 19 August 2015 at 09:11, Hudson, Derrick <dhudson@redcom.com> wrote:
There is the following change, which may be related:

    https://jdbc.postgresql.org/documentation/changelog.html#version_9.2-1003

    Author: Tom Dunstan Date: Sun Jul 7 16:20:41 2013 +0930
    Make PreparedStatement.getObject() for an enum type return a string rather than a PGObject



________________________________________
From: pgsql-jdbc-owner@postgresql.org [pgsql-jdbc-owner@postgresql.org] On Behalf Of rcohen@e1b.org [rcohen@e1b.org]
Sent: Tuesday, August 18, 2015 5:21 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] sql type reported for enum

I have some metadata utilities that query jdbc column metadata,
and I'm fairly certain that my enum colums, which used to be reported at
Type.OTHER, are now being reported as Type.VARCHAR.

That is,
        rs.getInt("DATA_TYPE")
returns Type.OTHER

The type names, however, seem to be correct.  Thus,
        rs.getString("TYPE_NAME")
returns the actual enum name.

Not only does my code now behave differently, but I also
see that 3rd party libraries (DBUnit) are also now missing
enum columns.

Thus, DBUnit's PostgresqlDataTypeFactory code for returning enums never
gets hits because the sqlType is never Types.OTHER:

        if (sqlType == Types.OTHER)
            if ("uuid".equals(sqlTypeName))
                return new UuidType();
            else if ("interval".equals(sqlTypeName))
                    return new IntervalType();
            else if ("inet".equals(sqlTypeName))
                return new InetType();
            else
            {
                // Finally check whether the user defined a custom datatype
                if(isEnumType(sqlTypeName))
                {
                    if(logger.isDebugEnabled())
                    return new GenericEnumType(sqlTypeName);
                }
            }


So did something change recently in the drivers?   I've recently upgraded my database from
9.1 to 9.4, along with my jdbc drivers.

Thanks,
Ross


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

---

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.

done



From:        Dave Cramer <pg@fastcrypt.com>
To:        rcohen@e1b.org,
Cc:        dmp <danap@ttc-cmc.net>, PostgreSQL JDBC <pgsql-jdbc@postgresql.org>
Date:        08/22/2015 07:23 PM
Subject:        Re: [JDBC] sql type reported for enum
Sent by:        pgsql-jdbc-owner@postgresql.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@e1b.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@ttc-cmc.net>
To:        rcohen@e1b.org, PostgreSQL JDBC <pgsql-jdbc@postgresql.org>,
Date:        08/19/2015 08:29 PM
Subject:        Re: [JDBC] sql type reported for enum

---

rcohen@e1b.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.