In v7.0.074, there are a few issues with updates and inserts when the isolation level is Read_Committed.
1. If you attempt to update a table with an index on .Not.Deleted() the table will be in a corrupt state and cannot be used.
2. An update to a table with no index does not work. There are no errors, the update just does not happen.
3. The insert succeeds but CDX file/index is not updated to reflect the change.
Reproduce problems 1 and 2:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DBFUpdate
{
static final String JDBC_DRIVER = "com.hxtt.sql.dbf.DBFDriver";
static final String UPDATE = "update so.dbo.client set Cclient_desc = 'test' where Cclient_desc = 'TestClient'";
public static void main(String[] args) throws Exception
{
if (args.length != 1)
{
System.err.println("Usage: DBFUpdate url");
System.exit(-1);
}
String url = args[0];
Class.forName(JDBC_DRIVER);
Connection connection = DriverManager.getConnection(url, "", "");
System.out.println("Executing sql '" + UPDATE + "'");
// This sets the isolation level and causes the problem
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
try (PreparedStatement ps = connection.prepareStatement(UPDATE))
{
ps.executeUpdate();
}
connection.close();
System.out.println("Done");
}
}
Reproduce problem 3 where the inserts don't update the index when isolation level is "ReadCommitted":
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DBFInsert
{
static final String JDBC_DRIVER = "com.hxtt.sql.dbf.DBFDriver";
static final String INSERT = "insert into so.dbo.ASSIGNED (cassigned_key, cdesc, lactive) values (?, ?, ?)";
static final Object[] VALUES = new Object[] { "test", "test", 1 };
public static void main(String[] args) throws Exception
{
if (args.length != 1)
{
System.err.println("Usage: DBFInsert url");
System.exit(-1);
}
String url = args[0];
Class.forName(JDBC_DRIVER);
Connection connection = DriverManager.getConnection(url, "", "");
System.out.println("Executing sql '" + INSERT + "'");
// This is needed to trigger the NullPointerException
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
try (PreparedStatement ps = connection.prepareStatement(INSERT))
{
ps.setString(1, (String)VALUES[0]);
ps.setString(2, (String)VALUES[1]);
ps.setInt(3, ((Number)VALUES[2]).intValue());
ps.executeUpdate();
}
connection.close();
System.out.println("Done");
}
}
Here's the code in FoxPro for creating the dbc and tables:
jmess = MESSAGEBOX('Warning - Are you in a blank test folder?',36,'Test for HXTT')
IF jmess <> 6 && yes
RETURN
ENDIF
ERASE assigned.dbf
ERASE assigned.cdx
ERASE so.*
CREATE DATABASE 'SO.DBC'
CREATE TABLE 'ASSIGNED.DBF' NAME 'ASSIGNED' (CASSIGNED_KEY C(10) NOT NULL DEFAULT SYS(2015), ;
CDESC C(30) NOT NULL, ;
LACTIVE L NOT NULL DEFAULT .T.)
***** Create each index for ASSIGNED *****
ALTER TABLE 'ASSIGNED' ADD PRIMARY KEY CASSIGNED_KEY TAG AAAA COLLATE 'MACHINE'
INDEX ON CDESC TAG DESC FOR .NOT.DELETED() CANDIDATE COLLATE 'MACHINE'
insert into assigned(cassigned_key,cdesc,lactive) values ('test','test',.t.)
=MESSAGEBOX('Expect to see assigned.dbf and assigned.cdx with a new modified date after closing out of foxpro.',64,'Test for HXTT')
QUIT
|
|
2020-03-10 fixed a transaction bug for TRANSACTION_READ_COMMITTED level since 2019-12-3
|
Hello.
The first two items are fixed. Thank you.
However the third item remains a problem. Inserting into a table that has an index Not.Deleted() and with TRANSACTION_READ_COMMITTED does not update the index so it cannot be seen by other clients connected using the jdbc driver.
In the original post there is a small java class to reproduce the problem, as well as VFP code to generate a DBC and table to help reproduce the problem.
|
|
Supported. Please download the latest package.
|
Hello again.
Unfortunately we're getting a NullPointerException again on insert into a record with .NotDeleted() filter.
Executing sql 'insert into so.dbo.ASSIGNED (cassigned_key, cdesc, lactive) values (?, ?, ?)'
Exception in thread "main" java.sql.SQLException: java.lang.NullPointerException
at com.hxtt.sql.d7.if(Unknown Source)
at com.hxtt.sql.f.a(Unknown Source)
at com.hxtt.sql.ec.a(Unknown Source)
at com.hxtt.sql.ec.a(Unknown Source)
at com.hxtt.sql.ag.a(Unknown Source)
at com.hxtt.sql.e3.a(Unknown Source)
at com.hxtt.sql.dbf.i.a(Unknown Source)
at com.hxtt.sql.dl.if(Unknown Source)
at com.hxtt.sql.b7.for(Unknown Source)
at com.hxtt.sql.cf.do(Unknown Source)
at com.hxtt.sql.ef.a(Unknown Source)
at com.hxtt.sql.b4.a(Unknown Source)
at com.hxtt.sql.b4.if(Unknown Source)
at com.hxtt.sql.b4.a(Unknown Source)
at com.hxtt.sql.ao.a(Unknown Source)
at com.hxtt.sql.ao.a(Unknown Source)
at com.hxtt.sql.eg.executeUpdate(Unknown Source)
at DBFInsert.main(DBFInsert.java:36)
|
|
Recurred. Supported. Please download it.
|
|
This time all tests passed. Thank you.
|
Main
webmaster@hxtt.com