Re: getTypeInfo

On 14 December 2016 at 08:34, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

>
> On 14 December 2016 at 07:32, Jan Motl <yzan(at)volny(dot)cz> wrote:
>
>> Hi,
>>
>> I noticed that getTypeInfo() may not return data in the order specified
>> in Oracle documentation (https://docs.oracle.com/javas
>> e/7/docs/api/java/sql/DatabaseMetaData.html#getTypeInfo()):
>> Retrieves a description of all the data types supported by this database.
>> They are ordered by DATA_TYPE and then by how closely the data type maps to
>> the corresponding JDBC SQL type.
>> With the following code snippet:
>>
>> try (Connection connection = dataSource.getConnection(); ResultSet rs =
>> connection.getMetaData().getTypeInfo()) {
>> while (rs.next()) {
>> System.out.println(rs.getInt("DATA_TYPE"));
>> }
>> } catch (SQLException e) {}
>>
>> I am getting DATA_TYPE in a non-monotonic order:
>> -7
>> -2
>> 1
>> 12
>> -5
>> ...
>>
>> Additional metadata:
>> JDBC driver: PostgreSQL 9.4.1212
>> Server version: 9.6beta1.0 (9.6beta1.0)
>>
>> What do I do wrong?
>>
>> Nothing, I just looked at the code and there is no order by on there
>
> Dave Cramer
>
> davec(at)postgresintl(dot)com
> www.postgresintl.com
>
>
Looked at this and here is the query that we use:

"SELECT t.typname,t.oid FROM pg_catalog.pg_type t"
+ " JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid) "
+ " WHERE n.nspname != 'pg_toast'";

So we could order on typname, but there's no guarantee that it would line
up with java.sqltypes

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

<http://www.postgresintl.com/>