Message-Id: <200006181640.e5IGetM44212@hub.org>
From: "=?iso-8859-1?q?Christian_Pr=F6hl?=" <proehl@gmx.de>
To: "pgsql-interfaces" <pgsql-interfaces@postgresql.org>
Date: Sun, 18 Jun 2000 18:40:30 +0200 (CEST)
Reply-To: "=?iso-8859-1?q?Christian_Pr=F6hl?=" <proehl@gmx.de>
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Subject: JDBC 7.0 driver: Metadata support

Hello,

I'm just exploring the metadata support of postgreSQL 7.0's JDBC-driver. So I 
found a bug concerning table meta datas:

In jdbcX\DatabaseMetadaData.java following methods should be fixed in the 
following way (tested on OS/2 with JDK 1.1.7 and PostgreSQL 7.0). Then you can 
query what a table has. Code changes are commented with [CP].

----------------------------)schnipp(-------------------------------------

  public java.sql.ResultSet getTableTypes() throws SQLException
  {
    Field f[] = new Field[1];
    Vector v = new Vector();
    byte[][] tuple = new byte[1][0];
    f[0] = new Field(connection,new String("TABLE_TYPE"),iVarcharOid,32);
    for(int i=0;i<getTableTypes.length;i++) {
      /* [CP] The following line was missing */
      tuple = new byte[1][0];
      tuple[0] = getTableTypes[i][0].getBytes();
      v.addElement(tuple);
    }
    return new ResultSet(connection,f,v,"OK",1);
  }


  public java.sql.ResultSet getTables(String catalog, String schemaPattern, 
String tableNamePattern, String types[]) throws SQLException
  {
    // 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, new String("TABLE_CAT"), iVarcharOid, 32);
    f[1] = new Field(connection, new String("TABLE_SCHEM"), iVarcharOid, 32);
    f[2] = new Field(connection, new String("TABLE_NAME"), iVarcharOid, 32);
    f[3] = new Field(connection, new String("TABLE_TYPE"), iVarcharOid, 32);
    f[4] = new Field(connection, new String("REMARKS"), iVarcharOid, 32);
    
    // Now form the query
    /* [CP] Added "relkind" to select clause, needed below */
    StringBuffer sql = new StringBuffer("select relname,oid,relkind from 
pg_class where (");
    boolean notFirst=false;
    for(int i=0;i<types.length;i++) {
      if(notFirst)
	sql.append(" or ");
      for(int j=0;j<getTableTypes.length;j++)
	if(getTableTypes[j][0].equals(types[i])) {
	  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();
	
	tuple[0] = null;		// Catalog name
	tuple[1] = null;		// Schema name
	tuple[2] = r.getBytes(1);	// Table name
	tuple[3] = null;		// Table type
        tuple[3] = r.getBytes(3);
        /* [CP] return table type as string */
        tuple[3] = queryTableType(r.getInt(2),types);
	tuple[4] = remarks;		// Remarks
	v.addElement(tuple);
      }
    r.close();
    return new ResultSet(connection, f, v, "OK", 1);
  }

  // Additional method to query table type for given
  // PostgreSQL object. Used by getTables(...) for
  // TABLE_TYPE
  // Method added by Christian Pr\u00F6hl <proehl@gmx.de>
  private byte[] queryTableType(int oid, String[] types) throws SQLException
  {
   if ((types==null)||(types.length==0)) return null;
   String[][] tableTypes = (getTableTypes);
   for (int i=0; i<tableTypes.length; i++)
   {
     for (int j=0; j<types.length; j++)
     {
       if (tableTypes[i][0].equals(types[j]))
       {
         String sql = "select oid from pg_class where oid="+oid+
                      " and "+tableTypes[i][1];
         // Now run the query
         java.sql.ResultSet r = connection.ExecSQL(sql.toString());
         if (r.next())
             // Yippie! ResultSet contains a row,
             // so oid is current table type
         {
           r.close();
           return tableTypes[i][0].getBytes();
         }
         r.close();
       }
     }
   }
   return null;
  }


----------------------------)schnipp(-------------------------------------

Some other questions:

* Is it possible to support getCatalogs()? PostgreSQL supports different 
databases, so it this list could be retrieved by this method.

* Can the avaiable tables be distinguished by other types apart from tables, 
indices, sequences (as defined by defaultTableTypes[]), e.g. Views? The psql 
command '\d' can distinguish between tables and views.


Bye
Christian