db-derby-dev mailing list archives

[jira] Commented: (DERBY-4430) Make ij's SHOW and DESCRIBE commands more db agnostic

Date

Mon, 02 Nov 2009 14:10:59 GMT

[ https://issues.apache.org/jira/browse/DERBY-4430?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12772512#action_12772512
]
Knut Anders Hatlen commented on DERBY-4430:
-------------------------------------------
Example using ij to access a PostgreSQL database:
ij version 10.6
ij> connect 'jdbc:postgresql://localhost/test';
ij> create table my_table(x int);
0 rows inserted/updated/deleted
ij> show tables;
table_schem |table_name |remarks
------------------------------------------------------------------------
information_schema |sql_features |NULL
information_schema |sql_implementation_info |NULL
.
.
.
pg_catalog |pg_type |NULL
public |my_table |NULL
48 rows selected
The newly created table (my_table) shows up in the output from "SHOW TABLES", but it's stored
in lower case. Showing only the tables in the public schema is not possible, since ij converts
the identifier to upper case:
ij> show tables in public;
table_schem |table_name |remarks
------------------------------------------------------------------------
0 rows selected
And ij only supports unquoted identifiers:
ij> show tables in "public";
ERROR 42601: ERROR: syntax error at or near "in"
> Make ij's SHOW and DESCRIBE commands more db agnostic
> -----------------------------------------------------
>
> Key: DERBY-4430
> URL: https://issues.apache.org/jira/browse/DERBY-4430
> Project: Derby
> Issue Type: Improvement
> Components: Tools
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
> Priority: Minor
>
> ij's SHOW and DESCRIBE commands use DatabaseMetaData in order to be portable to other
databases than Derby, and in many cases they work fine with other databases. However, the
variants of the commands that take a table name or a schema name assume that unquoted identifiers
are converted to and stored in upper case internally. This is not true for all databases,
and since these commands don't accept quoted identifiers, there's currently no way to access
tables/schemas that are not all upper case.
> One possible fix is to make the identifier() method in ij.jj use the DatabaseMetaData
methods storesLowerCaseIdentifiers(), storesMixedCaseIdentifiers() and storesUpperCaseIdentifiers()
to decide whether it should convert the identifier to lower case, keep it unchanged, or convert
it to upper case. Currently, that method always converts the identifiers to upper case.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.