Case sensitive column names

[vsaji]

Hi,

I am currently evaluating H2 DB to use one of my project.

While testing one of the requirement, I found that the column names
read from ResultSetMeta is uppercase whereas the table columns are
created with case sensitive.

From "SQL Grammer" section, I understood that "Quoted Name" would help
to achive my requirement, but when I fire the select statement its
throwing Column not found exception


Ex 1:
CREATE TABLE TestTable (EmpId INT,EmpName VARCHAR(50));
select * from TestTable
ResultSetMeatData.getColumnName(1)= > "EMPID". // Im expecting
"EmpId" here.

Ex2:
CREATE TABLE TestTable ("EmpId" INT,"EmpName" VARCHAR(50));
select * from TestTable EmpId=2
Column EMPID not found

Please help.

Thanks in advance
Saji

[Thotheolh]

I did the following to confirm if vsaji's claims are true using H2 web
console targeting for Ex2.

1) copied create table statement "CREATE TABLE TestTable ("EmpId"
INT,"EmpName" VARCHAR(50)); " and run directly in web console...
things are fine.
2) Tried to do select "select * from TestTable EmpId=2 " and noticed
that I forget to add some value...
3) Add value " insert into testtable ( empid , empname ) values
(2,"Gerald"); " and hit the followng...

insert into testtable ( empid , empname ) values (2,"Gerald");
Column EMPID not found; SQL statement:
insert into testtable ( empid , empname ) values (2,"Gerald")
[42122-76] 42S22/42122 (Help)

From here... I doubt if I can progress to do the select in Ex2.

4) Change some casing to match those found in the tree view of the
table rows in web console ..

insert into testtable ( EmpId , EmpName ) values (2,"Gerald");
Column EMPID not found; SQL statement:
insert into testtable ( EmpId , EmpName ) values (2,"Gerald")
[42122-76] 42S22/42122 (Help)

Note that the web console have an auto complete function so I used the
auto cimplete as fully as possible by using as much as what the web
console pop up showed.

I wonder if the above testing have anything to do with trying to
verify vsaji's in the original post.

[vsaji]

After positing my issue, I happend to see the similar issue had been
posted by another member in this forum and for which Thomas had
already responded

http://groups.google.com/group/h2-database/browse_thread/thread/517236333e47337b/559a9345dfcf5938?lnk=gst&q=Case+sensitive+column+names#559a9345dfcf5938

Though he is telling it is a ANSI SQL standar, it would be a very help
if the case sensitive column support is enable by passing some
properties on the connection URL.Something like this:

jdbc:h2:<url>;CASE_SENSITIVE_COLUMN={TRUE|FALSE} .

Your comments would be much appreciated.

Saji

[Thomas Mueller]

Hi,

> I am currently evaluating H2 DB to use one of my project.

Did you try another database before? If yes which one?

> While testing one of the requirement, I found that the column names
> read from ResultSetMeta is uppercase whereas the table columns are
> created with case sensitive.

See
http://www.h2database.com/html/grammar.html#name
"Names are not case sensitive. "
http://www.h2database.com/html/grammar.html#quotedname
Quoted names are case sensitive, and can contain spaces. There is no
maximum name length.
Two double quotes can be used to create a single double quote inside
an identifier.

> CREATE TABLE TestTable (EmpId INT,EmpName VARCHAR(50));
> select * from TestTable
> ResultSetMeatData.getColumnName(1)= > "EMPID".

You didn't use quoted identifiers when creating the table, therefore
H2 stores them as uppercase. This is according to the SQL standard.

> CREATE TABLE TestTable ("EmpId" INT,"EmpName" VARCHAR(50));
> select * from TestTable EmpId=2
> Column EMPID not found

If you use quoted identifiers when creating the table, and you use
mixed case identifiers, you also need to use quoted identifiers when
selecting: select * from TestTable where "EmpId"=2. This is also
according to the SQL standard. Your forgot WHERE.

[Thomas Mueller]

Hi,

> CREATE TABLE TestTable ("EmpId" INT,"EmpName" VARCHAR(50));

> insert into testtable ( empid , empname ) values (2,"Gerald");

You made two mistakes: you didn't use quoted in one place and not the
other; and you used double quotes instead of single quotes for the
varchar value. Try:

insert into testtable ( "EmpId", "EmpName") values (2,'Gerald');

Regards,
Thomas

[Thomas Mueller]

Hi,

> Though he is telling it is a ANSI SQL standar, it would be a very help
> if the case sensitive column support is enable by passing some
> properties on the connection URL.Something like this:
>
> jdbc:h2:<url>;CASE_SENSITIVE_COLUMN={TRUE|FALSE} .

That's an idea, but the setting would have to be stored in the
database. I wonder why you don't just want to use the SQL standard?

Regards,
Thomas

[vsaji]

Hi Thomas.

Thanks for your comments.

I just want to comment on your following point which you had posted
earlier.

<!------ In fact all major DBMS (Postgres, Oracle, Firebird, DB2, SQL
Server,
Sybase, Ingres) behave like this. At least when it comes to quoted
identifiers. ----->

This is not true, because I have created a table with case senstive
columns in sysbase and while iterating the column names
(ResultSetMetaData.getColumnName(1)) it returns the orginal case that
was used at the time of table creation.

ANSI SQL standard might me valid, but it is always the user call
whether they want to follow or not. Even the Oracle and Sybase Driver
are resulting the same case of the column names (ie.
ResultSetMetaData.getColumnName(1)="EmpId" not "EMPID"). Then why H2
cannot support that.

My current requirement would be to cache few records of a sysbase
table (which was created 1990's so we cannot expect any changes now)
in H2, later use it in my application. Few senarios I am reading this
cached records and storing (key is column name from ResultSetMetaData)
it into HashMap and trying to retry using the same column name of the
actual DB (sybase) and its is failing.

I tried using TreeMap to overcome this issue, but resulting bad
performance.

Please advice

[Thomas Mueller]

Hi,

> I just want to comment on your following point which you had posted
> earlier.
>
> <!------ In fact all major DBMS (Postgres, Oracle, Firebird, DB2, SQL
> Server,
> Sybase, Ingres) behave like this. At least when it comes to quoted
> identifiers. ----->

For for completeness, I wrote before:

"
This is how the ANSI SQL standard requires the usage of (quoted)
identifiers:
Not quoted --> Case insensitive
Quoted --> Case sensitive

In fact all major DBMS (Postgres, Oracle, Firebird, DB2, SQL Server,
Sybase, Ingres) behave like this. At least when it comes to quoted
identifiers.

Some actually chose to store identifiers in lower case - which is not
standard compliant. The ANSI standard actually requires that all non-
quoted identifiers are stored in upper case.

But all adhere to the standard that quoted identifiers are case-
sensitive.
"

> This is not true, because I have created a table with case senstive
> columns in sysbase and while iterating the column names
> (ResultSetMetaData.getColumnName(1)) it returns the orginal case that
> was used at the time of table creation.

I didn't mean to say all databases return uppercase or behave exactly
the same. Actually there is quite a big difference in how databases
store and return unquoted identifiers. Only when using quoted
identifiers, almost all databases work in the same way.

Maybe a solution would be to use quoted identifiers, or do you already
do that? Could you post the code you have used so far?

Regards,
Thomas