1

Is there any other way to properly close the connection to ms access database at the end of the infinite loop? Because if a record is inserted into the table, with my code below I can't see the new row/rows inserted. It looks like that the database is not closing corectly or something...no idea why it is acting like this. If I manually close or open the database (with my program still running in the background) everything is ok - the new row/rows will appear in my query.

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {

    public static void main(String[] args) throws RemoteException,
            InterruptedException, SQLException {
        while (true) {
            Connection con = DBConnection.getDBConnection();
            System.out.println("Connection OK!");
            Statement s = null;

            try {
                    ResultSet rs = null;
                    con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
                    con.setAutoCommit(true);
                    rs = s.executeQuery("SELECT Tel, Msg, Procesat FROM RcvMsg WHERE Procesat = 'NOK'");

                    while (rs.next()) 
                    {
                        String pn = rs.getString(1);
                        String str = rs.getString(2);
                        //do something
                    }
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    } finally {
                        s.close();
                        con.close();
                    }   

            Thread.sleep(5000);
        }
    }
}
3
  • What are you trying to do at '// do something'?? Commented Mar 18, 2015 at 12:55
  • Nothing relevant. Let's say - printing something. The point is that my program can't see new inserts while the program is running - just when closing/opening database manually. Commented Mar 18, 2015 at 13:09
  • I am unable to recreate your issue using UCanAccess 2.0.9.4. My test code is here. When I add a new row from another application (VBScript using OLEDB) while my Java app is running it displays the new row on the next iteration through the while (true) loop. If you are using an older version of UCanAccess then try upgrading to the latest version. Commented Mar 18, 2015 at 14:57

4 Answers 4

1

The code you're showing is correct, but totally irrelevant for you problem because you are just reading. How do you insert the records? Manually with Access, so using another process(different from the java process) ? In this case, ucanaccess will be able to read those new data when they are phisically stored on the access file (and it may happen at the closure of a table in access and always when you close the database).

Also, if you inserted the data with another thread(different from that one in polling), you would see all records inserted (without any apparent delay problem).

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

Comments

0

If you set AutoCommit to false (thus disabling automatically committing) you should call Connection.commit() manually, otherwise nothing will ever end up permanently in the database.

1 Comment

I set autocommit to true, still no changes :(
0

You are setting Auto Commit false. Set it to true. Then your changes will reflect permanently or by default, new connections are in auto-commit mode so you can remove that line.

5 Comments

I updated my code and tried it...still the same, the program won't see new rows. I updated also my code in the question.
Can you update the code with your insert query which you are using??
Or you can try to create the statement like this con.createStatement();
I'm not inserting, some 3rd party is inserting new row/rows. I tried also with con.createStatement(); still the same, I can't figure out this ms access. In the past I tried some similar code on an oracle database. It worked like a charm...
Ah, ok. Have you tried adding a finally block after the catch part in which you call con.close() (like in the catch block)?
0

try to close all the open tables and queries in the MSAccess client and click on "Refresh All" and then check if the data is reflecting

1 Comment

Welcome to Stack Overflow! This was posted as an answer, but it does not attempt to answer the question. It should possibly be an edit or a comment.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.