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.
Dan.
