Delphi - MS SQL adatbázis kezelés Delphi-ből

A cached update egy nagyszerű, mégis ritkán alkalmazott eszköz arra, hogy az SQL Server számára előkészített adatainkat késleltetve küldjük el, és így kivárjuk az arra legalkalmasabb időpontot. Egy tipikus Delphi alkalmazásban, ahogy a felhasználó befejezi egy rekord szerkesztését, a BDE kiad egy Post utasítást, és a változtatás érvényre jut az adatbázisszerveren is. Ha módosításról van szó, az SQL Server egy UPDATE utasítást kap, ha beszúrásról, egy INSERT-et, törléskor pedig egy DELETE tSQL utasítás hajtódik végre. A cached update technika alkalmazása alapvetően két területen nyújt előnyt a kliens/szerver alkalmazások számára. Először is az előbb említett metódustól teljesen különböző adatmanipulációs lehetőségeket biztosít, miközben továbbra is használhatjuk a Delphi adatvezérlő komponenseit. A funkció bekapcsolásával a változtatások lokálisan tárolódnak egy tranzakciós táblába. A felhasználó úgy érzékeli, hogy a módosításai azonnal érvényre jutnak, valójában azonban az SQL Server táblája csak akkor fog megváltozni, ha a programozó erre explicit módon utasítást ad. Másodszor a cached update technika segítségével egészen pontosan meghatározhatjuk, hogy a rekordok beszúrása, módosítása és törlése milyen módon történjen. Kihagyhatjuk tehát a BDE 'posting' algoritmusát.

Ha egy adatokat kezelő alkalmazásban a felhasználó élő adatokat szerkeszt, minden egyes post-nál a BDE összeállít egy UPDATE utasítást és elküldi azt az SQL Servernek. Ha 6.5-ös vagy régebbi SQL Servert használunk, akkor az legfeljebb lap szintű zárolásra képes, így az UPDATE utasítás végrehajtásához egy egész lapnyi rekord kerül zár alá, így egyszerre több rekord is elérhetetlenné válik. Még jobban kidomborodik az eljárás hátránya, ha a felhasználó az egymás utáni rekordokat sorban módosítja, és ugyanaz a lap minden post után zár alá kerül. A cached update megspórolja az SQL Servernek a folyamatos zárolás feladatát azzal, hogy lokálisan összegyűjti a módosításokat, és egyszerre vezeti át az adatbázisszerverbe.

A cached update funkciót a TTable, TQuery és TStoredProc komponenseken a CachedUpdate property igazra állításával tudjuk bekapcsolni. Ez után a BDE egy helyi táblában vezeti a módosításokat mindaddig, amíg a saját vagy a TDatabase komponens ApplyUpdates metódusát meg nem hívjuk. Ekkor a változtatások egyetlen tranzakcióban kerülnek érvényesítésre az SQL Serveren. Ha menet közben a CachedUpdate property-t hamisra állítjuk vagy meghívjuk a CancelUpdates metódust, az eltárolt változtatások elvesznek.

A TDatabase és a TTable komponensek ApplyUpdates metódusa nem ugyanaz. A TDatabase metódusának egy tömbben kell átadnunk, hogy mely adathalmazok változtatásait küldje át a szervernek. A TDataSet komponensek ApplyUpdate-je természetesen csak saját magukra vonatkozik. További különbség, hogy a TDatabase komponens esetében a változtatások commitálódnak is a szerveren, míg a TDataset komponenseknél ehhez meg kell hívnunk a CommitChanges metódust is.

A TDatabase.ApplyUpdates metódusát kikereshetjük a Delphi VCL forrásának dbtables unitjából.

procedure TDatabase.ApplyUpdates(const DataSets: 
        array of TDBDataSet);
var
  I: Integer;
  DS: TDBDataSet;
begin
  StartTransaction;
  try
    for I := 0 to High(DataSets) do
    begin
      DS := DataSets[I];
      if DS.Database <> Self then
        DatabaseError(Format(SUpdateWrongDB, 
             [DS.Name, Name]));
      DataSets[I].ApplyUpdates;
    end;
    Commit;
  except
    Rollback;
    raise;
  end;
  for I := 0 to High(DataSets) do
    DataSets[I].CommitUpdates;
end;

Jól látszik, hogy egy tranzakción belül az összes - az adatbázishoz tartozó - adathalmaz ApplyUpdates metódusa meghívásra kerül, majd a műveletek commitálódnak is.

Az adatváltozások átvezetésének még finomabb irányításához használhatjuk a TUpdateSQL komponenst. Ennek az objektumnak három fontos property-je van: DeleteSQL, InsertSQL és ModifySQL. Mindhárom property egy SQL utasítást kell tartalmazzon, ami a három féle módosítást vezérli.

A DeleteSQL property célszerűen egy DELETE utasítás lesz. Hogy melyik táblában törlünk, azt a FROM záradékban jelezzük, hogy melyik rekordot töröljük, azt pedig a WHERE feltételben jelöljük ki. Egy mező régi értékére paraméterrel kell hivatkoznunk, a paraméter neve az 'OLD_' sztringből és a mező nevéből áll. Ha a paraméter ilyen formájú, a BDE tudni fogja, hogy a mező régi értékéről van szó. Ha nem ilyen, akkor egy közönséges paraméternek minősül, és az értékét nekünk kell megadnunk a szokásos módon.

Ha például a jobs táblából törlünk a TUpdateSQL komponensen keresztül, a DeleteSQL property-be a következő utasítást kell beírni:

delete from jobs
where
  job_id = :OLD_job_id

Az InsertSQL property egy INSERT parancsot tartalmaz. Hogy melyik táblába fogunk beszúrni, azt az INTO kulcsszó után kell beírnunk, ezt követik a mezők nevei, végül a VALUES után vesszővel elválasztva jönnek a paraméterek. Ha a paraméter neve megegyezik a mező nevével, az értéke automatikusan a cache-elt rekord megfelelő mezejének értékét veszi fel. Ha a név nem ilyen, a paraméter értékét mi állíthatjuk be. A jobs táblára ez így néz ki:

insert into jobs
  (job_desc, min_lvl, max_lvl)
values
  (:job_desc, :min_lvl, :max_lvl)

A ModifySQL property az előzőekhez teljesen hasonlóan egy UPDATE utasítást fog tartalmazni. A tábla nevét FORM záradékban kell megadni, a módosítandó mezőket pedig a SET kulcsszó után lehet hozzárendelések (mezőnév = :paraméternév) formájában felsorolni. Ha a paraméter neve megegyezik a mező nevével, az érték automatikusan a cache-elt rekord ugyanolyan nevű mezejének értéke lesz. Az egyes mezőkhöz teljesen más értékeket is hozzárendelhetünk, ha a paraméter nevét másképpen választjuk meg. Hogy éppen melyik rekordot változtatjuk, azt a WHERE feltétellel adhatjuk meg. Ahogy a DeleteSQL-nél, itt is az 'OLD_' prefixszel hivatkozhatunk egy mező régi értékére. A jobs tábla esetén egy lehetséges UpdateSQL a következő:

update jobs
set
  job_desc = :job_desc,
  min_lvl = :min_lvl,
  max_lvl = :max_lvl
where
  job_id = :OLD_job_id

Hogy a három utasítás közül melyik rekordnál melyik hajtódik végre, azt az UpdateKind paraméter határozza meg, ami rekord módosításánál automatikusan beállítódik.

Ha tervezési időben állítjuk be a fenti property-k értékét, segítséget is kérhetünk a Delphitől. Ha a TUpdateSQL komponensre duplát kattintunk, egy Update SQL Editor ablak nyílik ki. Itt kiválaszthatjuk, hogy melyik táblát szeretnénk módosítani. Az Update Fields ablakban fel kell sorolnunk, hogy mely rekordokat fogjuk változtatni, a Key Fields ablakban pedig azokat a rekordokat kell feltüntetnünk, amik alapján megtaláljuk majd a módosítandó rekordot. Ez fog tehát szerepelni a WHERE feltételben. A jobs táblának például összesen négy mezeje van. Mind a négyet szeretnénk módosítani, ezért az Update oszlopban mind a négyet kijelöljük, a Key oszlopban viszont elég a job_id mezőt kijelölni, mivel elsődleges kulcs, így egyedi, és önmagában is azonosítja a rekordot. Ezek után a Generate SQL gombra kattintva a Delphi legyártja helyettünk a három utasítást, amit az Update SQL Editor ablak SQL fülén tudunk megnézni, és ha szükséges, átszerkeszteni.

A cached update technika egyik nagy előnye tehát, hogy az adatmódosítás vezérléséről a programozó maga gondoskodhat. Óvatosan kell azonban használni egy olyan környezetben, ahol a tranzakciók nagy mennyiségben és gyorsan követik egymást. Ha túl sokáig késleltetjük az update-et, a felhasználónk sokszor fog hibára futni, ha a ténykedése közben más is módosítja ugyanazt az adatot.

Van egy terület, ahol a cached update sebességben is kiemelkedő teljesítményt nyújt, ez pedig az update megszakítása. Egy módosítási műveletet megszakítása logikailag azonos a tranzakció visszagörgetésével. A különbség csak annyi, hogy az update megszakításában az adatbázisszerver nem vesz részt. Ha a rollback mechanizmus körülményes, mert bonyolult megvalósítani, hogy mindenki, akinek az adatát a rollback érinti, értesüljön az eseményről, sőt maga a rollback is az érintett felhasználóktól függene, használjunk cached update-et. A megszakítás ekkor csak a helyi tranzakciós tábla törlését jelenti és nem terheljük az SQL Servert a tranzakciós naplója böngészésével.

OnUpdateRecord

Nem beszéltünk a cached update technika még egy kellemes eleméről, az OnUpdateRecord eseményről. Ez az esemény akkor kerül meghívásra, ha a cache-elt adathalmaz egy rekordját töröljük, módosítjuk, vagy az adathalmazba egy új rekordot beszúrunk. Ha nem szeretnénk, hogy a Delphi maga érvényesítse a cache-beli változtatásokat, hanem mi szeretnénk felügyelni azt, akkor kell ezt az eseményt megírnunk.

De miért van erre szükség? Azért, mert előfordulhat olyan eset, amikor az ApplyUpdates nem működik helyesen. Ha például egy táblán olyan trigger van, ami egy hibás adatbevitel esetén ROLLBACK TRANSACTION utasítást hajt végre, egy post kiadásakor a BDE megzavarodik, és hibát jelez, hogy nem találja a BEGIN TRANSACTION-t. Minden ezt követő post kísérletkor a BDE közölni fogja velünk, hogy egy tranzakció már folyamatban van. Ebből levonhatjuk azt a következtetést, hogy a BDE nincs korrektül felkészülve a szerver által kiváltott rollback-ekre. Az OnUpdateRecord esemény megírásával saját magunk irányíthatjuk a módosítások végrehajtását.

Az esemény három paramétert használ. A DataSet paramétere mutatja a módosításokat elszenvedő adathalmazt. Itt három property-re kell felhívnunk a figyelmet: Value, OldValue és NewValue. A Value property egy Field[] tömb, amiben annak a rekordnak a mezői szerepelnek, amire a kurzor éppen mutat. Az OldValue a rekord régi értékeit, a NewValue a módosított értékeket tartalmazza. Az UpdateKind paraméter az update típusára utal, értéke lehet ukInsert, ukModify és ukDelete. Végül a harmadik paraméter, az UpdateAction mondja meg a BDE-nek, hogy az hogyan reagáljon az update-re. Négy értéket szokás itt használni: uaFail, uaAbort, uaSkip, és uaApplied. Ha az UpdateAction értékét uaAbort-ra állítjuk, a BDE abbahagyja a módosítások végrehajtását, és egy néma kivételt generál, ami attól néma, hogy nem fog megjelenni. uaApplied érték esetén a BDE feltételezi, hogy az update sikeres volt, és az update rekordot kiveszi a cache-ből. uaFail esetén szintén abbamaradnak a módosítások, viszont hibaüzenet generálódik. uaSkip esetén nem történik meg az update, de a BDE a rekordot benn hagyja a cache-ben.

Az OnUpdateRecord eseményből a DeleteSQL, InsertSQL és ModifySQL property-ket legegyszerűbben a TUpdateSQL komponens Query property-jén keresztül érhetjük el. Mivel csak olvasható, az Object Inspector-ban nem jelenik meg. TQuery típusú és paraméterként az esemény UpdateKind paraméterét kapja meg. Egy példa, hogyan tudjuk futásidőben eldönteni, hogy milyen SQL utasítás fusson le az egyes update-ekkor:

procedure TForm1.Sajat_UpdateRecord(DataSet: TDataSet;
  UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
begin
  with UpdateSQL1 do 
  begin
    case UpdateKind of
      ukModified:
        begin
          Query[UpdateKind].Text := 
            Format('update emptab set Salary =
                   %d where EmpNo = %d',
                   [EmpAuditSalary.NewValue, 
                    EmpAuditEmpNo.OldValue]);
          ExecSQL(UpdateKind);
        end;
      ukInserted:
        {...}
      ukDeleted:
        {...}
    end;
  end;
  UpdateAction := uaApplied;
end;

A példából az is kiderül, hogy az update-et az ExecSQL metódussal hajtottuk végre. Valójában legalább háromféleképpen érvényesíthetjük az adatbázisszerveren a változtatásokat. Az Apply metódus két lépésben végzi el ezt a feladatot, először a rekord értékeit az update SQL utasításában szereplő paraméterekhez köti, majd végrehajtja az utasítást. Az Apply metódust akkor használjuk, ha az update objektum nincs DataSet-hez kötve annak UpdateObject property-jén keresztül, és így soha nem hajtódna végre automatikusan. Az OnUpdaterecord eseményben az UpdateKind segítségével dönthetjük el, hogy melyik SQL utasítást kell használnunk a három közül, így azt továbbadhatjuk az Applynak.

procedure TForm1.EmpAuditUpdateRecord(DataSet: TDataSet;
  UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
begin
  UpdateSQL1.Apply(UpdateKind);
  UpdateAction := uaApplied;
end;

A másik módszer, hogy a két lépést külön hajtjuk végre a SetParam és az ExecSQL utasítások egymás utáni meghívásával.

procedure TForm1.EmpAuditUpdateRecord(DataSet: TDataSet;
  UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
begin
  with (DataSet.UpdateObject as TUpdateSQL) do 
  begin
    SetParams(UpdateKind);
    ExecSQL(UpdateKind);
  end;
  UpdateAction := uaApplied;
end;

Ha olyan paramétert is használunk, ami nem az update record mezői közül kerül ki, akkor azt a két lépés között állíthatjuk be. Például a módosítás időpontját is el kell tárolnunk:

procedure TForm1.EmpAuditUpdateRecord(DataSet: TDataSet;
  UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
begin
  with (DataSet.UpdateObject as TUpdateSQL) do 
  begin
    SetParams(UpdateKind);
    if UpdateKind = ukModified then
      Query[UpdateKind].ParamByName('DateChanged').Value := Now;
    ExecSQL(UpdateKind);
  end;
  UpdateAction := uaApplied;
end;

A harmadik módszert az első OnUpdateRecord példában láttuk, ahol a paramétereket egyszerűen a Format függvénnyel helyettesítettük, és csak az ExecSQL metódust hívtuk meg.

Hibakezelés

Az adathalmaz objektumok OnUpdateError eseménye segít abban, hogy lekezeljük az update során keletkező hibákat. Az üres esemény így néz ki:

procedure TForm1.DataSetUpdateError(DataSet:
   TDataSet; E: EDatabaseError; UpdateKind: TUpdateKind; 
   var UpdateAction: TUpdateAction);
begin
  ;
end;

A DataSet, UpdateKind és UpdateAction paraméterek megegyeznek a OnUpdateRecord eseménynél bemutatott paraméterekkel. Egy kis változás azért van, az UpdateAction felvehet uaRetry értéket is, ami azt jelenti, hogy a BDE ismét végrehajtja az update műveletet. Az újdonság az EDBEngineError típusú E paraméter, aminek a Message property-jében például visszakapjuk a hiba szövegét. Ezenkívül arra is használhatjuk, hogy a hiba típusától (ErrorCode) függően hajtsuk végre a további feladatokat. Egy rövid példa:

if (E is EDBEngineError) then
  with EDBEngineError(E) do 
  begin
    if Errors[ErrorCount - 1].ErrorCode = DBIERR_KEYVIOL then
      UpdateAction := uaSkip 
    else
      UpdateAction := uaAbort;
  end;

Vagyis hiba esetén ha 'csak' egy key violation hiba merült fel, kihagyjuk a rekordot és folytatjuk tovább az update-et, más hibák esetén mivel nem tudjuk, hogy mi lehet, abortáljuk a további műveleteket.

A példaprogramban szerepel egy Borland által megírt unit is (errform), ami egy komplex hibakezelő ablak váza, mindenki kedvére magyaríthatja.

MS SQL adatbázis kezelés Delphi-ből

18. rész