JDBC 7.0 driver: Metadata support - Mailing list pgsql-interfaces

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
thenew 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);      //
Nowform the query   /* [CP] Added "relkind" to select clause, needed below */   StringBuffer sql = new
StringBuffer("selectrelname,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 nametuple[1] = null;        // Schema
nametuple[2]= r.getBytes(1);    // Table nametuple[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;        //
Remarksv.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 //
Methodadded 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();
returntableTypes[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