Thread: JDBC - DatabaseMetaData.getTables() null pointer exception

JDBC - DatabaseMetaData.getTables() null pointer exception

From
pgsql-bugs@postgresql.org
Date:
Dave Antal (dantal@vigilos.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
JDBC - DatabaseMetaData.getTables() null pointer exception

Long Description
When you pass the DatabaseMetaData.getTables() method a types array that contains "VIEW" a null pointer exception is
generated.This is in the JDBC driver that is with Postgres 7.1.2.  This is caused by the code not checking for a View
typeon the relkind column returned from the select of pg_class. The code example below contains a version of the method
witha fix in place. 

Sample Code
  /**
   * Get a description of tables available in a catalog.
   *
   * <p>Only table descriptions matching the catalog, schema, table
   * name and type criteria are returned. They are ordered by
   * TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
   *
   * <p>Each table 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>TABLE_TYPE</b> String => table type. Typical types are "TABLE",
   * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL
   * TEMPORARY", "ALIAS", "SYNONYM".
   * <li><b>REMARKS</b> String => explanatory comment on the table
   * </ol>
   *
   * <p>The valid values for the types parameter are:
   * "TABLE", "INDEX", "SEQUENCE", "SYSTEM TABLE" and "SYSTEM INDEX"
   *
   * @param catalog a catalog name; For org.postgresql, this is ignored, and
   * should be set to null
   * @param schemaPattern a schema name pattern; For org.postgresql, this is ignored, and
   * should be set to null
   * @param tableNamePattern a table name pattern. For all tables this should be "%"
   * @param types a list of table types to include; null returns
   * all types
   * @return each row is a table description
   * @exception SQLException if a database-access error occurs.
   */
  public java.sql.ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[])
throwsSQLException 
  {
    // Handle default value for types
    if(types==null)
      types = defaultTableTypes;

    if(tableNamePattern==null)
      tableNamePattern="%";

    // the field descriptors for the new ResultSet
    Field f[] = new Field[5];
    java.sql.ResultSet r;    // ResultSet for the SQL query that we need to do
    Vector v = new Vector();        // The new ResultSet tuple stuff

    f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
    f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
    f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
    f[3] = new Field(connection, "TABLE_TYPE", iVarcharOid, 32);
    f[4] = new Field(connection, "REMARKS", iVarcharOid, 32);

    // Now form the query
    StringBuffer sql = new StringBuffer("select relname,oid,relkind from pg_class where (");
    boolean notFirst=false;
    for(int i=0;i<types.length;i++) {
      for(int j=0;j<getTableTypes.length;j++)
    if(getTableTypes[j][0].equals(types[i])) {
      if(notFirst)
        sql.append(" or ");
      sql.append(getTableTypes[j][1]);
      notFirst=true;
    }
    }

    // Added by Stefan Andreasen <stefan@linux.kapow.dk>
    // Now take the pattern into account
    sql.append(") and relname like '");
    sql.append(tableNamePattern.toLowerCase());
    sql.append("'");

    // Now run the query
    r = connection.ExecSQL(sql.toString());

    byte remarks[];

    while (r.next())
      {
    byte[][] tuple = new byte[5][0];

    // Fetch the description for the table (if any)
    java.sql.ResultSet dr = connection.ExecSQL("select description from pg_description where objoid="+r.getInt(2));
    if(((org.postgresql.ResultSet)dr).getTupleCount()==1) {
      dr.next();
      remarks = dr.getBytes(1);
    } else
      remarks = defaultRemarks;
    dr.close();

    String relKind;
    switch (r.getBytes(3)[0]) {
    case 'r':
        relKind = "TABLE";
        break;
    case 'i':
        relKind = "INDEX";
        break;
    case 'S':
        relKind = "SEQUENCE";
        break;
    case 'v':
        relKind = "VIEW";
        break;
    default:
        relKind = null;
    }

    tuple[0] = null;        // Catalog name
    tuple[1] = null;        // Schema name
    tuple[2] = r.getBytes(1);    // Table name
    tuple[3] = relKind.getBytes();    // Table type
    tuple[4] = remarks;        // Remarks
    v.addElement(tuple);
      }
    r.close();
    return new ResultSet(connection, f, v, "OK", 1);
  }

No file was uploaded with this report

Re: JDBC - DatabaseMetaData.getTables() null pointer exception

From
Bruce Momjian
Date:
Download newer version at:

    http://jdbc.fastcrypt.com

This bug is fixed.


> Dave Antal (dantal@vigilos.com) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> JDBC - DatabaseMetaData.getTables() null pointer exception
>
> Long Description
> When you pass the DatabaseMetaData.getTables() method a types array that contains "VIEW" a null pointer exception is
generated.This is in the JDBC driver that is with Postgres 7.1.2.  This is caused by the code not checking for a View
typeon the relkind column returned from the select of pg_class. The code example below contains a version of the method
witha fix in place. 
>
> Sample Code
>   /**
>    * Get a description of tables available in a catalog.
>    *
>    * <p>Only table descriptions matching the catalog, schema, table
>    * name and type criteria are returned. They are ordered by
>    * TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
>    *
>    * <p>Each table 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>TABLE_TYPE</b> String => table type. Typical types are "TABLE",
>    * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL
>    * TEMPORARY", "ALIAS", "SYNONYM".
>    * <li><b>REMARKS</b> String => explanatory comment on the table
>    * </ol>
>    *
>    * <p>The valid values for the types parameter are:
>    * "TABLE", "INDEX", "SEQUENCE", "SYSTEM TABLE" and "SYSTEM INDEX"
>    *
>    * @param catalog a catalog name; For org.postgresql, this is ignored, and
>    * should be set to null
>    * @param schemaPattern a schema name pattern; For org.postgresql, this is ignored, and
>    * should be set to null
>    * @param tableNamePattern a table name pattern. For all tables this should be "%"
>    * @param types a list of table types to include; null returns
>    * all types
>    * @return each row is a table description
>    * @exception SQLException if a database-access error occurs.
>    */
>   public java.sql.ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[])
throwsSQLException 
>   {
>     // Handle default value for types
>     if(types==null)
>       types = defaultTableTypes;
>
>     if(tableNamePattern==null)
>       tableNamePattern="%";
>
>     // the field descriptors for the new ResultSet
>     Field f[] = new Field[5];
>     java.sql.ResultSet r;    // ResultSet for the SQL query that we need to do
>     Vector v = new Vector();        // The new ResultSet tuple stuff
>
>     f[0] = new Field(connection, "TABLE_CAT", iVarcharOid, 32);
>     f[1] = new Field(connection, "TABLE_SCHEM", iVarcharOid, 32);
>     f[2] = new Field(connection, "TABLE_NAME", iVarcharOid, 32);
>     f[3] = new Field(connection, "TABLE_TYPE", iVarcharOid, 32);
>     f[4] = new Field(connection, "REMARKS", iVarcharOid, 32);
>
>     // Now form the query
>     StringBuffer sql = new StringBuffer("select relname,oid,relkind from pg_class where (");
>     boolean notFirst=false;
>     for(int i=0;i<types.length;i++) {
>       for(int j=0;j<getTableTypes.length;j++)
>     if(getTableTypes[j][0].equals(types[i])) {
>       if(notFirst)
>         sql.append(" or ");
>       sql.append(getTableTypes[j][1]);
>       notFirst=true;
>     }
>     }
>
>     // Added by Stefan Andreasen <stefan@linux.kapow.dk>
>     // Now take the pattern into account
>     sql.append(") and relname like '");
>     sql.append(tableNamePattern.toLowerCase());
>     sql.append("'");
>
>     // Now run the query
>     r = connection.ExecSQL(sql.toString());
>
>     byte remarks[];
>
>     while (r.next())
>       {
>     byte[][] tuple = new byte[5][0];
>
>     // Fetch the description for the table (if any)
>     java.sql.ResultSet dr = connection.ExecSQL("select description from pg_description where objoid="+r.getInt(2));
>     if(((org.postgresql.ResultSet)dr).getTupleCount()==1) {
>       dr.next();
>       remarks = dr.getBytes(1);
>     } else
>       remarks = defaultRemarks;
>     dr.close();
>
>     String relKind;
>     switch (r.getBytes(3)[0]) {
>     case 'r':
>         relKind = "TABLE";
>         break;
>     case 'i':
>         relKind = "INDEX";
>         break;
>     case 'S':
>         relKind = "SEQUENCE";
>         break;
>     case 'v':
>         relKind = "VIEW";
>         break;
>     default:
>         relKind = null;
>     }
>
>     tuple[0] = null;        // Catalog name
>     tuple[1] = null;        // Schema name
>     tuple[2] = r.getBytes(1);    // Table name
>     tuple[3] = relKind.getBytes();    // Table type
>     tuple[4] = remarks;        // Remarks
>     v.addElement(tuple);
>       }
>     r.close();
>     return new ResultSet(connection, f, v, "OK", 1);
>   }
>
> No file was uploaded with this report
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026