Re: [BUGS] BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name
Kris Jurka on 12/02/08 16:40, wrote: On Tue, 12 Feb 2008, Adam Hardy wrote: Because this rules out certain important features of the JPA framework such as native SQL queries, you may want to prioritize this issue. I will have to use Oracle or mySQL until PostgreSQL can rectify things. I wouldn't expect postgresql to change anytime soon. Could you explain in more detail what you can't use and why the JPA side couldn't be fixed to support postgresql? You could also consider always quoting all identifiers in both database creation scripts and queries. This guarantees that the database won't mess with the case of the object although it can be a pain to type if you writing a lot of queries by hand. Well, JPA is just a standard, so a 'JPA fix' would require each JPA provider (Toplink, JDO, OpenJPA, Hibernate etc) to rectify their postgresql-specific dialect / SQL syntax builder. This issue arises using PostgreSQL with a schema name when running native SQL queries. That means I write pure SQL in my JPA mapping files. It's a common practice. Using a schema name is also a common practice. However I will, at some stage in the future, code a test batch, which tests basic CRUD functionality and OR mapping across different combinations of JPA providers and DB vendors. So I will keep the bug reference handy and let you know how many JPA providers suffer this issue once I run the test. Best regards Adam ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name
Kris, thanks for the advice. Because this rules out certain important features of the JPA framework such as native SQL queries, you may want to prioritize this issue. I will have to use Oracle or mySQL until PostgreSQL can rectify things. Thanks and regards Adam Kris Jurka on 01/02/08 20:45, wrote: On Fri, 1 Feb 2008, Adam Hardy wrote: it seems to me from what you just said that PostgreSQL server and JDBC driver require the schema name to be lower case deliberately, and that any given name that is not all lower case is converted to lower case by the server or the driver. Am I correct? Anything that is not quoted is converted to lowercase. This is contrary to the SQL spec (it says to convert it to uppercase), but that's unlikely to change anytime soon. A JDBC app can portably detect this case by checking DatabaseMetaData.storesLowerCaseIdentifiers() and adjusting the other metadata calls appropriately. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name
On Tue, 12 Feb 2008, Adam Hardy wrote: Because this rules out certain important features of the JPA framework such as native SQL queries, you may want to prioritize this issue. I will have to use Oracle or mySQL until PostgreSQL can rectify things. I wouldn't expect postgresql to change anytime soon. Could you explain in more detail what you can't use and why the JPA side couldn't be fixed to support postgresql? You could also consider always quoting all identifiers in both database creation scripts and queries. This guarantees that the database won't mess with the case of the object although it can be a pain to type if you writing a lot of queries by hand. Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name
I think my message below didn't get through, according to error notifications
from [EMAIL PROTECTED]
Hopefully this will be successful. See copy below.
Kris Jurka on 23/01/08 08:51, wrote:
On Tue, 22 Jan 2008, Adam Hardy wrote:
The following bug has been logged online:
Bug reference: 3894
Description:JDBC DatabaseMetaData.getTables is inconsistently
case-sensitive with schema name
Details:
create schema DEV;
but then DatabaseMetaData.getTables(null, DEV, %, new String[]
{TABLE}) returned an empty resultset.
However giving it the schema name dev returns the full resultset.
This is by design. While SQL provides case folding and quoting rules
those are not the same as the JDBC driver's rules. In SQL an unquoted
identifier is case folded while quoted identifiers are not. To
provide the most straightforward mapping for the JDBC driver, we
require an exact match because quoting in Java String objects isn't
the same as SQL. It would be odd to say getTable(..., \Dev\,...)
to imply that you wanted a case sensitive match. Even odder would it
be to do a search for a schema with a quote in it's name using SQL
identifier rules in Java code.
Kris Jurka
Hi Kris,
it seems to me from what you just said that PostgreSQL server and JDBC
driver require the schema name to be lower case deliberately, and that
any given name that is not all lower case is converted to lower case by
the server or the driver. Am I correct?
Thanks
Adam
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [BUGS] BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name
On Fri, 1 Feb 2008, Adam Hardy wrote: it seems to me from what you just said that PostgreSQL server and JDBC driver require the schema name to be lower case deliberately, and that any given name that is not all lower case is converted to lower case by the server or the driver. Am I correct? Anything that is not quoted is converted to lowercase. This is contrary to the SQL spec (it says to convert it to uppercase), but that's unlikely to change anytime soon. A JDBC app can portably detect this case by checking DatabaseMetaData.storesLowerCaseIdentifiers() and adjusting the other metadata calls appropriately. Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name
The following bug has been logged online:
Bug reference: 3894
Logged by: Adam Hardy
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.5
Operating system: Linux
Description:JDBC DatabaseMetaData.getTables is inconsistently
case-sensitive with schema name
Details:
I created a schema with this cmd:
create schema DEV;
set search_path to DEV;
but then DatabaseMetaData.getTables(null, DEV, %, new String[]
{TABLE}) returned an empty resultset.
However giving it the schema name dev returns the full resultset.
It took me 4 hours to isolate this problem which was hidden in the code of
DbUnit.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name
On Tue, 22 Jan 2008, Adam Hardy wrote:
The following bug has been logged online:
Bug reference: 3894
Description:JDBC DatabaseMetaData.getTables is inconsistently
case-sensitive with schema name
Details:
create schema DEV;
but then DatabaseMetaData.getTables(null, DEV, %, new String[]
{TABLE}) returned an empty resultset.
However giving it the schema name dev returns the full resultset.
This is by design. While SQL provides case folding and quoting rules
those are not the same as the JDBC driver's rules. In SQL an unquoted
identifier is case folded while quoted identifiers are not. To provide
the most straightforward mapping for the JDBC driver, we require an exact
match because quoting in Java String objects isn't the same as SQL. It
would be odd to say getTable(..., \Dev\,...) to imply that you wanted
a case sensitive match. Even odder would it be to do a search for a
schema with a quote in it's name using SQL identifier rules in Java code.
Kris Jurka
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name
Kris Jurka on 23/01/08 08:51, wrote:
On Tue, 22 Jan 2008, Adam Hardy wrote:
The following bug has been logged online:
Bug reference: 3894
Description:JDBC DatabaseMetaData.getTables is inconsistently
case-sensitive with schema name
Details:
create schema DEV;
but then DatabaseMetaData.getTables(null, DEV, %, new String[]
{TABLE}) returned an empty resultset.
However giving it the schema name dev returns the full resultset.
This is by design. While SQL provides case folding and quoting rules
those are not the same as the JDBC driver's rules. In SQL an unquoted
identifier is case folded while quoted identifiers are not. To
provide the most straightforward mapping for the JDBC driver, we
require an exact match because quoting in Java String objects isn't
the same as SQL. It would be odd to say getTable(..., \Dev\,...)
to imply that you wanted a case sensitive match. Even odder would it
be to do a search for a schema with a quote in it's name using SQL
identifier rules in Java code.
Kris Jurka
Hi Kris,
it seems to me from what you just said that PostgreSQL server and JDBC
driver require the schema name to be lower case deliberately, and that
any given name that is not all lower case is converted to lower case by
the server or the driver. Am I correct?
Thanks
Adam
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
