Обсуждение: Bug in JDBC driver V. 7.0 ?

Поиск
Список
Период
Сортировка

Bug in JDBC driver V. 7.0 ?

От
Anders Svensson
Дата:
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


RE: Bug in JDBC driver V. 7.0 ?

От
Peter Mount
Дата:
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


RE: Bug in JDBC driver V. 7.0 ?

От
"Christian Pröhl"
Дата:
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







Re: Bug in JDBC driver V. 7.0 ?

От
"Peter Mount"
Дата:
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
>
>
>
>
>