Обсуждение: behavior of getTables() wrt capitalization of type
Hello, There seems to be a bug in DatabaseMetaData.getTables() with respect to the capitalization of the values in the 'types' array. I apologize for not having the latest source, but I looked at some slightly older source and found that there is an static final array of types (VIEW, TABLE, ...) and the corresponding sql clause to query it. When the type values are capitalized all works fine, but if they are not then the exception below occurs. It seems as though each value in the types array should be capitalized when doing the comparison with the values in the static final array. I tried figuring it out dynamically through the various supports*() and stores*() methods on DMD but only supportsMixedCaseIdentifiers() returned true and from my testing this does not seem to be the case. I did download and test with the latest developer snapshot but the same bug occurs. Results from running the enclosed test case: Attempting type [TABLE] table: zxtesting SUCCESS with type [TABLE] Attempting type [table] ERROR: parser: parse error at or near ")" FAILURE with type [table] thanks, brian
Вложения
I had a look at the documentation, and it is suggested that the table types s/b uppercase. Does anyone know what the correct answer is here? Currently the driver only supports uppercase table types Dave -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of brian zimmer Sent: Wednesday, January 09, 2002 1:54 AM To: pgsql-jdbc@postgresql.org Subject: [JDBC] behavior of getTables() wrt capitalization of type Hello, There seems to be a bug in DatabaseMetaData.getTables() with respect to the capitalization of the values in the 'types' array. I apologize for not having the latest source, but I looked at some slightly older source and found that there is an static final array of types (VIEW, TABLE, ...) and the corresponding sql clause to query it. When the type values are capitalized all works fine, but if they are not then the exception below occurs. It seems as though each value in the types array should be capitalized when doing the comparison with the values in the static final array. I tried figuring it out dynamically through the various supports*() and stores*() methods on DMD but only supportsMixedCaseIdentifiers() returned true and from my testing this does not seem to be the case. I did download and test with the latest developer snapshot but the same bug occurs. Results from running the enclosed test case: Attempting type [TABLE] table: zxtesting SUCCESS with type [TABLE] Attempting type [table] ERROR: parser: parse error at or near ")" FAILURE with type [table] thanks, brian
Dave, Where did you read about being in capitals? The JDBC spec talks only about typical values, but doesn't mention anything about capitalization as far as I can tell. If capitals it is, I have no problem with that but the sql parse exception should still be fixed I would think. thanks, brian > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Dave Cramer > Sent: Wednesday, January 09, 2002 8:14 AM > To: 'brian zimmer'; pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] behavior of getTables() wrt capitalization of type > > > I had a look at the documentation, and it is suggested that > the table types s/b uppercase. Does anyone know what the > correct answer is here? > > Currently the driver only supports uppercase table types > > Dave > > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On > Behalf Of brian > zimmer > Sent: Wednesday, January 09, 2002 1:54 AM > To: pgsql-jdbc@postgresql.org > Subject: [JDBC] behavior of getTables() wrt capitalization of type > > > Hello, > > There seems to be a bug in DatabaseMetaData.getTables() with > respect to the capitalization of the values in the 'types' > array. I apologize for not having the latest source, but I > looked at some slightly older source and found that there is > an static final array of types (VIEW, TABLE, > ...) and the corresponding sql clause to query it. When the > type values are capitalized all works fine, but if they are > not then the exception below occurs. It seems as though each > value in the types array should be capitalized when doing the > comparison with the values in the static final array. > > I tried figuring it out dynamically through the various > supports*() and > stores*() methods on DMD but only > supportsMixedCaseIdentifiers() returned true and from my > testing this does not seem to be the case. I did download > and test with the latest developer snapshot but the same bug occurs. > > Results from running the enclosed test case: > > Attempting type [TABLE] > table: zxtesting > SUCCESS with type [TABLE] > > Attempting type [table] > ERROR: parser: parse error at or near ")" > FAILURE with type [table] > > thanks, > > brian > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > http://www.postgresql.org/users-lounge/docs/faq.html
Brian, If you look at the description of the getTables call in the jdbc api documentation they refer to typical values and they are in upper case Each table description has the following columns: TABLE_CAT String => table catalog (may be null) TABLE_SCHEM String => table schema (may be null) TABLE_NAME String => table name TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM". REMARKS String => explanatory comment on the table Dave -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of brian zimmer Sent: Wednesday, January 09, 2002 10:12 AM To: Dave@micro-automation.net; pgsql-jdbc@postgresql.org Subject: Re: [JDBC] behavior of getTables() wrt capitalization of type Dave, Where did you read about being in capitals? The JDBC spec talks only about typical values, but doesn't mention anything about capitalization as far as I can tell. If capitals it is, I have no problem with that but the sql parse exception should still be fixed I would think. thanks, brian > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Dave Cramer > Sent: Wednesday, January 09, 2002 8:14 AM > To: 'brian zimmer'; pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] behavior of getTables() wrt capitalization of type > > > I had a look at the documentation, and it is suggested that > the table types s/b uppercase. Does anyone know what the > correct answer is here? > > Currently the driver only supports uppercase table types > > Dave > > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On > Behalf Of brian > zimmer > Sent: Wednesday, January 09, 2002 1:54 AM > To: pgsql-jdbc@postgresql.org > Subject: [JDBC] behavior of getTables() wrt capitalization of type > > > Hello, > > There seems to be a bug in DatabaseMetaData.getTables() with > respect to the capitalization of the values in the 'types' > array. I apologize for not having the latest source, but I > looked at some slightly older source and found that there is > an static final array of types (VIEW, TABLE, > ...) and the corresponding sql clause to query it. When the > type values are capitalized all works fine, but if they are > not then the exception below occurs. It seems as though each > value in the types array should be capitalized when doing the > comparison with the values in the static final array. > > I tried figuring it out dynamically through the various > supports*() and > stores*() methods on DMD but only > supportsMixedCaseIdentifiers() returned true and from my > testing this does not seem to be the case. I did download > and test with the latest developer snapshot but the same bug occurs. > > Results from running the enclosed test case: > > Attempting type [TABLE] > table: zxtesting > SUCCESS with type [TABLE] > > Attempting type [table] > ERROR: parser: parse error at or near ")" > FAILURE with type [table] > > thanks, > > brian > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > http://www.postgresql.org/users-lounge/docs/faq.html ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
I am migrating a database and applications from Oracle to PostgreSQL
7.1.
We have a J2EE applications consisting of JSP, servlets and EJB that is
executed in BEA WebLogic. We have successfully configured a WebLogic
connection pool to access Postgres using the JDBC driver
(jdbc7.1-1.3.jar) and the application runs perfectly.
However, I have a concern based on Postgres behavior regarding
transactions. I would be deeply grateful if you could tell me if the
following is correct:
- Postgres always issue a commit after each user statement, unless you
submit a "begin" statement to demarcate the beginning of a transaction.
There is no way to disable the "auto-commit" behavior; we need to submit
"begin" in every transaction that embodies more than one statement.
Further, considering that Postgres doesn't have a configuration
parameter to disable "auto-commit", what would be the effect of the
following piece of Java code?
...
Connection con;
con.setAutoCommit(false);
...
After the "commit" that follows this piece of code, does the
"auto-commit" remain disabled for other transactions using the same
connection? What about transactions created by other connections?
My concern is that WebLogic, like any J2EE app server, has a transaction
service that interfaces with resource managers (i.e. PostgreSQL). Thus,
if one uses Oracle or Cloudscape or DB2 (not to mention others that I'm
not familiar), your code typically does not have
"con.setAutoCommit(false)"; it's up to the app server transaction
service to control transactions according to some hints you give it - if
you're familiar with J2EE, these hints would be the transaction
attributes in the DDs for BMT components.
It seems that I'll need to insert this "con.setAutoCommit(false)" in the
beginning of every transaction; and it seems that this JDBC statement
translates to a Postgres "begin" statement. Could you please clarify
this point?
Paulo Merson
Summa Technologies - www.summa-tech.com
The jdbc driver does the "right" thing according to the jdbc spec. Calling setAutoCommit(false) on the connection will cause the driver to issue an implicit 'begin' after every call to rollback() or commit(). Therefore all statements executed will end up running inside a single transaction which is ended by a commit() or rollback() call (which then starts the next transaction). thanks, --Barry Paulo Merson wrote: > I am migrating a database and applications from Oracle to PostgreSQL > 7.1. > We have a J2EE applications consisting of JSP, servlets and EJB that is > executed in BEA WebLogic. We have successfully configured a WebLogic > connection pool to access Postgres using the JDBC driver > (jdbc7.1-1.3.jar) and the application runs perfectly. > > However, I have a concern based on Postgres behavior regarding > transactions. I would be deeply grateful if you could tell me if the > following is correct: > > - Postgres always issue a commit after each user statement, unless you > submit a "begin" statement to demarcate the beginning of a transaction. > There is no way to disable the "auto-commit" behavior; we need to submit > "begin" in every transaction that embodies more than one statement. > > Further, considering that Postgres doesn't have a configuration > parameter to disable "auto-commit", what would be the effect of the > following piece of Java code? > ... > Connection con; > con.setAutoCommit(false); > ... > > After the "commit" that follows this piece of code, does the > "auto-commit" remain disabled for other transactions using the same > connection? What about transactions created by other connections? > > My concern is that WebLogic, like any J2EE app server, has a transaction > service that interfaces with resource managers (i.e. PostgreSQL). Thus, > if one uses Oracle or Cloudscape or DB2 (not to mention others that I'm > not familiar), your code typically does not have > "con.setAutoCommit(false)"; it's up to the app server transaction > service to control transactions according to some hints you give it - if > you're familiar with J2EE, these hints would be the transaction > attributes in the DDs for BMT components. > It seems that I'll need to insert this "con.setAutoCommit(false)" in the > beginning of every transaction; and it seems that this JDBC statement > translates to a Postgres "begin" statement. Could you please clarify > this point? > > Paulo Merson > Summa Technologies - www.summa-tech.com > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >