Problems escaping tables and field names in Derby and Hsqldb

Question

I am having a problem with my ORMLite package. When I create the schema for the table, I thought it would be good practice to avoid all entity names. This will protect some Java class or field name from the SQL reserved word:

CREATE TABLE "footable" ("stuff" VARCHAR(255)) 

Now I am adding raw query support so that ORMLite can help users complete their own queries. However, I find that with Derby and Hsqldb, object names cannot be used without escaping. For example, the following query:

 SELECT * FROM footable 

generates the following errors:

 Derby: ERROR 42X05: Table/View 'FOOTABLE' does not exist. Hsqldb: Table not found in statement [select * from footable] 

It works fine if the selection table is also escaped as "footable" . Other databases supported by ORMLite work fine with or without screens: MySQL, Postgres, Microsoft SQL Server, H2, and Sqlite.

Are there any better ways to avoid reserved words in Derby and Hsqldb? Other ideas on how to do this in a portable way?

Thanks.

Answer 1

So Brian praises for leading me along the way, although his answer was not entirely correct.

It turns out that because I create the database as "footable" , then, according to Brian, it will create the case sensitively. However, when I made the choice on footable (without quotes), Derby and Hsqldb promote it to be in uppercase, so I actually do:

 SELECT * FROM FOOTABLE 

It's not about being case insensitive without quotes (which would work), but about promoting entity names to all capitals when there are no quotes, and then matching the case. I would say that there was a mistake ...

In any case, I changed my Derby and Hsqldb to use all entity names in ORMLite , and everything works. Ugly IMO but working.

Answer 2

You just need to make sure this case matches.

So if it is:

create table "Footable" ("Stuff" varchar (25))

Then it should be:

insert into "Footable" ("Stuff") values 'hi mom'

If the table / column name is in double quotation marks, the register is saved as is.

If the table / column name is not in double quotation marks, then Derby treats it case-insensitive.