File locking issues with Visual FoxPro |
Phil Ehrlich |
2015-03-17 09:37:59 |
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 |
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 |
>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 |
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 |
>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 |
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 |
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 |
>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 |
>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 |
|
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 |
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 |
|
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 |
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.
|
Main
webmaster@hxtt.com