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(at)retep(dot)org(dot)uk, peter(at)retepdigital(dot)com, me(at)petermount(dot)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(at)gmx(dot)de>
To: pgsql-interfaces <pgsql-interfaces(at)postgresql(dot)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(at)linux(dot)kapow(dot)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(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 (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(at)linux(dot)kapow(dot)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(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 (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
>
>
>
>
>