Solved

Table corrupted when connection to database terminated during updating database

Posted on 2001-07-01
8
319 Views
Last Modified: 2008-02-26
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
Comment
Question by:liseko
8 Comments
 
LVL 1

Accepted Solution

by:
superchook earned 150 total points
ID: 6243829
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
 
LVL 5

Expert Comment

by:ianouii
ID: 6244408
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 7140027
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
 
LVL 5

Expert Comment

by:ianouii
ID: 7140042
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 49

Expert Comment

by:DanRollins
ID: 7140116
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
 
LVL 5

Expert Comment

by:ianouii
ID: 7142205
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
 
LVL 1

Expert Comment

by:Computer101
ID: 7178905
Points split

Computer101
E-E Moderator
0
 

Author Comment

by:liseko
ID: 11508786
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access 2016 VB code 9 86
VBA open file from excel cell 4 34
How does CurrentUser work? 10 26
Added a column screws up code 5 17
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now