• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

exception in updatable ResultSet

 
Puspender Tanwar
Ranch Hand
Posts: 393
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

while executing this code i am getting an error at runtime: com.mysql.jdbc.NotUpdatable: Result Set not updatable (referenced table has no primary keys).This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE.........
but i have made my ResultSet updatable at //line1. Then why am i getting this error.
one more thing, what is this, an error or an exception ?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Puspender Tanwar wrote:com.mysql.jdbc.NotUpdatable: Result Set not updatable (referenced table has no primary keys).

You can only update a table using a ResultSet if each row can be unique identified using a primary key. Because that seems not to be the case here, you'll get an exception at runtime. You iterate over each row to update it accordingly in the database, but this only works if you can identify the row in the database (using a primary key) because the database might contain 10 rows with name "puspen". And without a primary key it's impossible to know which row to update. And you might need to add the FOR UPDATE clause to the SELECT statement as well (more info can be found here).

Puspender Tanwar wrote:one more thing, what is this, an error or an exception ?

In English there is no difference between error and exception, both indicate an exceptional case. In Java there's a difference between Error and Exception. And com.mysql.jdbc.NotUpdatable is an java.sql.SQLException and thus java.lang.Exception.

Hope it helps!
Kind regards,
Roel
 
Puspender Tanwar
Ranch Hand
Posts: 393
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
K&B book have explained this concept and they have not specified any such points as by you.
slightly difference in my code and book's code: the column and the query
select unitPrice from Book where Format-'hardcover'
unitPrive too can't be taken as a primary key as many books can have the same price. Rest or the code is same
Has the book explained it incorrectly ?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Puspender Tanwar wrote:select unitPrice from Book where Format-'hardcover'
unitPrive too can't be taken as a primary key as many books can have the same price. Rest or the code is same
Has the book explained it incorrectly ?

That doesn't mean that not every record has a primary key. The book table might have an ISBN column which is defined as primary key...
 
Puspender Tanwar
Ranch Hand
Posts: 393
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
means it doesn't matter which column is the primary key ?
and does making a primary key in my table will solve my problem ??
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Puspender Tanwar wrote:means it doesn't matter which column is the primary key ?
and does making a primary key in my table will solve my problem ??

You definitely need a primary key in your table. Of course it doesn't matter if it's an id (integer) or some code (string). The only requirement is that it's a primary key and thus unique for every row.

If this column is not used in the SELECT statement, it will be used behind the scenes by the JDBC driver to uniquely identify the database record with the ResultSet row.
 
Puspender Tanwar
Ranch Hand
Posts: 393
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Puspender Tanwar wrote:means it doesn't matter which column is the primary key ?
and does making a primary key in my table will solve my problem ??

You definitely need a primary key in your table. Of course it doesn't matter if it's an id (integer) or some code (string). The only requirement is that it's a primary key and thus unique for every row.

If this column is not used in the SELECT statement, it will be used behind the scenes by the JDBC driver to uniquely identify the database record with the ResultSet row.

ok. thank you roel
 
Puspender Tanwar
Ranch Hand
Posts: 393
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello Roel, i have updated my table
i have 4 columns in my table DOCTOR
name id speciality joining_date
and as per your suggestion i made the id as primary key. But still getting the same exception ? why ?
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If it's the exact same error (no primary keys), then you need to double check your table.
What table creation DDL did you use to define the table structure?
Could you post it here?

And I know you are just trying out stuff from your book, but I don't think I've ever actually seen an updateable result set used in real life...
Every example I see I can't help but think "why didn't you just do an UPDATE"?
 
Puspender Tanwar
Ranch Hand
Posts: 393
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:If it's the exact same error (no primary keys), then you need to double check your table.
What table creation DDL did you use to define the table structure?
Could you post it here?

i had made the table some days back, but when roel ask me for a primary key, i used : ALTER TABLE doctor ADD PRIMARY KEY(id);

Dave Tolls wrote:And I know you are just trying out stuff from your book, but I don't think I've ever actually seen an updateable result set used in real life...
Every example I see I can't help but think "why didn't you just do an UPDATE"?

because the book says that in some cases updating the data using ResultSet is more efficient ,useful and fast .
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:If it's the exact same error (no primary keys), then you need to double check your table.
What table creation DDL did you use to define the table structure?
Could you post it here?

Agreed! Here you'll find the example code from the Java JDBC Tutorial.

Dave Tolls wrote:And I know you are just trying out stuff from your book, but I don't think I've ever actually seen an updateable result set used in real life...
Every example I see I can't help but think "why didn't you just do an UPDATE"?

True! Using a PreparedStatement you'll achieve exactly the same and it definitely makes more sense and your code will be more concise. I wonder if an updatable ResultSet will appear on the exam. It might be added to the study guide to provide 110% coverage of the JDBC API to proactively anticipate in possible exam objective changes.
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Puspender Tanwar wrote:i had made the table some days back, but when roel ask me for a primary key, i used : ALTER TABLE doctor ADD PRIMARY KEY(id);


I would check on that (MySQL has a DESCRIBE I think).
Or produce some new DDL and rebuild the table from scratch.

Puspender Tanwar wrote:
because the book says that in some cases updating the data using ResultSet is more efficient ,useful and fast .


I find it hard to believe that it is faster getting data from the database then turning around and sending a modified version back, compared to a single hit UPDATE.
As I say, I have never seen one used in anger.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Puspender Tanwar wrote:
Dave Tolls wrote:If it's the exact same error (no primary keys), then you need to double check your table.
What table creation DDL did you use to define the table structure?
Could you post it here?

i had made the table some days back, but when roel ask me for a primary key, i used : ALTER TABLE doctor ADD PRIMARY KEY(id);

In my MySQL book that should do the trick You can easily verify: try inserting a row with an already existing id: if it fails with a constraint violation exception, the primary key is working as expected. If the row is actually added, the table doesn't have a primary key constraint on that column.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Puspender Tanwar wrote:
Dave Tolls wrote:And I know you are just trying out stuff from your book, but I don't think I've ever actually seen an updateable result set used in real life...
Every example I see I can't help but think "why didn't you just do an UPDATE"?

because the book says that in some cases updating the data using ResultSet is more efficient ,useful and fast .

That would probably strongly depend on the implementation of the JDBC driver. In any case, the JDBC driver adds another layer of abstraction between you and the database, and if you don't read the JDBC driver documentation carefully (assuming it even exists for your database), you might get some nasty surprises.

Regarding the efficiency in this case, the driver makes the resultset updatable only if there's a primary key defined in the table. In my opinion, that means the driver runs update statements behind the scenes, and needs the primary key to locate the updated rows. Regular updates could hardly be slower in this instance.
 
Puspender Tanwar
Ranch Hand
Posts: 393
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:In my MySQL book that should do the trick You can easily verify: try inserting a row with an already existing id: if it fails with a constraint violation exception, the primary key is working as expected. If the row is actually added, the table doesn't have a primary key constraint on that column.

i already done this..
Roel, Martin and dave, primary key exists in my table and that is id.. i have have checked it by sending duplicate id, but resulting in exception which proves that id is the primary key
and as per the post of you all, their is no need to use updatable ResultSet ??
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK.
So that proves that.

Have you tried adding FOR UPDATE to the query, as Roel suggested earlier?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
While I think that it's better to avoid updatable resultsets, if the driver supports them, they should work.

One more idea: I'd suggest to include the column(s) of the primary key in the SELECT clause of the statement you use to open the resultset. (I guess the name column you're selecting now isn't the primary key itself.)
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Puspender Tanwar wrote:
Roel De Nijs wrote:In my MySQL book that should do the trick You can easily verify: try inserting a row with an already existing id: if it fails with a constraint violation exception, the primary key is working as expected. If the row is actually added, the table doesn't have a primary key constraint on that column.

i already done this..

Ok, now it's time to proceed to the next step. Change your SQL statement to If that doesn't work either, try adding the FOR UPDATE clause which seems to be a MySQL specific thing.

Puspender Tanwar wrote:and as per the post of you all, their is no need to use updatable ResultSet ??

In production code I have never seen an updatable ResultSet being used, so it's very unlikely you'll ever need/use one. But of course that won't help you on the actual exam if you encounter a question about updatable result sets
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:... the FOR UPDATE clause which seems to be a MySQL specific thing.

No, it isn't MySQL specific. A lot of databases support it (PostgreSQL, Oracle, and SQL Server probably too, as far as I was able to tell), and I believe it is part of the SQL standard (though I might be wrong on this).

You use the FOR UPDATE clause to tell the database you're going to modify the selected rows. Most implementations then place WRITE locks on selected rows. It helps to avoid non-repeatable reads and/or serializability issues in read committed/serializable isolation levels.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:
Roel De Nijs wrote:... the FOR UPDATE clause which seems to be a MySQL specific thing.

No, it isn't MySQL specific.

I meant "MySQL specific" as in "required to add to the SELECT statement in order to update a row using an updatable result set".
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:Ok, now it's time to proceed to the next step. Change your SQL statement to [/code]

In the mean time I tried it myself. Using this SELECT statement will do the trick. Bottom line: you'll need to add the primary key(s) to the column list of the SELECT statement.

If you don't add the primary key to the column list of the SELECT statement, you'll get this runtime exception:
SQLException: Result Set not updatable (references no primary keys).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.
SQLState: S1000
VendorError: 0


And this runtime exception is different from the initial runtime exception the OP mentioned in his first post:
Result Set not updatable (referenced table has no primary keys).This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE.........

So stating that the runtime exception is still the same after adding a primary key to the table is incorrect! The message of the runtime exception actually gives a very good description about what was wrong with the code, you only have to read it (more) carefully. Having an eye for details (like this one) will be very important on the actual exam (and also when working as a Java developer).

Hope it helps!
Kind regards,
Roel
 
Puspender Tanwar
Ranch Hand
Posts: 393
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Roel, Marti and Dave
but i am amazed with my netbeans behavior ... some minutes back when i opened my nebeans, and run the same code which i posted in my first post, that code is running fine, no such exception is there for which this thread started
and roel, it is working same of

and

well i am amazed of this... why not that problem is occuring with the same code now ???
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Puspender Tanwar wrote:well i am amazed of this... why not that problem is occuring with the same code now ???

My guess is that on the first successful run, all names in the database which were equal to "puspen" were changed to "puspender", and on subsequent runs your code never enters the body of the if statement and the update doesn't happen. Therefore, no error.
 
Puspender Tanwar
Ranch Hand
Posts: 393
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
but morgan, now even if again i am changing the name in database, no such error encounter . not on 1st run, not on 2nd or nor on 3rd,4th.....etc
and why the code was giving error before ?

and i don't know you will believe me or not, but this netbeans behavior is not firts time with me, i have encountered it many times, at night code compiling fine and in morning it give exception
(i thing my netbeans get jealous of me as i alone go to bed living him behind so it gets disloyal to me )
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When you need to find out what's going on in your code, a few strategically placed System.out.println() statements can go a long way.

In other words, add some logging to your while loop and if statement to be able to tell what your program is doing when you run it. Ideally, output the details of each record as you process it. Then post the output of your program here, as well as your current code.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Puspender Tanwar wrote:and i don't know you will believe me or not, but this netbeans behavior is not firts time with me, i have encountered it many times, at night code compiling fine and in morning it give exception

That's probably NetBeans' way to tell you: you are preparing for the OCPJP7 certification, so you should not be using any IDE at all
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Puspender Tanwar wrote:but morgan, now even if again i am changing the name in database, no such error encounter . not on 1st run, not on 2nd or nor on 3rd,4th.....etc
and why the code was giving error before ?

I have tried it once more with my own code. If I do not include the primary key column in the column list of the SELECT statement I get the aforementioned error at runtime. And I'm using a MySQL database as well.

Can you change your code snippet so that it's guaranteed to always try to update the record (instead of some conditional statement)? Something like this should do the job

Hope it helps!
Kind regards,
Roel
 
Puspender Tanwar
Ranch Hand
Posts: 393
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:Can you change your code snippet so that it's guaranteed to always try to update the record (instead of some conditional statement)? Something like this should do the job
Roel

its running fine and updating the name of the first row.
Roel De Nijs wrote:That's probably NetBeans' way to tell you: you are preparing for the OCPJP7 certification, so you should not be using any IDE at all

Roel, till now i have only used notepad,but i didn't found the way how can i connect the jdbc with my notepad so i used netbeans
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Puspender Tanwar wrote:its running fine and updating the name of the first row.

With both queries? Because with my code, the update fails each time I don't include the primary key column in the SELECT statement. And I'm using MySQL as well with their JDBC driver (mysql-connector-java-5.1.36-bin.jar).

Puspender Tanwar wrote:Roel, till now i have only used notepad,but i didn't found the way how can i connect the jdbc with my notepad so i used netbeans

You'll need to add your JDBC driver to the classpath when running your application. So something likeYou need to use the appropriate path separator: on Windows it's a semicolon, on Linux/Unix a colon.
 
Puspender Tanwar
Ranch Hand
Posts: 393
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Puspender Tanwar wrote:its running fine and updating the name of the first row.

With both queries? Because with my code, the update fails each time I don't include the primary key column in the SELECT statement. And I'm using MySQL as well with their JDBC driver (mysql-connector-java-5.1.36-bin.jar).

same is happening with me when i use, SELECT NAME FROM doctor
error is something like this com.mysql.jdbc.NotUpdatable: Result Set not updatable (references no primary keys)
but Roel, why is it so ??
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Puspender Tanwar wrote:error is something like this com.mysql.jdbc.NotUpdatable: Result Set not updatable (references no primary keys)
but Roel, why is it so ??

Probably because the JDBC driver needs to update the row in the database table and therefore needs to be able to identify the row based on the information in the result set. And if you select only name it doesn't have any clue which record it needs to update (as a gazillion records can have the same name). Therefore the primary key is needed, as that uniquely identifies each record in the database table.
 
Puspender Tanwar
Ranch Hand
Posts: 393
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Roel
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic