I am running a script to delete a bunch of records from a local database. The script has many SQL statements in it, and the last 4 are all deletes - deleting no longer required records from the DB.

Every now and then, a record lock error occurs, and the entire script therefore fails.

The SQL specific to the error is similar to:

DELETE FROM Image
WHERE SiteVisitID IN (0, 17);

I run the script via the following code:

   qryScript.SQL.Text := thisSQL;
   qryScript.ConvertSQL();
   qryScript.ExecScript;

Recently, this has lead to the following error:

ElevateDB Error #1005 An error occurred with the statement at line 19 and column 22 (Cannot lock the row in the table Image).

I have not been able to reproduce the error, and after occurring a number of times, the user shut his laptop down, went home, and tried again later, and it worked without error.

My questions are:
1. is there a mechanism for extracting more information (or properties of the components) from the database / script component relating to the error?

2. I have used data aware controls throughout the application, and have multiple TEDBTable components connected to the same physical table. Whilst I realise this is very poor design, I'd like to focus on an expedient solution to this current problem.

If, before executing the script (ExecScript),  I close all pointers to the Image table, ie

tblImage<etc>.Active := False;
...
...
...
etc
   qryScript.ExecScript;

will this mean there is only one DB component accessing the table, and therefore it should have no problems with <DELETE RECORD> table locking issues?

Aaron

>My questions are:
>1. is there a mechanism for extracting more information (or properties of the components) from the database / script component relating to the error?

I don't know of any but that's not to say they're not there.

>2. I have used data aware controls throughout the application, and have multiple TEDBTable components connected to the same physical table. Whilst I realise this is very poor design, I'd like to focus on an expedient solution to this current problem.

I do the same thing with table components, and don't see anything wrong with it. It does, however, mean that you need to sort things out occasionally as here.

>If, before executing the script (ExecScript), I close all pointers to the Image table, ie
>
>tblImage<etc>.Active := False;
>..
>..
>..
>etc
> qryScript.ExecScript;
>
>will this mean there is only one DB component accessing the table, and therefore it should have no problems with <DELETE RECORD> table locking issues?

Yes, but, if its possible, the easiest way is to just close the database itself that means everything is disconnected.

An alternative is to learn the scripts programming language then, depending if you mind stuff hanging around or not, you could use exception handling and try several times before giving up in disgust or writing the info to a "sort it out later" table

Roy Lambert [Team Elevate].

Aaron,

<< 1. is there a mechanism for extracting more information (or properties of
the components) from the database / script component relating to the error?
>>

No, but I can add this to the list of enhancements.  Constraint errors do
this already by describing the primary key values.

<< 2. I have used data aware controls throughout the application, and have
multiple TEDBTable components connected to the same physical table. Whilst I
realise this is very poor design, I'd like to focus on an expedient solution
to this current problem. >>

That's not an issue, per se, but it could mean that the user has a row in
"edit" mode without being totally aware of the fact.

<< If, before executing the script (ExecScript),  I close all pointers to
the Image table, ie >>

Is this application single-user or multi-user ?  In either case, you don't
need to close all instances of the table, just making sure that any rows
aren't being edited is enough:

tblImage<etc>.CheckBrowseMode;

Tim Young
Elevate Software
www.elevatesoft.com

Thanks for the response, Roy, good to know I am not the only one doing things the "wrong way".

Tim: it's single-user.

I hear what you're saying about checkbrowsemode, but the thing is, nowhere in the app can you leave something in edit mode. In this app, if you add an image the record is saved automatically. As soon as you type in a descriptive name for the image, it's saved automatically. Rotate it and it's auto saved.

Thanks for the heads up, was unaware of checkbrowsemode, will definitely look into it.

The app (or in reality the laptop) may get shut down or lose power at any stage, and these are guys in the field, so as soon as you change something, it's saved. We got rid of all the save buttons. And I can't really see where you could put the table into edit mode and then leave it in that state while going to the Upload tab in the app and uploading the data.

Hence my blanket "do this and everything will be fine", "solution".

We want to start deploying on Wednesday too, (ie tomorrow) so quick and dirty wins for now.

Really appreciate the feedback. I told the IT manager we should be fine now, but it's nice to get some authoritative backup.

Aaron,

<< I hear what you're saying about checkbrowsemode, but the thing is,
nowhere in the app can you leave something in edit mode. In this app, if you
add an image the record is saved automatically. As soon as you type in a
descriptive name for the image, it's saved automatically. Rotate it and it's
auto saved. >>

Well, *something* has the row locked.  One thing you may want to try is
to hook up your application to the EDB Server temporarily and then do a
query on the Configuration.ServerSessionLocks table.  That will show you
what sessions have what rows locked in which tables.

Tim Young
Elevate Software
www.elevatesoft.com

Aaron,

I think part of your problem is probably that the problem is intermittent & therefore you probably don't have a firm handle on what is causing it. The first step I always take in that situation is actually to work to find the precise situation that causes the error to occur reliably. Once you can reliably create the error it becomes far clearer what is causing it.

DB Controls are fine, especially in a single-user situation.

I may be out of line here, (I use Query components alwaysnot tables) but I _always_ call Query.Close and Query.Unprepare when I want to be totally sure that a record is flushed to the table and the row is unlocked. I think you should perhaps try something similar in your application.

Perhaps <table>.Flushbuffers?? I regularly used to use this when I used tables.

Adam