db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Øystein Grøvlen <Oystein.Grov...@Sun.COM>
Subject Re: Show commands are not orthogonal
Date Wed, 18 Oct 2006 23:34:17 GMT
Andrew McIntyre wrote:
> On 10/17/06, Oystein Grovlen - Sun Norway <Oystein.Grovlen@sun.com> 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

Mime
View raw message