The moose likes JDBC and the fly likes creating updatable resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "creating updatable resultset" Watch "creating updatable resultset" New topic
Forums: JDBC Programmer Certification (SCJP/OCPJP)
Author

creating updatable resultset

Manish Kapasiya
Greenhorn

Joined: May 29, 2014
Posts: 13


getting java.sql.SQLEception: Invalid operation for read only result set..
Tim Cooke
Bartender

Joined: Mar 28, 2008
Posts: 1234
    
  67

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.


Tim Driven Development
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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
Bartender

Joined: Mar 28, 2008
Posts: 1234
    
  67

That's interesting. I've never used them in that way. I've learned something new today, thanks Martin.
E Armitage
Rancher

Joined: Mar 17, 2012
Posts: 892
    
    9
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

Joined: May 29, 2014
Posts: 13
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.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18991
    
    8

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

Joined: May 29, 2014
Posts: 13
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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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

Joined: Mar 17, 2012
Posts: 892
    
    9
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

Joined: May 29, 2014
Posts: 13
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.....
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: creating updatable resultset