JDBC and MS SQL-2000 hanging on update

[DaveGill]

What I'd like to do is read some data from SQL 2000, processes it and update a flag field in the database to say it had been processed. I'm using the Microsoft SQL Server 2000 driver for JDBC to access the database. I can read the data okay but the rs.updateRow() call just hangs. Any ideas

Thanks
David

Connection co = DriverManager.getConnection(
SQLConnectString, "sa", ""
Statement st = co.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

ResultSet rs = st.executeQuery("SELECT * FROM tablename
WHERE archivedflag = 0"

while(rs.next()) {
.
.
.
rs.updateShort("archivedflag", (short)1);
rs.updateRow();
}

 

[palbano]

Just wild guessing here.

Working with all forms of data access down through the years I have seen different problems related to this same pattern.

Your statement criteria is: archivedflag = 0

Now update the data element such that the row of the cursor is no longer matches the query result based on the criteria:

rs.updateShort("archivedflag", (short)1);

You might try asking about this in the SQL Server forum. The folks in there really know thier database engines.

Good luck
-pete

 

[DaveGill]

Thanks for the reply Pete.

I removed the WHERE clause from the original select statement now check the setting of archivedflag before I process a record. The updateRow now returns but on the first record it sets archivedflag to 1 for all the records!!!

 

[palbano]

Dave,

Not sure exactly what your goal is but looking at your original post it seems you could accomplish what it is doing by running an update statement


Update tablename
set archivedflag = 1
where archivedflag = 0



Good luck
-pete

 

[DaveGill]

Pete,
Within the while loop there is some other processing that may or may not require the archivedflag to be changed for each record. I wanted to try and update this flag as I went along but it's not looking good at the moment!
Regards
Dave

 

[palbano]

Dave,

Do you have a Unique ID for each row in the table? Or actually any way to identify a specific row by combining column criteria will do.

If so you can run an UPDATE statement inside the loop for each individual row. Or better yet write a stored procedure and use that.


Update tablename
set archivedflag = 1
where rowid = 102


-pete