Geek Answers Handbook

Exception while working with CachedRowSet in JDBC exception "cannot be fixed during autodiscover"

I am trying to implement a simple graphical application for working with database records. It will contain the type of records and the ability to edit them. This is what my code looks like:

class MainPanel extends JPanel { private CachedRowSet crs; private List<JTextField> fields = new LinkedList<>(); MainPanel() { try { //Fill result set with data crs = new CachedRowSetImpl(); crs.setUrl("jdbc:postgresql:testdb"); crs.setUsername("username"); crs.setPassword("password"); crs.setCommand("SELECT * FROM products"); crs.execute(); //Create text fields with labels ResultSetMetaData meta = crs.getMetaData(); for (int a = 1; a <= meta.getColumnCount(); a++) { JTextField field = new JTextField(10); add(new JLabel(meta.getColumnLabel(a))); fields.add(field); add(field); } //Fill fields on startup crs.next(); updateFields(); //Buttons JButton nextButton = new JButton("Next"); nextButton.addActionListener(...); add(nextButton); JButton prevButton = new JButton("Previous"); prevButton.addActionListener(...); add(prevButton); JButton saveButton = new JButton("Save changes"); saveButton.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent event) { try { for (int a=0; a<fields.size(); a++) { crs.updateString(a+1, fields.get(a).getText()); } crs.acceptChanges(); } catch (SQLException ex) { ex.printStackTrace(); } } }); add(saveButton); } catch (SQLException ex) { ex.printStackTrace(); } } private void updateFields() throws SQLException { for (int a = 0; a < fields.size(); a++) { fields.get(a).setText(crs.getString(a + 1).trim()); } } }

The next and previous works correctly. But when I try to save the records, I get an exception. The stack trace is as follows:

 org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled. at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:705) at com.sun.rowset.internal.CachedRowSetWriter.commit(CachedRowSetWriter.java:1396) at com.sun.rowset.CachedRowSetImpl.acceptChanges(CachedRowSetImpl.java:893) at test.MainPanel$3.actionPerformed(MainPanel.java:85) at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018) ...

How can I prevent this exception? What is auto commit? I know that the Connection class has the option to set the value to false, but I do not want to use the Connection class. I want to do everything using a CachedResultSet. How can I fix this problem?

+4
source share
4 answers

Just add

crs.getConnection ().setAutoCommit (false);

+2
source

Add the following to the end of the URL :

 "?relaxAutoCommit=true"
+1
source

I had the same problem: CachedRowSetImpl.acceptChanges () does not disable autocommit, and when it switches to committing changes, an exception is thrown because they have already been executed individually.

Perhaps this is due to the fact that PostgreSQL is a bit picky about auto-negotiation. PostgreSQL does not allow you to disable autocommit through SQL queries (although this happened in the early days). Instead, any SQL statements that should be committed as a group should be sandwiched between the SQL BEGIN and COMMIT statements. The PostgreSQL JDBC driver supports Connection.setAutoCommit (false), but I bet it just motivates the driver to create BEGIN-COMMIT sandwiches behind the scenes. I assume that CachedRowSetImpl.acceptChanges () is trying to use SQL statements to disable autocommit instead of using setAutoCommit (false) and why it fails.

The only way to find this problem is to create a temporary connection, disconnect the autocommit, and transfer the connection with one version of acceptChanges. So instead:

 crs.acceptChanges()

you will have something like this:

 try (Connection con = DriverManager.getConnection(url, username, password)) { con.setAutoCommit(false); crs.acceptChanges(con); }

This requires using the Connection class, which you tried to avoid, but there can be no alternative (except for using JDBC). You cannot use CachedRowSetImpl.getConnection () because it only retrieves the connection that was created from the outside and then transferred. You cannot specify through the URL that you want the auto command to be disabled, since PostgreSQL does not allow this. It would be great if CachedRowSetImpl.acceptChanges () could be updated so that it automatically turns off auto-reporting. However, this seems unlikely as my compiler warns that CachedRowSetImpl may be removed in a future version.

0
source

I had similar problems when working with CachedRowset. I am using a MySQL database . trying to find many solutions. Finally, I came up with two workarounds that worked for me. I would like to mention them here:

  • Using the Connection object and pass them to the execute(con) and acceptChanges(con) methods. Also set setAutoCommit(false) . Example:

     Connection conn = DriverManager.getConnection(databaseUrl, username, password); conn.setAutoCommit(false); CachedRowSet rowSet=new CachedRowSetImpl(); rowSet.setCommand("SELECT * FROM tableName"); int [] keys = {1}; // Set column 1 as the key column in the RowSet rowSet.setKeyColumns(keys); rowSet.execute(conn); // Execute on connection rowSet.moveToInsertRow(); rowSet.updateInt(1, 89); // Use column number rowSet.updateString(2, "Programming"); rowSet.updateInt(3, 77); rowSet.insertRow(); // Need to move away from insert row before apply changes rowSet.moveToCurrentRow(); // Reconnect to data source to apply change in the RowSet. rowSet.acceptChanges(conn); // On non-autocommit Connection

Note. In this case, you do not need to set the CachedRowset properties, such as setURL() , setUser() and setPassword() . Because the CachedRowset object uses the Connection object to connect to a manually created database and using autocommit=false

  1. Add ?relaxAutoCommit=true to the URL as indicated by bobby-paulose

     Example: String url = "jdbc:mysql://localhost:3306/DatabaseName?relaxAutoCommit=true";
0
source

More articles:


All Articles