Thread: Missing fields in getColumns() result
Hi list,
I have just found out that the ResultSet that is returned by the
getColumns() method of a DatabaseMetaData object does only contain the
fields from the JDBC 2 (pre JDK 1.4) spec, even if I use the JDBC 3
driver. The missing fields are "SCOPE_CATLOG", "SCOPE_SCHEMA",
"SCOPE_TABLE" and "SOURCE_DATA_TYPE".
I have had a look at the source code of the driver, and the problem
seems to be that the "getColumns()" method is not redefined in
AbstractJdbc3DatabaseMetaData. The method from
AbstractJdbc2DatabaseMetaData is compliant with the JDBC 2 spec which
did not contain the additional fields.
Is this a known bug? I could not find anything about it on the driver
homepage. (By the way, how can I search the mailing list archive for the
term "SOURCE_DATA_TYPE"? The underscores seem to be ignored so I get
each posting which contains the three terms "source", "data" and "type" ...)
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
On Tue, 18 Dec 2007, Christian Schröder wrote: > I have just found out that the ResultSet that is returned by the > getColumns() method of a DatabaseMetaData object does only contain the > fields from the JDBC 2 (pre JDK 1.4) spec, even if I use the JDBC 3 > driver. Is this a known bug? Yes. http://archives.postgresql.org/pgsql-jdbc/2004-12/msg00038.php And the more generic entry here: The JDBC 3 DatabaseMetaData methods sometimes return additional information. Currently we only return JDBC 2 data for these methods. http://jdbc.postgresql.org/todo.html > By the way, how can I search the mailing list archive for the term > "SOURCE_DATA_TYPE"? The underscores seem to be ignored so I get each > posting which contains the three terms "source", "data" and "type" > I'm not sure, but restricting the search to just the jdbc list makes your post come out first. Kris Jurka
Hi list, by now I have found two threads in the archive about this issue: http://archives.postgresql.org/pgsql-jdbc/2006-06/msg00038.php http://archives.postgresql.org/pgsql-jdbc/2004-12/msg00008.php I have also seen the following entry in the "Todo" section on the driver homepage: "[JDBC3] The JDBC 3 DatabaseMetaData methods sometimes return additional information. Currently we only return JDBC 2 data for these methods." So the problem doesn't seem to be solved yet. I have implemented a patch that specifically adds the "SOURCE_DATA_TYPE" field to the result of "getColumns()". The other missing fields are added, but always have a "null" value. I had to do changes to both the AbstractJdbc2DatabaseMetaData and the AbstractJdbc3DatabaseMetaData class, but I hope that I didn't break the JDBC 2 code. The test cases completed without errors. It would be great if you could have a look at my proposed patch and decide if you will include it into the CVS repository. 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.42 diff -c -r1.42 AbstractJdbc2DatabaseMetaData.java *** org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java 2 Dec 2007 06:48:43 -0000 1.42 --- org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java 23 Dec 2007 23:12:41 -0000 *************** *** 2202,2280 **** 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); ! f[2] = new Field("TABLE_NAME", Oid.VARCHAR); ! f[3] = new Field("COLUMN_NAME", Oid.VARCHAR); ! f[4] = new Field("DATA_TYPE", Oid.INT2); ! f[5] = new Field("TYPE_NAME", Oid.VARCHAR); ! f[6] = new Field("COLUMN_SIZE", Oid.INT4); ! f[7] = new Field("BUFFER_LENGTH", Oid.VARCHAR); ! f[8] = new Field("DECIMAL_DIGITS", Oid.INT4); ! f[9] = new Field("NUM_PREC_RADIX", Oid.INT4); ! f[10] = new Field("NULLABLE", Oid.INT4); ! f[11] = new Field("REMARKS", Oid.VARCHAR); ! f[12] = new Field("COLUMN_DEF", Oid.VARCHAR); ! f[13] = new Field("SQL_DATA_TYPE", Oid.INT4); ! f[14] = new Field("SQL_DATETIME_SUB", Oid.INT4); ! f[15] = new Field("CHAR_OCTET_LENGTH", Oid.VARCHAR); ! f[16] = new Field("ORDINAL_POSITION", Oid.INT4); ! f[17] = new Field("IS_NULLABLE", Oid.VARCHAR); ! String sql; if (connection.haveMinimumServerVersion("7.3")) { --- 2202,2233 ---- return (ResultSet) ((BaseStatement)createMetaDataStatement()).createDriverResultSet(f, v); } ! ! protected Vector getColumnsFieldDef() { ! Vector f = new Vector(); ! f.addElement(new Field("TABLE_CAT", Oid.VARCHAR)); ! f.addElement(new Field("TABLE_SCHEM", Oid.VARCHAR)); ! f.addElement(new Field("TABLE_NAME", Oid.VARCHAR)); ! f.addElement(new Field("COLUMN_NAME", Oid.VARCHAR)); ! f.addElement(new Field("DATA_TYPE", Oid.INT2)); ! f.addElement(new Field("TYPE_NAME", Oid.VARCHAR)); ! f.addElement(new Field("COLUMN_SIZE", Oid.INT4)); ! f.addElement(new Field("BUFFER_LENGTH", Oid.VARCHAR)); ! f.addElement(new Field("DECIMAL_DIGITS", Oid.INT4)); ! f.addElement(new Field("NUM_PREC_RADIX", Oid.INT4)); ! f.addElement(new Field("NULLABLE", Oid.INT4)); ! f.addElement(new Field("REMARKS", Oid.VARCHAR)); ! f.addElement(new Field("COLUMN_DEF", Oid.VARCHAR)); ! f.addElement(new Field("SQL_DATA_TYPE", Oid.INT4)); ! f.addElement(new Field("SQL_DATETIME_SUB", Oid.INT4)); ! f.addElement(new Field("CHAR_OCTET_LENGTH", Oid.VARCHAR)); ! f.addElement(new Field("ORDINAL_POSITION", Oid.INT4)); ! f.addElement(new Field("IS_NULLABLE", Oid.VARCHAR)); ! return f; ! } ! ! protected Vector getColumnsValues(Field[] f, String catalog, String schemaPattern, String tableNamePattern, StringcolumnNamePattern) throws SQLException { ! Vector v = new Vector(); String sql; if (connection.haveMinimumServerVersion("7.3")) { *************** *** 2332,2338 **** ResultSet rs = connection.createStatement().executeQuery(sql); while (rs.next()) { ! byte[][] tuple = new byte[18][]; int typeOid = (int)rs.getLong("atttypid"); int typeMod = rs.getInt("atttypmod"); --- 2285,2291 ---- ResultSet rs = connection.createStatement().executeQuery(sql); while (rs.next()) { ! byte[][] tuple = new byte[f.length][]; int typeOid = (int)rs.getLong("atttypid"); int typeMod = rs.getInt("atttypmod"); *************** *** 2392,2397 **** --- 2345,2406 ---- } rs.close(); + return 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 + { + Field[] f = (Field[]) getColumnsFieldDef().toArray(new Field[0]); // The field descriptors for the new ResultSet + Vector v = getColumnsValues(f, catalog, schemaPattern, tableNamePattern, columnNamePattern); // The new ResultSettuple stuff + return (ResultSet) ((BaseStatement)createMetaDataStatement()).createDriverResultSet(f, v); } 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 23 Dec 2007 23:12:41 -0000 *************** *** 3,9 **** * Copyright (c) 2004-2005, PostgreSQL Global Development Group * * IDENTIFICATION ! * $PostgreSQL: pgjdbc/org/postgresql/jdbc3/AbstractJdbc3DatabaseMetaData.java,v 1.11 2005/02/15 08:56:26 jurka Exp $ * *------------------------------------------------------------------------- */ --- 3,9 ---- * Copyright (c) 2004-2005, PostgreSQL Global Development Group * * IDENTIFICATION ! * $PostgreSQL: pgjdbc/org/postgresql/jdbc3/AbstractJdbc3DatabaseMetaData.java,v 1.10 2005/01/11 08:25:46 jurka Exp $ * *------------------------------------------------------------------------- */ *************** *** 11,16 **** --- 11,19 ---- import java.sql.*; + import java.util.Vector; + import org.postgresql.core.Field; + import org.postgresql.core.Oid; public abstract class AbstractJdbc3DatabaseMetaData extends org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData { *************** *** 366,370 **** { return false; } ! } --- 369,432 ---- { return false; } ! ! protected Vector getColumnsFieldDef() { ! Vector f = super.getColumnsFieldDef(); ! f.add(new Field("SCOPE_CATLOG", Oid.VARCHAR)); ! f.add(new Field("SCOPE_SCHEMA", Oid.VARCHAR)); ! f.add(new Field("SCOPE_TABLE", Oid.VARCHAR)); ! f.add(new Field("SOURCE_DATA_TYPE", Oid.INT2)); ! return f; ! } ! ! protected Vector getColumnsValues(Field[] f, String catalog, String schemaPattern, String tableNamePattern, StringcolumnNamePattern) throws SQLException { ! Vector v = super.getColumnsValues(f, catalog, schemaPattern, tableNamePattern, columnNamePattern); ! String sql; ! if (connection.haveMinimumServerVersion("7.3")) { ! sql = "SELECT 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) " + ! " WHERE a.attnum > 0 AND NOT a.attisdropped "; ! if (schemaPattern != null && !"".equals(schemaPattern)) { ! sql += " AND n.nspname LIKE '" + escapeQuotes(schemaPattern) + "' "; ! } ! if (tableNamePattern != null && !"".equals(tableNamePattern)) ! { ! sql += " AND c.relname LIKE '" + escapeQuotes(tableNamePattern) + "' "; ! } ! if (columnNamePattern != null && !"".equals(columnNamePattern)) ! { ! sql += " AND a.attname LIKE '" + escapeQuotes(columnNamePattern) + "' "; ! } ! sql += " ORDER BY nspname,relname,attnum "; ! ResultSet rs = connection.createStatement().executeQuery(sql); ! int i = 0; ! while (rs.next()) { ! int baseTypeOid = (int) rs.getLong("typbasetype"); ! ! byte[][] tuple = (byte[][]) v.get(i++); ! 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 ! } ! rs.close(); ! } ! else { ! // Domains have been added in PostgreSQL 7.3 so we can simply leave the ! // additional fields null if we have an older server. ! for (int i = 0; i < v.size(); i++) { ! byte[][] tuple = (byte[][]) v.get(i); ! tuple[18] = null; // SCOPE_CATLOG ! tuple[19] = null; // SCOPE_SCHEMA ! tuple[20] = null; // SCOPE_TABLE ! tuple[21] = null; // SOURCE_DATA_TYPE ! } ! } ! return v; ! } ! }
Hi list,
did you already have time to look at my proposed patch?
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
On Mon, 24 Dec 2007, Christian Schr�der wrote: > So the problem doesn't seem to be solved yet. I have implemented a patch > that specifically adds the "SOURCE_DATA_TYPE" field to the result of > "getColumns()". I don't like splitting the code into columns + values and I don't like splitting the query into two parts as inconsistencies may occur in the face of concurrent updates. What about moving the existing implementation of getColumns to getColumns(int jdbcVersion), making it do different things based on the version provided and having getColumns() in Jdbc2/3 just provide the version to the new code. Kris Jurka
Kris Jurka wrote:
> I don't like splitting the code into columns + values and I don't like
> splitting the query into two parts as inconsistencies may occur in the
> face of concurrent updates. What about moving the existing
> implementation of getColumns to getColumns(int jdbcVersion), making it
> do different things based on the version provided and having
> getColumns() in Jdbc2/3 just provide the version to the new code.
The possible inconsistencies are indeed a problem!
I see three possible solutions:
1. Implement it as you suggested.
The only disadvantage of this approach is that the
AbstractJdbc2DatabaseMetaData would have to contain also the jdbc3
code. I personally would not expect to find the jdbc3
implementation in the jdbc2 class, but if that's ok to you I won't
complain about it.
2. Duplicate everything from the AbstractJdbc2DatabaseMetaData class
to AbstractJdbc3DatabaseMetaData and add the additional code here.
Doesn't seem an option to me. In my experience code duplication
should always be avoided.
3. Further split up the code (which you probably would like even less
*g*).
The database query could be constructed in one or more separate
functions which would be overridden in the
AbstractJdbc3DatabaseMetaData class. Sounds rather complicated,
but would keep the jdbc2 and jdbc3 implementations clearly separated.
It's up to you which approach you prefer. If you tell me which one you
want to have then I could implement it and send you a patch.
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
On Mon, 7 Jan 2008, Christian Schröder wrote: > 1. Implement it as you suggested. > The only disadvantage of this approach is that the > AbstractJdbc2DatabaseMetaData would have to contain also the jdbc3 > code. I personally would not expect to find the jdbc3 > implementation in the jdbc2 class, but if that's ok to you I won't > complain about it. 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. Kris Jurka
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::oidAS
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::oidAS
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,NULLAS 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,NULLAS adsrc,NULL AS description,NULL
AStypbasetype " +
" 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);
+ }
+
}
On Mon, 7 Jan 2008, Christian Schr�der wrote: > Please see the attached patch where I have implemented your solution. Is > it what you had in mind? > Patch applied. I've also added the new JDBC4 column and added tests. Kris Jurka