Bug #4713 fail to insert a row in scrollable result set

• Submitted: 23 Jul 2004 2:25
• Modified: 3 Aug 2004 4:50
• Reporter: S Srinivas
• Status: Not a Bug
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 3.0.14
• OS: Windows (xp)
• Assigned to: Matthew Lord
• CPU Architecture: Any

[23 Jul 2004 2:25] S Srinivas

Description:
hi, i was following the tutorial of using scrollable result set as defined in jdbc 2.0 api. unfortunately, i get the following error

mySQL  4.0.20a-debug
connector/J 3.0.14

     [java] com.mysql.jdbc.NotUpdatable: Result Set not updatable.This result se
t must come from a statement that was created with a result set type of ResultSe
t.CONCUR_UPDATABLE, the query must select only one table, and must select all pr
imary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for
more details.
     [java]     at com.mysql.jdbc.UpdatableResultSet.moveToInsertRow(UpdatableRe
sultSet.java:507)
     [java]     at LoadDriver.insertJDBC2Way(Unknown Source)
     [java]     at LoadDriver.main(Unknown Source)
     [java]     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     [java]     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcces
sorImpl.java:39)
     [java]     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMet
hodAccessorImpl.java:25)
     [java]     at java.lang.reflect.Method.invoke(Method.java:324)
     [java]     at org.apache.tools.ant.taskdefs.ExecuteJava.run(ExecuteJava.jav
a:208)
     [java]     at org.apache.tools.ant.taskdefs.ExecuteJava.execute(ExecuteJava
.java:150)
     [java]     at org.apache.tools.ant.taskdefs.Java.run(Java.java:443)
     [java]     at org.apache.tools.ant.taskdefs.Java.executeJava(Java.java:163)

     [java]     at org.apache.tools.ant.taskdefs.Java.execute(Java.java:108)
     [java]     at org.apache.tools.ant.Task.perform(Task.java:341)
     [java]     at org.apache.tools.ant.Target.execute(Target.java:309)
     [java]     at org.apache.tools.ant.Target.performTasks(Target.java:336)
     [java]     at org.apache.tools.ant.Project.executeTarget(Project.java:1339)

     [java]     at org.apache.tools.ant.Project.executeTargets(Project.java:1255
)
     [java]     at org.apache.tools.ant.Main.runBuild(Main.java:609)
     [java]     at org.apache.tools.ant.Main.start(Main.java:196)
     [java]     at org.apache.tools.ant.Main.main(Main.java:235)

How to repeat:
execute the following code

import java.sql.*;

// Notice, do not import com.mysql.jdbc.* 
// or you will have problems! 

public class LoadDriver 
{ 

    private Connection _con;
    

    public LoadDriver()
    {
    }

    public static void main(String[] args) 
    { 
	try
	{
	    LoadDriver d = new LoadDriver();
	    d.openDB();
	    d.createCoffeeTable();
	    d.clearCoffeeTable();
	    d.insertIntoCoffeeTable();
	    d.getDataFromCoffeeTable();
	    d.updateCoffeeTable();
	    d.getDataFromCoffeeTable();
	    System.out.println("********************");
	    d.insertJDBC2Way();
	    d.closeDB();
	}
	catch (Exception e)
	{
		e.printStackTrace();
	}
    }

    private void openDB()
	    throws SQLException
    {
        try 
	{ 
            Class.forName("com.mysql.jdbc.Driver").newInstance(); 
	    System.out.println("done initializing");
            _con = DriverManager.getConnection(
		    "jdbc:mysql://localhost/sudhir_db",
                     "root", "");
	    System.out.println("done connecting");
        } 
	catch (Exception ex) 
	{ 
            // handle the error 
	    //
	    ex.printStackTrace();
        }
    }

    private void closeDB()
	    throws SQLException
    {
        _con.close();
    }
    
    private void createCoffeeTable()
	    throws SQLException
    {
	try
	{
	    String createTableCoffees = "CREATE TABLE coff2 " +
	    "(COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, " +
	    "SALES INTEGER, TOTAL INTEGER)";
            Statement stmt = _con.createStatement();
            stmt.executeUpdate(createTableCoffees);
	}
	catch (Exception e)
	{
	}
    }

    private void clearCoffeeTable()
	    throws SQLException
    {
	String createTableCoffees = "DELETE FROM coff2";
	Statement stmt = _con.createStatement();
	stmt.executeUpdate(createTableCoffees);

    }

    private void insertIntoCoffeeTable()
	    throws SQLException
    {
	Statement stmt = _con.createStatement();
	stmt.executeUpdate(
	    "INSERT INTO coff2 " + 
	    "VALUES ('Colombian', 101, 7.99, 0, 0)");
	stmt.executeUpdate("INSERT INTO coff2 " +
	    "VALUES ('French_Roast', 49, 8.99, 0, 0)");
	stmt.executeUpdate("INSERT INTO coff2 " +
	    "VALUES ('Espresso', 150, 9.99, 0, 0)");
	stmt.executeUpdate("INSERT INTO coff2 " +
	    "VALUES ('Colombian_Decaf', 101, 8.99, 0, 0)");
	stmt.executeUpdate("INSERT INTO coff2 " +
	    "VALUES ('French_Roast_Decaf', 49, 9.99, 0, 0)");
        
    }

    private void getDataFromCoffeeTable()
	    throws SQLException
    {
	Statement stmt = _con.createStatement();

	String query = "SELECT COF_NAME, SALES FROM coff2";
	ResultSet rs = stmt.executeQuery(query);
	while (rs.next())
	{
	    String s = rs.getString("COF_NAME");
	    float n = rs.getInt("SALES");
	    System.out.println(s + "   " + n);
	}
    }

    private void updateCoffeeTable()
	    throws SQLException
    {
	PreparedStatement updateSales;
	String updateString = "update coff2 " +
	  "set SALES = ? where COF_NAME like ?";
	updateSales = _con.prepareStatement(updateString);
	int [] salesForWeek = {175, 150, 60, 155, 90};
	String [] coffees = {"Colombian", "French_Roast", "Espresso",
	     "Colombian_Decaf", "French_Roast_Decaf"};
	int len = coffees.length;
	for(int i = 0; i < len; i++)
	{	
		updateSales.setInt(1, salesForWeek[i]);
		updateSales.setString(2, coffees[i]);
		updateSales.executeUpdate();
	}
    }

    private void insertJDBC2Way()
	    throws SQLException
    {	
        Statement stmt = _con.createStatement(
		ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        ResultSet uprs = stmt.executeQuery("SELECT * FROM coff2");
	while (uprs.next())
	{
	    String s = uprs.getString("COF_NAME");
	    float n = uprs.getInt("SALES");
	    System.out.println(s + "   " + n);
	}
	uprs.moveToInsertRow();
        uprs.updateString("COF_NAME", "Kona");
        uprs.updateInt("SUP_ID", 150);
        uprs.updateFloat("PRICE", 10.99f);
        uprs.updateInt("SALES", 0);
        uprs.updateInt("TOTAL", 0);
        uprs.insertRow();
        uprs.updateString("COF_NAME", "Kona_Decaf");
        uprs.updateInt("SUP_ID", 150);
        uprs.updateFloat("PRICE", 11.99f);
        uprs.updateInt("SALES", 0);
        uprs.updateInt("TOTAL", 0);
        uprs.insertRow();
        uprs.beforeFirst();
        System.out.println("Table coff2 after insertion:");
	getDataFromCoffeeTable();
	    
    }

}

[3 Aug 2004 4:18] Matthew Lord

Hi,

Thank you for your bug report!

This is documented behavior as mysql does not currently support
server side cursors.  Multiple read-only cursor support has just been
merged into the 5.0 tree but updatable cursors may not be in mysql until 5.1 or later.

Best Regards

[3 Aug 2004 4:50] Mark Matthews

Just to add some information. Connector/J _does_ support scrollable result sets, and inserting rows, but the result set must meet the restrictions that the exception states (which actually come from the JDBC spec), which are:

1.) The query producing the result set must only reference one table.
2.) The table must have at least _one_ column declared as a primary key
3.) The query producing the result set must reference _all_ columns that comprise the primary key.

Without seeing your schema, my guess is that your query fails either #2 or #3.