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)
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:
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.