• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

creating updatable resultset

 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


getting java.sql.SQLEception: Invalid operation for read only result set..
 
Sheriff
Posts: 5555
326
IntelliJ IDE Python Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The error is fairly self explanatory. The ResultSet you have is read only so you cannot mutate it. I suspect the error is being raised due to the following lines:

This is not how you update a row in a database. The ResultSet in JDBC is just a copy of the data set in your db. You need to explicitly run an insert or update command with JDBC to write to your database. There are plenty of tutorials and examples on the web so I won't go into it here.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Cooke wrote:This is not how you update a row in a database. The ResultSet in JDBC is just a copy of the data set in your db. You need to explicitly run an insert or update command with JDBC to write to your database. There are plenty of tutorials and examples on the web so I won't go into it here.


Actually, this is not true. ResultSets are meant to be updatable, if some conditions are met (see the Oracle's tutorial, for example).

That said, I prefer to do database updates using the INSERT, UPDATE and DELETE SQL command myself, so I don't have much experience with updatable resultsets. I'd say that given the ResultSet.CONCUR_UPDATABLE is used when creating the statement, the resultset should be updatable; if the database wouldn't support it, an exception should be thrown earlier in the program.

I'd probably try to change ResultSet.TYPE_SCROLL_SENSITIVE to ResultSet.TYPE_SCROLL_INSENSITIVE, but that would be just my hunch. I don't see anything that would indicate a problem in the given code (apart from not using PreparedStatement, empty catch clause and so on, but that is not immediately connected to the problem).
 
Tim Cooke
Sheriff
Posts: 5555
326
IntelliJ IDE Python Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That's interesting. I've never used them in that way. I've learned something new today, thanks Martin.
 
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You will need to select the specific column names rather than "select *" for the update to work.

Edit: Here is the link (http://docs.oracle.com/cd/B28359_01/java.111/b31224/resltset.htm#i1024720) which explains workarounds and other reasons the ResultSet might get downgraded.
 
Manish Kapasiya
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Mr. Armitage.
problem is solved.
can you tell me why it is necessary to give specific coloumn names?
the link you provided is not clearing my doubt.
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The short answer is "Because if you don't specify the column names, then the Oracle software won't classify your ResultSet as updatable."

I expect this is an implementation decision made by the writers of the JDBC driver for reasons which I can't guess at. The rule may appear arbitrary but since it's a rule, I would suggest writing code which conforms to it.

Also, I think that most people are like Martin and use INSERT and UPDATE statements, rather than using an updatable ResultSet. I would go so far as to suggest that you should do that as well, if only because it's what most people do. Real database specialists might be able to supply better reasons.
 
Manish Kapasiya
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
sir i am a student and preparing for ocpjp 1.7 and the topic is on the course that's why i am doing that way (updatable resultset).
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

E Armitage wrote:You will need to select the specific column names rather than "select *" for the update to work.


Interesting. The JDBC tutorial I've linked to earlier use select * in the example of an updatable resultset. It's pretty confusing. Apparently it works with some JDBC drivers, but not with others.
 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, it's an implementation specific quirk. I think the Oracle tutorial uses Derby for its examples, I don't know if it allows * so it might work there.

@OP, It's probably not important for you to know these implementation oddities for the exam apart from that some drivers may not implement some operations and what the expected outcome should be in those scenarios (e.g type of Exception thrown). The restriction itself is not surprising because allowing updates on records based on a select statement by its nature has problems. What if the select columns include joins on other tables or results from subqueries and do you allow updating a column that wasn't included in the select in the first place? These problems are the reasons why most people shun from updating via resultset because you are now a bit crippled on how to fetch the data. Changing the select part also now affects how the update part is written which may not be obvious to others that may be required to update the code.
 
Manish Kapasiya
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sir Martin,
I have used Resultset.TYPE_SCROLL_INSENSITIVE rather... Don't be confused with my early code posted because I typed it mistakenly.... It's my mistake.....
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic