JDBC 7.0 driver: Metadata support
От | Christian Pröhl |
---|---|
Тема | JDBC 7.0 driver: Metadata support |
Дата | |
Msg-id | 200006181640.e5IGetM44212@hub.org обсуждение исходный текст |
Список | 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
В списке pgsql-interfaces по дате отправления: