Unable to update multiple rows in access help!!!!!

[Alfonso Pisani]

I'm trying to update multiple rows i a Microsoft access database using ucanaccess in Java. Problem is that after updating the first row nothing else happens as if cursor were closed. Here is my code:
Connection con = DriverManager.getConnection("jdbc:ucanaccess://"+ filepath);
String qry = "SELECT * FROM Elenco_Aziende";

Statement stmtautisti = on.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE,ResultSet.HOLD_CURSORS_OVER_COMMIT);

String qryautisti = "SELECT * FROM Elenco_Autisti";
ResultSet rsautisti = stmtautisti.executeQuery(qryautisti);

while (rsautisti.next()) {
rsautisti.updateString("Problemi", "Nessuno");
rsautisti.updateRow();
}

after executing just the first row is updated...is this a known bug with ucanaccess or something else? Please HELP ME!!!!!

[Gord Thompson]

I was able to recreate the issue under UCanAccess 3.0.1 with the following code

package com.example.SO32907205;

import java.io.File;
import java.sql.*;

public class SO32907205 {

    public static void main(String[] args) {
        String dbFileSpec = "C:/Users/Public/test/SO32907205.accdb";
        String sql;
        sql = "SELECT ID, Problemi FROM Elenco_Autisti";
    //  sql = "SELECT ID, Problemi FROM Elenco_Autisti ORDER BY ID";

        (new File(dbFileSpec)).delete();
        try (Connection conn = DriverManager.getConnection("jdbc:ucanaccess://" + dbFileSpec + ";newdatabaseversion=V2010")) {
            // setup
            try (Statement st = conn.createStatement()) {
                st.execute("CREATE TABLE Elenco_Autisti (ID INT PRIMARY KEY, Problemi TEXT(50))");
                st.execute("INSERT INTO Elenco_Autisti (ID, Problemi) VALUES (1, 'foo')");
                st.execute("INSERT INTO Elenco_Autisti (ID, Problemi) VALUES (2, 'bar')");
            }

            // test
            try (
                    Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
                    ResultSet rs = st.executeQuery(sql)) {
                System.out.println("ResultSet opened");
                System.out.println(String.format("rs.next() returned %s", rs.next()));
                System.out.println(String.format("[Problemi] is currently '%s'", rs.getString("Problemi")));
                rs.updateString("Problemi", "newValue");
                rs.updateRow();
                System.out.println("updateRow() completed");
                System.out.println(String.format("rs.next() returned %s", rs.next()));
            }
        } catch (Exception e) {
            e.printStackTrace(System.err);
        }
    }

}

 
When I run the code as above I see ...

ResultSet opened
rs.next() returned true
[Problemi] is currently 'foo'
updateRow() completed
rs.next() returned false

 
If I uncomment the second "sql = " statement to add the ORDER BY clause then the rs.updateString line triggers the following exception

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.1 attempt to assign to non-updatable column

 
which is actually thrown by HSQLDB.

[Marco Amadei]

Gord, I did your same test and now I can reproduce the issue. I'll let you know my findings asap(I'm very tires in this very moment, I think I'll analyze that tomorrow)

[Marco Amadei]

Ok, it works fine if you put conn.setAutoCommit(false); at the start(before executing the query) and a conn.commit(); at the end. Not energy enough to explain.

[Gord Thompson]

Thanks, Marco. I can confirm that turning off AutoCommit does fix the original issue. That is, the following code works under UCanAccess 3.0.1:

package com.example.SO32907205;

import java.io.File;
import java.sql.*;

public class SO32907205 {

    public static void main(String[] args) {
        String dbFileSpec = "C:/Users/Public/test/SO32907205.accdb";
        String sql;
        sql = "SELECT ID, Problemi FROM Elenco_Autisti";
    //  sql = "SELECT ID, Problemi FROM Elenco_Autisti ORDER BY ID";

        (new File(dbFileSpec)).delete();
        try (Connection conn = DriverManager.getConnection("jdbc:ucanaccess://" + dbFileSpec + ";newdatabaseversion=V2010")) {
            // setup
            try (Statement st = conn.createStatement()) {
                st.execute("CREATE TABLE Elenco_Autisti (ID INT PRIMARY KEY, Problemi TEXT(50))");
                st.execute("INSERT INTO Elenco_Autisti (ID, Problemi) VALUES (1, 'foo')");
                st.execute("INSERT INTO Elenco_Autisti (ID, Problemi) VALUES (2, 'bar')");
            }

            // test
            conn.setAutoCommit(false);
            try (
                    Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
                    ResultSet rs = st.executeQuery(sql)) {
                System.out.println("ResultSet opened");
                System.out.println(String.format("rs.next() returned %s", rs.next()));
                System.out.println(String.format("[Problemi] is currently '%s'", rs.getString("Problemi")));
                rs.updateString("Problemi", "newValue");
                rs.updateRow();
                System.out.println("updateRow() completed");
                System.out.println(String.format("rs.next() returned %s", rs.next()));
            }
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace(System.err);
        }
    }

}

 
By "works" I mean that the second rs.next() returns true, indicating that we can continue to loop through the remaining rows in the ResultSet.

Presumably AutoCommit must be disabled because, according to the HSQLDB documentation here ...

http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_jdbc_autocommit

In autocommit mode, all ResultSet objects are read-only and holdable.

 
However, even with AutoCommit disabled I still get the "attempt to assign to non-updatable column" exception if I use the SELECT statement with the ORDER BY clause.

[Alfonso Pisani]

Hello guys thanks I'm waiting for extra news if any. Anyhow for now I solved by doing this way:
each time I find a row to update I:

1)store in a local variable its key and
2)declare a new connection let' s call it con1 and a new statement let's call it update and then update the row using the key value stored to point at it calling execupdate method

Connection con1 = DriverManager.getConnection("jdbc:ucanaccess://"
                                            + filepath);

Statement update = con1.createStatement();
sql1 = "UPDATE Elenco_Autisti SET Problemi ='Something'" + "WHERE key ='key value I stored in a local variable';                                
update.executeUpdate(sql1);

really overkilling, but works...do not remember if it works if I try to use the same connection used for the query without declaring a new one...

[Marco Amadei]

Accordingly with the Gord's answer, you don't need any workaround but just put a con.setAutoCommit(false) before executing the query and a con.commit() after the loop. In this way you'll be able to do what you originally wanted.