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
Record deletion not updating the dbf/cdx
Devika
2013-07-18 06:45:25.0
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.0
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.0
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.0
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.0
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.0
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.0
>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.0
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.0
>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.0
>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.0
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.0
>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.0
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.0
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.0
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.0
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

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