Error when column name starts with AS

[Cathy Artigues]

Hi -- I'm upgrading some code to Java 8 and thought I'd try replacing the MS ODBC driver with UCanAccess. However, I'm running into a problem with this query:

select max(GroupID),max(abs(Read)) from Books where ASIN in ('xxxxxxxxxx','yyyyyyyyy')

The error is below. I suppose it doesn't like ASIN as a column name, but I can't seem to find a way to escape it with square brackets or double quotes, and qualifiying it with the table name isn't working either. Is there any way to make this work without changing the column name in the DB?

UCAExc:::3.0.6 unexpected token: AS
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:154)
...
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: AS
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
at net.ucanaccess.jdbc.Execute.executeWrapped(Execute.java:56)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:152)
at net.ucanaccess.jdbc.Execute.execute(Execute.java:46)
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:152)
... 2 more
Caused by: org.hsqldb.HsqlException: unexpected token: AS
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanPrimaryOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanTestOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanFactorOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanTermOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanValueExpression(Unknown Source)
at org.hsqldb.ParserDQL.readWhereGroupHaving(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 8 more

[Gord Thompson]

Hi Cathy. I just tried using Books.ASIN and it worked for me:

String sql = "select max(GroupID),max(abs(Read)) from Books where Books.ASIN in ('xxxxxxxxxx','yyyyyyyyy');";
try (
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(sql)) {
    rs.next();
    System.out.println(rs.getString(1));
}

[Cathy Artigues]

Thanks for the reply. You are correct. I didn't notice at first that the error message changed when I added the qualified name. It is required to qualify the ASIN field though, and I had to do it throughout the class to fix a bunch of other SQL errors where the parser wants to read ASIN as AS.

The new error was actually that abs(Read) was an invalid operation for the data type. "Read" is a boolean in this database. I suspect the old driver probably treated bools as ints of -1 or 0, while the new driver is probably returning an actual true/false. I didn't write this code and the comments are sparse, so I've got to do some debugging on old and new to see exactly what's going on.

I'm also finding some problems with updates and inserts into datetime fields where it's complaining about 2016 being an invalid token, so that's another thing to work on Monday.

Overall, this is not quite a drop-in replacement for the old driver as it does require quite a lot of code changes aside from just setting a new connection string, but I think I can make it work eventually.

[Gord Thompson]

Yes, unfortunately UCanAccess will probably never be a 100% complete drop-in replacement for Access ODBC. HSQLDB is much more strict than Access SQL when it comes to doing silly things like trying to take the absolute value of a Boolean. (And what is that actually supposed to mean, anyway?)

As for dates, be aware that UCanAccess expects the usual Access hash-mark delimiters for date literals (e.g., #2016-07-23#) and is not as permissive as Access itself when it comes to date literals as strings (e.g., '2016-07-23' won't work).