"unsupported collating sort order" error updating Access database from Java

Question

I want to make a small change in an Access table using UCanAccess via NetBeans, but I encountered a problem at line

pst.executeUpdate();

Database details:

database name : duruBistro.accdb
table name : person
field names: tc_no    (text)
             name     (text)
             surname  (text)
             salary   (number)

Code:

Connection conn = DriverManager.getConnection("jdbc:ucanaccess://C:\\Users\\ysnndr    \\Documents\\accessDB\\duruBistro.accdb");
String query = "UPDATE PERSON SET SALARY = ? WHERE TC_NO = '189'";
PreparedStatement pst = conn.prepareStatement(query);
pst.setInt(1, 2500);         
pst.executeUpdate();

Exception:

run:
java.lang.IllegalArgumentException: Given index Index@53f65459[
  name: (PERSON) PrimaryKey
  number: 0
  isPrimaryKey: true
  isForeignKey: false
  data: IndexData@3b088d51[
    dataNumber: 0
    pageNumber: 317
    isBackingPrimaryKey: true
    isUnique: true
    ignoreNulls: false
    columns: [
      ReadOnlyColumnDescriptor@1786dec2[
        column: TextColumn@711f39f9[
          name: (PERSON) TC_NO
          type: 0xa (TEXT)
          number: 17
          length: 22
          variableLength: true
          compressedUnicode: true
          textSortOrder: SortOrder[1055(0)]
        ]
        flags: 1
      ]
    ]
    initialized: false
    pageCache: IndexPageCache@74650e52[
      pages: (uninitialized)
    ]
  ]
] is not usable for indexed lookups due to unsupported collating sort order SortOrder[1055(0)] for text index
    at com.healthmarketscience.jackcess.impl.IndexCursorImpl.createCursor(IndexCursorImpl.java:111)
net.ucanaccess.jdbc.UcanaccessSQLException: Given index Index@53f65459[
  name: (PERSON) PrimaryKey
  number: 0
  isPrimaryKey: true
  isForeignKey: false
  data: IndexData@3b088d51[
    dataNumber: 0
    pageNumber: 317
    at com.healthmarketscience.jackcess.CursorBuilder.toCursor(CursorBuilder.java:302)
    at net.ucanaccess.commands.IndexSelector.getCursor(IndexSelector.java:148)
    isBackingPrimaryKey: true
    isUnique: true
    at net.ucanaccess.commands.CompositeCommand.persist(CompositeCommand.java:83)
    ignoreNulls: false
    columns: [
      ReadOnlyColumnDescriptor@1786dec2[
        column: TextColumn@711f39f9[
          name: (PERSON) TC_NO
          type: 0xa (TEXT)
          number: 17
          length: 22
          variableLength: true
          compressedUnicode: true
          textSortOrder: SortOrder[1055(0)]
        ]
        flags: 1
      ]
    ]
    initialized: false
    pageCache: IndexPageCache@74650e52[
      pages: (uninitialized)
    ]
  ]
] is not usable for indexed lookups due to unsupported collating sort order SortOrder[1055(0)] for text index
    at net.ucanaccess.jdbc.UcanaccessConnection.flushIO(UcanaccessConnection.java:312)
    at net.ucanaccess.jdbc.UcanaccessConnection.commit(UcanaccessConnection.java:202)
    at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:143)
    at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:56)
    at net.ucanaccess.jdbc.UcanaccessPreparedStatement.executeUpdate(UcanaccessPreparedStatement.java:248)
    at com.ui.AccdbcConnection.main(AccdbcConnection.java:29)
BUILD SUCCESSFUL (total time: 1 second)

Answer 1

To paraphrase the error message:

java.lang.IllegalArgumentException: Given index ... (PERSON) PrimaryKey ... is not usable for indexed lookups due to unsupported collating sort order

This is a known limitation of Jackcess, the record manager that UCanAccess uses to read and write the Access database file. In order to perform updates on tables with Primary Keys of type Text, Jackcess requires the Access database to use the "General" or "General - Legacy" sort order.

To change the sort order for the Access database file in question:

• Open the database in Access. Under File > Options change the "New database sort order" to "General" (or "General - Legacy").

• Perform a "Compact and Repair Database" on the database. (In Access 2010+ it is located on the "Database Tools" tab of the Ribbon Bar.)

• Exit Access.

Your Java application should no longer throw the exception. However, if the problem persists then there might also be an issue with your Windows locale. See this answer for another possible solution.