SQL errors related to unquoted keywords after upgrading H2.

[motlin]

I recently upgrade Reladomo from 18.0.0 to 18.1.0, and H2 from 1.4.200 to 2.2.224. I ran into issues with SQL syntax errors, related to unquoted keywords user, which I use as a table name, and key and value, which I use as column names. I was able to work around the problem. I'll share my workarounds here, in case they make sense to upstream. I'm also wondering if there's a better way to handle this. It seems like Reladomo is designed to handle this, since the file SqlKeywords.java includes all of these keywords. Is there a way to turn on identifier quoting for all sql?

First, I created a custom DatabaseType, to copy patterns I found in e5d754c. As far as I can tell, there's no equivalent of SET QUOTED_IDENTIFIER ON for H2. Instead I ran SET NON_KEYWORDS USER, KEY, VALUE. I did not need SET MODE LEGACY.

    @Override
    public void configureConnection(Connection connection)
            throws SQLException
    {
        // this.fullyExecute(connection, "SET QUOTED_IDENTIFIER ON");
        // this.fullyExecute(connection, "SET MODE LEGACY");
        this.fullyExecute(connection, "SET NON_KEYWORDS USER, KEY, VALUE");
    }

I found I was still getting sql syntax errors and after debugging, I found some connections used by Reladomo that had not been passed to configureConnection. So next, I changed my ConnectionManager.

    @Override
    public Connection getConnection()
    {
        try
        {
            Connection connection = this.dataSource.getConnection();
            this.databaseType.configureConnection(connection);
            return connection;
        }
        catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
    }

Thoughts on these changes? Is there another way to get identifiers to be quoted instead?

[mohrezaei]

You can simply quote them in you xml. Just remember to escape literal quotes in xml.

[motlin]

🤦 thank you, I will try this tomorrow

[motlin]

I've tried different combinations, and I can't get quoting to work.

When I put quotes around the table name in xml...

    <ClassName>Parameter</ClassName>
    <DefaultTable>&quot;PARAMETER&quot;</DefaultTable>

... the generated java code doesn't compile. In ParameterDatabaseObjectAbstract:

	public String getDefaultTableName()
	{
		return ""PARAMETER"";
	}

If I don't quote the table name in the xml, but I do quote it during schema creation, then I get a sql error saying table not found.

When I put quotes around column names in the xml, I also get compilations in the Java code.

    <Attribute
            name="name"
            javaType="String"
            primaryKey="true"
            nullable="false"
            maxLength="256"
            finalGetter="true"
            columnName="&quot;NAME&quot;"
            trim="false" />

	private static final String PK_WITH_ALIAS = "t0."NAME" = ?";
	private static final String PK_INDEX_COLS = ""NAME"";

I tried a bunch of other things too, but I figured I should stop and report back at this point.

[mohrezaei]

I just double checked and putting quotes in the xml is the older solution that should no longer be necessary. The test class User has a column named "NAME", which gets quoted in java automatically:

xml:

    <Attribute name="name" javaType="String" columnName="NAME" maxLength="128"/>

generated finder:

                result = this.mapper == null ? SingleColumnStringAttribute.generate("\"NAME\"", ...

generated db object:

    private static final String COL_LIST_WITHOUT_PK = "USERID,\"NAME\",ACTIVE...

Are you sure you're using the correct generator version?

In case you need to do it the old way, the correct syntax is "backslash-quote", like so:

    <DefaultTable>\&quot;USER_TBL\&quot;</DefaultTable>
...
    <Attribute name="profileId" nullable="false" javaType="int" columnName="\&quot;PROFILE_OID\&quot;"/>
...

[motlin]

Using Reladomo 18.1.0, when I use columnName="\"id\"" I get generated invalid generated code in a few places, including the *DatabaseObjectAbstract class.

	public String getPrimaryKeyWhereSql()
	{
		return "\"id\" = ?";
	}

and for tables:

    <DefaultTable>\&quot;TABLE\&quot;</DefaultTable>

	public String getDefaultTableName()
	{
		return "\"TABLE\"";
	}

[motlin]

I was able to upgrade to H2 version 2.

• I had to downgrade to Reladomo 18.0.0
• I quoted every identifier in every Reladomo definition xml, every table name and column name
• I turned on Liquibase's objectQuotingStrategy="QUOTE_ALL_OBJECTS" which does the same thing
• Not sure if this is strictly necessary, but I changed every identifier to be all upper-case. Previously my columns used lower-case.

I couldn't get Reladomo 18.1.0 to work in any combination of configuration options I tried.