UCanAccess SQL插入失败；查询在MS Access意外令牌中工作正常(UcanaccessStatement.java:222) [英] UCanAccess SQL Insert failing; query works fine in MS Access unexpected token (UcanaccessStatement.java:222)

问题描述

这是我第一次尝试用Java构建东西，最终目的是将excel文件简单地导入到MS Access数据库中.

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.

我怀疑存在某种数据不兼容错误，或者UCA正在清理查询，但是我对Java或UCanAccess的了解不足以追踪它.

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.

这是我组装的查询，可以很好地将数据直接插入MS Access中:

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)]) 
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);

某些数据已被X和0代替，但所有列名称都相同.

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

我已经尝试了几种变体来查看行之有效的方法，并且可以插入资金代号，唯一ID和现金及现金；现金等价基于这些尝试，我认为日期格式正在破坏它或某些列名称，但是找不到遇到此确切问题的其他人.

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.

这是java:

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

...

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

以及完整的跟踪记录:

[2016-02-18 13:37:29.053]: SQL Insert failed for item 1. Aborting with exception: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.3.1 unexpected token: [
    at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:222)
    at DailyImporter.main(DailyImporter.java:226)
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(ExecuteUpdate.java:67)
    at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:152)
    at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:50)
    at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:220)
    ... 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

这里是否需要解决列名或其他数据格式问题?还是除了UCanAccess之外的其他可以在MS Access数据库上简单运行SELECT/INSERT查询的库?

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?

推荐答案

UCanAccess当前难以解析字段名称中包含撇号(也称为单引号"字符)'，例如

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)";

该问题已报告给UCanAccess开发团队，预计将在以后的UCanAccess版本中修复.

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

同时，如果您不需要执行任何复杂的SQL查询，则可以import com.healthmarketscience.jackcess.*;并直接使用Jackcess API.例如，要执行与上面的INSERT语句等效的操作，您可以

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 = DatabaseBuilder.open(new File(dbFileSpec))) {
    Table tbl = db.getTable("XXXX_XA_Data_1");
    HashMap rowData = new HashMap();
    rowData.put("Today's Future Variation Margin", 1);
    tbl.addRowFromMap(rowData);
}

这篇关于UCanAccess SQL插入失败；查询在MS Access意外令牌中工作正常(UcanaccessStatement.java:222)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！