JAVA Writing to Access database and retrieving and index

Question

I'm writing data from Java to an Access database on Windows 32 bit. When I write a record, I need to retrieve the row ID / primary key so that I can a) update the record easily if I want to and b) cross reference other data to that record.

When did something similar in C, I could make a updatable cursor which allowed me to write a new record and simultaneously retrieve the row ID. With Java, it looks as though I should be able to do this, but it throws an exception with the following code.

    con = openAccessDatabase();
    String selectString = "SELECT ID, RunCount FROM SpeedTable";
    try {
        PreparedStatement selectStatement = con.prepareStatement(selectString, 
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        ResultSet idResult = selectStatement.executeQuery();
        int id;
        for (int i = 0; i < nWrites; i++) {
            idResult.moveToInsertRow();
            idResult.updateObject(1, null); // this line makes no difference whatsoever !
            idResult.updateInt(2, i);
            idResult.insertRow(); // throws java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Error in row
            id = idResult.getInt(1);
        }
        selectStatement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

The only thing I've been able to do is to write a new record and then run a different query to get the Row id back ...

    String insertString = "INSERT INTO SpeedTable (RunCount) VALUES (?)";
    String idString = "SELECT ID FROM SpeedTable ORDER BY ID DESC";
    //      
    try {
        ResultSet idResult = null;
        PreparedStatement preparedStatement, idStatement;
        preparedStatement = con.prepareStatement(insertString,
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        idStatement = con.prepareStatement(idString, 
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);

        for (int i = 0; i < nWrites; i++) {
            // write the data into the database
            preparedStatement.setInt(1, i);
            preparedStatement.execute();
            // re-run the query to get the index back from the database. 
            idResult = idStatement.executeQuery();
            idResult.next();
            int lastIndex = idResult.getInt(1);
            idResult.close();
        }

This works but becomes impossibly slow when the table has more than a few 10's of 1000's of records in it. There is also a risk of returning the wrong ID if two parts of the program start writing at the same time (unlikely but not impossible).

I know that at least one suggestion will be to either not use Java or not use Access, but they are not options. It's also part of a free open source software package, so I'm reluctant to pay for anything. Writing my own C JNI interface which provides the basic functionality that I need for my application is even less appealing.

You should be able to select @@identity against the connection instance. — Fionnuala
– Fionnuala, Commented Nov 22, 2012 at 20:33

Evgeniy Dorofeev · Accepted Answer · 2012-11-30 09:20:21Z

0

Not sure if this works for MS Access but you can try:

st.executeUpdate("INSERT INTO SpeedTable (RunCount) VALUES (1000)", Statement.RETURN_GENERATED_KEYS);
ResultSet rs = st.getGeneratedKeys();
rs.next();
long id = rs.getLong(1);

edited Nov 30, 2012 at 9:20

answered Nov 23, 2012 at 6:26

Evgeniy Dorofeev

137k31 gold badges209 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Doug Gillespie Over a year ago

Thanks. It doesn't work with MS Access. Further reading indicates that there isn't a solution to this. I'll work around, probably by reading the index once and then keeping an internal counter, except in instances when the index is extremely critical.

Collectives™ on Stack Overflow

JAVA Writing to Access database and retrieving and index

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related