MySQL Bugs: #8527: ResultSet.CONCUR_UPDATABLE not being set

Bug #8527	ResultSet.CONCUR_UPDATABLE not being set
Submitted:	15 Feb 2005 23:11	Modified:	16 Feb 2005 21:07
Reporter:	Neil Tipper	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	mysql-connector-java-3.1.6	OS:	Windows (Windows XP)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
(Using MySQL 4.1) ResultSet.CONCUR_UPDATABLE is not being set. A call to getConcurrency returns CONCUR_READ_ONLY. Generates the following  SQLException when updates are called:

"SQLException: Result Set not updatable.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details."

... even though the statement is created with a valid Connection object:

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test2?user=test&password=test");
stmt = con.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);

How to repeat:
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test2?user=test&password=test");
        stmt = con.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
        ResultSet uprs = stmtUP.executeQuery("SELECT COF_NAME, SUP_ID, PRICE, SALES, TOTAL FROM COFFEES");
        
        int concurr = uprs.getConcurrency();
        if (concurr == java.sql.ResultSet.CONCUR_UPDATABLE){
            System.out.println("ResultSet is CONCUR_UPDATABLE");
        } else if ( concurr == java.sql.ResultSet.CONCUR_READ_ONLY){
            System.out.println("ResultSet is CONCUR_READ_ONLY");
        }
        // returns CONCUR_READ_ONLY
        // and fails before the next operation can be performed.
        uprs.moveToInsertRow();
        uprs.updateString("COF_NAME", "Kona");
// etc.

Suggested fix:
No fix. Have to create a normal read only ResultSet and close it when you want to make updates. Then create a new ResultSet.

JDBC drivers are not required to have all result sets be updatable (the list of requirements for updatability include but are not limited to the table in question having a primary key, and the query selecting all primary key components for the table).

As far as I'm aware, most major JDBC vendors do the same thing, i.e. you won't get an error until you actually try and call something that updates the result set.

I'm not quite sure what you're claiming as a bug here? Is it the fact that you expect the result set to be updatable, but it isn't? If so, does it meet all the requirements for updatability as the exception asks?

Hi Mark

yes I expected the result set to be updatable, but it isn't and it 
does meet all the requirements for updatability as the exception
asks. So far I haven't found any reason as to why an updatable 
ResultSet shouldn't be returned so I'm assuming this is a bug.

Neil,

What's the DDL for the table in question. I couldn't tell you whether it meets the requirements without seeing that first.

Hi Mark

Here are the statements used to create the table (the dbase is of type InnoDB):

// String for DDL Statement to create the table
        String createTableCoffees = "CREATE TABLE COFFEES " +
            "(COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, " +
            "SALES INTEGER, TOTAL INTEGER)";
           
        try { 
            Class.forName("com.mysql.jdbc.Driver").newInstance(); 
         } catch (Exception ex) { 
            System.out.println("MySQL JDBC Driver not found ... ");
            System.out.println("Exception : " + ex.getMessage());
            System.exit(1);
        }

        try {
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test2?user=test&password=test");

            stmt = conn.createStatement();

// DDL statement string sent here
            stmt.executeUpdate(createTableCoffees);

// Table is populated here
            stmt.executeUpdate("INSERT INTO COFFEES " + 
                "VALUES ('Colombian', 101, 7.99, 0, 0)");
            stmt.executeUpdate("INSERT INTO COFFEES " +
                "VALUES ('French_Roast', 49, 8.99, 0, 0)");
            stmt.executeUpdate("INSERT INTO COFFEES " +
                "VALUES ('Espresso', 150, 9.99, 0, 0)");
            stmt.executeUpdate("INSERT INTO COFFEES " +
                "VALUES ('Colombian_Decaf', 101, 8.99, 0, 0)");
            stmt.executeUpdate("INSERT INTO COFFEES " +
                "VALUES ('French_Roast_Decaf', 49, 9.99, 0, 0)");
            stmt.close();
            conn.close();
        } catch (SQLException sqlex) {
            System.err.println("SQLException: " + sqlex.getMessage());
        }

> String createTableCoffees = "CREATE TABLE COFFEES " +
>           "(COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, " +
>            "SALES INTEGER, TOTAL INTEGER)";

Notice, no primary key in this DDL, which is a requirement for updatable result sets. You probably want to add one, however the only likely candidate is COF_NAME, and 'names' are not usually used as a primary key..I assume you're following Sun's tutorial, which happens to leave out a primary key, so in the interest of remaining 'true' to the tutorial (but maybe not so to RDBMS theory), you can get this working by changing your DDL to the following:

CREATE TABLE COFFEES
         (COF_NAME VARCHAR(32) NOT NULL PRIMARY KEY, 
          SUP_ID INTEGER, 
          PRICE FLOAT, 
          SALES INTEGER, 
          TOTAL INTEGER)

Thanks for the help Mark. I am following Sun's JDBC tutorial, which says that COF_NAME is the primary key (and neglects to mention that it needs to be explicitly set). Sorry for thinking this was a bug in connector/J