I want to update raw in my ResultSet while I looping through the result set. Following is my code
try {
String query="SELECT * FROM smsmessage WHERE recipient = ? and sent_status = 'pending' LIMIT ? ";
PreparedStatement prepStmt = conn.prepareStatement(query);
prepStmt.setString(1,shortCode);
prepStmt.setInt(2, Integer.parseInt(batchSize));
ResultSet rs=prepStmt.executeQuery();
while (rs.next()) {
//update the selected message sent status to "sent" from "pending"
rs.updateString("sent_status","sent");
rs.updateRow();
}
} catch (SQLException e) {
log.error("MySQL exception",e);
}
What should be the possible reason for this?
I am getting following error
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_UPDATABLE, the query must select only one table, can
not use functions and must select all primary keys from that table.
See the JDBC 2.1 API Specification, section 5.6 for more details.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, can not use functions and must select all primary keys from
that table. See the JDBC 2.1 API Specification, section 5.6 for more
details.
As the stacktrace tells, you have to create a statement that allows its resultset to be updateable:
PreparedStatement prepStmt= conn.prepareStatement(query,
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
From the API of ResultSet (http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html):
A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. See ResultSet fields for other options.
Statement stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");
// rs will be scrollable, will not show changes made by others,
// and will be updatable
Well, you should start by carefully reading the error text:
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_UPDATABLE, the query must select only one
table, can not use functions and must select all primary keys from
that table. See the JDBC 2.1 API Specification, section 5.6 for more
details.
This means that the result set is not updateable. You can't update a result set that is not updateable.
It also says that this result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE. This means that your statement - in this case a PreparedStatement, must be created with that option.
Finally, it directs you to the documentation. JDBC 2.1 is a bit outdated, and you can find all the relevant data in the usual J2SE Documentation. Let's start from the documentation for ResultSet. It says:
A default ResultSet object is not updatable and has a cursor that
moves forward only. Thus, you can iterate through it only once and
only from the first row to the last row. It is possible to produce
ResultSet objects that are scrollable and/or updatable. The following
code fragment, in which con is a valid Connection object, illustrates
how to make a result set that is scrollable and insensitive to updates
by others, and that is updatable. See ResultSet fields for other
options.
And the code fragment it shows is:
Statement stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");
// rs will be scrollable, will not show changes made by others,
// and will be updatable
So you see, they are using a statement where you add two parameters, that enable you to scroll through the data and update it. But they are using a Statement rather than a PreparedStatement. Is this applicabe to PreparedStatement as well?
Going to the PreparedStatement documentation will not help you much, but wait, you are using the connection object to prepare the statement, perhaps it will help you?
Yes, indeed, there is a method in Connection that allows you to pass the parameters, just like the Statement in the example.
PreparedStatement prepareStatement(String sql,
int resultSetType,
int resultSetConcurrency)
throws SQLException
So now you must ask yourself, what kind of resultSetType do I need, and what kind of resultSetConcurrency?
Scrolling your statement is not necessary for your current question, so you can use the default. If you look at the documentation of the plain preparedStatement(String) you'll see:
Result sets created using the returned PreparedStatement object will
by default be type TYPE_FORWARD_ONLY and have a concurrency level of
CONCUR_READ_ONLY. The holdability of the created result sets can be
determined by calling getHoldability().
...which is why your initial prepared statement was not updatable, by the way. But anyway, it tells you that the default for the scrolling type is TYPE_FORWARD_ONLY.
What about the updating, which is the important part? Well, the options are CONCUR_READ_ONLY and CONCUR_UPDATABLE. So you need to use the second one. That's what the error message told you, after all.
Conclusion:
You need to use
PreparedStatement prepStmt = conn.prepareStatement(
query,
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
This will give you an updatable result set.
And this is how to find your answer from the error message that you get.
