My application uses a table TableLastIDs (LastIDs.dat) where I keep track of
the last record IDs assigned to different datas (companies, employees etc).
With only 2 fields: Type and LastID.
I've been updating these informations throught direct table access, like
this (MyLastID is a global variable it will be used later by the program):

   With TableLastIDs do
       begin
          LockTable;
          Open;
          Locate('Type', 'Company',[]);
          MyLastID := FieldByName('LastID').AsInteger;
          Inc(MyLastID);
          Edit;
          FieldByName('LastID').AsInteger := MyLastID;
          Post;
          UnlockTable;
       end;

I wanted to do all this with queries.
I know the EXCLUSIVE keyword can be used at the query, according to the
Dbisam4 manual, but I am a little confused on how to guarantee the table
will be used exclusively during all the process of SELECTing, OPENing,
calculating the new value and then closing the Query to be able to UPDATE
it.
Thanks,

Marcio Ehrlich

M.E.

> I know the EXCLUSIVE keyword can be used at the query, according to the
> Dbisam4 manual, but I am a little confused on how to guarantee the table
> will be used exclusively during all the process of SELECTing, OPENing,
> calculating the new value and then closing the Query to be able to UPDATE

Use a transaction. There is a section with examples in the manual as I
recall.

Eryk

That's interesting, although there are no specific examples on how to use
transactions with queries.
Anyway, I guess if I use a transaction I will not need the EXCLUSIVE keyword
at the query, right?

Marcio


"Eryk Bottomley" <no@way.com> escreveu:

> Use a transaction.

M.E.,

> That's interesting, although there are no specific examples on how to use
> transactions with queries.

Ah, it is a long time since I looked at that, sorry.

> Anyway, I guess if I use a transaction I will not need the EXCLUSIVE keyword
> at the query, right?

EXCLUSIVE prevents anything else opening the table to read it whereas a
transaction only places a write lock, much the same as Table.LockTable.
To move to SQL you'll need to use two independent statements (a SELECT
and an UPDATE) and to ensure that these execute atomically you need to
wrap them in a single transaction. Off the top of my head (meaning
'untested'):

var
  tables : TStringList;
  Query : TDBISAMQuery;
begin
  Result := -1;
  tables := TStringList.Create;
  tables.Add('TableLastIDs');
  try
    try
      Database.StartTransaction(tables)
      Query:= TDBISAMQuery.Create(nil);
      try
        Query.Database := 'Whatever';
        Query.SQL.Add('UPDATE TableLastIDs SET LastID := LastID+1 WHERE
Type = 'Company');
        try
          Query.ExecSQL;
          if Query.RowsAffected = 1 then begin
            Query.SQL[0] := 'SELECT LastID FROM TableLastIDs WHERE Type
= 'Company');
            try
              Query.Open;
              Result := Query.Fields[0].AsInteger;
            finally
              Query.Close;
              Database.Commit;
            end
          else
            raise Exception.Create('Farkled Data');
        except
          //Probable timeout or Farkling
          Result := -1;
          Database.RollBack;
        end;
      finally
        Query.Free;
      end;
    except
      //Transaction lock timed out - probable conflict
    end;
  finally
    tables.Free;
  end;
end;

OMG, that was great!!
I will test right now.
Thanks so much.

Marcio

"Eryk Bottomley" <no@way.com> escreveu:
> Off the top of my head (meaning 'untested'):
>
> var
>   tables : TStringList;
>   Query : TDBISAMQuery;
> begin
(...)

Eryk,
Worked very fine, but two things I've noticed and had to correct (for the
record and to let others profit from the example):
1) It seems the tables list asks for the real TableName, not the
TDBISAMTable component name.
2) The query SQL does not allow ":". Of course you know that, it's the habit
of typing it before the equal sign. So it is LastID = LastID+1.
Thank you very much for your time.
Marcio

"Eryk Bottomley" <no@way.com> escreveu:
> Off the top of my head (meaning 'untested'):

>   tables.Add('TableLastIDs');
>   Query.SQL.Add('UPDATE TableLastIDs SET LastID := LastID+1 WHERE Type =
> 'Company');

Marcio,

<< That's interesting, although there are no specific examples on how to use
transactions with queries. >>

For future reference, there's an example here:

http://www.elevatesoft.com/manual?action=mantopic&id=dbisam4&product=d&version=7&category=3&topic=8

under the LASTAUTOINC() function for INSERT statements, but the concept is
the same for UPDATE statements.

--
Tim Young
Elevate Software
www.elevatesoft.com

"M.E." <no@no.com.br> wrote in message
news:91846287-89B1-4C77-9F5F-2C92CB7F1C14@news.elevatesoft.com...
> OMG, that was great!!
> I will test right now.
> Thanks so much.
>

You need to add something else. Since DBISAM does not support nested
transactions, you will have to test to see if you already are in a
trnsaction.

var MyTrans : boolean;

MyTrans := not Database.Intransaction;
..... rest of stuff
if MyTrans then databse.commit;

Personally, I don't like to use a transaction for this purpose. I would set
up the table with a unique index (concatenation of type and LastId) and if
you get a duplicate key on the update (because someone else grabbed your
key) simply repeat the loop.

Robert

Thank you, Tim.
M.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> escreveu:

> For future reference, there's an example here:
> http://www.elevatesoft.com/manual?action=mantopic&id=dbisam4&product=d&version=7&category=3&topic=8