RE: 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