JDBC-ODBC w/ SQL Server throws Exception w/ MS Access works fine

[The Craggle]

Hello,

I'm using the default com.sun.java.JdbcOdbcDriver in JDK 2 (jdk1.2.1) to
access a MS SQL Server 7.0 database. As long as I run one query against the
database at a time and access the columns in order from 1 to N for all rows
in sequence 1 to M, I seem to have no problem - regardless of whether I
close the result set or not. However, if I run a query that returns 100
rows, retrieve the first row and with a value from one of the columns
perform a second query, I get an Exception.
If I use the EXACT same code, changing only the JDBC URL from the SQL7->
"jdbc:odbc:SQL_DB" to "jdbc:odbc:ACCESS_DB" and run it, the MS Access
database does not throw an Exception. In order to setup the SQL 7 data
source under ODBC, I have to select the SQL Server driver (v3.60.03.19
SQLSRV32.DLL) for MS Access: MS Access driver is (v3.51.1713.00
ODBCJT32.DLL).

Code Follows:

System.out.print("Loading JDBC Driver...");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
System.out.println("Done.");

// System.out.println("Getting connection to MS Access DB");
// Connection con =
DriverManager.getConnection("jdbc:odbc:ACCESS_DB");
// System.out.println("Done.");

System.out.println("Getting connection to MS SQL 7.0 DB");
Connection con = DriverManager.getConnection("jdbc:odbc:SQL_DB");
System.out.println("Done.");

Statement statement0 = con.createStatement();
ResultSet resultSet0 = statement0.executeQuery("SELECT * FROM aTable");
while (rs.next());
// do something

Statement statement1 = con.createStatement();
ResultSet resultSet1 = statement1.executeQuery("SELECT id FROM
anIDTable");

while (resultSet1.next()) {
int id = resultSet1.getInt("id");
Statement statement2 = con.createStatement()
ResultSet resultSet2 = statement2.executeQuery("SELECT value FROM
aValueTable WHERE valueID = "+id); // throws java.sql.SQLException here
while (resultSet2.next()) {
...
}
resultSet2.close();
statement2.close();
}
resultSet1.close();
statement1.close();

Exception Follows:

java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Connection is busy
with results for another hstmt
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:4089)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:4246)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:1172)
at
sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:206)
at
sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:131)
at ResultSet resultSet2 = statement2.executeQuery("SELECT
value FROM aValueTable WHERE valueID = "+id);

For SQL 7:
I took a look at the java.sql.Connection that is returned by
DriverManager.getConnection(...) and the HashTable of Statements inside the
Connection have an int field "hstmt" and a boolean field "keepHstmt".
keepHstmt = true, hstmt = some large int (different for every statement in
the HashTable).

I haven't looked at the values for MS Access yet. Does anybody know why
this Exception is being thrown and how I can prevent it from happening?
Does it have anything to do with JDBC 2.0's scrollable cursors or having
autocommit on?

I know the JDBC-ODBC bridge supplied by Sun sucks, but using a comercial
driver ($$$) is not currently an option - if I install my software at
several locations, each location would then have to buy the driver.

Thank you for your assistence.

--
Thomas J. Taylor
Thomas...@INFOTECHSoft.com
INFOTECHSoft, Inc.

[Stefan Zschocke]

Hi,
our driver is royalty free. You can distribute it with your application. It
supports scrollable and updatable ResultSets.
Stefan
(http://www.infozoom.de/javaado.html)

"The Craggle" <Cra...@Adelphia.net> wrote in message
news:EPoQ4.18838$i_6.4...@news2.mia...