Using UCanAccess getting ERROR "unexpected token: table-name

Question

Related to earlier questions about "unexpected token"

I get the following error

         UCAExc:::3.0.3.1 unexpected token: $BRANDRAP

When I run the code below in the NB IDE

try { Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");

    Connection conn =  DriverManager.getConnection("jdbc:ucanaccess://E:/DEV05/AmexDW/$TPMAIN.MDB");
    Statement s = conn.createStatement();
    ResultSet rs = s.executeQuery("SELECT [l BL Item Headers].[BL Counter], [l BL Item Headers].[BL A/C], [l BL Item Headers].[BL Entry] FROM [l BL Item Headers]");

    while (rs.next()) 
    {
        System.out.print(rs.getString(2));
        System.out.print(" :  ");
        System.out.print(rs.getString(3));
        System.out.print(" :  ");
        System.out.print(rs.getString(4));
        System.out.print("\n"); 
    }  
  } 
  catch (SQLException e) 
  {
    System.out.println(e.getMessage());
    return;
  }
  catch (ClassNotFoundException nf) 
  {
    System.out.println(nf.getMessage());
    return;
  }

NB. $BRANDRAP is a table in the $TPMAIN.MDB database but I'm SELECTING from the table [l BL Header Items]

jamadei · Answer 1 · 2016-01-10T00:26:38.523

1

It's because the exception is thrown at the connection time. $ in table or column names wasn't supported in the previous versions because of a bug. The related 3.0.3 fix didn't solve a particular case (when the table name starts with $). A complete fix will be in the 3.0.4. It will be released soon.

edited Jan 10 '16 at 00:26

answered Jan 10 '16 at 00:20

jamadei

1,680
8
8

There are no "$" characters in any of the column names in the table [l BL Item Headers] but there are several other tables in the MS Access db $TPMAIN – Rawle Ramkeesoon Jan 10 '16 at 00:34
As I said the exception is thrown before executing the query, at the connection time. So sorry. – jamadei Jan 10 '16 at 00:47
After using another DB with no "$" in dbname and no tables with "$" and no columns with "$" as relevant lines of code below: – Rawle Ramkeesoon Jan 10 '16 at 00:48
Connection conn = DriverManager.getConnection("jdbc:ucanaccess://S:/UAT/BSP_Reconciliation/BSPUPDATE.MDB"); Statement s = conn.createStatement(); ResultSet rs = s.executeQuery("SELECT * FROM bspRecords"); – Rawle Ramkeesoon Jan 10 '16 at 00:48
Special character $ is already held except when it is the very first character. – jamadei Jan 10 '16 at 00:56
Sorry for delay in answering. – Rawle Ramkeesoon Jan 10 '16 at 16:11
When I use another Access DB that has no "$" in its table names or in the column names ....as in the code below: Connection conn = DriverManager.getConnection("jdbc:ucanaccess://S:/bspupdate.mdb"); Statement s = conn.createStatement(); ResultSet rs = s.executeQuery("SELECT Ticket FROM bspRecords"); I get: Exception in thread "main" java.lang.OutOfMemoryError: Java heap space – Rawle Ramkeesoon Jan 10 '16 at 16:39
This is another issue. It depends on the db size and it can be solved setting either a higher -Xmx or the connection parameter memory to false. See the ucanaccess web site for more details. – jamadei Jan 10 '16 at 18:26
Adding "memory=false" as in the line below: Connection conn = DriverManager.getConnection ("jdbc:ucanaccess://S:/UAT/BSP_Reconciliation/BSPUPDA‌TE.MDB;memory=false") allowed the DB connection to be made. The connection took a long time though. – Rawle Ramkeesoon Jan 11 '16 at 09:07
Notice that just the very first connection in the whole JVM life will be slow, the next ones will be instantaneous. Anyway there are several parameter options (depending on the specific situation you're facing) to drastically drop down the connection time. – jamadei Jan 13 '16 at 08:50

Using UCanAccess getting ERROR "unexpected token: table-name

1 Answers1