Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Client/Server » View Thread |
Messages 1 to 7 of 7 total |
Direct access or Middleware? |
Wed, Mar 22 2006 3:15 PM | Permanent Link |
Tim Shields | Hi all,
Picture this : I have a table with one column which holds telephone numbers. I have a number of clients on different machines. Each client will dial a number, do some stuff, then ask for another number. Is it possible to have a kind of global record bookmark for a table, held on the DBISAM server, that each client has access to, or will I have to write some middleware to dish out the records in sequential order? I hope I've explained this OK! Thanks all, Tim. |
Wed, Mar 22 2006 5:06 PM | Permanent Link |
Steve Forbes Team Elevate | Hi Tim,
Not sure of your DB schema, so this may, or may not be appropriate. I am assuming you have some method of ordering the telephone numbers (could be a record id, date/time etc.), and some column to record whether the call has been made (probably an Operator Id and a Call date/time). You could do the following ... Wrap a get/update into a transaction e.g. (pseudocode) BeginTransaction; SQL := 'SELECT MIN(RecordId) FROM PhoneList WHERE OperatorId IS NULL'; RecordId := GetPhoneDetails; SQL := 'UPDATE PhoneList SET OperatorId = OpId, CallTime = CallTime WHERE RecordId = RecordId'; ParamByName('pmOpId').AsInteger := TIMS_OP_ID; ParamByName('pmCallTime').AsDateTime := Now; ParamByName('pmRecordId').AsInteger := RecordId; (from SELECT) EndTransaction; HTH -- Best regards Steve "Tim Shields" <tim@t-e-sys.com> wrote in message news:23401424fa2f8c81c04beef9958@news.elevatesoft.com... > Hi all, > > Picture this : I have a table with one column which holds telephone > numbers. I have a number of clients on different machines. Each client > will dial a number, do some stuff, then ask for another number. Is it > possible to have a kind of global record bookmark for a table, held on the > DBISAM server, that each client has access to, or will I have to write > some middleware to dish out the records in sequential order? > > I hope I've explained this OK! > > Thanks all, > > Tim. > > |
Thu, Mar 23 2006 5:52 AM | Permanent Link |
Tim Shields | Hello Steve,
Yup - I like this idea. However, I am a little concerned about what would happen if two clients access the database simultaniously? I don't know a whole lot about transactions, so I am guessing here from what I read in the manual. Assume 2 clients, Foo & Bar. Foo get's in first, gets its transaction lock, does the SELECT. Bar comes in after Foo has done SELECT but _before_ Foo has done UPDATE. Transaction lock, so can do reads but not writes. It manages to do the SELECT. Now Foo & Bar are looking at the same record??? What happens if Bar now tries the UPDATE (i.e. _before Foo)? Is an exception generated or will it wait till Foo completes the transaction? Or am I totally on the wrong path with this whole transactions business???!! (most likely!) Thanks again, Tim. > Hi Tim, > > Not sure of your DB schema, so this may, or may not be appropriate. I > am assuming you have some method of ordering the telephone numbers > (could be a record id, date/time etc.), and some column to record > whether the call has been made (probably an Operator Id and a Call > date/time). You could do the following ... > > Wrap a get/update into a transaction e.g. (pseudocode) > > BeginTransaction; > SQL := 'SELECT MIN(RecordId) FROM PhoneList WHERE OperatorId IS NULL'; > RecordId := GetPhoneDetails; > SQL := 'UPDATE PhoneList SET OperatorId = OpId, CallTime = > CallTime > WHERE RecordId = RecordId'; > ParamByName('pmOpId').AsInteger := TIMS_OP_ID; > ParamByName('pmCallTime').AsDateTime := Now; > ParamByName('pmRecordId').AsInteger := RecordId; (from SELECT) > EndTransaction; > HTH > > Steve > > "Tim Shields" <tim@t-e-sys.com> wrote in message > news:23401424fa2f8c81c04beef9958@news.elevatesoft.com... > >> Hi all, >> >> Picture this : I have a table with one column which holds telephone >> numbers. I have a number of clients on different machines. Each >> client will dial a number, do some stuff, then ask for another >> number. Is it possible to have a kind of global record bookmark for a >> table, held on the DBISAM server, that each client has access to, or >> will I have to write some middleware to dish out the records in >> sequential order? >> >> I hope I've explained this OK! >> >> Thanks all, >> >> Tim. >> |
Thu, Mar 23 2006 6:20 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
Another thought - use semaphore locks. The code below is chopped from an example Tim posted yonks back. Essentially have an index on phone number & been used indicator (bit like Steve suggested) Have a filter so only the unused ones were listed To get a phone number check semaphore lock if free set lock grab number update table free lock procedure TMainForm.LoginButtonClick(Sender: TObject); var SaveLockRetryCount: Integer; SaveLockWaitTime: Integer; begin { Verify the user ID first } if Users.FindKey([UserIDEdit.Text]) then begin { And then the password } if (PasswordEdit.Text = UsersPassword.AsString) then begin { Now check for an open user slot, starting at the first valid slot of 1 (valid slots are 1-1024). Notice the reduction of the lock retry count and lock wait time and then their restoration after the login. Semaphore locks use the same lock parameters as table and record locks } SaveLockRetryCount := Session.LockRetryCount; SaveLockWaitTime := Session.LockWaitTime; try Session.LockRetryCount := 0; Session.LockWaitTime := 0; FCurrentUserSlot := 1; while (FCurrentUserSlot <= TOTAL_USERS_ALLOWED) and (not Users.LockSemaphore(FCurrentUserSlot)) do Inc(FCurrentUserSlot); if (FCurrentUserSlot > TOTAL_USERS_ALLOWED) then begin MessageDlg('The total number of user licenses has been exceeded, ' + 'please contact your software supplier', mtInformation, [mbOk], 0); FCurrentUserSlot := 0; end else begin MessageDlg('Login successful', mtInformation, [mbOk], 0); LoginButton.Visible := False; LogoutButton.Visible := True; end; finally Session.LockRetryCount := SaveLockRetryCount; Session.LockWaitTime := SaveLockWaitTime; end; end else MessageDlg('Invalid password entered', mtError, [mbOk], 0); end else MessageDlg('Invalid user name entered', mtError, [mbOk], 0); end; procedure TMainForm.LogoutButtonClick(Sender: TObject); begin { Logout the user if they are already logged in (FCurrentUserSlot <> 0) } if (FCurrentUserSlot <> 0) then begin Users.UnlockSemaphore(FCurrentUserSlot); FCurrentUserSlot := 0; MessageDlg('User logged out', mtInformation, [mbOk], 0); LoginButton.Visible := True; LogoutButton.Visible := False; end; end; Roy Lambert |
Thu, Mar 23 2006 10:59 AM | Permanent Link |
Fernando Dias Team Elevate | Hi,
> What happens if Bar now tries the UPDATE (i.e. _before Foo)? Is an > exception generated or will it wait till Foo completes the transaction? The second transaction will only start when the first commits (or rollback), so when the second SELECT starts, the first update is done, and all users will see the same records. That's why transactions should be as short as possible. -- Fernando Dias |
Thu, Mar 23 2006 3:32 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Tim,
<< Yup - I like this idea. However, I am a little concerned about what would happen if two clients access the database simultaniously? I don't know a whole lot about transactions, so I am guessing here from what I read in the manual. >> Transactions are serialized in DBISAM, therefore two transactions cannot be executed at the same time and Bar will wait for Foo to finish before it will start. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Mar 23 2006 6:00 PM | Permanent Link |
Tim Shields | Thanks all you guys,
This has saved me a whole bunch of time and effort ... man I love DBISAM! Tim. Hello Tim Young [Elevate Software], > Tim, > > << Yup - I like this idea. However, I am a little concerned about what > would happen if two clients access the database simultaniously? I > don't know a whole lot about transactions, so I am guessing here from > what I read in the manual. >> > > Transactions are serialized in DBISAM, therefore two transactions > cannot be executed at the same time and Bar will wait for Foo to > finish before it will start. > |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |