"Unsupported sort sort order" error updating Access database with Java

Question

I want to make a small change to the Access table using UCanAccess via NetBeans, but I ran into a problem in the row

pst.executeUpdate(); 

Database Information:

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

the 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(); 

An 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 rephrase the error message:

java.lang.IllegalArgumentException: given index ... (PERSON) PrimaryKey ... is not used for indexed queries due to unsupported sort order

This is a well-known limitation of Jackcess, the record manager that UCanAccess uses to read and write the Access database file. To perform table updates with primary keys of type Text , Jackcess requires that the Access database use the sort order of General or General - Legacy.

To change the sort order for an Access database file:

• Open the database in Access. In the File > Options , change the "new order of the database collation" on the "General" (or "Common - Legacy").

• Run the Compact and Recovery Database in the database. (In Access 2010+, it is on the Database Tools tab in the Ribbon panel.)

• Exit access.

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