Re: Bug in JDBC driver V. 7.0 ?

I found one of these earlier this evening (the missing line), and that's now
in cvs. However, I'll look at the other one tomorrow morning.

Peter

--
Peter T Mount peter@retep.org.uk, peter@retepdigital.com, me@petermount.com
Homepage: http://www.retep.org.uk Contact details @ http://petermount.com
PostgreSQL JDBC: http://www.retep.org.uk/postgres/
Java PDF generator: http://www.retep.org.uk/pdf/

----- Original Message -----
From: Christian Pröhl <proehl@gmx.de>
To: pgsql-interfaces <pgsql-interfaces@postgresql.org>
Sent: Thursday, July 20, 2000 6:57 PM
Subject: RE: [INTERFACES] Bug in JDBC driver V. 7.0 ?


> On Thu, 20 Jul 2000 15:56:48 +0100, Peter Mount wrote:
>
> >I'll check. That one should be working...
>
> Hello,
>
> I've also found a bug concerning meta data support:
>
> 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
>
>
>
> 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
>
>
>
>
>