The following bug has been logged online:
Bug reference: 1170
Logged by: Michael Mutschler
Email address: mutschler@trustsec.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++;
}
}