|
|
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)
HXTT DBF
|
Record deletion not updating the dbf/cdx |
Devika |
2013-07-18 06:45:25 |
Hi,
I am trying to delete records from a dbf file, but the records are still visible in the file. Also, corresponding dbf/cdx file seems to be not getting updated accordingly, as per the timestamp.
Are there any separate properties to be set fo deleting records? The connection is set to AutoCommit = False and with YRANSACTION_SERIALIZABLE isolation level.
Please advise ASAP as need to match the deadline for the project.
Regards,
Devika
|
Re:Record deletion not updating the dbf/cdx |
HXTT Support |
2013-07-18 07:00:16 |
Firt, you should call connection.commit() to submit your modification.
Secondly, deletesAreVisible connection property: Indicates whether DBF's resultSet includes deleted records. Default: false
If you can't see those deleted row with default deletesAreVisible=false, but can see it in your Foxpro application. It's normal.
Thirdly, if you wish to remove your deleted records for ever, you can use "pack table yourtable" sql to remove all deleted roecord once.
|
Re:Re:Record deletion not updating the dbf/cdx |
Devika |
2013-07-18 07:11:48 |
Dear Support Team,
Thanks for the prompt reply. I am deleting the records through Java Program and they should no more be visible in the DBF file , and hence should not be visible in the Legacy application that uses the same DBF file.
So as to achieve this what are the necessary steps ?
1. Yes, I am calling connection.commit();
2. Don't really understand this point.
2.1 Is it mandatory to set this property? What should the value it should be set to so that these records are not visible int eh DBF ?
3. Yes, these records are to be deleted permanatly. I looked at the documentation and found some instances where it explains above the above properties. But, is there any demo example available to understand it better ?
Regards,
Devika
|
Re:Re:Re:Record deletion not updating the dbf/cdx |
HXTT Support |
2013-07-18 07:35:04 |
DBF format only mark deleted row, but doesn't delete it in fact. So if you wish your data to be destroy at once, you need to use
update yourtable set yourColumn=null,...,yourColumnN=null where your deletecondtion;
Then
delete yourtable where youColumn=null and ...
Or
update yourtable set yourColumn='nothing',...,yourColumnN=0 where your deletecondtion;
Then
delete yourtable where youColumn='nothing' and ...
to let it become a deleted row.
Others can only see delete row with empty information or rubbish information.
Another way is run once "pack table yourtable" to remove all deleted rows before you exit your program.
All SQL syntax is lsted at here
|
Re:Re:Re:Re:Record deletion not updating the dbf/cdx |
Devika |
2013-07-18 07:56:42 |
I am doing following now :
1. DELETE FROM WHERE CONDITION;
- aquire the row level lock first , by setting _lockFlag = true for the row
- execute the statement to delete the record where
- unlock the record (as I have not yet committed the changes so the record still exists)
- commit the changes
- fire a statement PACK TABLE
I get following error :
java.sql.SQLException: java.nio.channels.ClosedChannelException ERROR : Please check the log file for error details
at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.concurrent.ap.a(Unknown Source)
at com.hxtt.concurrent.a.a(Unknown Source)
at com.hxtt.sql.a7.a(Unknown Source)
at com.hxtt.sql.dbf.i.if(Unknown Source)
at com.hxtt.sql.dbf.w.do(Unknown Source)
at com.hxtt.sql.bz.a(Unknown Source)
at com.hxtt.sql.bz.a(Unknown Source)
at com.hxtt.sql.bz.a(Unknown Source)
at com.hxtt.sql.am.a(Unknown Source)
at com.hxtt.sql.am.a(Unknown Source)
at com.hxtt.sql.am.a(Unknown Source)
at com.hxtt.sql.am.executeUpdate(Unknown Source)
at com.dlr.sqlrunner.SQLRunner.parseAndExecute(SQLRunner.java:211)
at com.dlr.sqlrunner.SQLRunner.main(SQLRunner.java:41)
For PACK TABLE to be used, is it mandatory to take the DBF's offline ?
Re:Re:Re:Re:Record deletion not updating the dbf/cdx |
Devika |
2013-07-18 07:57:53 |
Sorry, I copied wrong error stack trace. Please refer below :
I get following error :
java.sql.SQLException: Failed to pack table D:\apps\UOD\Prod\MSPORSUM.dbf because it has been locked by another process
ERROR : Please check the log file for error details
at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.sql.dbf.i.if(Unknown Source)
at com.hxtt.sql.dbf.w.do(Unknown Source)
at com.hxtt.sql.bz.a(Unknown Source)
at com.hxtt.sql.bz.a(Unknown Source)
at com.hxtt.sql.bz.a(Unknown Source)
at com.hxtt.sql.am.a(Unknown Source)
at com.hxtt.sql.am.a(Unknown Source)
at com.hxtt.sql.am.a(Unknown Source)
at com.hxtt.sql.am.executeUpdate(Unknown Source)
at com.dlr.sqlrunner.SQLRunner.parseAndExecute(SQLRunner.java:211)
at com.dlr.sqlrunner.SQLRunner.main(SQLRunner.java:41)
|
Re:Re:Re:Re:Re:Re:Record deletion not updating the dbf/cdx |
HXTT Support |
2013-07-18 08:04:51 |
>java.sql.SQLException: Failed to pack table D:\apps\UOD\Prod\MSPORSUM.dbf because
>it has been locked by another process
PACK TABLE sql will require an exclusive table lock. If you're using lockType=Foxpro for Xbase's concurrent access, it thrown Timeout exception because that table is holding by other application. So on that occasion, use PACK TABLE sql is not advisable.
>- aquire the row level lock first , by setting _lockFlag = true for the row
On most occassions, need not set lock manually, because HXTT DBF can do it automatically for your sql. So you need only update sql or delete sql.
|
Re:Re:Re:Re:Re:Re:Re:Record deletion not updating the dbf/cdx |
Devika |
2013-07-18 08:27:05 |
Hi,
OK. So, that means PACK TABLE can not be used, as this table would be shared by the application.
Sorry for being a pain, but still not clear on how should I perform the delete operation. I am manually marking the transactions and committing them so as to handle atomic transactions.
May I please request to provide any demo code snippet to get the delete working, as it seems it is not that straightforward. If you have any sample program to demonstrate this , that would do wonders.
Regards,
Devika
|
Re:Re:Re:Re:Re:Re:Re:Re:Record deletion not updating the dbf/cdx |
HXTT Support |
2013-07-18 08:41:26 |
>OK. So, that means PACK TABLE can not be used, as this table would be shared by the application.
Yeah. If you use a bigger lockTimeout connection property, it won't throw Timeout exception, and wait other application's lock release.
>I am manually marking the transactions and committing them so as to handle atomic transactions.
You needn't manuall work, and HXTT DBF supports TRANSACTION_READ_UNCOMMITTED on Xbase compatible mode.
|
Re:Re:Re:Re:Re:Re:Re:Re:Record deletion not updating the dbf/cdx |
HXTT Support |
2013-07-18 08:42:36 |
>May I please request to provide any demo code snippet to get the delete working
What's your want method? Deleted it throw ResultSet, RowSet, or SQL?
|
Re:Re:Re:Re:Re:Re:Re:Re:Record deletion not updating the dbf/cdx |
Devika |
2013-07-18 09:00:21 |
Hi,
Ok. Little background information :
1. We have a legacy application in Xbase using DBF
2. What I am working on is a program that would take .sql script , parse and execute the statements in an atomic manner. i.e. either all executed or NONE. Hence, I am manually handling the locks.
3. The program is working fine if the script has INSERT, UPDATE statements , but it's only DELETE which is not working. I do not get any sql exception while executing the DELETE operation, so it 'appears' as if the entire script went through successfully, but if I manually check the data in DBF, delete operation is misleading as it has not actually deleted the data.
The code snippet is as follows :
1. Parse each SQL statement from the .sql script
2. handle locking and unlocking mechanism for DELETE / UPDATE operations
3. If no error is detected for the sql statements from the .sql file - indicates that the statements should be committed.
String sql = DELTE statement from .sql file
lock and unlock the record to make sure that no other process is working on the same record (sql.executeUpdate)
and then,commit it
|
Re:Re:Re:Re:Re:Re:Re:Re:Record deletion not updating the dbf/cdx |
HXTT Support |
2013-07-18 18:51:38 |
>delete operation is misleading as it has not actually deleted the data.
2013-07-19 provides deletesAreBlank connection property for DELETE operation.
Indicates whether one data record will become one deleted blank record when delete it. Default:false
|
Re:Re:Re:Re:Re:Re:Re:Re:Record deletion not updating the dbf/cdx |
Devika |
2013-07-19 01:08:43 |
Hi there,
Thanks. May I please request to provide some sample code snippet for delete functionality or some demo program to illustrate the usage? I neded to get this working on priority so a sample program would be beneficial.
Regards,
Devika
|
Re:Re:Re:Re:Re:Re:Re:Re:Record deletion not updating the dbf/cdx |
Devika |
2013-07-19 03:13:39 |
Hi,
I tried with setting the above mentioned property to true, but no luck . It seems that the delete is NOT even marking the rows as deleted.
I will be grateful if you could please provide some code snippet for reference please.
Regards,
Devika
|
Re:Re:Re:Re:Re:Re:Re:Re:Record deletion not updating the dbf/cdx |
HXTT Support |
2013-07-19 03:48:33 |
First, you need to download the latest package.
Secondly, you need to add deletesAreBlank=true connection property.
Then your DELETE sql will work.
|
Re:Re:Re:Re:Re:Re:Re:Re:Record deletion not updating the dbf/cdx |
Devika |
2013-07-23 06:47:39 |
Dear Support Team,
Thank you very much for your help in this issue.The problem was in the way connections were handled. Now, this is resolved.
Thanks again,
Devika
|
|
| |
Main
