Re: Missing fields in getColumns() result - Mailing list pgsql-jdbc

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);
+     }
+
  }