2

Is it possible to create table and it's name in lower case using JavaDB/Derby? To check if table exists I'm using:

ResultSet rs = dbmd.getTables(null, "APP", "user_properties", null);
if (!rs.next()) {/*do something*/};

But table name 'user_properties' must mach the case in catalog.

Ernestas Gruodis
  • 8,567
  • 14
  • 55
  • 117
  • cant you use create table table_name ....? – SpringLearner Oct 03 '13 at 10:19
  • If I do `st.execute("CREATE TABLE user_properties");` in Schema 'APP' it is recorded as 'USER_PROPERTIES' - in upper case.. I want it to be in lower case. That's not a very big problem, just asking if it is possible :) – Ernestas Gruodis Oct 03 '13 at 10:24

1 Answers1

12

Derby follows the ANSI standard which requires unquoted identifiers to be folded to uppercase. So, the following statement:

create table user_properties
(
   id integer not null
);

will create a table with then name USER_PROPERTIES and a column named ID.

You can force Derby to store lower case (or actually mixed cased) names by using quoted identifiers:

create table "user_properties"
(
   "id" integer not null
);

will create a table with then name user_properties and a column named id.

When you use quoted identifiers (aka "delimited identifiers") they become case-sensitive, so the second table must always be referenced using double quotes.

The statement select * from user_properties will not work if the table was created with the second statement. You have to always quote it: select * from "user_properties".

Using quoted identifiers is usually causing much more trouble than it's worth. If you never ever quote your identifiers you can safely use the upper case name in the call to getTables()

  • Yes, that worked for me. MySQL syntax is a bit easier comparing to this situation, but it cost too much to use commercially :) I think I will stay with upper cases and without quotes. – Ernestas Gruodis Oct 03 '13 at 16:05
  • @ErnestasGruodis: actually if you run MySQL in ANSI mode it behaves pretty much the same. –  Oct 03 '13 at 16:19