Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
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
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT DBF
File locking issues with Visual FoxPro
Phil Ehrlich
2015-03-17 09:37:59.0
Hello,

Our company purchased your DBF driver and we have been trying to use it in a web application (Oracle ADF/WebLogic 12c), and we are getting errors in the java application referencing file locks. We see these 2 errors often:

Error (1)

java.sql.SQLException: Failed to lock the data region[2147483646,2147483647] of purch.dbf
==> Additional Info: query: SELECT count (*) FROM cmsmsg msg INNER JOIN purch ON msg.pono = purch.pono AND purch.void = false WHERE msg.timeread is null AND msg.oprecvid = '000000002338'

Error (2)

java.sql.SQLException: Index file websubset.CDX has been locked by another process


Both of these errors occur during SELECT queries.

I am getting my JDBC connection like this:

Connection c = DriverManager.getConnection("jdbc:dbf:/c:/System/Live/Sas/data?lockType=VFP");

There are 2 other applications using this database: a Visual FoxPro application (7.0 and 9.0) and an ASP.NET application using the FoxPro OleDB driver. The VFP app and the ASP.NET website have been working fine together for years, but whenever we add the java application (using HXTT) then the file lock errors above start to appear and all 3 applications start to hang and experience slowness.

Our java environment is J2SE 1.7 and I am using your DBF_JDBC40.jar package. I am not using the 'Support Library for Xbase applications' compatible lock on Windows, Linux, and OS2' library because the documentation leads me to believe it is not necessary for java 1.7.

Please advise.

Thank you
Phil Ehrlich
Re:File locking issues with Visual FoxPro
Phil Ehrlich
2015-03-17 11:16:17.0
Hi,

To further clarify, we need a way to query tables using HXTT even if they are locked by one of the other applications. For instance, if I have a table locked in Visual FoxPro, I can open another instance of Visual FoxPro and query that table. I cannot update the table (since it is locked) but I can query from it. I need to be able to do the same thing using the HXTT driver (query a locked table).

I have tried all settings of java.sql.Connection.setTransactionIsolation() and that did not help.

The question is why is HXTT trying to lock tables and indexes for SELECT operations? And how can this be resolved?

Thank you
Phil Ehrlich
Re:Re:File locking issues with Visual FoxPro
HXTT Support
2015-03-18 03:21:09.0
>The question is why is HXTT trying to lock tables and indexes for SELECT operations?
To aovid the possible modification from other application so that read obsoleted data. If you need only read from Java application, you can remove lockType=VFP connection property, and add delayedClose=0 connection property.
Re:Re:Re:File locking issues with Visual FoxPro
Phil Ehrlich
2015-03-19 08:34:45.0
Hi, removing the lockType=VFP connection property appears to fix the locking issue (we can query tables that are locked), however we have noticed that removing this connection property causes some query results to be incorrect.

For instance, when I execute a query with lockType=VFP, I get the correct results (22 records found). I then execute the exact same query without the lockType=VFP connection property and get 0 records returned. This is on a development system where I am the only user, so the data is not changing. I can also go back and forth - running the exact same query, every time I specify lockType=VFP I get 22 records found, every time I remove it, I get 0 records found.

Please advise

Thanks
Phil
Re:Re:Re:Re:File locking issues with Visual FoxPro
HXTT Support
2015-03-19 08:52:12.0
>every time I specify lockType=VFP I get 22 records found, every time I remove it, I get 0 records found.
What's your select sql?
Re:Re:Re:Re:Re:File locking issues with Visual FoxPro
Phil Ehrlich
2015-03-19 08:55:52.0
The select SQL is:

SELECT count (*)
FROM cmsmsg msg
INNER JOIN purch
ON msg.pono = purch.pono
AND purch.void = false
WHERE msg.timeread is null
AND msg.clrecvid = '000000000002'

Re:Re:Re:Re:Re:Re:File locking issues with Visual FoxPro
Phil Ehrlich
2015-03-19 09:15:54.0
I will also add that there are many indexes on these 2 tables. Relevant indexes include:

cmsmsg.pono (type=regular, expression=pono)
cmsmsg.timeread (type=regular, expression=timeread)
cmsmsg.clrecvid (type=regular, expression=clrecvid)
purch.pono (type=regular, expression=pono)

Thanks
Phil
Re:Re:Re:Re:Re:Re:Re:File locking issues with Visual FoxPro
HXTT Support
2015-03-23 06:36:43.0
>This is on a development system where I am the only user, so the data is not changing.
Your VFP application hasn't run?

>msg.timeread is null
Try msg.timeread='' to see what happen. It should be an index issue, and HXTT DBF chose the different index match for your sql.
Re:Re:Re:Re:Re:Re:Re:Re:File locking issues with Visual FoxPro
Phil Ehrlich
2015-03-23 07:14:46.0
>Your VFP application hasn't run?

--That's correct. The VFP application is not running on this machine at all. I took a copy of the tables to a completely different machine to troubleshoot this issue.


>Try msg.timeread='' to see what happen. It should be an index issue, and HXTT DBF chose the different index match for your sql.

--OK. I just tried changing the query to say msg.timeread='' and that returns 0 records for both lockType=VFP and not. Which is the incorrect answer. The correct answer is 22 records.

I also do not understand why HXTT would choose a different index path based on whether I include lockType=VFP or not? The query is the same, the only difference is that connection parameter.

In any case, the query modification does not wor, so we still don't have a solution or workaround for querying tables and getting correct results without specifying lockType=VFP.

Please advise.

Thanks
Phil
Re:Re:Re:Re:Re:Re:Re:Re:File locking issues with Visual FoxPro
HXTT Support
2015-03-23 21:13:29.0
If possible, you can email us your test database sample.
Re:Re:Re:Re:Re:Re:Re:Re:File locking issues with Visual FoxPro
Phil Ehrlich
2015-03-25 07:48:59.0
Hi, I think I have found a workaround. Apparently when HXTT query plan looks only at the index, it sees false and null as equivalent (maybe this is a FoxPro thing in how they build the index?) but when HXTT looks directly at the table, it sees false and null values as different. So, I have changed my query to use AND nvl(purch.void, false) = false and that seems to work in all cases.

Thanks
Phil
Re:Re:Re:Re:Re:Re:Re:Re:File locking issues with Visual FoxPro
HXTT Support
2015-03-25 09:22:14.0
Yeah. HXTT DBF provides emptyStringAsNullfor string comparison. But false and null are different value in SQL.
Re:Re:Re:Re:Re:Re:Re:Re:File locking issues with Visual FoxPro
HXTT Support
2015-03-26 00:24:42.0
Have tested.
1st. When void is a nullable logical column, which is supported by VFP. You can use
select * from table2 where void=null; //which get ros for null value.
select * from table2 where void=false;//which get all rows for false or empty value.

2nd. If void is a logical column, which allows empty value, but doesn't support .NULL. value.
A. If void is indexed column:
select * from table2 where void=null; //which get no row.
select * from table2 where void=false;//which get all rows for false value or empty value.
B. If void is not indexed column:
select * from table2 where void=null; //which get rows for empty value.
select * from table2 where void=false;//which get rows for false value.

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap