Andrew McIntyre wrote:
On 10/17/06, Oystein Grovlen - Sun Norway <[EMAIL PROTECTED]> wrote:
I find it strange that while "SHOW INDEXES", "SHOW PROCEDURES", and
"SHOW SYNONYMS" by default list all corresponding objects in the
database, "SHOW TABLES" and "SHOW VIEWS" by default only list the
corresponding objects in the current schema. Would it not be better
if all commands behaved the same way? Are there reasons for not
changing this?
Having looked at the ij code, but not having gone to look at the
corresponding metadata code or the JDBC spec, I can say that this is
the current behavior when calling DatabaseMetadata.getTables() with
null as the argument for the schema parameter and "TABLE" as the table
type. I don't see a reason why it couldn't be fixed, although the fix
may be to the metadata method, and not necessarily to ij. I suspect
that if the schema is null, Derby uses the current connection schema
to restrict the set of tables that is returned.
Seems like I was fooled by the documentation here. When I actually try
it out, it works the way I would expect it to work, except that it does
not display system tables:
ij> show tables;
TABLE_SCHEM |TABLE_NAME |REMARKS
------------------------------------------------------------------------
APP |T |
1 row selected
ij> create schema user1;
0 rows inserted/updated/deleted
ij> set schema user1;
0 rows inserted/updated/deleted
ij> create table u (j integer, i integer);
0 rows inserted/updated/deleted
ij> show tables;
TABLE_SCHEM |TABLE_NAME |REMARKS
------------------------------------------------------------------------
APP |T |
USER1 |U |
2 rows selected
I will file a bug report for the documentation tomorrow. The
documentation should proabably also say that system tables are only
displayed if a schema is specified. (I guess this is what fooled the
documentor into thinking that only tables for the current schema is
displayed. This is not a feature of the DatabaseMetaData
implementation, but implemented by ij).
--
Øystein
