Rob Kimball Rob Kimball - 11 months ago 216
SQL Question

UCanAccess SQL Insert failing; query works fine in MS Access unexpected token (

This is my first attempt at building something in Java, and the end goal here is to simply import an excel file into a MS Access database.

I suspect there's some sort of data incompatibility error, or something that UCA is doing to clean queries, but I don't know enough about Java or UCanAccess to track it down.

Here's the query I have assembled, which runs fine to insert data in MS Access directly:

INSERT INTO XXXX_XA_Data_1 ([Date],[Fund Ticker],[Unique ID],[Cash & Cash Equiv],[Mkt Value of Security Holdings],[Today's Future Variation Margin],[Other Net Assets],[Total Net Assets],[Fund Shares],[NAV],[Fund Sales ($)],[Fund Sales (Shares)],[Fund Redemptions ($)],[Fund Redemptions (Shares)],[Expense Reimbursement By Advisor - Receivable],[12B-1 Fees- Liability],[Shareholder Servicing - Liability],[Audit - Liability],[Advisor Fees - Liability],[Custody - Liability],[Licensing - Liability],[Trustee's - Liability],[Fund Accounting - Liability],[Insurance - Liability],[Professional - Liability],[Legal - Liability],[Misc - Liability],[Printing - Liability],[Registration - Liability],[State Registration - Liability],[Transfer Agent Expense - Liability],[Transfer Agent Fee - Liability],[Administration Fee - Liability],[12B-1 Fees - Daily Accrual],[Shareholder Servicing Fees - Daily Accrual],[Audit Fees - Daily Accrual],[Advisor Fees - Daily Accrual],[Custody Fees - Daily Accrual],[Licensing - Daily Accrual],[Trustee's Fees - Daily Accrual],[Fund Accounting Fees - Daily Accrual],[Insurance Fees - Daily Accrual],[Professional Fees - Daily Accrual],[Legal Fees - Daily Accrual],[Misc Fees - Daily Accrual],[Printing Fees - Daily Accrual],[Registration Fees - Daily Accrual],[State Registration Fees - Daily Accrual],[Transfer Agent Expense Fees - Daily Accrual],[Transfer Agent Fees - Daily Accrual],[Expense Reimbursement Fees - Daily Accrual],[Administration Fee - Daily Accrual],[12B-1 Fees - YTD Accrual],[Shareholder Servicing Fees - YTD Accrual],[Audit Fees - YTD Accrual],[Advisor Fees - YTD Accrual],[Custody Fees - YTD Accrual],[Licensing - YTD Accrual],[Trustee's Fees - YTD Accrual],[Fund Accounting Fees - YTD Accrual],[Insurance Fees - YTD Accrual],[Professional Fees - YTD Accrual],[Legal Fees - YTD Accrual],[Printing Fees - YTD Accrual],[Registration Fees - YTD Accrual],[State Registration Fees - YTD Accrual],[Transfer Agent Expense Fees - YTD Accrual],[Transfer Agent Fees - YTD Accrual],[Expense Reimbursement Fees - YTD Accrual],[Administration Fee - YTD Accrual],[PAM Dividend Factor],[PowerAgent Dividend Factor])
VALUES (#12/01/2001#,"XXXXX","00000000-XXXXX",0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000);

Some data has been replaced with X's and 0s, but all of the column names are the same.

I've tried several variants to see what will work, and I can get it to insert Fund Ticker, Unique ID and Cash & Cash Equiv. Based on these attempts, I'm thinking that the date format is breaking it, or some of the column names, but I can't find anyone else who's encountered this exact issue.

Here's the java:

db = DriverManager.getConnection("jdbc:ucanaccess://C:" + path_db);


Statement s = db.createStatement();
try {
int result = s.executeUpdate(rowquery);
} catch (Exception e) {

And the full trace:

[2016-02-18 13:37:29.053]: SQL Insert failed for item 1. Aborting with exception: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc::: unexpected token: [
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(
at DailyImporter.main(
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.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
at net.ucanaccess.jdbc.ExecuteUpdate.executeWrapped(
at net.ucanaccess.jdbc.AbstractExecute.executeBase(
at net.ucanaccess.jdbc.ExecuteUpdate.execute(
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(
... 1 more
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.readSimpleColumnName(Unknown Source)
at org.hsqldb.ParserDQL.readSimpleColumnNames(Unknown Source)
at org.hsqldb.ParserDML.compileInsertStatement(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)
... 7 more

Are there column names or other data format issues that need to be resolved here? Or libraries other than UCanAccess that can simply run SELECT/INSERT queries on a MS Access db?

Answer Source

UCanAccess currently has difficulty parsing SQL statements with field names that include an apostrophe (a.k.a. "single quote" character) ', e.g.,

sql = "INSERT INTO XXXX_XA_Data_1 ([Today's Future Variation Margin]) VALUES (1)";

The issue has been reported to the UCanAccess development team and is expected to be fixed in a future release of UCanAccess.

In the meantime, if you don't need to do any sophisticated SQL queries then you could import com.healthmarketscience.jackcess.*; and use the Jackcess API directly. For example, to perform the equivalent of the INSERT statement above you could do

String dbFileSpec = "C:/Users/Public/example.accdb";
try (Database db = File(dbFileSpec))) {
    Table tbl = db.getTable("XXXX_XA_Data_1");
    HashMap rowData = new HashMap();
    rowData.put("Today's Future Variation Margin", 1);