Return-Path: 
 <tomcat-user-return-36340-apmail-jakarta-tomcat-user-archive=jakarta.apache.org@jakarta.apache.org>
Delivered-To: apmail-jakarta-tomcat-user-archive@jakarta.apache.org
Received: (qmail 56189 invoked by uid 500); 30 May 2001 07:29:41 -0000
Mailing-List: contact tomcat-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: tomcat-user@jakarta.apache.org
list-help: <mailto:tomcat-user-help@jakarta.apache.org>
list-unsubscribe: <mailto:tomcat-user-unsubscribe@jakarta.apache.org>
list-post: <mailto:tomcat-user@jakarta.apache.org>
Delivered-To: mailing list tomcat-user@jakarta.apache.org
Received: (qmail 53850 invoked from network); 30 May 2001 07:25:41 -0000
Received: from scribe.eastcoast.co.za (196.33.34.233)
  by h31.sny.collab.net with SMTP; 30 May 2001 07:25:41 -0000
Received: (qmail 6180 invoked from network); 30 May 2001 07:25:28 -0000
Received: from dial06.eastcoast.co.za (HELO jet) (196.33.34.134)
  by scribe.eastcoast.co.za with SMTP; 30 May 2001 07:25:28 -0000
Message-Id: <SAK.2001.05.30.gngrcsne@jet>
Date: Wed, 30 May 2001 09:25:22 GMT
X-Priority: 3
From: Twylite <twylite@crypt.co.za>
X-Mailer: Mail Warrior
To: "tomcat-user@jakarta.apache.org" <tomcat-user@jakarta.apache.org>
MIME-Version: 1.0
Subject: Re:Problems with ResultSet
Content-Type: Text/Plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8Bit
X-Mailer-Version: v3.55
X-Spam-Rating: h31.sny.collab.net 1.6.2 0/1000/N

Hi,

>Statement s=cn.createStatement
(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
>           if (!rs.isFirst()) rs.previous(); 
>              %><tr> 
>              <td><%=rs.getString(1)%></td><%
>           }

>	[Microsoft][ODBC Driver Manager] Invalid cursor state

You never close your result set.  If you try executing this more than on the same connection, it will fail.  For 
some drivers (specifically NOT ODBC) you can use any number of new ResultSet objects over the same 
connection, but not reuse any one unless you close the result set.

MSAccess in particular, only supports one cursor per connection, and JDBC ResultSets attempt 
(unsuccessfully) to share that cursor.  Hence the "Invalid cursor state".

Also be careful with the cursor position (as reflected in the ResultSet): the initial position is before the first row, 
and the final position (if iterating forwards) will be after the last row.  Use rs.first() to move to the beginning of the 
ResultSet instead of iterating to get there.

Correct JDBC logic:

	Class.forName(DRIVER_CLASS);
	Connection con = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
	Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
		ResultSet.CONCUR_READ_ONLY);
	{ // This can be done in a loop
		ResultSet r = stmt.executeQuery("SELECT * FROM MYTABLE");
		if (r != null) {
			while (r.next()) {
				// Do something with r.getString(1)
			}
			r.close();
		}
	}
	stmt.close();
	con.close();

Alternative for inner loop:
		ResultSet r = stmt.executeQuery("SELECT * FROM MYTABLE");
		if (r != null && r.last()) {
			while (r.previous()) {
				// Do something with r.getString(1)
			}
			r.close();
		}