JDBC Connection.getColumns and case sensitivity

[Silvio]

Hello,

I am using H2 and have created a table programmatically using "create
table mytest ( id : uuid, name : varchar(150))"

Now when I use getColumns(null,"public","mytest","%") no columns are
returned. When I try getColumns(null,"PUBLIC","MYTEST","%") I do get
both columns, with names converted to uppercase.

I can live with the automatic uppercasing (not very pretty, btw) but I
think it should be done consistenly allowing supplying names in the
API in lowercase. My problem is that this code is inside a script
interpreter and the users scripts are mostly defined in lowercase. On
PostgreSQL this was never an issue. I am planning to convert most if
not all to H2 but this is a real show-stopper for me.

Is there a way to circumvent this? Since I am running on multiple
databases doing the uppercasing upfront may break existing stuff and
apart from that is far from pretty.

Thanks in advance,

Silvio

[Thomas Mueller]

Hi,

H2 converts identifier names to uppercase, as some other databases also do. Not PostgreSQL and MySQL, I know.

There is a compatibility feature, but you need to create a new database, and always append ";database_to_upper=false" to the database URL:

    jdbc:h2:~/temp/test3;database_to_upper=false

This will not work for existing databases.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Shammat]

On Apr 14, 7:47 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> H2 converts identifier names to uppercase, as some other databases also do.

Which is what the SQL Standard requires.

[Silvio Bierman]

Thanks Thomas.

That will work for me now. Is there any chance the uppercasing will be extended to getColumns and similar APIs?

Cheers,

Silvio

[Silvio Bierman]

Well, yes.

But do you agree that getColumns and similar APIs should do the same
thing then?

Cheers,

Silvio

[Shammat]

On Apr 15, 12:14 am, Silvio Bierman <sbier...@jambo-software.com>
wrote:

> Well, yes.
>
> But do you agree that getColumns and similar APIs should do the same
> thing then?

No, because the JDBC API clearly requires:

"tableNamePattern - a table name pattern; must match the table name as
it is stored in the database"

See:
http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getColumns%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String%29

[Silvio Bierman]

True, but since "match" is akin a LIKE match here since SQL patterns are
allowed allowing case insensitive matching can make sense, since LIKE
matches are or can be case insensitive in several databases.

[Thomas Mueller]

Hi,

See:
http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getColumns%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String%29

True, but since "match" is akin a LIKE match here since SQL patterns are allowed allowing case insensitive matching can make sense, since LIKE matches are or can be case insensitive in several databases.

H2 supports a compatibility feature: http://h2database.com/javadoc/org/h2/constant/DbSettings.html#DATABASE_TO_UPPER

Whether LIKE is case sensitive is not is unrelated to this problem. H2 needs to follow the JDBC API specification.

Regards,

Thomas