Re: [JDBC] Missing fields in getColumns() result

From: Christian Schröder <cs(at)deriva.de>
Date: Mon Jan 07 2008 - 11:22:16 EST

Kris Jurka wrote:

> I think this is the way to go.  It avoids all the confusion of 
> duplication or splitting it into a half-dozen functions.  It's OK for 
> a base class to implement more functionality than it needs to make 
> life easier for its children.

Please see the attached patch where I have implemented your solution. Is it what you had in mind?

Regards,

    Christian

-- 
Deriva GmbH                         Tel.: +49 551 489500-42
Financial IT and Consulting         Fax:  +49 551 489500-91
Hans-Böckler-Straße 2                  
http://www.deriva.de
D-37079 Göttingen

Deriva CA Certificate: 
http://www.deriva.de/deriva-ca.cer

Index: org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java =================================================================== RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java,v retrieving revision 1.33.2.3 diff -c -r1.33.2.3 AbstractJdbc2DatabaseMetaData.java
*** org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java 23 Jul 2007 17:30:46 -0000 1.33.2.3
--- org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java 7 Jan 2008 16:17:34 -0000 ***************
*** 2141,2198 ****
return (ResultSet) ((BaseStatement)createMetaDataStatement()).createDriverResultSet(f, v); } ! /* ! * Get a description of table columns available in a catalog. ! * ! * <P>Only column descriptions matching the catalog, schema, table ! * and column name criteria are returned. They are ordered by ! * TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION. ! * ! * <P>Each column description has the following columns: ! * <OL> ! * <LI><B>TABLE_CAT</B> String => table catalog (may be null) ! * <LI><B>TABLE_SCHEM</B> String => table schema (may be null) ! * <LI><B>TABLE_NAME</B> String => table name ! * <LI><B>COLUMN_NAME</B> String => column name ! * <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types ! * <LI><B>TYPE_NAME</B> String => Data source dependent type name ! * <LI><B>COLUMN_SIZE</B> int => column size. For char or date ! * types this is the maximum number of characters, for numeric or ! * decimal types this is precision. ! * <LI><B>BUFFER_LENGTH</B> is not used. ! * <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits ! * <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2) ! * <LI><B>NULLABLE</B> int => is NULL allowed? ! * <UL> ! * <LI> columnNoNulls - might not allow NULL values ! * <LI> columnNullable - definitely allows NULL values ! * <LI> columnNullableUnknown - nullability unknown ! * </UL> ! * <LI><B>REMARKS</B> String => comment describing column (may be null) ! * <LI><B>COLUMN_DEF</B> String => default value (may be null) ! * <LI><B>SQL_DATA_TYPE</B> int => unused ! * <LI><B>SQL_DATETIME_SUB</B> int => unused ! * <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the ! * maximum number of bytes in the column ! * <LI><B>ORDINAL_POSITION</B> int => index of column in table ! * (starting at 1) ! * <LI><B>IS_NULLABLE</B> String => "NO" means column definitely ! * does not allow NULL values; "YES" means the column might ! * allow NULL values. An empty string means nobody knows. ! * </OL> ! * ! * @param catalog a catalog name; "" retrieves those without a catalog ! * @param schemaPattern a schema name pattern; "" retrieves those ! * without a schema ! * @param tableNamePattern a table name pattern ! * @param columnNamePattern a column name pattern ! * @return ResultSet each row is a column description ! * @see #getSearchStringEscape ! */ ! public java.sql.ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException { Vector v = new Vector(); // The new ResultSet tuple stuff ! Field f[] = new Field[18]; // The field descriptors for the new ResultSet f[0] = new Field("TABLE_CAT", Oid.VARCHAR); f[1] = new Field("TABLE_SCHEM", Oid.VARCHAR); --- 2141,2151 ---- return (ResultSet) ((BaseStatement)createMetaDataStatement()).createDriverResultSet(f, v); } ! protected java.sql.ResultSet getColumns(int jdbcVersion, String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException { + int numberOfFields = jdbcVersion >= 3 ? 22 : 18; Vector v = new Vector(); // The new ResultSet tuple stuff ! Field f[] = new Field[numberOfFields]; // The field descriptors for the new ResultSet f[0] = new Field("TABLE_CAT", Oid.VARCHAR); f[1] = new Field("TABLE_SCHEM", Oid.VARCHAR); ***************
*** 2213,2225 ****
f[16] = new Field("ORDINAL_POSITION", Oid.INT4); f[17] = new Field("IS_NULLABLE", Oid.VARCHAR); String sql; if (connection.haveMinimumServerVersion("7.3")) { ! sql = "SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description " + " FROM pg_catalog.pg_namespace n " + " JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) " + " JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) " + " LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) " + " LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) " + " LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') " + --- 2166,2186 ---- f[16] = new Field("ORDINAL_POSITION", Oid.INT4); f[17] = new Field("IS_NULLABLE", Oid.VARCHAR); + if (jdbcVersion >= 3) { + f[18] = new Field("SCOPE_CATLOG", Oid.VARCHAR); + f[19] = new Field("SCOPE_SCHEMA", Oid.VARCHAR); + f[20] = new Field("SCOPE_TABLE", Oid.VARCHAR); + f[21] = new Field("SOURCE_DATA_TYPE", Oid.INT2); + } + String sql; if (connection.haveMinimumServerVersion("7.3")) { ! sql = "SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description,t.typbasetype " + " FROM pg_catalog.pg_namespace n " + " JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) " + " JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) " + + " JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) " + " LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) " + " LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) " + " LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') " + ***************
*** 2232,2238 ****
} else if (connection.haveMinimumServerVersion("7.2")) { ! sql = "SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description " + " FROM pg_class c " + " JOIN pg_attribute a ON (a.attrelid=c.oid) " + " LEFT JOIN pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) " + --- 2193,2199 ---- } else if (connection.haveMinimumServerVersion("7.2")) { ! sql = "SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description,NULL::oid AS typbasetype " + " FROM pg_class c " + " JOIN pg_attribute a ON (a.attrelid=c.oid) " + " LEFT JOIN pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) " + ***************
*** 2242,2248 ****
} else if (connection.haveMinimumServerVersion("7.1")) { ! sql = "SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description " + " FROM pg_class c " + " JOIN pg_attribute a ON (a.attrelid=c.oid) " + " LEFT JOIN pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) " + --- 2203,2209 ---- } else if (connection.haveMinimumServerVersion("7.1")) { ! sql = "SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description,NULL::oid AS typbasetype " + " FROM pg_class c " + " JOIN pg_attribute a ON (a.attrelid=c.oid) " + " LEFT JOIN pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) " + ***************
*** 2252,2258 ****
else { // if < 7.1 then don't get defaults or descriptions. ! sql = "SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,NULL AS adsrc,NULL AS description " + " FROM pg_class c, pg_attribute a " + " WHERE a.attrelid=c.oid AND a.attnum > 0 "; } --- 2213,2219 ---- else { // if < 7.1 then don't get defaults or descriptions. ! sql = "SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,NULL AS adsrc,NULL AS description,NULL AS typbasetype " + " FROM pg_class c, pg_attribute a " + " WHERE a.attrelid=c.oid AND a.attnum > 0 "; } ***************
*** 2270,2276 ****
ResultSet rs = connection.createStatement().executeQuery(sql); while (rs.next()) { ! byte[][] tuple = new byte[18][]; int typeOid = rs.getInt("atttypid"); int typeMod = rs.getInt("atttypmod"); --- 2231,2237 ---- ResultSet rs = connection.createStatement().executeQuery(sql); while (rs.next()) { ! byte[][] tuple = new byte[numberOfFields][]; int typeOid = rs.getInt("atttypid"); int typeMod = rs.getInt("atttypmod"); ***************
*** 2326,2331 ****
--- 2287,2301 ---- tuple[16] = rs.getBytes("attnum"); // ordinal position tuple[17] = connection.encodeString(rs.getBoolean("attnotnull") ? "NO" : "YES"); // Is nullable + if (jdbcVersion >= 3) { + int baseTypeOid = (int) rs.getLong("typbasetype"); + + tuple[18] = null; // SCOPE_CATLOG + tuple[19] = null; // SCOPE_SCHEMA + tuple[20] = null; // SCOPE_TABLE + tuple[21] = baseTypeOid == 0 ? null : connection.encodeString(Integer.toString(connection.getSQLType(baseTypeOid))); // SOURCE_DATA_TYPE + } + v.addElement(tuple); } rs.close(); ***************
*** 2334,2339 ****
--- 2304,2362 ---- } /* + * Get a description of table columns available in a catalog. + * + * <P>Only column descriptions matching the catalog, schema, table + * and column name criteria are returned. They are ordered by + * TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION. + * + * <P>Each column description has the following columns: + * <OL> + * <LI><B>TABLE_CAT</B> String => table catalog (may be null) + * <LI><B>TABLE_SCHEM</B> String => table schema (may be null) + * <LI><B>TABLE_NAME</B> String => table name + * <LI><B>COLUMN_NAME</B> String => column name + * <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types + * <LI><B>TYPE_NAME</B> String => Data source dependent type name + * <LI><B>COLUMN_SIZE</B> int => column size. For char or date + * types this is the maximum number of characters, for numeric or + * decimal types this is precision. + * <LI><B>BUFFER_LENGTH</B> is not used. + * <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits + * <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2) + * <LI><B>NULLABLE</B> int => is NULL allowed? + * <UL> + * <LI> columnNoNulls - might not allow NULL values + * <LI> columnNullable - definitely allows NULL values + * <LI> columnNullableUnknown - nullability unknown + * </UL> + * <LI><B>REMARKS</B> String => comment describing column (may be null) + * <LI><B>COLUMN_DEF</B> String => default value (may be null) + * <LI><B>SQL_DATA_TYPE</B> int => unused + * <LI><B>SQL_DATETIME_SUB</B> int => unused + * <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the + * maximum number of bytes in the column + * <LI><B>ORDINAL_POSITION</B> int => index of column in table + * (starting at 1) + * <LI><B>IS_NULLABLE</B> String => "NO" means column definitely + * does not allow NULL values; "YES" means the column might + * allow NULL values. An empty string means nobody knows. + * </OL> + * + * @param catalog a catalog name; "" retrieves those without a catalog + * @param schemaPattern a schema name pattern; "" retrieves those + * without a schema + * @param tableNamePattern a table name pattern + * @param columnNamePattern a column name pattern + * @return ResultSet each row is a column description + * @see #getSearchStringEscape + */ + public java.sql.ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException + { + return getColumns(2, catalog, schemaPattern, tableNamePattern, columnNamePattern); + } + + /* * Get a description of the access rights for a table's columns. * * <P>Only privileges matching the column name criteria are Index: org/postgresql/jdbc3/AbstractJdbc3DatabaseMetaData.java =================================================================== RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/jdbc3/AbstractJdbc3DatabaseMetaData.java,v retrieving revision 1.11 diff -c -r1.11 AbstractJdbc3DatabaseMetaData.java
*** org/postgresql/jdbc3/AbstractJdbc3DatabaseMetaData.java 15 Feb 2005 08:56:26 -0000 1.11
--- org/postgresql/jdbc3/AbstractJdbc3DatabaseMetaData.java 7 Jan 2008 16:17:34 -0000 ***************
*** 367,370 ****
--- 367,375 ---- return false; } + public java.sql.ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException + { + return getColumns(3, catalog, schemaPattern, tableNamePattern, columnNamePattern); + } + }

---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate Received on Mon Jan 7 11:35:58 2008