AppletTalk.com

Daniel Hagen · Guest

Hi,

we are using the JdbcOdbc-Bridge to access a Microsoft Access Database
from Java 1.1/Microsoft VM. (Access XP is installed on the same
machine)
(I know, I know, this isn't exactly the perfect combination for a
stable application, but we do not have a choice...)
We have written our own connection-pooling-facility for this.

Recently I encountered a strange problem retrieving updates which were
made using another connection.

The problem looks like this:

- Field A in Record R has value X
- Field A in Record R is updated to Y using Connection 1
- Field A in Record R is read using Connection 1
The result is correct (Y)
- Field A in Record R is read using Connection 2 (approx. 2 Seconds
later)
The result is the old Value (X) instead of Y

This looks like a Transaction Isolation issue to me but, afaik, Access
doesn't support transactions, so I am a little confused.
My only guess is that the Jet Engine is doing it's write operations
asynchronously.
Does anyone know how to prevent this or can give me some background
information to this behaviour? I have tried all ODBC Connection
Options I found, but to no effect.

Any help would be appreciated.

(Below is a code example which demonstrates the behaviour)

Thanks

Daniel Hagen

Begin Code Example:

import java.sql.*

/*
Assumes a table "Test" With the Fields "ID" (Number) and "Field"
(Number), and one Record:
ID | Field
==============
1 | 0
*/
public class TestAccessConnections
{
try
{
String jdbcDriver = "com.ms.jdbc.odbc.JdbcOdbcDriver";
String user = "admin";
String password = "secret";
DriverManager.registerDriver( (Driver) Class.forName( jdbcDriver
).newInstance() );

String jdbcUrl = "Jdbc:Odbc:driver={Microsoft Access Driver
(*.mdb)};dbq=d:\temp\test.mdb;MaxBufferSize=8192;Threads=16";

Connection connection1 = DriverManager.getConnection( jdbcUrl, user,
password );
Connection connection2 = DriverManager.getConnection( jdbcUrl, user,
password );

Statement statement1 = connection1.createStatement();
Statement statement2 = connection2.createStatement();

// original value for field was 0
String sqlUpdate = "UPDATE Test SET Field=1 WHERE ID=1";
String sqlSelect = "SELECT Field FROM Test WHERE ID=1";

// update row
statement1.executeUpdate( sqlUpdate );

// make select on same connection and print content of field 1
ResultSet resultSet1 = statement1.executeQuery( sqlSelect );
resultSet1.next();
int value1 = resultSet1.getInt( "Field" );
resultSet1.close();
// this will give "Connection 1: 1" as it should be
System.out.println ( "Connection 1: " + value1 );

// make select on same connection and print content of field 1
ResultSet resultSet2 = statement2.executeQuery( sqlSelect );
resultSet2.next();
int value2 = resultSet2.getInt( "Field" );
resultSet2.close();
// this will give "Connection 2: 0" which is the old value
System.out.println ( "Connection 2: " + value2 );

statement2.close();
statement1.close();
connection1.close();
connection2.close();

}
catch ( Throwable t )
{
t.printStackTrace();
}
}

Joseph Weinstein · Guest

Daniel Hagen wrote: