I'm retrieving a list of names from MySql database in Java using JDBC with the help of CachedRowSet.
After retrieving the list I'm appending a counter value to every name.
Finally I'm updating the modified names in the database.


I'm using:
MySql: 5.1
Connector/J 5.1.7
JRE 1.6
Win Xp SP3


here is the code:

import java.sql.*;
import javax.sql.*;
import javax.sql.rowset.*;
import com.sun.rowset.CachedRowSetImpl;

public class CRSTest
{
	public static void main(String a[])
	{
		Connection con;
		CachedRowSet crs;
		int keys[]={1};
		int ctr=0;
		String name;
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/bri", "username", "password");
			
			crs = new CachedRowSetImpl();
			crs.setKeyColumns(keys);
			crs.setCommand("SELECT names FROM test");
			crs.execute(con);
			
			while(crs.next())
			{
				ctr++;
				name=crs.getString(1);
				System.out.println("Name: "+name);
				name=name+ctr;
				crs.updateString(1,name);
				crs.updateRow();				
			}
			crs.acceptChanges(con);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
}

On executing the above code, its displaying the names, updating the names in the database and then showing the following exception

java.sql.SQLException: Can't call commit when autocommit=true
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:930)
        at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1552)
        at com.sun.rowset.internal.CachedRowSetWriter.commit(Unknown Source)
        at com.sun.rowset.CachedRowSetImpl.acceptChanges(Unknown Source)
        at com.core.CRSTest.main(CRSTest.java:35)
javax.sql.rowset.spi.SyncProviderException: Can't call commit when autocommit=true
        at com.sun.rowset.CachedRowSetImpl.acceptChanges(Unknown Source)
        at com.core.CRSTest.main(CRSTest.java:35)

The values are updated in the database

I'm unable to understand the reason for this exception.
I a solution to avoid this exception.

autocommit is true by default.

a method at line 35 tries to call a commit on the db state.

hence the exception.

autocommit is true by default.

a method at line 35 tries to call a commit on the db state.

hence the exception.

Is there any way to set "autocommit" to false ?
Or any other way to avoid this exception ?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.