Java Database Connectivity (JDBC)

Hello,

I�ve been working to learn some Java and now JDBC over the past 10 or so months.

I think I have a general understanding of how to perform queries and work with data using JDBC. However, I�ve run into a problem. I�m trying to do a query of a set of data in a database based on the value of a status column. I want to loop over the messages and perform various functions with the data then update their status in the database. It�s preferable to do these 250 to 1000 rows at a time, but no more and no less.

I�m connecting to MS SQL Server 2000, currently with ODBC. I�ve also tried it with the Java SQL Server 2000 drivers provided by Microsoft with the same results.

I�ve found that I can do a one table query and loop though it with a while (rs.next()) {�} and run an Update statement with executeUpdate on each iteration without any problems, no matter the number of rows returned in query.

I have not been able to use the updateString and updateRow inside the while loop. I keep getting errors like this at the line with the updateRow():

Exception in thread "main" java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Row update failed.

This occurs no mater how many rows I select, 1 or more.

The real problem I�ve been having is that the query I need to loop though joins across several tables and returns some rows from some of those tables. This only seems to work when I query for 38 or less selected rows and I use an Update statement with executeUpdate on each iteration. The updateString and updateRow methods never work. Any number of rows selected greater than 38 causes a deadlock where the Update is waiting for the select to compete on the server and the Update can�t proceed until the Select is complete.

As I stated above I�ve tried both ODBC and the native SQL Server driver with the same results. I have not tried any other databases, but that�s moot as my data is already in MS SQL.

Questions:

How can I avoid or get around this 38 row limit without selecting each row, one at a time?

What am I doing wrong with the updateString and updateRow?

Is there a better approach that anyone can suggest?

Here�s some sample code with the problem:

import java.sql.*;

public class db1{
public static void main(String[] args) throws Exception{
/*
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:eBrochure_live";
Connection con = DriverManager.getConnection(url, "sa", "d3v3l0p");
*/
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://dcm613u2\\dcm613u2_dev:1433", "sa", "d3v3l0p");

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
Statement stmt2 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

stmt.executeUpdate("USE [myDatabase]");
stmt2.executeUpdate("USE [myDatabase]");

String qGetMessages = "SELECT TOP 250 t1.messageUUID, t1.subjectHeader, t2.emailAddress as toAddress " +
"FROM APP_Messages as t1 JOIN APP_addressBook_contacts as t2 " +
" On t1.toContactID = t2.contactID " +
"WHERE t1.statusID = 'queued'";

ResultSet rs = stmt.executeQuery(qGetMessages);
while (rs.next()) {
String messageUUID = rs.getString("messageUUID");
String subjectHeader = rs.getString("subjectHeader");
System.out.println(messageUUID + " " + subjectHeader);

String updateString = "UPDATE APP_Messages " +
"SET statusID = 'sent' " +
"WHERE messageUUID = '" + messageUUID + "' ";
stmt2.executeUpdate(updateString);
}

con.close();
}
}


Thanks for the help,

Doug Hughes