Re: Missing fields in getColumns() result

[Christian Schröder <cs(at)deriva(dot)de>]

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, String columnNamePattern) 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 ResultSet tuple 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, String columnNamePattern) 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;
!     }
!     
  }