7

I am getting this error

javax.servlet.ServletException: com.mysql.jdbc.NotUpdatable: Result Set not updatable.

I know this error is regarding the primary key but for all my tables I initially insert a primary key.So for this table also I have a primary key.I am posting part of my code.

Statement st=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs=st.executeQuery("Select * from test3 order by rand() limit 5");
List arrlist = new ArrayList();
while(rs.next()){
   String xa =rs.getString("display");
   if(xa.equals("1")){
      arrlist.add(rs.getString("question_text"));
   }
   rs.updateString("display", "0");
   rs.updateRow();

Just tell me if something is going wrong in this code.please help. This is my database

+----------------+---------------+------+-----+---------+----------------+
| Field          | Type          | Null | Key | Default | Extra          |
+----------------+---------------+------+-----+---------+----------------+
| id             | int(11)       | NO   | PRI | NULL    | auto_increment |
| index_question | varchar(45)   | YES  |     | NULL    |                |
| question_no    | varchar(10)   | YES  |     | NULL    |                |
| question_text  | varchar(1000) | YES  |     | NULL    |                |
| file_name      | varchar(128)  | YES  |     | NULL    |                |
| attachment     | mediumblob    | YES  |     | NULL    |                |
| display        | varchar(10)   | YES  |     | NULL    |                |
+----------------+---------------+------+-----+---------+----------------+
pnuts
  • 58,317
  • 11
  • 87
  • 139
Standin.Wolf
  • 1,224
  • 1
  • 10
  • 32
  • You did not show how you allocated the statement (`st`). You must set it updatable in the `createStatement` call. – Jim Garrison Oct 01 '13 at 06:46
  • 1
    That is also there Statement st=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE); – Standin.Wolf Oct 01 '13 at 06:48
  • Did you try adding the "FOR UPDATE" clause to the SELECT? – Jim Garrison Oct 01 '13 at 06:58
  • No,how do you do that? – Standin.Wolf Oct 01 '13 at 07:08
  • You read the [MySQL Manual description of SELECT](http://dev.mysql.com/doc/refman/5.5/en/select.html) and look for "FOR UPDATE" – Jim Garrison Oct 01 '13 at 07:11
  • Thanks for the link @JimGarrison but I need to update the row later not along with the select statement. – Standin.Wolf Oct 01 '13 at 07:16
  • 1
    Then you are confused about how updates work. Either you update the row immediately after you have fetched it (`FOR UPDATE` and `rs.updateRow()`, OR you write an `UPDATE tablename set ... where ...` statement to update a row at any time. – Jim Garrison Oct 01 '13 at 07:20
  • For transactional integrity, and when you need to read the row before updating it, you should announce this intention by including `FOR UPDATE` on the `SELECT`. That blocks anyone else from changing the row -- which might lead to the `UPDATE` not doing what you expect. – Rick James Apr 18 '15 at 18:15

2 Answers2

1

You have to update the row immediately after you have fetched it (FOR UPDATE and rs.updateRow(),

OR

you have to write an UPDATE tablename set = where statement to update a row at any time

logan
  • 7,946
  • 36
  • 114
  • 185
0

The query can not use functions. Try removing the "rand()" from the SQL query string.
See the JDBC 2.1 API Specification, section 5.6 for more details.

x.projekt
  • 507
  • 2
  • 7
  • 20