Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 9 of 9 total |
SQL & Lockings |
Mon, Jun 30 2008 8:00 AM | Permanent Link |
"M.E." | 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 |
Mon, Jun 30 2008 8:05 AM | Permanent Link |
Eryk Bottomley | 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 |
Mon, Jun 30 2008 8:30 AM | Permanent Link |
"M.E." | 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. |
Mon, Jun 30 2008 9:12 AM | Permanent Link |
Eryk Bottomley | 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; |
Mon, Jun 30 2008 9:26 AM | Permanent Link |
"M.E." | 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 (...) |
Mon, Jun 30 2008 10:19 AM | Permanent Link |
"M.E." | 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'); |
Mon, Jun 30 2008 12:06 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Jun 30 2008 5:09 PM | Permanent Link |
"Robert" | "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 |
Tue, Jul 1 2008 11:40 AM | Permanent Link |
"M.E." | 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |