Thread: Bug in JDBC driver V. 7.0 ?
When I call the method getTableTypes() in java.sql.DatabaseMetaData, all that's returned is: SYSTEM INDEX. The code for org.posgresql.DatabaseMetaData suggest that I should get at least /** * Get the table types available in this database. The results * are ordered by table type. * * <P>The tabletype is: * <OL> * <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE", * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", * "LOCAL TEMPORARY", "ALIAS", "SYNONYM". * </OL> * * @returnResultSet each row has a single String column that is a * table type */ but not the one (only) I get returned snip: java.sql.ResultSet rs = dbmd.getTableTypes (); while (rs.next()) System.out.println(rs.getString(1)); just to rule out a codeing problem -- Simu Soft v/Anders Svensson Nørrebrogade 140, 4 th 2200 København N e-mail : Anders@simusoft.dk tlf. : +45 3583 8126
I'll check. That one should be working... -- Peter Mount Enterprise Support Maidstone Borough Council Any views stated are my own, and not those of Maidstone Borough Council -----Original Message----- From: Anders Svensson [mailto:anders@simusoft.dk] Sent: Thursday, July 20, 2000 3:53 PM To: pgsql-interfaces@postgresql.org Subject: [INTERFACES] Bug in JDBC driver V. 7.0 ? When I call the method getTableTypes() in java.sql.DatabaseMetaData, all that's returned is: SYSTEM INDEX. The code for org.posgresql.DatabaseMetaData suggest that I should get at least /** * Get the table types available in this database. The results * are ordered by table type. * * <P>The tabletype is: * <OL> * <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE", * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", * "LOCAL TEMPORARY", "ALIAS", "SYNONYM". * </OL> * * @return ResultSet each row has a single String column that is a * table type */ but not the one (only) I get returned snip: java.sql.ResultSet rs = dbmd.getTableTypes (); while (rs.next()) System.out.println(rs.getString(1)); just to rule out a codeing problem -- Simu Soft v/Anders Svensson Nørrebrogade 140, 4 th 2200 København N e-mail : Anders@simusoft.dk tlf. : +45 3583 8126
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
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
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
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 > > > > >