Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

Table corrupted when connection to database terminated during updating database

Platform: Win98(client), Windows NT 4.0 (server)
Development software: MS Visual Basic 6.0
Other: ODBC Visual FoxPro driver version 6.00.8428.00 or 6.00.8167.00 or 6.01.8629.01,
VB6.0 references: MS ADO 2.0 library

Steps to Reproduce Behavior
---------------------------
Create a database with some dummy tables using MS Visual Foxpro 6.0. In this example, the table used
is 'table1' table with fields: field1 (numeric, size 10, primary keys) and field2(character, size 4).
Table must have primary keys. Situated the database in a PC as server (file server) and the following
codes is executed in another PC as client. Program in client side will running this VB program using
ADO 2.0 to access to the database in another PC via ODBC Visual Foxpro driver in the client PC.
Run following codes.

Dim cnn1 As New ADODB.Connection
Dim rs1 As New Recordset
Dim i As Integer

cnn1.Open "DSN=test"
cnn1.BeginTrans
Set rs1 = cnn1.Execute("Select * from table1")

For i = 1 To 10000
  rs1.AddNew
  rs1!field1 = i
  rs1!field2 = "test"
  rs1.Update
Next

cnn1.CommitTrans
cnn1.Close

During execution of the transaction, update table1, interrupt the transaction by
1) Terminate the network connection such as pull off the network cable between client PC and server
PC.
or
2) Cause power failure to the either PC.
The updating table (table1) will be corrupted. Possible cause, table1.dbf and table.cdx file is not
updated relatively.

Problem
-------
After the table is corrupted, update table on the next time does not return error noting the table had
been corrupted. Transaction is done as if there are not problem or corruption on the database. ADO or
ODBC Visual FoxPro DO NOT trigger error or detect problem when accessing/updating the corrupted table.
ADO or ODBC Visual FoxPro DO NOT be able to repair the corrupted table.

Answer needed
------
How to detect the corrupted table on the following accessing to the table. If possible, through the
same method of VB 6.0 -> ADO -> ODBC VFP -> VFP database. 'Writing a dummy data into the table and retrieve
it again to check whether the table is corrupted' is not a acceptable solution because it cause much
processing to do so and not practical for operations that update the database frequently as detection
of corrupted table must be done on fly or immediately after the occurance.

How to repair the corrupted table. If possible, through the same method of VB 6.0 -> ADO -> ODBC VFP
-> VFP database.

Open to any possible answers!!
0
liseko
Asked:
liseko
1 Solution
 
superchookCommented:
If you are using a non client-server database, then you do run the risk od non-sync and/or incomplete data transactions - leading to inevitable corruption.

You have been quite lucky - as at least you have seen the errors in the form of outright inability access the data.  The more insidious corruption is where everything seems ok, but there is a linked table or some other relationship that hasn't been updated correctly.

The LAN disconnect is probably the worst case, as it potentially combines both types of error scenario...  remember that with your database methods, you are processing the database locally, so full pages need to be pulled across the network, modified and written back to the 'server'.

In a client-server model, only the data to be written is sent.  The processing is performed a the remote 'server' end of the connection.

There is only one almost foolproof way of protecting yourself in a shared database environment use transaction style batching of additions/updates...  look at the BeginTrans, Rollback and EndTrans functions to assist in this area.

Good luck

0
 
ianouiiCommented:
make an error catcher in your database function.

Dim cnn1 As New ADODB.Connection
Dim rs1 As New Recordset
Dim i As Integer

on error goto ErrHandleror

cnn1.Open "DSN=test"
cnn1.BeginTrans
Set rs1 = cnn1.Execute("Select * from table1")

For i = 1 To 10000
 rs1.AddNew
 rs1!field1 = i
 rs1!field2 = "test"
 rs1.Update
Next

cnn1.CommitTrans
cnn1.Close

ErrHandleror:
do the necessary things here.

with the error catcher, u should be able to know when was the error happen & what to do next. the error can be easily view by putting this codes.

err.description
err.number

good luck.

0
 
DanRollinsCommented:
Hi liseko,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will suggest to:

    Split points between: superchook and ianouii

liseko, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will follow up.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ianouiiCommented:
I suppose I answered the question.

> How to detect the corrupted table on the following
> accessing to the table. If possible, through the
> same method of VB 6.0 -> ADO -> ODBC VFP -> VFP
> database. 'Writing a dummy data into the table and
> retrieve it again to check whether the table is
> corrupted' is not a acceptable solution because it cause > much processing to do so and not practical for
> operations that update the database frequently as
> detection of corrupted table must be done on fly or
> immediately after the occurance.

must we share the points?
0
 
DanRollinsCommented:
Are we getting just a tad greedy?  superchook posted first with some perceptive comments (include the pivotal concept of using transactions) and there is no guarantee that your answer actually solves the problem.  Let's see if anything happens this week.  -- Dan
0
 
ianouiiCommented:
hehe... sometimes, those points really drive me crazy... somehow, I'm lazy to answer new posting questions since there're many abandoned & opened questions.
Well, it's up to u guys to decide then. I'm ok either way. :D
0
 
Computer101Commented:
Points split

Computer101
E-E Moderator
0
 
lisekoAuthor Commented:
INFO: Possible Causes of Data Corruption in Visual FoxPro

Microsoft Knowledge Base:
Last Reviewed: 5/12/2003 (2.0)  
Keywords: kbDatabase kbinfo KB264867
Q264867
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now