Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 13 total |
What is maximum count of tables open in local cache? Serious AutoInc problem encountered? |
Wed, Jan 25 2006 12:34 PM | Permanent Link |
"David Mustard" | I have been wrestling with a problem for the last two or three days
concerning the acquisition of the LastAutoIncValue and finally after trying practically everything I could think of found out the following for which I would be grateful for confirmation. I am developing an application which currently is based on a Local Session. On opening the application is creates around 30 .dat files in localdir cache. I want to start a transaction, create a master record and post it then, using the LastAutoIncValue from the master table, insert a new record in the detail table(s) so that they are linked for future queries. If the user cancels the input from the detail record then a rollback situation occurs removing the master record and restoring the last autoinc value to its previous one. For the past couple of days on querying the last autoinc value of the master table I have been getting an absurd value in excess of 67 million; either using the LastAutoIncValue method or doing a select max(id) from mastertable. The record was not written to the master table and the whole scenario was regularly collapsing without completing the data write. Creating a quick and dirty test application with only the one table on board and testing the value of last autoinc after inserting to the table worked perfectly; even my colleague had no problems accessing and writing to the table. It was as soon as I used the current application that the problem kept persisting. To solve the situation I created an extra datasession and database, linked the master table to these new creations and inserted a new row. It worked perfectly, returned a valid correct last autoinc value and wrote the records to the detail tables as expected. Two to three days this cost me to sort it so far. Maybe I miss the point here but I would have thought that only 30 tables open in my local cache would not cause the problem to occur and quite honestly I couldn't find any reference to a limit or to the problem anywhere. Is there a way round this - or has anybody else encountered this? Are there any more wrinkles or limitations like this that we should be aware of? This one nearly drove me to distraction and caused me seriously to consider abandoning DBISAM for something else. cheers - David |
Wed, Jan 25 2006 12:37 PM | Permanent Link |
"David Mustard" | Forgot to mention: Delphi 7, DBISAM 4.22.4
Sorry! > |
Wed, Jan 25 2006 1:23 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | David
If all you did was to create another session & database it suggests to me a threading / isolation problem although the ones I know of haven't affected lastautoinc. Also a value in excess of 67 million suggests either a very large table or, more likely, there is corruption. DBISAM is pretty robust, but it is possible to screw it up. On the positive side, if you can create a demo that causes the problem on a repeatable basis Tim would be "delighted" to identify the root cause and fix it. Its one of the genuine benefits of using DBISAM as I know. The main problem I had was producing the demo app to reproduce the problem on a reliable basis. Roy Lambert |
Wed, Jan 25 2006 4:10 PM | Permanent Link |
"Frans van Daalen" | "David Mustard" <d dot m dot mustard at chello dot nl> wrote in message news:3DEC5538-0ED4-407C-9770-F6A3D5D31843@news.elevatesoft.com... >I have been wrestling with a problem for the last two or three days >concerning the acquisition of the LastAutoIncValue and finally after trying >practically everything I could think of found out the following for which I >would be grateful for confirmation. > .... > > I want to start a transaction, create a master record and post it then, > using the LastAutoIncValue from the master table, insert a new record in > the detail table(s) so that they are linked for future queries. If the > user cancels the input from the detail record then a rollback situation > occurs removing the master record and restoring the last autoinc value to > its previous one. > This last line sounds strange to me (but I'm no expert) : Can you share your code into showing us how you restore lastautoinc ? Is this a single user application ? |
Wed, Jan 25 2006 5:30 PM | Permanent Link |
"David Mustard" | I don't actually physically "restore" the last autoinc value; perhaps I
oversimplify the situation in my thread. The master record is written within a transaction pernding committal on acceptance of the detail record (by the user clicking OK). If OK then the detail record (within the same transaction) is written and the on the following commit statement both master and detail are written to the physical table. If the user clicks cancel on the detail entry form then the transaction is rolled back which removes the master record from the buffers and the autoinc value of the master table is not increased since nothing has been effectively posted to the physical table. Maybe restoring was the wrong word to use in this case. Of course it is possible to adjust the last autoinc value in code or in a DML sql statement as in ALTER TABLE tablename LAST AUTOINC value but if the ID also happens to be the primary index this has to be done with some care since it would cerainly have to be an unique value. Yes, in this current case it is a single user application but also written with a view to mulit-user usgae once thoroughly tested so most of the activity is transactional based. "Frans van Daalen" <Account@is.invalid> wrote in message news:17713DDA-0594-4422-8789-B7FE275F138A@news.elevatesoft.com... > > "David Mustard" <d dot m dot mustard at chello dot nl> wrote in message > news:3DEC5538-0ED4-407C-9770-F6A3D5D31843@news.elevatesoft.com... >>I have been wrestling with a problem for the last two or three days >>concerning the acquisition of the LastAutoIncValue and finally after >>trying practically everything I could think of found out the following for >>which I would be grateful for confirmation. >> > ... >> >> I want to start a transaction, create a master record and post it then, >> using the LastAutoIncValue from the master table, insert a new record in >> the detail table(s) so that they are linked for future queries. If the >> user cancels the input from the detail record then a rollback situation >> occurs removing the master record and restoring the last autoinc value to >> its previous one. >> > This last line sounds strange to me (but I'm no expert) : Can you share > your code into showing us how you restore lastautoinc ? Is this a single > user application ? > |
Wed, Jan 25 2006 5:41 PM | Permanent Link |
"David Mustard" | Hi Roy,
The table actually "offically" in testing has 13 record rows so not as large as you suspect! Although it would be possible to use such a large number by setting the last autoinc value in either code or in an sql statement. No I believe that this situation was possibly caused by the number of handles allocated at the time of attempting to read from and write to the table. By experimenting and adding a temporary session to address the table this allowed access to the master table and produced the correct reading of the last autoinc value. It also committed the record to the database. The reason why an eccessive number of tables were open is actaully a mystery because there shouldn't have been right at that moment but the master and detail grids do make a lot of use of lookup tables so that could explain it. I believe that the number of concurrently open tables can be adjusted in the engine but I need to take a closer look at the documentation and the source before I move further on this. I'm just relieved that after a couple of days there was some movement in the blockage although it was a pretty desperate action which solved it. The application and source are pretty complicated to cobble up an example for Tim to pore through and I'm not sure if I could actually extract just this one module from the application to show the problem adequately reliably. If I can I will certainly send Tim a demo if it cannot be sorted out after a little more reading up on the documnetaion and the source. cheers - David. "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message news:572089AF-FFFF-4DDB-98E4-94DE04294416@news.elevatesoft.com... > David > > > If all you did was to create another session & database it suggests to me > a threading / isolation problem although the ones I know of haven't > affected lastautoinc. > > Also a value in excess of 67 million suggests either a very large table > or, more likely, there is corruption. DBISAM is pretty robust, but it > is possible to screw it up. > > On the positive side, if you can create a demo that causes the problem on > a repeatable basis Tim would be "delighted" to identify the root cause and > fix it. Its one of the genuine benefits of using DBISAM as I know. The > main problem I had was producing the demo app to reproduce the problem on > a reliable basis. > > Roy Lambert > |
Wed, Jan 25 2006 7:04 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | David,
<< For the past couple of days on querying the last autoinc value of the master table I have been getting an absurd value in excess of 67 million; either using the LastAutoIncValue method or doing a select max(id) from mastertable. The record was not written to the master table and the whole scenario was regularly collapsing without completing the data write. >> Did you verify the tables to ensure that none were corrupted ? You can do so interactively using the DBSYS utility that comes with the Additional Software and Utilities download. That would be the first place to look. If that doesn't solve the problem, then my next suggestion would be to simply try a SELECT Max(ID) query from DBSYS on the table in question. If that works, then something is wrong in your code. You can also view the LastAutoIncValue property for a table in DBSYS, so that should answer any questions about whether the table has a whacky value in it for the last autoinc value. Also, just so you know - using transactions while waiting for user input is strongly discouraged with DBISAM. I noticed that you mentioned this in your other post, and I just wanted to point it out. You should only use transactions when you know that there is a definite and short timeframe for the transaction. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Jan 25 2006 7:28 PM | Permanent Link |
"David Mustard" | Thanks for the reply Tim; I had done all the checks you mention (honest!)
and although I would be the first to suspect my own code I had a colleague check it through twice - we also tried - the sql select max() route, - using an on-the-fly copy of the table and shooting for the lastautoincvalue, - testing the sql code in DBSYS, - testing input in DBSYS, - testing the code in a standalone application, - stepping through the code on tip-toe (!), - white heather and a rabbit's foot, - voodoo dolls, - totally rebuilding the table and resetting the data, - doing a repair on all tables, - crossing our fingers and rebuilding everything, - crossing our fingers and upgrading to 4.22.4 from 4.18, - crossing our eyes just in case it looked better, - crossing our legs because things were getting pretty dire, - doing a rename and copy and rename and using practically all the tools available in DBSYS (yes we were getting pretty desperate by this time) and - finally without making any changes in the actual code itself but just adding in another session and database to the data module and linking the table into the new session and database it all worked. Setting the database and session back to the original caused the same problem. The code I used was the simplest you could imagine: LastMasterID := tblAURolMaster.LastAutoIncValue; which kept failing whether or not the table was open or not. The debugger inspector was showing that some of the values were causing violations so it appeared that the table couldn't be opened for writing properly. This part of the application is too large and complicated to reproduce without sending all tables and source and I rely on DevExpress components so I would have to include the source on that as well. The crazy thing is that it had worked in the past and I had done no work on it recently so there is/was no reason to suspect the code. I take your point on the transaction matter and will rearrange the timing on writing data to the database - maybe the detail first, then the master then update the detail with the new master record id. That would mean that I could do this within a transaction on the user accepting the new detail record and would guarantee that the master record autoinc value returned would genuinely be the last one on the table. As I mentioned before I was getting really distressed on this one and still don't know what was causing the problem having covered practically every angle I and my coileague could think of. cheers - David Starange thing is that "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message news:ACBD86BF-9D56-4B6D-8D2B-032703064E11@news.elevatesoft.com... > David, > > << For the past couple of days on querying the last autoinc value of the > master table I have been getting an absurd value in excess of 67 million; > either using the LastAutoIncValue method or doing a select max(id) from > mastertable. The record was not written to the master table and the whole > scenario was regularly collapsing without completing the data write. >> > > Did you verify the tables to ensure that none were corrupted ? You can do > so interactively using the DBSYS utility that comes with the Additional > Software and Utilities download. That would be the first place to look. > If that doesn't solve the problem, then my next suggestion would be to > simply try a SELECT Max(ID) query from DBSYS on the table in question. If > that works, then something is wrong in your code. You can also view the > LastAutoIncValue property for a table in DBSYS, so that should answer any > questions about whether the table has a whacky value in it for the last > autoinc value. > > Also, just so you know - using transactions while waiting for user input > is strongly discouraged with DBISAM. I noticed that you mentioned this in > your other post, and I just wanted to point it out. You should only use > transactions when you know that there is a definite and short timeframe > for the transaction. > > -- > Tim Young > Elevate Software > www.elevatesoft.com > > |
Thu, Jan 26 2006 3:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | David
Ah someone else who uses my debugging system - but you forgot the step which starts "get drunk" (and often ends there as well) Had you thought of using memory tables for the user interface? Nice and easy, you can use all the normal data aware tools but it isolates things from the live data until you're ready to commit. Roy Lambert |
Thu, Jan 26 2006 8:26 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | David,
<< Thanks for the reply Tim; I had done all the checks you mention (honest!) and although I would be the first to suspect my own code I had a colleague check it through twice - we also tried >> What was the result of the checks that I recommended ? Is the table okay and so is the last autoinc value for the table ? -- Tim Young Elevate Software www.elevatesoft.com |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |