The following bug has been logged online:

Bug reference: 1170
Logged by: Michael Mutschler

Email address: mutschler (AT) trustsec (DOT) de

PostgreSQL version: 7.3.4

Operating system: Windows/Linux

Description: jdbc-driver: DatabaseMetaData.getTypeInfo() gives
incorrect result

Details:

I wanted to use DatabaseMetaData.getTypeInfo() to get the supported types of
the connected DB. The Java-doc (1.4.2) says:
------------------------------------------
Retrieves a description of all the standard SQL 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.
------------------------------------------

The list of datatypes I get is not sorted by data-type nor do you get the
closest match. Additionally there seem to be types missing (decimal).
Here is the beginning of the list:

1: SqlType=BIT: bool
2: SqlType=BINARY: bytea
3: SqlType=CHAR: char
4: SqlType=VARCHAR: name
5: SqlType=ARRAY: int8
6: SqlType=SMALLINT: int2
7: SqlType=OTHER: int2vector
8: SqlType=INTEGER: int4
9: SqlType=OTHER: regproc
10: SqlType=VARCHAR: text
11: SqlType=INTEGER: oid
12: SqlType=OTHER: tid
13: SqlType=OTHER: xid
....

I would assume the best match for Types.VARCHAR would be "varchar", but this
is at the end of the list. There is no datatype available for LONGVARCHAR or
DECIMAL.

All jdbc-drivers I tested show the same results (7.5b302, 7.4b214, 7.3 for
jdk1.4). I used JDK1.4.2_04 from sun, and the database is running on linux.

here the code for displaying the list:
public static void displaySqlTypes(final Connection con) throws
SQLException {
final DatabaseMetaData dbm = con.getMetaData();
ResultSet rs = dbm.getTypeInfo();
int i=1;
while(rs.next()) {
System.out.println(i + ": SqlType=" + rs.getInt("DATA_TYPE") +
": "
+ rs.getString("TYPE_NAME"));
i++;
}
}



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org