com.mysql.jdbc.NotUpdatable: Result Set not updatable

[Pankaja Shinde]

I am using MySQL 4.1.22, mysql connector 3.0.17 and java 1.5
While running this example, I got this runtime error.
com.mysql.jdbc.NotUpdatable: Result Set not updatable
Please help me.

My jdbc code

File name jdbcTutorial_7.java


import java.sql.*;

public class jdbcTutorial_7 {
public static void main(String args[]){
try {
// delets database that if it exists previously
Statement stmt;
Connection con;
Class.forName("com.mysql.jdbc.Driver");
String url ="jdbc:mysql://localhost:3306/mysql";
con =DriverManager.getConnection(url, "root", "pankaj"); // pankaj is password
stmt = con.createStatement();
stmt.executeUpdate("DROP DATABASE MyDB");
stmt.close();
con.close();
}catch( Exception e ) {
//e.printStackTrace();
}

try {
Statement stmt;
Connection con;
ResultSet rs;
Class.forName("com.mysql.jdbc.Driver");
String url ="jdbc:mysql://localhost:3306/mysql";
con =DriverManager.getConnection(url, "root", "pankaj");
stmt = con.createStatement();
stmt.executeUpdate("CREATE DATABASE MyDB");
url ="jdbc:mysql://localhost:3306/MyDB";
con =DriverManager.getConnection(url, "root", "pankaj");
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
String createTableCoffees = "CREATE TABLE COFFEES " +
"(COF_NAME VARCHAR(32), PRICE FLOAT)";
stmt.executeUpdate(createTableCoffees);
stmt.executeUpdate("INSERT INTO COFFEES VALUES ('Colombian', 7.99)");
stmt.executeUpdate("INSERT INTO COFFEES VALUES ('French_Roast', 8.99)");
rs = stmt.executeQuery("SELECT * from COFFEES");

// Display COFFEES table
System.out.println("Display COFFEES table:");
System.out.println(" COF_NAME" + "\t PRICE");
while(rs.next()){
String name = rs.getString("COF_NAME");
float price = rs.getFloat("PRICE");
System.out.println(" " + name + "\t " + price);
}

rs.last();
rs.updateFloat("PRICE", 10.99f);
rs.updateRow();

stmt.executeUpdate("DROP DATABASE MyDB");
stmt.close();
con.close();
}catch( Exception e ) {
e.printStackTrace();
}
}
}


Complete error message

com.mysql.jdbc.NotUpdatable: Result Set not updatable.This result set must come
from a statement that was created with a result set type of ResultSet.CONCUR_UPD
ATABLE, the query must select only one table, and must select all primary keys f
rom that table. See the JDBC 2.1 API Specification, section 5.6 for more details
.
at com.mysql.jdbc.UpdatableResultSet.generateStatements(UpdatableResultS
et.java:1849)
at com.mysql.jdbc.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:
1970)
at com.mysql.jdbc.UpdatableResultSet.updateFloat(UpdatableResultSet.java
:1327)
at com.mysql.jdbc.UpdatableResultSet.updateFloat(UpdatableResultSet.java
:1353)
at jdbcTutorial_7.main(jdbcTutorial_7.java:50)


Thanks

Pankaj Shinde
(SCJP)
[ January 18, 2008: Message edited by: Pankaja Shinde ]

[Pankaja Shinde]

I got the answer.

For JDBC 2.0 API to work with MySQL, we have to set primary key in the table.
Otherwise we will get exception as
com.mysql.jdbc.NotUpdatable: Result Set not updatable.

But JDBC 1.0 API will work Ok with MySQL without setting primary key.

This does not applies to MSAccess. As JDBC 1.0 API and 2.0 API work correctly with MS Access without setting primary key.

Pankaj Shinde