File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes ResultSet is not updateable. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ResultSet is not updateable." Watch "ResultSet is not updateable." New topic
Author

ResultSet is not updateable.

Welsh Ding
Greenhorn

Joined: Dec 29, 2003
Posts: 14
jdbc to SqlServer2000.
when I insert some records to dasebase,a error occured
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]ResultSet is not updateable.
How to resolve the problem? thanks!


Welsh<br />SCJP 1.4
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8927
    
    9

Without seeing your code I can only guess. When you created the Statement object, did you pass ResultSet.CONCUR_UPDATABLE to the Connection so the ResultSet would be updatable?


"blabbing like a narcissistic fool with a superiority complex" ~ N.A.
[How To Ask Questions On JavaRanch]
Joe Gilvary
Ranch Hand

Joined: May 11, 2001
Posts: 152
Did you specify ResultSet.CONCUR_UPDATEABLE when you created the Statement object? From Sun's online API documentation for Connection.createStatement() method:
Result sets created using the returned Statement object will by default be type TYPE_FORWARD_ONLY and have a concurrency level of CONCUR_READ_ONLY.
Check the constants in the Field Summary for the online ResultSet API documentation at Sun.
http://java.sun.com/j2se/1.4.2/docs/api/index.html
HTH,
Joe
Welsh Ding
Greenhorn

Joined: Dec 29, 2003
Posts: 14
I created the statement like this:
Statement stmt= con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
but it still report the error:java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]ResultSet is not updateable.
is any other reasons can cause the error? thanks a lot!
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8927
    
    9

You might note that just specifying that a result set be updatable does not guarantee that the result set you get is updatable. If a driver does not support updatable result sets, it will return one that is readonly. The query you send can also make a difference. In order to get an updatable result set, the query must generally specify the primary key as one of the columns selected, and it should select columns from only one table.
The following line of code checks whether the ResultSet object uprs is updatable.
int concurrency = uprs.getConcurrency();
The variable concurrency will be one of the following:
1007 to indicate ResultSet.CONCUR_READ_ONLY
1008 to indicate ResultSet.CONCUR_UPDATABLE

The JDBC Tutorial, chapter 3
Welsh Ding
Greenhorn

Joined: Dec 29, 2003
Posts: 14
thanks Joe
I get the concurrency, it is 1007, so my resultset is readonly, I don't know how to set the rs to be updateable.
here is my code:
---------------------------------------------------------------
String getNoRowSql = "SELECT mytable.* FROM mytable where col_primay is null"
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(getNoRowsSql);
//here I get the rs.concurrency = 1007
rs.moveToInsertRow(); // moves cursor to the insert row
rs.updateString(col_1,value1);
//the error occured here, when set the col_1's value as value1
rs.updateString(col_2,value2);
rs.updateString(col_3,value3);
rs.updateString(col_4,value4);
rs.insertRow(); // insert a record to database
rs.moveToCurrentRow();
-----------------------------------------------------------------
I think the getNoRowSql may be the reason. How to make the resultset updateable? thanks a lot!
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8927
    
    9

Are you using latest version of the driver?
MS SQL Server 2000 JDBC Driver SP2?
Welsh Ding
Greenhorn

Joined: Dec 29, 2003
Posts: 14
Yes,Joe
I am using MS SQL Server 2000 JDBC Driver SP2.
I can get the updateable resultset like this:
------------------------------------------------------------
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;User=sa;Password=123");
stmt= conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
sql="select * from mytable where col_primay is null";
rs=stmt.executeQuery(sql);
System.out.println("concurrency = "+rs.getConcurrency());
//here concurrency=1008
------------------------------------------------------------
in my program,I used ConnectionPool to establish connections,maybe there are something wrong with the connection which create by the ConnectionPool.but I can also do almost all operates to resultset except update. What is the reason?
Is a bad connection can cause the resultset not updateable?
this problem of "resultset is not updateable" troubled me too much.
thanks for your help,Joe.
With Regards!
Sincerely Welsh
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8927
    
    9

Originally posted by Welsh Ding:

I can get the updateable resultset like this:

So what are you doing differently between this code and the code that won't work? A "bad connection" would fail with the getConnection() or createStatement() call.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ResultSet is not updateable.