Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Client/Server » View Thread |
Messages 1 to 6 of 6 total |
Using a control table for integer keys |
Fri, Nov 14 2008 11:29 PM | Permanent Link |
Tony Pomfrett | Hi,
I am using a Control table to issue integer keys for various tables in my client/server application. This is to avoid using autoinc (for historical reasons that I can't recall). This involves locking the control table, obtaining the integer key, incrementing the key and then unlocking the control table ready for the next access. The Control table has one record of around 30 fields each representing the next key for a particular table. The plan worked well initially. But with more users accessing the database over the Internet we are experiencing slower operation due to less available bandwidth per user. The effect is that some users are unable to lock the control table within their allotted number of attempts and this occurrence is not handled properly - basically, their app crashes. Secondly, some users are managing to retrieve keys that are null valued - probably a coding error allowing this to slip through. The first time it happens a user obtains and uses a null key. The second user to obtain a null key gets a duplicate key error and his app crashes. So what I need is a bullet proof routine for retrieving keys from a control table and updating the next key value. I also need to make the app tolerant to the situation where a client app cannot lock the control table due to heavy usage. i.e. ask the user to wait and try again. Does anybody have such a routine or can anybody point me in the right direction? |
Sat, Nov 15 2008 3:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tony
If you have to continue with this rather than switch to AUTOINCs the first thing I'd do is use a record rather than a field paradigm ie one record for each integer you want to manage. I don't know what the spread of required new numbers is but any time anyone wants a new number for any one of those 30 fields you have to lock the record. That's increasing the odds of timeouts. With 30 records you have a minor performance hit as they have to do a FindKey each time but you're reducing contention. This is a function I used to use for managing record locks function LockTheRecord(WhichTable: TDBISAMTable; AbortAllowed: boolean): boolean; var ExitControl: boolean; TempMsg: string; WhichButtons: set of TMsgDlgBtn; begin ExitControl := True; Result := True; if AbortAllowed then WhichButtons := [mbOK, mbIgnore] else WhichButtons := [mbOK]; while ExitControl do begin try WhichTable.Edit; ExitControl := False; except on E: EDataBaseError do begin TempMsg := 'Unable to lock ..' + #13#10; TempMsg := TempMsg + #13 + E.Message; TempMsg := TempMsg + #13 + 'Try again'; if AbortAllowed then TempMsg := TempMsg + ' or ignore'; TDBISAMTable(WhichTable).FlushBuffers; WhichTable.Refresh; if MessageDlg(TempMsg, mtConfirmation, WhichButtons, 0) = mrIgnore then begin ExitControl := False; Result := False; end; end; end; end; end; Naturally you'll have to manage program flow around it. Roy Lambert [Team Elevate] |
Mon, Nov 17 2008 3:53 PM | Permanent Link |
"John Easley" | > I am using a Control table to issue integer keys for various tables in my
> client/server application. This is to avoid using autoinc (for historical > reasons that I can't recall). Have you thought about using GUIDs as keys? John |
Mon, Nov 17 2008 5:26 PM | Permanent Link |
"Robert" | "Tony Pomfrett" <tonyp@aline.com.au> wrote in message news:6A0B301F-43CB-466E-9FA8-32A1A3943F75@news.elevatesoft.com... > > Does anybody have such a routine or can anybody point me in the right > direction? Make sure you do a refresh before you edit, otherwise you could be assigning a duplicate key. Then trap the timeout exception, and simply try again. Robert > |
Mon, Nov 17 2008 9:24 PM | Permanent Link |
"Jeff Cook" | Robert wrote:
> > "Tony Pomfrett" <tonyp@aline.com.au> wrote in message > news:6A0B301F-43CB-466E-9FA8-32A1A3943F75@news.elevatesoft.com... > > > > Does anybody have such a routine or can anybody point me in the > > right direction? > > Make sure you do a refresh before you edit, otherwise you could be > assigning a duplicate key. Then trap the timeout exception, and > simply try again. > > Robert > > Like this ... procedure TapmDM.GetNewKey(Tab: TDBISAMTable; Min: Integer; Max: Integer); { Get a sequential key - returned in NewKey - Min is the start number of the sequence - Max is the top of the sequence - then revert to Min} var Waited: Boolean; sSimpleText: string; iColor: integer; begin Waited := False; OpenTable(LastKeyTable); with LastKeyTable do begin Refresh; if FindKey([Tab.Name]) then begin repeat try Edit; Break; except on E: Exception do begin if (E is EDBISAMEngineError) then begin if (EDBISAMEngineError(E).ErrorCode = DBISAM_RECLOCKFAILED) or (EDBISAMEngineError(E).ErrorCode = DBISAM_LOCKED) then begin Waited := True; with mainForm.mainStatusBar do begin sSimpleText := SimpleText; iColor := Color; SimplePanel := True; SimpleText := 'Waiting to lock LastKey record for ' + Tab.Name; end; Application.ProcessMessages; Screen.Cursor := crHourGlass; Continue; end else if (EDBISAMEngineError(E).ErrorCode = DBISAM_KEYORRECDELETED) then begin apmDM.LastKeyTable.Refresh; Continue; end else begin MessageDlg(E.Message, mtError, [mbOK], 0); Break; end; end else begin MessageDlg(E.Message, mtError, [mbOK], 0); Break; end; end; end; until False; if Waited then begin mainForm.mainStatusBar.SimpleText := sSimpleText; mainForm.mainStatusBar.Color := iColor; Application.ProcessMessages; Screen.Cursor := crDefault; end; NewKey := apmDM.LastKeyTableLastValueUsed.Value + 1; if NewKey > Max then NewKey := Min; end else begin Insert; apmDM.LastKeyTableKeyName.Value := Tab.Name; NewKey := Min; end; apmDM.LastKeyTableLastValueUsed.Value := NewKey; Post; end; end; .... mainly copied out of these newsgroups Obviously tailored for my own needs, but appears bulletproof for years now (famous last words!). -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz + Joan and Jeff Cook The Cooks Oasis www.cookislandsoasis.com |
Tue, Nov 18 2008 8:08 AM | Permanent Link |
Tony Pomfrett | Hi,
Thanks for all the suggestions. I'll check my code and see if I can incorporate some of your ideas. I'll post what I come up with, just in case anybody is interested. Tony. |
This web page was last updated on Wednesday, March 27, 2024 at 05:29 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |