rnicholus
asked on
Problems using ResultSet to update a DB record
If I run the code listed at the end of this message, an exception is thrown. Here is the output during a recent execution attempt:
1
26768073
java.sql.SQLException: Invalid operation for read only resultset: updateString
at oracle.jdbc.dbaccess.DBErr or.throwSq lException (DBError.j ava:134)
at oracle.jdbc.dbaccess.DBErr or.throwSq lException (DBError.j ava:179)
at oracle.jdbc.driver.BaseRes ultSet.upd ateString( BaseResult Set.java:3 53)
at oracle.jdbc.driver.OracleR esultSet.u pdateStrin g(OracleRe sultSet.ja va:
2824)
at test2.main(test2.java:46)
Why is this happening?
public static void main (String args[])
{
String path;
Statement stmt = null;
Connection con = null;
ResultSet results = null;
try
{
Class.forName(JDBC_DRIVER) .newInstan ce();
path = DB_DRIVER + ":@" + DB_HOST + ":" + DB_PORT + ":" + DB_SID;
con = DriverManager.getConnectio n(path, USER, PWD);
//con.setAutoCommit(false) ;
stmt = con.createStatement(Result Set.TYPE_S CROLL_INSE NSITIVE,
ResultSet.CONCUR_UPDATABLE );
results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = \'WDC\'");
while (!results.isLast())
{
results.next();
System.out.println(results .getRow()) ;
System.out.println(results .getInt("I D"));
results.updateString("desc ription", "TEST");
}
//System.out.println(resul ts.getFetc hSize());
}
catch (Exception ex) { ex.printStackTrace(); }
finally
{
try
{
if (stmt != null)
stmt.close();
if (con != null)
con.close();
}
catch (SQLException ex) { System.err.println("Cannot close connection to Oracle DB."); }
}
}
1
26768073
java.sql.SQLException: Invalid operation for read only resultset: updateString
at oracle.jdbc.dbaccess.DBErr
at oracle.jdbc.dbaccess.DBErr
at oracle.jdbc.driver.BaseRes
at oracle.jdbc.driver.OracleR
2824)
at test2.main(test2.java:46)
Why is this happening?
public static void main (String args[])
{
String path;
Statement stmt = null;
Connection con = null;
ResultSet results = null;
try
{
Class.forName(JDBC_DRIVER)
path = DB_DRIVER + ":@" + DB_HOST + ":" + DB_PORT + ":" + DB_SID;
con = DriverManager.getConnectio
//con.setAutoCommit(false)
stmt = con.createStatement(Result
ResultSet.CONCUR_UPDATABLE
results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = \'WDC\'");
while (!results.isLast())
{
results.next();
System.out.println(results
System.out.println(results
results.updateString("desc
}
//System.out.println(resul
}
catch (Exception ex) { ex.printStackTrace(); }
finally
{
try
{
if (stmt != null)
stmt.close();
if (con != null)
con.close();
}
catch (SQLException ex) { System.err.println("Cannot
}
}
Do you have write access in that account to that table?
ASKER
Well, I can execute UPDATE and INSERT SQL statements from my program without any problems. The problem seems to occur when I attempt to change the table through ResultSet objects.
You also need to ensure that the driver you're using will support an updateable ResultSet
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm using this driver: oracle.jdbc.driver.OracleD river
How do I know if this driver (or any for that matter) supports an updatable ResultSet? Also, if this one does not support updatable ResultSets, how can I find an acceptable one that does? I apologize for these questions - I'm fairly new to JDBC.
How do I know if this driver (or any for that matter) supports an updatable ResultSet? Also, if this one does not support updatable ResultSets, how can I find an acceptable one that does? I apologize for these questions - I'm fairly new to JDBC.
ASKER
neonlines:
No dice. Same exception is thrown.
No dice. Same exception is thrown.
Try the following:
while (results.next())
{
results.moveToInsertRow();
System.out.println(results .getRow()) ;
System.out.println(results .getInt("I D"));
results.updateString("desc ription", "TEST");
}
while (results.next())
{
results.moveToInsertRow();
System.out.println(results
System.out.println(results
results.updateString("desc
}
ASKER
That code results in:
java.sql.SQLException: Invalid operation for read only resultset: moveToInsertRo
w
at oracle.jdbc.dbaccess.DBErr or.throwSq lException (DBError.j ava:134)
at oracle.jdbc.dbaccess.DBErr or.throwSq lException (DBError.j ava:179)
at oracle.jdbc.driver.BaseRes ultSet.mov eToInsertR ow(BaseRes ultSet.jav a:5
93)
at test2.main(test2.java:44)
java.sql.SQLException: Invalid operation for read only resultset: moveToInsertRo
w
at oracle.jdbc.dbaccess.DBErr
at oracle.jdbc.dbaccess.DBErr
at oracle.jdbc.driver.BaseRes
93)
at test2.main(test2.java:44)
ASKER
Ok, I think I found a solution.
I read somewhere that "SELECT *" queries are not allows when dealing with ResultSets. So, I modified my SELECT statement from:
results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = \'WDC\'");
to:
results = stmt.executeQuery("SELECT " + TABLE + ".* FROM " + TABLE + " WHERE city = \'WDC\'");
then, I attempted to use my original while clause. No dice. So, I attempted to use the last suggested while clause. This also didn't work (no exception, but no update). Finally, I used the while clause with the rowcount variable. This DID work. Now, my question is, why DID the clause with the rowcount variable work (with the modified SELECT statement), but not any of the other two?
I read somewhere that "SELECT *" queries are not allows when dealing with ResultSets. So, I modified my SELECT statement from:
results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = \'WDC\'");
to:
results = stmt.executeQuery("SELECT " + TABLE + ".* FROM " + TABLE + " WHERE city = \'WDC\'");
then, I attempted to use my original while clause. No dice. So, I attempted to use the last suggested while clause. This also didn't work (no exception, but no update). Finally, I used the while clause with the rowcount variable. This DID work. Now, my question is, why DID the clause with the rowcount variable work (with the modified SELECT statement), but not any of the other two?
>>I read somewhere that "SELECT *" queries are not allows when dealing with ResultSets
No, that's not the case - it's fine. Can you post the code that *did* work?
No, that's not the case - it's fine. Can you post the code that *did* work?
You might also try
results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = \'WDC\' FOR UPDATE");
results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = \'WDC\' FOR UPDATE");
Actually that shouldn't need any escape characters:
results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = 'WDC' FOR UPDATE");
results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = 'WDC' FOR UPDATE");
ASKER
My code suggests otherwise. If my original select statement is present, an exception is thrown. If the new select statement is used instead, it works fine. Anyways, here is the code that works:
public static void main (String args[])
{
String path;
Statement stmt = null;
Connection con = null;
ResultSet results = null;
try
{
Class.forName(JDBC_DRIVER) .newInstan ce();
path = DB_DRIVER + ":@" + DB_HOST + ":" + DB_PORT + ":" + DB_SID;
con = DriverManager.getConnectio n(path, USER, PWD);
//con.setAutoCommit(false) ;
stmt = con.createStatement(Result Set.TYPE_S CROLL_INSE NSITIVE,
ResultSet.CONCUR_UPDATABLE );
results = stmt.executeQuery("SELECT " + TABLE + ".* FROM " + TABLE + " WHERE city = \'WDC\'");
int rowcount=1;
while (!results.isLast())
{
results.absolute(rowcount) ; // moves the cursor to the rowcount th row of result set
results.updateString("desc ription", "test");
results.updateRow(); // updates the row in the data source
rowcount++;
//con.commit();
}
//System.out.println(resul ts.getFetc hSize());
}
catch (Exception ex) { ex.printStackTrace(); }
finally
{
try
{
if (stmt != null)
stmt.close();
if (con != null)
con.close();
}
catch (SQLException ex) { System.err.println("Cannot close connection to Oracle DB."); }
}
}
public static void main (String args[])
{
String path;
Statement stmt = null;
Connection con = null;
ResultSet results = null;
try
{
Class.forName(JDBC_DRIVER)
path = DB_DRIVER + ":@" + DB_HOST + ":" + DB_PORT + ":" + DB_SID;
con = DriverManager.getConnectio
//con.setAutoCommit(false)
stmt = con.createStatement(Result
ResultSet.CONCUR_UPDATABLE
results = stmt.executeQuery("SELECT " + TABLE + ".* FROM " + TABLE + " WHERE city = \'WDC\'");
int rowcount=1;
while (!results.isLast())
{
results.absolute(rowcount)
results.updateString("desc
results.updateRow(); // updates the row in the data source
rowcount++;
//con.commit();
}
//System.out.println(resul
}
catch (Exception ex) { ex.printStackTrace(); }
finally
{
try
{
if (stmt != null)
stmt.close();
if (con != null)
con.close();
}
catch (SQLException ex) { System.err.println("Cannot
}
}
ASKER
You're right about the escape characters. My mistake. My above post refers to your 2nd to last comment, BTW.
ASKER
Sorry, I'm losing track of the posts here. Your suggestion to change my select statement to
results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = 'WDC' FOR UPDATE");
results in:
java.sql.SQLException: ORA-01002: fetch out of sequence
at oracle.jdbc.dbaccess.DBErr or.throwSq lException (DBError.j ava:134)
at oracle.jdbc.ttc7.TTIoer.pr ocessError (TTIoer.ja va:289)
at oracle.jdbc.ttc7.Oall7.rec eive(Oall7 .java:573)
at oracle.jdbc.ttc7.TTC7Proto col.doOall 7(TTC7Prot ocol.java: 1891)
at oracle.jdbc.ttc7.TTC7Proto col.fetch( TTC7Protoc ol.java:11 98)
at oracle.jdbc.driver.OracleS tatement.d oExecuteQu ery(Oracle Statement. jav
a:2400)
at oracle.jdbc.driver.OracleS tatement.d oExecuteWi thTimeout( OracleStat eme
nt.java:2672)
at oracle.jdbc.driver.OracleS tatement.d oScrollStm tExecuteQu ery(Oracle Sta
tement.java:7030)
at oracle.jdbc.driver.OracleS tatement.e xecuteQuer y(OracleSt atement.ja va:
578)
at test2.main(test2.java:38)
results = stmt.executeQuery("SELECT * FROM " + TABLE + " WHERE city = 'WDC' FOR UPDATE");
results in:
java.sql.SQLException: ORA-01002: fetch out of sequence
at oracle.jdbc.dbaccess.DBErr
at oracle.jdbc.ttc7.TTIoer.pr
at oracle.jdbc.ttc7.Oall7.rec
at oracle.jdbc.ttc7.TTC7Proto
at oracle.jdbc.ttc7.TTC7Proto
at oracle.jdbc.driver.OracleS
a:2400)
at oracle.jdbc.driver.OracleS
nt.java:2672)
at oracle.jdbc.driver.OracleS
tement.java:7030)
at oracle.jdbc.driver.OracleS
578)
at test2.main(test2.java:38)
ASKER
Also, my information regarding my statement that "SELECT *" are not allowed when dealing with ResultSets comes from
http://forum.java.sun.com/thread.jspa?forumID=48&messageID=1846790&threadID=417560
http://forum.java.sun.com/thread.jspa?forumID=48&messageID=1846790&threadID=417560
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jim,
You make some good points about SELECT. You're right, and I don't need to select all columns. I will attempt to try this next and see what happens. I'm afraid I don't even know what driver type or version I am using <hides>. How do I figure this out?
CEHJ,
The example listed on the page you linked to throws the exception I listed in my original question.
You make some good points about SELECT. You're right, and I don't need to select all columns. I will attempt to try this next and see what happens. I'm afraid I don't even know what driver type or version I am using <hides>. How do I figure this out?
CEHJ,
The example listed on the page you linked to throws the exception I listed in my original question.
In one of the forms i came across it points the same issue like not able to use 'select * from tablename' They suggested 'select tablename.* from tablename'
Please check if this works
Please check if this works
ASKER
This does work.
ok then i will take a note of this as solution for this issue.
Yes, all this seems pretty weird