Skip site navigation (1) Skip section navigation (2)

JDBC 7.0 driver: Metadata support

From: Christian Pröhl <proehl(at)gmx(dot)de>
To: "pgsql-interfaces" <pgsql-interfaces(at)postgresql(dot)org>
Subject: JDBC 7.0 driver: Metadata support
Date: 2000-06-18 16:40:30
Message-ID: (view raw or flat)
Lists: pgsql-interfaces

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\ 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].


  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();
    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
      types = defaultTableTypes;
    // 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 
    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++) {
	sql.append(" or ");
      for(int j=0;j<getTableTypes.length;j++)
	if(getTableTypes[j][0].equals(types[i])) {
    // Added by Stefan Andreasen <stefan(at)linux(dot)kapow(dot)dk>
    // Now take the pattern into account
    sql.append(") and relname like '");

    // Now run the query
    r = connection.ExecSQL(sql.toString());
    byte remarks[];
    while (
	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) {;
	  remarks = dr.getBytes(1);
	} else
	  remarks = defaultRemarks;
	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
    return new ResultSet(connection, f, v, "OK", 1);

  // Additional method to query table type for given
  // PostgreSQL object. Used by getTables(...) for
  // Method added by Christian Pr\u00F6hl <proehl(at)gmx(dot)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 (
             // Yippie! ResultSet contains a row,
             // so oid is current table type
           return tableTypes[i][0].getBytes();
   return null;


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.


pgsql-interfaces by date

Next:From: Adam RossiDate: 2000-06-19 19:26:39
Subject: Simple Q: Does the ODBC driver support LONGVARCHAR?
Previous:From: Bob KlineDate: 2000-06-17 18:29:47
Subject: getMoreResults() returns false incorrectly

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group