Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Inconsistent Error # 1005 (Cannot lock the row in the table ...) when deleting record(s) |
Sun, Jul 28 2013 7:55 PM | Permanent Link |
Aaron Christiansen | 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? |
Mon, Jul 29 2013 3:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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]. |
Mon, Jul 29 2013 1:16 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Jul 29 2013 11:57 PM | Permanent Link |
Aaron Christiansen | 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. |
Wed, Jul 31 2013 1:19 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Jul 31 2013 6:55 PM | Permanent Link |
Adam Brett Orixa Systems | 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 |
This web page was last updated on Wednesday, May 15, 2024 at 08:40 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |