Thread: behavior of getTables() wrt capitalization of type

behavior of getTables() wrt capitalization of type

From
"brian zimmer"
Date:
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

Attachment

Re: behavior of getTables() wrt capitalization of type

From
"Dave Cramer"
Date:
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


Re: behavior of getTables() wrt capitalization of type

From
"brian zimmer"
Date:
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


Re: behavior of getTables() wrt capitalization of type

From
"Dave Cramer"
Date:
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



PostgreSQL JDBC and WebLogic

From
"Paulo Merson"
Date:
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



Re: PostgreSQL JDBC and WebLogic

From
Barry Lind
Date:
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
>
>