db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: getImportedKeys() and getTables() return no rows
Date Tue, 08 Jan 2008 17:55:36 GMT
blyon wrote:
> Well, first of all, the "ij" tool that derby provides for interactive
> command-line SQL is case-insensitive for table and column names; if you
> execute all lower-case sql (as I did when I created my tables) the table and
> column names in derby come out all upper case.
> Second, when I execute sql commands using Statement.executeQuery(String), I
> get the correct answer using all lower-case table and column names.

So both ij and executeQuery are executing SQL statements and here Derby 
is following the SQL standard rules for SQL identifiers. Unquoted 
identifies (e.g. mytable, MyTable) correspond to the upper-case version 
of the identifier (i.e. MYTABLE), whereas quoted identifies (e.g. 
"myTable") correspond to the value without quotes and no case 
modification (i.e. myTable). Quoted identifiers also allow characters 
not allowed in unquoted identifiers, such as space, at-sign, exclamation 
point etc., e.g. "my Table !".

> I can
> also get the derby column names from the result set meta-data after a query
> such as "select * from service order by name" when the derby table name is
> apparently really SERVICE and the column name really NAME.

Here Derby is returning the actual column name, which is the converted 
form of the SQL identifier used in create table.

> So I was programming against derby for quite some time before I found that
> the case of the table or column names mattered; hence my bafflement when my
> attempts to get database meta-data (as opposed to result set meta-data) did
> not work.

It is true that column names in ResultSet getter methods are 
case-insensitive, whereas table, column, etc. names in DatabaseMetaData 
must match the stored form the identifier. This is defined by the JDBC 
specification, no idea why it is different, but Derby is just following 
the spec.


View raw message