7

How to turn off forced upper-case mode for table and column names in HSQL?

<artifactId>hsqldb</artifactId>
<version>2.3.1</version>

OS: Windows 7 x64

Arthur
  • 1,156
  • 3
  • 20
  • 49
  • 1
    Storing names in uppercase is required by the SQL standard. I don't think you can turn it off in HSQLDB –  Mar 24 '16 at 10:16
  • you can try synonyms, example and ddl below CREATE SYNONYM REG FOR OTHER_SCHEMA.REGISTRATION_DETAIL_TABLE ; SELECT R_ID, R_DATE FROM REG WHERE R_DATA > CURRENT_DATE - 3 DAY – Zeeshan Arif Dec 06 '16 at 12:00

2 Answers2

12

The rules around this are explained in the HSQLDB documentation:

When a database object is created with one of the CREATE statements or renamed with the ALTER statement, if the name is enclosed in double quotes, the exact name is used as the case-normal form. But if it is not enclosed in double quotes, the name is converted to uppercase and this uppercase version is stored in the database as the case-normal form.

Case sensitivity rules for identifiers can be described simply as follows:

  • all parts of SQL statements are converted to upper case before processing, except identifiers in double quotes and strings in single quotes
  • identifiers, both unquoted and double quoted, are then treated as case-sensitive
  • most database engines follow the same rule, except, in some respects, MySQL and MS SQLServer.

AFAIK this behaviour can't be turned off. (It's worth noting that standard SQL is case-insensitive when quoted identifiers aren't used.) But as mentioned above, a lower case identifier can be specified by enclosing in quotes, e.g:

CREATE TABLE "lowercasetablename" ("lowercasecolname" INT);
SELECT "lowercasecolname" FROM "lowercasetablename";
Steve Chambers
  • 37,270
  • 24
  • 156
  • 208
0

I am not sure, i understand the problem correctly but just trying to put some effort.

SET DATABASE COLLATION SQL_TEXT_UCC

May be you can refer http://hsqldb.org/doc/guide/dbproperties-chapt.html

dildeepak
  • 1,349
  • 2
  • 16
  • 34
  • From the documentation, it looks to be the right thing to use. Unfortunately, even adding this statement to my DB creation script, comparisons looks to be still performed with case sensitive on. – Pierre-Antoine Dec 05 '16 at 11:41
  • Note to you: serial voting will be reversed at the end of the day. Don't be surprised when you loose 200 rep tomorrow. Cheating the system doesn't work that way. – Adriaan May 03 '17 at 15:33
  • 3
    DO NOT USE THIS, from spec it not about the **names** but the data inside **VARCHAR** fields. – Peter Rader Nov 30 '17 at 09:25