Is there a way to quote columns ?

[Catull]

Dear H2 community

There is a DB I cannot change, whose DML and DDL we use in both its native form and for H2.

We use H2 for unit tests, by means of Hibernate ORM.

As it turns out, one column is named INTERVAL.

It is a reserved SQL keyword, so when H2 prepares a query, such as:

SELECT columnA, columnB, INTERVAL, columnC

FROM TABLE T

it fails with a Syntax Error.

But if I step through with a debugger, and change that query to

SELECT columnA, columnB, "INTERVAL", columnC

FROM TABLE T

then the query is executed fine.

Is there a way to have columns be quoted ?

It would be great if there was a switch for it.

Can I override the default implementation of org/h2/table.ColumnResolver#getColumnName(Column) ?

Thanks.

Ciao, derweil,

--

Carlo

[Evgenij Ryazanov]

Hello!

You need to quote column names in your application, there is nothing to do on H2 side. If you use Hibernate ORM, you can force quotation of all identifiers with hibernate.globally_quoted_identifiers setting.

In the worst case you can add ;NON_KEYWORDS=INTERVAL to JDBC URL, but this setting doesn't cover all possible cases and it should be considered only as a possible temporary workaround.

[Catull]

Thanks, Evgenij

With H2 version 2.1.214 and the JDBC URL jdbc:h2:tcp://localhost/MY_DB;AUTO_SERVER=TRUE;IGNORECASE=TRUE;NON_KEYWORDS=INTERVAL,VALUES

I get this Exception:

Caused by: org.h2.jdbc.JdbcSQLNonTransientConnectionException: Unsupported connection setting "NON_KEYWORDS" [90113-200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:622)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
at org.h2.message.DbException.get(DbException.java:205)
at org.h2.message.DbException.get(DbException.java:181)
at org.h2.engine.ConnectionInfo.readSettingsFromURL(ConnectionInfo.java:269)
at org.h2.engine.ConnectionInfo.<init>(ConnectionInfo.java:78)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:152)
at org.h2.Driver.connect(Driver.java:69)

However, I can set the parameter NON_KEYWORDS it in a H2 DB Session, see below.

I start H2 over TCP with this:

    java -cp /opt/h2-2.1.214/bin/h2-2.1.214.jar org.h2.tools.Server -baseDir $HOME/my_db -ifNotExists -tcp -tcpAllowOthers -tcpPort 9092 1>my_db.log 2>&1

Then I run an init script, before connecting the server application, like this:

    java -cp /opt/h2-2.1.214/bin/h2-2.1.214.jar org.h2.tools.RunScript -url jdbc:h2:tcp://localhost/MY_DB -user SOMEUSER -password SOMEPASSWORD -showResults -script h2-init.sql

The content of h2-init.sql is:

SET AUTOCOMMIT             TRUE;
SET AUTO_SERVER            TRUE;
SET DB_CLOSE_DELAY         -1;
SET IGNORECASE             TRUE;
SET MODE                   ORACLE;
SET NON_KEYWORDS           INTERVAL, VALUES;
SET OLD_INFORMATION_SCHEMA TRUE;

CREATE USER   IF NOT EXISTS "BACKEND_USER"  PASSWORD 'secret' ADMIN;
CREATE USER   IF NOT EXISTS "FRONTEND_USER" PASSWORD 'secret' ADMIN;

CREATE SCHEMA IF NOT EXISTS BACKEND_SCHEMA  AUTHORIZATION "BACKEND_USER";
CREATE SCHEMA IF NOT EXISTS FRONTEND_SCHEMA AUTHORIZATION "FRONTEND_USER";

SELECT * FROM INFORMATION_SCHEMA.USERS;

I get the impression "NON_KEYWORDS" is only supported in a DB session, not as JDBC connection parameter.

Regards,

--

Carlo

[Evgenij Ryazanov]

With H2 version 2.1.214 and the JDBC URL jdbc:h2:tcp://localhost/MY_DB;AUTO_SERVER=TRUE;IGNORECASE=TRUE;NON_KEYWORDS=INTERVAL,VALUES

I get this Exception:

Caused by: org.h2.jdbc.JdbcSQLNonTransientConnectionException: Unsupported connection setting "NON_KEYWORDS" [90113-200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:622)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
at org.h2.message.DbException.get(DbException.java:205)
at org.h2.message.DbException.get(DbException.java:181)
at org.h2.engine.ConnectionInfo.readSettingsFromURL(ConnectionInfo.java:269)
at org.h2.engine.ConnectionInfo.<init>(ConnectionInfo.java:78)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:152)
at org.h2.Driver.connect(Driver.java:69)

You need to use some modern version of H2 on client side too.

I start H2 over TCP with this:

    java -cp /opt/h2-2.1.214/bin/h2-2.1.214.jar org.h2.tools.Server -baseDir $HOME/my_db -ifNotExists -tcp -tcpAllowOthers -tcpPort 9092 1>my_db.log 2>&1

Documentation of H2 has clear warning about combination of -ifNotExists with -tcpAllowOthers, this combination of settings effectively creates a remote security hole on you system unless your ports are guarded somehow.
https://h2database.com/html/tutorial.html#creating_new_databases

[Catull]

Dear Evgenij

On Sunday, July 2, 2023 at 6:10:28 PM UTC+2 Evgenij Ryazanov wrote:

With H2 version 2.1.214 and the JDBC URL jdbc:h2:tcp://localhost/MY_DB;AUTO_SERVER=TRUE;IGNORECASE=TRUE;NON_KEYWORDS=INTERVAL,VALUES

I get this Exception:

Caused by: org.h2.jdbc.JdbcSQLNonTransientConnectionException: Unsupported connection setting "NON_KEYWORDS" [90113-200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:622)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
at org.h2.message.DbException.get(DbException.java:205)
at org.h2.message.DbException.get(DbException.java:181)
at org.h2.engine.ConnectionInfo.readSettingsFromURL(ConnectionInfo.java:269)
at org.h2.engine.ConnectionInfo.<init>(ConnectionInfo.java:78)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:152)
at org.h2.Driver.connect(Driver.java:69)

You need to use some modern version of H2 on client side too.

Indeed, it was the IDE which held onto H2 1.4.200, after a clean rebuild the correct version is picked up.

 

I start H2 over TCP with this:

    java -cp /opt/h2-2.1.214/bin/h2-2.1.214.jar org.h2.tools.Server -baseDir $HOME/my_db -ifNotExists -tcp -tcpAllowOthers -tcpPort 9092 1>my_db.log 2>&1

Documentation of H2 has clear warning about combination of -ifNotExists with -tcpAllowOthers, this combination of settings effectively creates a remote security hole on you system unless your ports are guarded somehow.
https://h2database.com/html/tutorial.html#creating_new_databases

Thanks again, I skimmed the documentation, was not aware of it; corrected it now.

Regards,

--

Carlo