Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.1
  HXTT Cobol v5.0
  HXTT DBF v7.1
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v6.1
  HXTT Paradox v7.1
  HXTT PDF v2.0
  HXTT Text(CSV) v7.1
  HXTT Word v1.1
  HXTT XML v4.0
Offshore Outsourcing
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT DBF
ReadCommitted problems with updates and inserts
Shackleford
2020-03-09 14:07:22.0
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
Re:ReadCommitted problems with updates and inserts
HXTT Support
2020-03-10 07:59:23.0
2020-03-10 fixed a transaction bug for TRANSACTION_READ_COMMITTED level since 2019-12-3
Re:Re:ReadCommitted problems with updates and inserts
Shackleford
2020-03-12 10:47:52.0
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.
Re:Re:Re:ReadCommitted problems with updates and inserts
HXTT Support
2020-03-12 22:37:19.0
Supported. Please download the latest package.
Re:Re:Re:Re:ReadCommitted problems with updates and inserts
Shackleford
2020-03-13 14:30:56.0
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)
Re:Re:Re:Re:Re:ReadCommitted problems with updates and inserts
HXTT Support
2020-03-13 20:55:48.0
Recurred. Supported. Please download it.
Re:Re:Re:Re:Re:Re:ReadCommitted problems with updates and inserts
Shackleford
2020-03-17 07:11:29.0
This time all tests passed. Thank you.

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Sitemap