Square Brackets Error in UcanaccessConnection.prepareCall()

[Steven Morrison]

I've been having some issues with an Access database query in which one of the column names contains spaces. I've been using square brackets around the column names, which has been leading to errors when the query is passed to the UcanaccessConnection.prepareCall() function.

Seems to me to be a result of passing the non-normalized sql string to the hsqlDBConnection.prepareCall() in the UcanaccessCallableStatement constructor. If this is not in fact a bug, please advise how this should be handled.

Stack Trace:
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: [
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCCallableStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareCall(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessConnection.prepareCall(UcanaccessConnection.java:481)
Caused by: org.hsqldb.HsqlException: unexpected token: [
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
at org.hsqldb.ParserBase.checkIsIdentifier(Unknown Source)
at org.hsqldb.ParserDQL.readColumnOrFunctionExpression(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.XreadValueExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadSelect(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.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)

[Gord Thompson]

Please show us a simple example of what you are trying to do. Are you using a CallableStatement with SQL command text for a SELECT query, e.g., "SELECT [First Column], ... FROM ..."? If so, then I would expect you to be using a PreparedStatement, not a CallableStatement.

[Steven Morrison]

Yes, I am using a CallableStatement for a SELECT query.

Connection conn = DriverManager.getConnection("jdbc:ucanaccess://C:/tmp/test.mdb");
CallableStatement stmt = conn.prepareCall("Select [col 1], [col 2] from [tbl]");
stmt.execute();

[Gord Thompson]

Try using a PreparedStatement instead.

[Steven Morrison]

I appreciate the assistance, but I am looking for information about the problem outlined in the original post, not alternatives.

[Gord Thompson]

There is currently (as of version 3.0.5) a known issue with the handling of square brackets in the command text of a CallableStatement. It will be fixed in a future release of UCanAccess.

However, be advised that even after the fix is implemented your code may not work because that is not what a CallableStatement is intended to do. According to the official JDBC documentation

The [CallableStatement] interface used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs.

 
Specifically, that escape syntax is

{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

 
or

{call <procedure-name>[(<arg1>,<arg2>, ...)]}

 
If you want to execute an arbitrary SELECT ... FROM ... statement then you should be using a Statement or a PreparedStatement.