2

I am converting an existing program from JDBC-ODBC Bridge to UCanAccess. It has been working for years. The Java program writes to an MDB file. After conversion, one Insert command stopped working.
It throws "net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: [".

After some experimentation I determined that the problem is caused by three columns that include apostrophes in their names. Example: [Sched'd PO Place Date]. Apparently JDBC-ODBC Bridge did not care but UCanAccess does.

I am somewhat trapped with the existing MDB structure or I'd simply rename the fields. But there is no telling how many downstream reports could be broken if I did that. The MDB is used for Ad Hoc reporting.

Here is a simplified SQL Select version of the problem. It throws the same error as the Insert. 

    String cJD = "net.ucanaccess.jdbc.UcanaccessDriver";
    String cS = "jdbc:ucanaccess://C:/MosAll/mosall.mdb";
    String uid = "";
    String pw = "";
    String sql4 =   "select [Sched'd PO Place Date] from [Tier 1] ";

    Class.forName(cJD);
    Connection con = DriverManager.getConnection(cS, uid, pw);
    PreparedStatement pstmt4;
    pstmt4 = con.prepareStatement(sql4);
    pstmt4.execute();

Is there a way to "escape" the apostrophes or reconfigure ucanaccess so that the driver can execute them?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Dave Martin
  • 21
  • 3

2 Answers2

1

This issue has been fixed in UCanAccess 2.0.9.4.

Previous answer:

I have been able to recreate the issue with UCanAccess version 2.0.9.3. 

select [Sched'd PO Place Date] ...

fails, but for now simply omitting the '

select [Schedd PO Place Date] ...

works okay. Interestingly, using backticks instead of square brackets

select `Sched'd PO Place Date` ...

also produces the exception

net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: [

With luck this will get fixed in a future release of UCanAccess.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 1
    Yes, it's simply a bug in the parser that normalizes SQL. Using the column internal name (the name without apostrophe) all works fine, yet I'll fix it in the next version. Backticks are treated like square brackets: they also are supported by access and they were introduced in ucanaccess mainly to implement the DatabaseMetaData.getIdentifierQuoteString method, useful for tools like openoffice or libreoffice. – – jamadei Feb 26 '15 at 13:56
  • Dropping the apostrophe works and it is a decent workaround. Thank you! I'd have never thought to try that. – Dave Martin Feb 26 '15 at 19:00
  • The only thing remaining is that I get this message: - unsupported data type BINARY for index, making read-only. Any idea how to eliminate this? I can repost this as a separate question if you'd like. I saw a few answers to this same question on the net, but I couldn't specifically relate the responses to what I am doing. – Dave Martin Feb 26 '15 at 19:01
  • It's a jackcess warning, so I think they can help you much better than me. – jamadei Feb 27 '15 at 18:09
  • Thank you all. You were very helpful! I'll take the last question to Jackcess. – Dave Martin Mar 04 '15 at 22:10
0

have you tried table, columns names between `` (they not apostrophe but the one next to ~), the \' may work, and finally the JDBC standard way is to define the escape character at the end of the query:

String sql4 = "select [Sched\'d PO Place Date] from [Tier 1] { escape '\'}"

You may want to try the one above also with ``instead of []

Finally, your last resource is "select *" and extract the right column yourself

Zielu
  • 8,312
  • 4
  • 28
  • 41