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.

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.
>
>

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.
>>

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

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

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

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.
>