Thread: Connection.setCatalog method
Everyone, I am working on yet another database administration application. I have run into some issues because the JDBC implementation of Connection.setCatalog() (org.postgresq.jdbc1.AbstractJdbc1Connection.java) does nothing when setCatalog() is called. My understanding is that doing nothing is allowed by the JDBC specification, but this is somewhat confusing as getCatalog() returns a value and DatabaseMetaData.getCatalogs() returns a list of values. While searching though this list's archives, I found a thread from July of 2001 that deals with this subject. Toward the end of the thread, it appears as though it was decided that the setCatalog() functionallity should be implemented and that a patch was to be accepted to make this happen: http://archives.postgresql.org/pgsql-jdbc/2001-07/msg00150.php What has become of this? Can someone shed some light on why Connection.getCatalog and DatabaseMetaData.getCatalogs() work, but Connection.setCatalog does not? -- Philip A. Chapman Application Development: Java, Visual Basic (MCP), PostgreSQL, MySQL, MSSQL Linux, Windows 9x, Windows NT, Windows 2000, Windows XP
Attachment
Yes, setCatalog() should switch the connection to another database (it can only be ignored if the driver does not support catalogs, which is not the case). I have no idea why that patch was not applied. Could not find anything after the thread you've pointed to. Regards, Fernando Philip A. Chapman wrote: > Everyone, > > I am working on yet another database administration application. I have > run into some issues because the JDBC implementation of > Connection.setCatalog() > (org.postgresq.jdbc1.AbstractJdbc1Connection.java) does nothing when > setCatalog() is called. My understanding is that doing nothing is > allowed by the JDBC specification, but this is somewhat confusing as > getCatalog() returns a value and DatabaseMetaData.getCatalogs() returns > a list of values. > > While searching though this list's archives, I found a thread from July > of 2001 that deals with this subject. Toward the end of the thread, it > appears as though it was decided that the setCatalog() functionallity > should be implemented and that a patch was to be accepted to make this > happen: > > http://archives.postgresql.org/pgsql-jdbc/2001-07/msg00150.php > > What has become of this? Can someone shed some light on why > Connection.getCatalog and DatabaseMetaData.getCatalogs() work, but > Connection.setCatalog does not? -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
That patch wasn't applied because I vetoed it. I am unhappy with the logic as it appeared in that patch, but due to the limitations of the server I don't know of anyway to implement setCatalog(). There are two problems I have with the patch as it was originally submitted: 1) Transaction problems. Lets say I am in the middle of a transaction when I call setCatalog(), since the implementation simply closes the current connection and opens a new one, my in process transaction is aborted. Which is certainly against the spec. In general, I don't like the patch because is under the covers replaces the existing connection with a new one that can have a whole slew of side effects that are not addressed (what happens to existing statement objects? existing result sets? existing database meta data objects?). 2) The patch makes the invalid assumption that just because the current username/password lets me connect to database A, it will also let me connect to database B. Thus calling setCatalog() if you have security setup will leave your connection hosed. Finally while it may seem on the surface that postgresql databases are the same thing as jdbc catalogs, there are many differences. Enough that in my opinion they should not be considered the same thing. For example: DatabaseMetaData.isCatalogAtStart() says: * Retrieves whether a catalog appears at the start of a fully qualified * table name. If not, the catalog appears at the end. Thus the jdbc concept of catalog also is a namespace concept (ala database.schema.table which postgres doesn't support). DatabaseMetaData.get*() methods that take a catalog parameter: All of these methods assume that you can get information from any catalog/database. But in postgres that isn't true. You can only get meta information for the database you are currently connected to. And because of the issue #2 above there really isn't any way to get information about other databases objects. Now I am certainly not against enhanceing postgres to support the jdbc view of catalogs, but I think it is much more work and likely will need to be done very differently than the below mentioned patch does things. thanks, --Barry Fernando Nasser wrote: > Yes, setCatalog() should switch the connection to another database (it > can only be ignored if the driver does not support catalogs, which is > not the case). > > I have no idea why that patch was not applied. Could not find anything > after the thread you've pointed to. > > Regards, > Fernando > > > Philip A. Chapman wrote: > >> Everyone, >> >> I am working on yet another database administration application. I have >> run into some issues because the JDBC implementation of >> Connection.setCatalog() >> (org.postgresq.jdbc1.AbstractJdbc1Connection.java) does nothing when >> setCatalog() is called. My understanding is that doing nothing is >> allowed by the JDBC specification, but this is somewhat confusing as >> getCatalog() returns a value and DatabaseMetaData.getCatalogs() returns >> a list of values. >> >> While searching though this list's archives, I found a thread from July >> of 2001 that deals with this subject. Toward the end of the thread, it >> appears as though it was decided that the setCatalog() functionallity >> should be implemented and that a patch was to be accepted to make this >> happen: >> >> http://archives.postgresql.org/pgsql-jdbc/2001-07/msg00150.php >> >> What has become of this? Can someone shed some light on why >> Connection.getCatalog and DatabaseMetaData.getCatalogs() work, but >> Connection.setCatalog does not? > > >
Barry Lind wrote: > That patch wasn't applied because I vetoed it. I am unhappy with the > logic as it appeared in that patch, but due to the limitations of the > server I don't know of anyway to implement setCatalog(). > We couldn't find the folow-up to the message where the patch seemed to have been slated for check in. Thanks for the clarification. Regards, Fernando > There are two problems I have with the patch as it was originally > submitted: > > 1) Transaction problems. Lets say I am in the middle of a transaction > when I call setCatalog(), since the implementation simply closes the > current connection and opens a new one, my in process transaction is > aborted. Which is certainly against the spec. In general, I don't like > the patch because is under the covers replaces the existing connection > with a new one that can have a whole slew of side effects that are not > addressed (what happens to existing statement objects? existing result > sets? existing database meta data objects?). > > 2) The patch makes the invalid assumption that just because the current > username/password lets me connect to database A, it will also let me > connect to database B. Thus calling setCatalog() if you have security > setup will leave your connection hosed. > > Finally while it may seem on the surface that postgresql databases are > the same thing as jdbc catalogs, there are many differences. Enough > that in my opinion they should not be considered the same thing. For > example: > > DatabaseMetaData.isCatalogAtStart() says: > * Retrieves whether a catalog appears at the start of a fully qualified > * table name. If not, the catalog appears at the end. > Thus the jdbc concept of catalog also is a namespace concept (ala > database.schema.table which postgres doesn't support). > > DatabaseMetaData.get*() methods that take a catalog parameter: > All of these methods assume that you can get information from any > catalog/database. But in postgres that isn't true. You can only get > meta information for the database you are currently connected to. And > because of the issue #2 above there really isn't any way to get > information about other databases objects. > > > Now I am certainly not against enhanceing postgres to support the jdbc > view of catalogs, but I think it is much more work and likely will need > to be done very differently than the below mentioned patch does things. > > thanks, > --Barry > > > > Fernando Nasser wrote: > >> Yes, setCatalog() should switch the connection to another database (it >> can only be ignored if the driver does not support catalogs, which is >> not the case). >> >> I have no idea why that patch was not applied. Could not find >> anything after the thread you've pointed to. >> >> Regards, >> Fernando >> >> >> Philip A. Chapman wrote: >> >>> Everyone, >>> >>> I am working on yet another database administration application. I have >>> run into some issues because the JDBC implementation of >>> Connection.setCatalog() >>> (org.postgresq.jdbc1.AbstractJdbc1Connection.java) does nothing when >>> setCatalog() is called. My understanding is that doing nothing is >>> allowed by the JDBC specification, but this is somewhat confusing as >>> getCatalog() returns a value and DatabaseMetaData.getCatalogs() returns >>> a list of values. >>> >>> While searching though this list's archives, I found a thread from July >>> of 2001 that deals with this subject. Toward the end of the thread, it >>> appears as though it was decided that the setCatalog() functionallity >>> should be implemented and that a patch was to be accepted to make this >>> happen: >>> >>> http://archives.postgresql.org/pgsql-jdbc/2001-07/msg00150.php >>> >>> What has become of this? Can someone shed some light on why >>> Connection.getCatalog and DatabaseMetaData.getCatalogs() work, but >>> Connection.setCatalog does not? >> >> >> >> > > > -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9