Attached is the patch requested by Tom Lane (see below). It
includes two changes in the JDBC driver:
1) When connected to a backend >= 7.2: use obj_description() and
col_description() instead of direct access to pg_description.
2) In DatabaseMetaData.getTables()/getColumns()/getProcedures():
when there is no comment on the object, return null in the
REMARKS column of the ResultSet, instead of the default string
"no remarks".
Change 2 first appeared as a side-effect of change 1, but it is
actually more compliant with the JDBC spec: "String object
containing an explanatory comment on the table/column/procedure,
which may be null". The default string "no remarks" was strictly
speaking incorrect, as it could not be distinguished from a real
user comment "no remarks". So I removed the default string
completely.
Change 2 might break existing code that doesn't follow the JDBC
spec and isn't prepared to handle a null in the REMARKS column
of getTables()/getColumns()/getProcedures.
Patch tested with jdbc2 against both a 7.1 and a CVS tip
backend. I did not have a jdbc1 environment to build and test
with, but since the touched code is identical in jdbc1 and jdbc2
I don't foresee any problems.
Regards,
René Pijlman
On Fri, 10 Aug 2001 16:08:50 -0400, Tom Lane wrote:
>Would some JDBC hacker develop a patch for the following issue? The
>change is just barely large enough that I don't want to commit untested
>code for it --- but not having a Java development environment at hand,
>I can't test the updated code.
>
>The problem is in DatabaseMetaData.java (same code in both jdbc1 and
>jdbc2, looks like). It does direct access to pg_description that isn't
>right anymore. In getTables, instead of
>
> java.sql.ResultSet dr = connection.ExecSQL("select description from
>pg_description where objoid="+r.getInt(2));
>
>it should be
>
> java.sql.ResultSet dr = connection.ExecSQL("select
>obj_description("+r.getInt(2)+",'pg_class')");
>
>In getColumns, the change is a little more involved, because
>pg_attribute doesn't have an OID column anymore. The initial query
>can't fetch a.oid, but should fetch a.attrelid instead, and then the
>pg_description query should become
>
> java.sql.ResultSet dr = connection.ExecSQL("select
>col_description("+r.getInt(1)+","+r.getInt(5)+")");
>
>(col_description takes the table OID and the column's attnum).
>
>The reason this is more than a 3-line change is that it should be done
>either the old way or the new way depending on whether server version >=
>7.2 or not, for backwards-compatibility of the driver.
>
>It's possible there are other similar changes needed that I missed in a
>quick lookover.
>
>So, would some enterprising person fix the JDBC code to work with CVS
>tip, and submit a patch?
>
> thanks, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
Regards,
René Pijlman
Index: src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java
===================================================================
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java,v
retrieving revision 1.24
diff -c -r1.24 DatabaseMetaData.java
*** src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java 2001/08/04
19:32:04 1.24
--- src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java 2001/08/13
17:31:51
***************
*** 43,52 ****
static final int iInt4Oid = 23; // OID for int4
static final int VARHDRSZ = 4; // length for int4
- // This is a default value for remarks
- private static final byte defaultRemarks[]="no remarks".getBytes();
-
-
public DatabaseMetaData(Connection conn)
{
this.connection = conn;
--- 43,48 ----
***************
*** 1517,1524 ****
java.sql.ResultSet r; // ResultSet for the SQL query that we need to do
Vector v = new Vector(); // The new ResultSet tuple stuff
- byte remarks[] = defaultRemarks;
-
f[0] = new Field(connection, "PROCEDURE_CAT", iVarcharOid, 32);
f[1] = new Field(connection, "PROCEDURE_SCHEM", iVarcharOid, 32);
f[2] = new Field(connection, "PROCEDURE_NAME", iVarcharOid, 32);
--- 1513,1518 ----
***************
*** 1540,1546 ****
tuple[1] = null; // Schema name
tuple[2] = r.getBytes(1); // Procedure name
tuple[3] = tuple[4] = tuple[5] = null; // Reserved
! tuple[6] = remarks; // Remarks
if (r.getBoolean(2))
tuple[7] =
Integer.toString(java.sql.DatabaseMetaData.procedureReturnsResult).getBytes();
--- 1534,1540 ----
tuple[1] = null; // Schema name
tuple[2] = r.getBytes(1); // Procedure name
tuple[3] = tuple[4] = tuple[5] = null; // Reserved
! tuple[6] = null; // Remarks
if (r.getBoolean(2))
tuple[7] =
Integer.toString(java.sql.DatabaseMetaData.procedureReturnsResult).getBytes();
***************
*** 1684,1689 ****
--- 1678,1684 ----
// Now form the query
StringBuffer sql = new StringBuffer("select relname,oid,relkind from pg_class
where (");
+
boolean notFirst=false;
for(int i=0;i<types.length;i++) {
for(int j=0;j<getTableTypes.length;j++)
***************
*** 1704,1722 ****
// 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();
String relKind;
--- 1699,1722 ----
// Now run the query
r = connection.ExecSQL(sql.toString());
while (r.next())
{
byte[][] tuple = new byte[5][0];
// Fetch the description for the table (if any)
! String getDescriptionStatement =
! connection.haveMinimumServerVersion("7.2") ?
! "select obj_description("+r.getInt(2)+",'pg_class')" :
! "select description from pg_description where objoid=" + r.getInt(2);
!
! java.sql.ResultSet dr = connection.ExecSQL(getDescriptionStatement);
!
! byte remarks[] = null;
!
if(((org.postgresql.ResultSet)dr).getTupleCount()==1) {
dr.next();
remarks = dr.getBytes(1);
! }
dr.close();
String relKind;
***************
*** 1919,1940 ****
if (columnNamePattern == null) columnNamePattern="%";
// Now form the query
! // Modified by Stefan Andreasen <[EMAIL PROTECTED]>
! r = connection.ExecSQL("select
a.oid,c.relname,a.attname,a.atttypid,a.attnum,a.attnotnull,a.attlen,a.atttypmod,d.adsrc
from pg_class c,pg_attribute a,pg_attrdef d where a.attrelid=c.oid and c.relname like
'"+tableNamePattern.toLowerCase()+"' and a.attname like
'"+columnNamePattern.toLowerCase()+"' and a.attnum>0 and c.oid=d.adrelid and
d.adnum=a.attnum order by c.relname,a.attnum");
! byte remarks[];
while(r.next()) {
byte[][] tuple = new byte[18][0];
// Fetch the description for the table (if any)
! java.sql.ResultSet dr = connection.ExecSQL("select description from
pg_description where objoid="+r.getInt(1));
if(((org.postgresql.ResultSet)dr).getTupleCount()==1) {
dr.next();
tuple[11] = dr.getBytes(1);
} else
! tuple[11] = defaultRemarks;
!
dr.close();
tuple[0] = "".getBytes(); // Catalog name
--- 1919,1953 ----
if (columnNamePattern == null) columnNamePattern="%";
// Now form the query
! String query =
! "select " +
! (connection.haveMinimumServerVersion("7.2") ? "a.attrelid" : "a.oid") +
! ",c.relname,a.attname,a.atttypid," +
! "a.attnum,a.attnotnull,a.attlen,a.atttypmod,d.adsrc from pg_class c," +
! "pg_attribute a,pg_attrdef d where a.attrelid=c.oid and " +
! "c.relname like '"+tableNamePattern.toLowerCase()+"' and " +
! "a.attname like '"+columnNamePattern.toLowerCase()+"' and " +
! "a.attnum>0 and c.oid=d.adrelid and d.adnum=a.attnum " +
! "order by c.relname,a.attnum";
! r = connection.ExecSQL(query);
while(r.next()) {
byte[][] tuple = new byte[18][0];
// Fetch the description for the table (if any)
! String getDescriptionStatement =
! connection.haveMinimumServerVersion("7.2") ?
! "select col_description(" + r.getInt(1) + "," + r.getInt(5) + ")" :
! "select description from pg_description where objoid=" + r.getInt(1);
!
! java.sql.ResultSet dr = connection.ExecSQL(getDescriptionStatement);
!
if(((org.postgresql.ResultSet)dr).getTupleCount()==1) {
dr.next();
tuple[11] = dr.getBytes(1);
} else
! tuple[11] = null;
dr.close();
tuple[0] = "".getBytes(); // Catalog name
***************
*** 1985,1991 ****
r.close();
return new ResultSet(connection, f, v, "OK", 1);
}
-
/**
* Get a description of the access rights for a table's columns.
*
--- 1998,2003 ----
Index: src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java
===================================================================
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java,v
retrieving revision 1.27
diff -c -r1.27 DatabaseMetaData.java
*** src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java 2001/08/04
19:32:04 1.27
--- src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java 2001/08/13
17:31:51
***************
*** 43,52 ****
static final int iInt4Oid = 23; // OID for int4
static final int VARHDRSZ = 4; // length for int4
- // This is a default value for remarks
- private static final byte defaultRemarks[]="no remarks".getBytes();
-
-
public DatabaseMetaData(Connection conn)
{
this.connection = conn;
--- 43,48 ----
***************
*** 1517,1524 ****
java.sql.ResultSet r; // ResultSet for the SQL query that we need to do
Vector v = new Vector(); // The new ResultSet tuple stuff
- byte remarks[] = defaultRemarks;
-
f[0] = new Field(connection, "PROCEDURE_CAT", iVarcharOid, 32);
f[1] = new Field(connection, "PROCEDURE_SCHEM", iVarcharOid, 32);
f[2] = new Field(connection, "PROCEDURE_NAME", iVarcharOid, 32);
--- 1513,1518 ----
***************
*** 1540,1546 ****
tuple[1] = null; // Schema name
tuple[2] = r.getBytes(1); // Procedure name
tuple[3] = tuple[4] = tuple[5] = null; // Reserved
! tuple[6] = remarks; // Remarks
if (r.getBoolean(2))
tuple[7] =
Integer.toString(java.sql.DatabaseMetaData.procedureReturnsResult).getBytes();
--- 1534,1540 ----
tuple[1] = null; // Schema name
tuple[2] = r.getBytes(1); // Procedure name
tuple[3] = tuple[4] = tuple[5] = null; // Reserved
! tuple[6] = null;
if (r.getBoolean(2))
tuple[7] =
Integer.toString(java.sql.DatabaseMetaData.procedureReturnsResult).getBytes();
***************
*** 1684,1689 ****
--- 1678,1684 ----
// Now form the query
StringBuffer sql = new StringBuffer("select relname,oid,relkind from pg_class
where (");
+
boolean notFirst=false;
for(int i=0;i<types.length;i++) {
for(int j=0;j<getTableTypes.length;j++)
***************
*** 1704,1722 ****
// 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();
String relKind;
--- 1699,1722 ----
// Now run the query
r = connection.ExecSQL(sql.toString());
while (r.next())
{
byte[][] tuple = new byte[5][0];
// Fetch the description for the table (if any)
! String getDescriptionStatement =
! connection.haveMinimumServerVersion("7.2") ?
! "select obj_description("+r.getInt(2)+",'pg_class')" :
! "select description from pg_description where objoid=" + r.getInt(2);
!
! java.sql.ResultSet dr = connection.ExecSQL(getDescriptionStatement);
!
! byte remarks[] = null;
!
if(((org.postgresql.ResultSet)dr).getTupleCount()==1) {
dr.next();
remarks = dr.getBytes(1);
! }
dr.close();
String relKind;
***************
*** 1919,1940 ****
if (columnNamePattern == null) columnNamePattern="%";
// Now form the query
! // Modified by Stefan Andreasen <[EMAIL PROTECTED]>
! r = connection.ExecSQL("select
a.oid,c.relname,a.attname,a.atttypid,a.attnum,a.attnotnull,a.attlen,a.atttypmod,d.adsrc
from pg_class c,pg_attribute a,pg_attrdef d where a.attrelid=c.oid and c.relname like
'"+tableNamePattern.toLowerCase()+"' and a.attname like
'"+columnNamePattern.toLowerCase()+"' and a.attnum>0 and c.oid=d.adrelid and
d.adnum=a.attnum order by c.relname,a.attnum");
! byte remarks[];
while(r.next()) {
byte[][] tuple = new byte[18][0];
// Fetch the description for the table (if any)
! java.sql.ResultSet dr = connection.ExecSQL("select description from
pg_description where objoid="+r.getInt(1));
if(((org.postgresql.ResultSet)dr).getTupleCount()==1) {
dr.next();
tuple[11] = dr.getBytes(1);
} else
! tuple[11] = defaultRemarks;
!
dr.close();
tuple[0] = "".getBytes(); // Catalog name
--- 1919,1953 ----
if (columnNamePattern == null) columnNamePattern="%";
// Now form the query
! String query =
! "select " +
! (connection.haveMinimumServerVersion("7.2") ? "a.attrelid" : "a.oid") +
! ",c.relname,a.attname,a.atttypid," +
! "a.attnum,a.attnotnull,a.attlen,a.atttypmod,d.adsrc from pg_class c," +
! "pg_attribute a,pg_attrdef d where a.attrelid=c.oid and " +
! "c.relname like '"+tableNamePattern.toLowerCase()+"' and " +
! "a.attname like '"+columnNamePattern.toLowerCase()+"' and " +
! "a.attnum>0 and c.oid=d.adrelid and d.adnum=a.attnum " +
! "order by c.relname,a.attnum";
! r = connection.ExecSQL(query);
while(r.next()) {
byte[][] tuple = new byte[18][0];
// Fetch the description for the table (if any)
! String getDescriptionStatement =
! connection.haveMinimumServerVersion("7.2") ?
! "select col_description(" + r.getInt(1) + "," + r.getInt(5) + ")" :
! "select description from pg_description where objoid=" + r.getInt(1);
!
! java.sql.ResultSet dr = connection.ExecSQL(getDescriptionStatement);
!
if(((org.postgresql.ResultSet)dr).getTupleCount()==1) {
dr.next();
tuple[11] = dr.getBytes(1);
} else
! tuple[11] = null;
dr.close();
tuple[0] = "".getBytes(); // Catalog name
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
