Thread: JDBC Patch
This patch for the 7.0.2 JDBC interface addresses four issues I
encountered while getting my reporting tool up and running with the
driver. All changes are in the DatabaseMetaData class.
Problem: The getDatabaseProductVersion() method was returning "6.5.2"
Resolution: Changed it to return "7.0.2"
Problem: A call to getTables() with an unsupported table type (in the
String array) resulted in a malformed SQL statement and subsequent
parsing error
Resolution: Unsupported table types are now ignored without error
Problem: In a getTables() call, tables and views were both returned by
the "TABLE" table type, and the "VIEW" table type was unsupported
Resolution: Changed the "TABLE" type to return only physical tables and
added support for the "VIEW" table type (returning only views)
Problem: The getIdentifierQuoteString() method was returning null
Resolution: This method now returns a double-quote
Regards ...
- Christopher
diff -U 5 -r postgresql-7.0.2/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java
postgresql-7.0.2a/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java
--- postgresql-7.0.2/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java Mon Apr 17 14:07:48 2000
+++ postgresql-7.0.2a/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java Thu Aug 24 11:42:28 2000
@@ -177,11 +177,11 @@
* @return the database version
* @exception SQLException if a database access error occurs
*/
public String getDatabaseProductVersion() throws SQLException
{
- return ("6.5.2");
+ return ("7.0.2");
}
/**
* What is the name of this JDBC driver? If we don't know this
* we are doing something wrong!
@@ -361,11 +361,11 @@
* @return the quoting string
* @exception SQLException if a database access error occurs
*/
public String getIdentifierQuoteString() throws SQLException
{
- return null;
+ return "\"";
}
/**
* Get a comma separated list of all a database's SQL keywords that
* are NOT also SQL92 keywords.
@@ -1652,14 +1652,14 @@
// Now form the query
StringBuffer sql = new StringBuffer("select relname,oid 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])) {
+ if(notFirst)
+ sql.append(" or ");
sql.append(getTableTypes[j][1]);
notFirst=true;
}
}
@@ -1704,22 +1704,23 @@
// Each supported type consists of it's name, and the sql where
// clause to retrieve that value.
//
// IMPORTANT: the query must be enclosed in ( )
private static final String getTableTypes[][] = {
- {"TABLE", "(relkind='r' and relname !~ '^pg_' and relname !~ '^xinv')"},
+ {"TABLE", "(relkind='r' and relhasrules='f' and relname !~ '^pg_' and relname !~ '^xinv')"},
+ {"VIEW", "(relkind='r' and relhasrules='t' and relname !~ '^pg_' and relname !~ '^xinv')"},
{"INDEX", "(relkind='i' and relname !~ '^pg_' and relname !~ '^xinx')"},
{"LARGE OBJECT", "(relkind='r' and relname ~ '^xinv')"},
{"SEQUENCE", "(relkind='S' and relname !~ '^pg_')"},
{"SYSTEM TABLE", "(relkind='r' and relname ~ '^pg_')"},
{"SYSTEM INDEX", "(relkind='i' and relname ~ '^pg_')"}
};
// These are the default tables, used when NULL is passed to getTables
// The choice of these provide the same behaviour as psql's \d
private static final String defaultTableTypes[] = {
- "TABLE","INDEX","SEQUENCE"
+ "TABLE","VIEW","INDEX","SEQUENCE"
};
/**
* Get the schema names available in this database. The results
* are ordered by schema name.
diff -U 5 -r postgresql-7.0.2/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java
postgresql-7.0.2a/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java
--- postgresql-7.0.2/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java Mon Apr 17 14:07:50 2000
+++ postgresql-7.0.2a/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java Wed Aug 23 15:14:50 2000
@@ -177,11 +177,11 @@
* @return the database version
* @exception SQLException if a database access error occurs
*/
public String getDatabaseProductVersion() throws SQLException
{
- return ("6.5.2");
+ return ("7.0.2");
}
/**
* What is the name of this JDBC driver? If we don't know this
* we are doing something wrong!
@@ -361,11 +361,11 @@
* @return the quoting string
* @exception SQLException if a database access error occurs
*/
public String getIdentifierQuoteString() throws SQLException
{
- return null;
+ return "\"";
}
/**
* Get a comma separated list of all a database's SQL keywords that
* are NOT also SQL92 keywords.
@@ -1652,14 +1652,14 @@
// Now form the query
StringBuffer sql = new StringBuffer("select relname,oid 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])) {
+ if(notFirst)
+ sql.append(" or ");
sql.append(getTableTypes[j][1]);
notFirst=true;
}
}
@@ -1704,22 +1704,23 @@
// Each supported type consists of it's name, and the sql where
// clause to retrieve that value.
//
// IMPORTANT: the query must be enclosed in ( )
private static final String getTableTypes[][] = {
- {"TABLE", "(relkind='r' and relname !~ '^pg_' and relname !~ '^xinv')"},
+ {"TABLE", "(relkind='r' and relhasrules='f' and relname !~ '^pg_' and relname !~ '^xinv')"},
+ {"VIEW", "(relkind='r' and relhasrules='t' and relname !~ '^pg_' and relname !~ '^xinv')"},
{"INDEX", "(relkind='i' and relname !~ '^pg_' and relname !~ '^xinx')"},
{"LARGE OBJECT", "(relkind='r' and relname ~ '^xinv')"},
{"SEQUENCE", "(relkind='S' and relname !~ '^pg_')"},
{"SYSTEM TABLE", "(relkind='r' and relname ~ '^pg_')"},
{"SYSTEM INDEX", "(relkind='i' and relname ~ '^pg_')"}
};
// These are the default tables, used when NULL is passed to getTables
// The choice of these provide the same behaviour as psql's \d
private static final String defaultTableTypes[] = {
- "TABLE","INDEX","SEQUENCE"
+ "TABLE","VIEW","INDEX","SEQUENCE"
};
/**
* Get the schema names available in this database. The results
* are ordered by schema name.
Hmmm, I thought I had caught all the version strings. Thanks for spotting it. I'll commit that one and the others later today. Peter -----Original Message----- From: Christopher Cain [mailto:ccain@mhsoftware.com] Sent: Thursday, August 24, 2000 8:30 PM To: pgsql-interfaces@postgresql.org; pgsql-patches@postgresql.org Subject: [INTERFACES] JDBC Patch This patch for the 7.0.2 JDBC interface addresses four issues I encountered while getting my reporting tool up and running with the driver. All changes are in the DatabaseMetaData class. Problem: The getDatabaseProductVersion() method was returning "6.5.2" Resolution: Changed it to return "7.0.2" Problem: A call to getTables() with an unsupported table type (in the String array) resulted in a malformed SQL statement and subsequent parsing error Resolution: Unsupported table types are now ignored without error Problem: In a getTables() call, tables and views were both returned by the "TABLE" table type, and the "VIEW" table type was unsupported Resolution: Changed the "TABLE" type to return only physical tables and added support for the "VIEW" table type (returning only views) Problem: The getIdentifierQuoteString() method was returning null Resolution: This method now returns a double-quote Regards ... - Christopher
The problem is that there are so many different places it can go. I was thinking of moving the version stuff into DriverClass.java (which is built dynamically by make), but I'm not sure how adding malefile.global would affect the compilation process. However, JDBC needs: Full string: "7.0.2" Major version: 7 Minor version: 0 and those are just from memory. If I get chance today, I'll look into it. Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: petermount@it.maidstone.gov.uk WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council -----Original Message----- From: Peter Eisentraut [mailto:peter_e@gmx.net] Sent: Friday, August 25, 2000 9:28 AM To: Peter Mount Cc: 'Christopher Cain'; pgsql-interfaces@postgresql.org; pgsql-patches@postgresql.org Subject: RE: [INTERFACES] JDBC Patch Peter Mount writes: > Hmmm, I thought I had caught all the version strings. Thanks for spotting > it. Peter, you can avoid this sort of thing in the future. Makefile.global defines a variable `VERSION' that you should compile into the binaries. I'm not sure exactly how it would work with Java, but perhaps something like this: javac -Dversion='"$(VERSION)"' xyz.java or run sed before the compilation. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Using the latest stable postgres and JDBC drivers, I am having a problem which I have seen mentioned here previously; namely, that after inserting a row with a Timestamp column, and while attempting to retrieve it again (via JDBC), I get exceptions; I believe this is due to a bug in the format string in the jdbc client. Some have mentioned that a fix is available in CVS, but (believe or not) I am newbie enough to have not yet used CVS or know how to retrieve patches via it. Is there a released (downloadable, even RPM-downloadable) version of the JDBC driver with the Timestamp issue fixed? Thanks in advance, Bryan
I'm thinking it would justify a 7.0.3 release of postgres just to keep the traffic about the Timestamp off the mailing list. Bryan Field-Elliot wrote: > > Using the latest stable postgres and JDBC drivers, I am having a problem > which I have seen mentioned here previously; namely, that after inserting a > row with a Timestamp column, and while attempting to retrieve it again (via > JDBC), I get exceptions; I believe this is due to a bug in the format string > in the jdbc client. > > Some have mentioned that a fix is available in CVS, but (believe or not) I > am newbie enough to have not yet used CVS or know how to retrieve patches > via it. > > Is there a released (downloadable, even RPM-downloadable) version of the > JDBC driver with the Timestamp issue fixed? > The official jdbc homepage is http://www.retep.org.uk/postgres/ but it appears to be the 7.0 driver. Peter? > Thanks in advance, > > Bryan
Should be by the end of Monday. Peter -- Peter T Mount peter@retep.org.uk, 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: "Bryan Field-Elliot" <bryan@netmeme.org> To: <pgsql-interfaces@postgresql.org> Sent: Friday, August 25, 2000 7:25 PM Subject: [INTERFACES] JDBC and java.sql.Timestamp > Using the latest stable postgres and JDBC drivers, I am having a problem > which I have seen mentioned here previously; namely, that after inserting a > row with a Timestamp column, and while attempting to retrieve it again (via > JDBC), I get exceptions; I believe this is due to a bug in the format string > in the jdbc client. > > Some have mentioned that a fix is available in CVS, but (believe or not) I > am newbie enough to have not yet used CVS or know how to retrieve patches > via it. > > Is there a released (downloadable, even RPM-downloadable) version of the > JDBC driver with the Timestamp issue fixed? > > Thanks in advance, > > Bryan >
Also, because I've been busy the last few months, I've not done anything since 7.0 was released. I'm spending Monday catching up with the major outstanding stuff with JDBC, and will get a compiled copy online. Peter -- Peter T Mount peter@retep.org.uk, 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: "Joseph Shraibman" <jks@selectacast.net> To: <pgsql-interfaces@postgresql.org> Sent: Friday, August 25, 2000 8:29 PM Subject: Re: [INTERFACES] JDBC and java.sql.Timestamp > I'm thinking it would justify a 7.0.3 release of postgres just to keep > the traffic about the Timestamp off the mailing list. > > Bryan Field-Elliot wrote: > > > > Using the latest stable postgres and JDBC drivers, I am having a problem > > which I have seen mentioned here previously; namely, that after inserting a > > row with a Timestamp column, and while attempting to retrieve it again (via > > JDBC), I get exceptions; I believe this is due to a bug in the format string > > in the jdbc client. > > > > Some have mentioned that a fix is available in CVS, but (believe or not) I > > am newbie enough to have not yet used CVS or know how to retrieve patches > > via it. > > > > Is there a released (downloadable, even RPM-downloadable) version of the > > JDBC driver with the Timestamp issue fixed? > > > The official jdbc homepage is http://www.retep.org.uk/postgres/ but it > appears to be the 7.0 driver. Peter? > > > > Thanks in advance, > > > > Bryan
Applied. Thanks.
> This patch for the 7.0.2 JDBC interface addresses four issues I
> encountered while getting my reporting tool up and running with the
> driver. All changes are in the DatabaseMetaData class.
>
> Problem: The getDatabaseProductVersion() method was returning "6.5.2"
> Resolution: Changed it to return "7.0.2"
>
> Problem: A call to getTables() with an unsupported table type (in the
> String array) resulted in a malformed SQL statement and subsequent
> parsing error
> Resolution: Unsupported table types are now ignored without error
>
> Problem: In a getTables() call, tables and views were both returned by
> the "TABLE" table type, and the "VIEW" table type was unsupported
> Resolution: Changed the "TABLE" type to return only physical tables and
> added support for the "VIEW" table type (returning only views)
>
> Problem: The getIdentifierQuoteString() method was returning null
> Resolution: This method now returns a double-quote
>
> Regards ...
>
> - Christopher
> diff -U 5 -r postgresql-7.0.2/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java
postgresql-7.0.2a/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java
> --- postgresql-7.0.2/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java Mon Apr 17 14:07:48 2000
> +++ postgresql-7.0.2a/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java Thu Aug 24 11:42:28 2000
> @@ -177,11 +177,11 @@
> * @return the database version
> * @exception SQLException if a database access error occurs
> */
> public String getDatabaseProductVersion() throws SQLException
> {
> - return ("6.5.2");
> + return ("7.0.2");
> }
>
> /**
> * What is the name of this JDBC driver? If we don't know this
> * we are doing something wrong!
> @@ -361,11 +361,11 @@
> * @return the quoting string
> * @exception SQLException if a database access error occurs
> */
> public String getIdentifierQuoteString() throws SQLException
> {
> - return null;
> + return "\"";
> }
>
> /**
> * Get a comma separated list of all a database's SQL keywords that
> * are NOT also SQL92 keywords.
> @@ -1652,14 +1652,14 @@
>
> // Now form the query
> StringBuffer sql = new StringBuffer("select relname,oid 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])) {
> + if(notFirst)
> + sql.append(" or ");
> sql.append(getTableTypes[j][1]);
> notFirst=true;
> }
> }
>
> @@ -1704,22 +1704,23 @@
> // Each supported type consists of it's name, and the sql where
> // clause to retrieve that value.
> //
> // IMPORTANT: the query must be enclosed in ( )
> private static final String getTableTypes[][] = {
> - {"TABLE", "(relkind='r' and relname !~ '^pg_' and relname !~ '^xinv')"},
> + {"TABLE", "(relkind='r' and relhasrules='f' and relname !~ '^pg_' and relname !~ '^xinv')"},
> + {"VIEW", "(relkind='r' and relhasrules='t' and relname !~ '^pg_' and relname !~ '^xinv')"},
> {"INDEX", "(relkind='i' and relname !~ '^pg_' and relname !~ '^xinx')"},
> {"LARGE OBJECT", "(relkind='r' and relname ~ '^xinv')"},
> {"SEQUENCE", "(relkind='S' and relname !~ '^pg_')"},
> {"SYSTEM TABLE", "(relkind='r' and relname ~ '^pg_')"},
> {"SYSTEM INDEX", "(relkind='i' and relname ~ '^pg_')"}
> };
>
> // These are the default tables, used when NULL is passed to getTables
> // The choice of these provide the same behaviour as psql's \d
> private static final String defaultTableTypes[] = {
> - "TABLE","INDEX","SEQUENCE"
> + "TABLE","VIEW","INDEX","SEQUENCE"
> };
>
> /**
> * Get the schema names available in this database. The results
> * are ordered by schema name.
> diff -U 5 -r postgresql-7.0.2/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java
postgresql-7.0.2a/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java
> --- postgresql-7.0.2/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java Mon Apr 17 14:07:50 2000
> +++ postgresql-7.0.2a/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java Wed Aug 23 15:14:50 2000
> @@ -177,11 +177,11 @@
> * @return the database version
> * @exception SQLException if a database access error occurs
> */
> public String getDatabaseProductVersion() throws SQLException
> {
> - return ("6.5.2");
> + return ("7.0.2");
> }
>
> /**
> * What is the name of this JDBC driver? If we don't know this
> * we are doing something wrong!
> @@ -361,11 +361,11 @@
> * @return the quoting string
> * @exception SQLException if a database access error occurs
> */
> public String getIdentifierQuoteString() throws SQLException
> {
> - return null;
> + return "\"";
> }
>
> /**
> * Get a comma separated list of all a database's SQL keywords that
> * are NOT also SQL92 keywords.
> @@ -1652,14 +1652,14 @@
>
> // Now form the query
> StringBuffer sql = new StringBuffer("select relname,oid 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])) {
> + if(notFirst)
> + sql.append(" or ");
> sql.append(getTableTypes[j][1]);
> notFirst=true;
> }
> }
>
> @@ -1704,22 +1704,23 @@
> // Each supported type consists of it's name, and the sql where
> // clause to retrieve that value.
> //
> // IMPORTANT: the query must be enclosed in ( )
> private static final String getTableTypes[][] = {
> - {"TABLE", "(relkind='r' and relname !~ '^pg_' and relname !~ '^xinv')"},
> + {"TABLE", "(relkind='r' and relhasrules='f' and relname !~ '^pg_' and relname !~ '^xinv')"},
> + {"VIEW", "(relkind='r' and relhasrules='t' and relname !~ '^pg_' and relname !~ '^xinv')"},
> {"INDEX", "(relkind='i' and relname !~ '^pg_' and relname !~ '^xinx')"},
> {"LARGE OBJECT", "(relkind='r' and relname ~ '^xinv')"},
> {"SEQUENCE", "(relkind='S' and relname !~ '^pg_')"},
> {"SYSTEM TABLE", "(relkind='r' and relname ~ '^pg_')"},
> {"SYSTEM INDEX", "(relkind='i' and relname ~ '^pg_')"}
> };
>
> // These are the default tables, used when NULL is passed to getTables
> // The choice of these provide the same behaviour as psql's \d
> private static final String defaultTableTypes[] = {
> - "TABLE","INDEX","SEQUENCE"
> + "TABLE","VIEW","INDEX","SEQUENCE"
> };
>
> /**
> * Get the schema names available in this database. The results
> * are ordered by schema name.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026