ResultSet delete current row fails because MariaDB decides to change the cursor type to SQL_CURSOR_STATIC, ignoring my request to change it to SQL_CURSOR_DYNAMIC and concurrency to SQL_CONCUR_READ_ONLY when SQL_CONCUR_LOCK requested

Start the JdbcOdbc MariaDB Integration tests
getConnection returning sun.jdbc.odbc.JdbcOdbcDriver
16-Oct-2025 08:49:56 - INFO - (DatabaseConnector.java:connect:86) - DatabaseConnector connected to JDBC URI : jdbc:odbc:jodbc-mariadb as user : GRYM
16-Oct-2025 08:49:56 - INFO - (VerifyResultSetDelete.java:validate:33) - Testing VeryfyResultSetDelete...
16-Oct-2025 08:49:56 - INFO - (VerifyResultSetDelete.java:validateResultSetDeleteValues:64) - Testing validateResultSetDeleteValues...
SQLWarning: reason(SQLSetStatementAttrInteger #SQLSetStmtAttr I:hStatement=1763324912224, attribute=6, value=2 [ma-3.2.6][11.4.8-MariaDB-ubu2404]Option value changed to default (SQL_CURSOR_STATIC)) SQLState(01S02) vendor code(0)
SQLWarning: reason(SQLSetStatementAttrInteger #SQLSetStmtAttr I:hStatement=1763324912224, attribute=7, value=2 [ma-3.2.6][11.4.8-MariaDB-ubu2404]Option value changed to default (SQL_CONCUR_READ_ONLY). ) SQLState(01S02) vendor code(0)
SQLState(S1000) vendor code(0)
java.sql.SQLException: SQLSetPosLong #SQLSetPos I:hStatement=1763324912224, row=1, operation=3, lockType=0 [ma-3.2.6][11.4.8-MariaDB-ubu2404]Can't build index for update/delete
at com.etlsolutions.jni.odbc.JODBCErrorManager.processError(JODBCErrorManager.java:144)
at com.etlsolutions.jni.odbc.JODBC.SQLSetPosLong(JODBC.java:1936)
at com.etlsolutions.jni.odbc.JODBC.SQLSetPos(JODBC.java:1890)
at com.etlsolutions.jniodbc.jdbc.JdbcResultSet.setPos(JdbcResultSet.java:8990)
at com.etlsolutions.jniodbc.jdbc.JdbcResultSet.deleteRow(JdbcResultSet.java:5848)
at com.etlsolutions.jodbc.integrationtests.mariadb_tests.verifiers.VerifyResultSetDelete.validateResultSetDeleteValues(VerifyResultSetDelete.java:90)
at com.etlsolutions.jodbc.integrationtests.mariadb_tests.verifiers.VerifyResultSetDelete.validate(VerifyResultSetDelete.java:45)
at com.etlsolutions.jodbc.integrationtests.mariadb_tests.JdbcOdbcController.validate(JdbcOdbcController.java:81)
at com.etlsolutions.jodbc.integrationtests.mariadb_tests.JdbcOdbcRunner.main(JdbcOdbcRunner.java:19)
16-Oct-2025 09:16:21 - ERROR - (VerifyResultSetDelete.java:validateResultSetDeleteValues:96) - validateResultSetDeleteValues failed E:SQLSetPosLong #SQLSetPos I:hStatement=1763324912224, row=1, operation=3, lockType=0 [ma-3.2.6][11.4.8-MariaDB-ubu2404]Can't build index for update/delete
16-Oct-2025 09:16:21 - INFO - (VerifyResultSetDelete.java:validateResultSetDeleteWasDeleted:166) - Testing validateResultSetDeleteWasDeleted...
16-Oct-2025 09:16:23 - ERROR - (VerifyResultSetDelete.java:validateResultSetDeleteWasDeleted:174) - validateResultSetDeleteWasDeleted failed, name 3 not deleted
16-Oct-2025 09:16:24 - INFO - (VerifyResultSetDelete.java:validate:54) - ------------------------------------------------------------
16-Oct-2025 09:16:24 - INFO - (VerifyResultSetDelete.java:validate:55) - Unsupported VeryfyResultSetDelete interface functions encountered : 0
16-Oct-2025 09:16:24 - ERROR - (JdbcOdbcRunner.java:main:24) - The JdbcOdbc MariaDB integration tests have been failed.
Exception in thread "main" java.lang.RuntimeException: The JdbcOdbc MariaDB integration tests have been failed.

boolean validateResultSetDeleteValues(List<BatchBean> listBatch)
{
boolean bSupportsUpdatable;
boolean result;
LogManager.getLogger(VerifyResultSetDelete.class).info("Testing validateResultSetDeleteValues...");

// Check if resultset delete currentrow supported
try

{ DatabaseMetaData metaData = m_Connection.getMetaData(); bSupportsUpdatable = metaData.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); }

catch(SQLException ex)

{ LogManager.getLogger(VerifyResultSetInsert.class).error("validateResultSetInsertValues supportsResultSetConcurrency failed E:" + ex.getMessage()); bSupportsUpdatable = false; }

if(bSupportsUpdatable) // <-- set to true
{
// TRUNCATE odbc38_batch_write then repopulate it
result = resetBaseData(listBatch);

if(result)
{
try(Statement statement = m_Connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)) // <-- my request is not honoured by MariaDB
{
try(ResultSet resultSet = statement.executeQuery("SELECT string_null from odbc38_batch_write where string_null = 'name 3' or string_null = 'name 5'"))
{
while(resultSet.next())

{ // Perform a ResultSet delete current row resultSet.deleteRow(); <-- fails here }

}
}
catch(SQLException ex)

{ LogManager.getLogger(VerifyResultSetDelete.class).error("validateResultSetDeleteValues failed E:" + ex.getMessage()); result = false; }

}
} else

{ LogManager.getLogger(VerifyResultSetDelete.class).error("validateResultSetDeleteValues failed, ResultSet.TYPE_SCROLL_INSENSITIVE ResultSet.CONCUR_UPDATABLE not supported"); result = false; // Not supported }

return result;
}

// m_Connection.createStatement from above runs this code
// JODBCDef.SQL_ATTR_CURSOR_TYPE = 6
// JODBCDef.SQL_CURSOR_DYNAMIC = 2

// drivers
if (m_rsConcurrency == ResultSet.CONCUR_UPDATABLE)

{ odbcCursorType = JODBCDef.SQL_CURSOR_DYNAMIC; // <-- executes this line }

// Set ODBC cursor type
try

{ setStmtOption(JODBCDef.SQL_ATTR_CURSOR_TYPE, odbcCursorType); <-- cursor type gets set to JODBCDef.SQL_CURSOR_DYNAMIC }

catch(SQLException ex) // No exception thrown
{
if (odbcCursorType != JODBCDef.SQL_CURSOR_FORWARD_ONLY)

{ throw ex; }
}

// Set ODBC cursor type
if (m_rsConcurrency == ResultSet.CONCUR_UPDATABLE)
{ odbcCursorType = JODBCDef.SQL_CURSOR_DYNAMIC; // 2 }

setStmtOption(JODBCDef.SQL_ATTR_CURSOR_TYPE, odbcCursorType); // SQL_ATTR_CURSOR_TYPE = 6, SQL_CURSOR_DYNAMIC = 2
SQLWarning: reason(SQLSetStatementAttrInteger #SQLSetStmtAttr I:hStatement=2177936450144, attribute=6, value=2 [ma-3.2.6][11.4.8-MariaDB-ubu2404]Option value changed to default (SQL_CURSOR_STATIC)) SQLState(01S02) vendor code(0)

// Set ODBC concurrency
//case ResultSet.CONCUR_UPDATABLE: // 1008
// return JODBCDef.SQL_CONCUR_LOCK; // 2
short odbcConcurrency = iConnection.getOdbcConcurrency(m_rsConcurrency); // m_rsConcurrency = 2 - SQL_CONCUR_LOCK

setStmtOption(JODBCDef.SQL_CONCURRENCY, odbcConcurrency); // SQL_CONCURRENCY = 7,
SQLWarning: reason(SQLSetStatementAttrInteger #SQLSetStmtAttr I:hStatement=2177936450144, attribute=7, value=2 [ma-3.2.6][11.4.8-MariaDB-ubu2404]Option value changed to default (SQL_CONCUR_READ_ONLY). ) SQLState(01S02) vendor code(0)

resultSet.deleteRow()) // <-- fails with the following :
java.sql.SQLException: SQLSetPosLong #SQLSetPos I:hStatement=2576641221216, row=1, operation=3, lockType=0 [ma-3.2.6][11.4.8-MariaDB-ubu2404]Can't build index for update/delete

/**
* Deletes the current row from this <code>ResultSet</code> object
* and from the underlying database. This method cannot be called when
* the cursor is on the insert row.
*
* @exception SQLException if a database access error occurs;
* the result set concurrency is <code>CONCUR_READ_ONLY</code>;
* this method is called on a closed result set
* or if this method is called when the cursor is on the insert row
* @exception SQLFeatureNotSupportedException if the JDBC driver does not support
* this method
*/
@Override
public void deleteRow() throws SQLException
{
String prefix = "*ResultSet.deleteRow";
if (JODBC.tracer.isTracing())
{ JODBC.tracer.trace(prefix); }

checkOpen(prefix);

if(m_bBlockCursor)
{ setPos(prefix, m_CurrentBlockCell, JODBCDef.SQL_DELETE); }
else
{
setPos(prefix, m_RowPosition, JODBCDef.SQL_DELETE); <-- Fails here
m_RowPosition--;
if(!m_bOwnDeletesAreVisible)
{ m_NumberOfRows--; }
}

if (m_bOwnDeletesAreVisible)
{ m_NumberOfRows--; }

}


Please note : This also occurs for resultSet.updateRow();

IB - Additional notes 21/04/2025

// SQL_CONCURRENCY options
public static final int SQL_CONCUR_READ_ONLY = (int)1;
public static final int SQL_CONCUR_LOCK = (int)2;
public static final int SQL_CONCUR_ROWVER = (int)3;
public static final int SQL_CONCUR_VALUES = (int)4;
public static final int SQL_CONCUR_DEFAULT = (int)SQL_CONCUR_READ_ONLY; // Default value


// Set ODBC concurrency
short odbcConcurrency = iConnection.getOdbcConcurrency(m_rsConcurrency); // <-- SQL_CONCUR_LOCK = 2
try
{ setStmtOption(JODBCDef.SQL_CONCURRENCY, odbcConcurrency); }
catch(SQLException ex)
{
if (odbcConcurrency != JODBCDef.SQL_CONCUR_READ_ONLY)
{ throw ex; }

}