Umíme to s Delphi: 95. díl – spustitelné uložené procedury

Uložené procedury jsou jedním z velmi důležitých databázových konceptů. Dnešní článek je zaměřen na praktické seznámení s jedním typem procedur – se spustitelnými uloženými procedurami. Nejprve společně takovou proceduru vytvoříme a uložíme na databázový server, a pak napíšeme v Delphi databázovou aplikaci, která ji využije.
Před týdnem jsme vytvořili uloženou proceduru naznanou „upravplatyv“. Jedná se o výběrovou proceduru, tedy o proceduru, která při svém běhu vrací nějaká databázová data. Dnešní díl ukáže, jakým způsobem takovouto proceduru spustit z aplikace v Delphi.

Aplikace, kterou vytvoříme, tedy spustí na databázovém uloženou proceduru. Procedura projde databázi Platy a příslušným způsobem upraví dané záznamy (řádky) v této tabulce. Připomeňme, že činnost procedury je následující:

  • má-li zaměstnanec nulové osobní ohodnocení a nulový profesní příplatek, přidá mu osobní ohodnocení 500 Kč,
  • má-li zaměstnanec nenulové osobní ohodnocení, zvýší mu jej o 10%.

Následně procedura vrátí jména a příjmení všech zaměstnanců, kterým byla změněna výše mzdy.

Vytváříme aplikaci v Delphi

Nyní vytvoříme aplikaci v Delphi. V některém z předchozích dílů jsme již teoreticky rozebrali způsob, jakým proceduru z aplikace spustit: vzhledem ke skutečnosti, že se jedná o výběrovou proceduru, zavoláme ji zcela běžným způsobem pomocí příkazu SELECT (a tedy pomocí komponenty IBQuery).

1. Vytvořte novou aplikaci, na formulář umístěte očekávané komponenty: IBDatabase, IBQuery, IBTransaction, DataSource, DBGrid a dvě tlačítka Button1 a Button2. Všimněte si, že nepoužíváme žádnou komponentu IBStoredProc: pro výběrové uložené procedury není tato komponenta vůbec potřeba.

2. Nastavte správně všechny vlastnosti, tedy:

  • pro komponentu IBDatabase nastavte vlastnost DatabaseName na c:\!\platy.gdb, vlastnost DefaultTransaction na IBTransaction1 a vlastnost Connected na True;
  • pro komponentu IBTransaction nastavte vlastnost DefaultDatabase na IBDatabase1 a vlastnost Active na True;
  • pro komponentu IBQuery nastavte vlastnost Database na IBDatabase1 a vlastnost Active ponechte na False;
  • pro komponentu DataSource nastavte vlastnost DataSet na IBQuery1;
  • pro komponentu DBGrid nastavte vlastnost DataSource na DataSource1.

Pokud se vám nechce všechny vlastnosti nastavovat ručně, můžete použít níže uvedenou obsluhu události OnCreate, v níž nastavíme všechny uvedené body programově:

procedure TForm1.FormCreate(Sender: TObject);
begin
  IBDatabase1.DatabaseName := `C:\!\platy.gdb`;
  IBDatabase1.DefaultTransaction := IBTransaction1;
  IBDatabase1.Connected := True;

  IBTransaction1.DefaultDatabase := IBDatabase1;
  IBTransaction1.Active := True;

  IBQuery1.Database := IBDatabase1;
  IBQuery1.Active := False;

  DataSource1.DataSet := IBQuery1;

  DBGrid1.DataSource := DataSource1;
end;

3. Zajistíme, aby po klepnutí na první tlačítko byly v komponentě DBGrid vypsáni všichni zaměstnanci, kterých by se měla týkat (nějaká) úprava platu, tedy všichni zaměstnanci, kteří

  • mají nulové osobní ohodnocení a nulový profesní příplatek, nebo kteří
  • mají nenulové osobní ohodnocení.

Ošetříme tedy událost OnClick tlačítka Button1. Reakcí bude vložení odpovídajícího dotazu do vlastnosti SQL komponenty Query:

procedure TForm1.Button1Click(Sender: TObject);
begin
  IBQuery1.SQL.Clear;
  IBQuery1.SQL.Add(`SELECT jmeno, prijmeni FROM ZAMESTNANEC`);
  IBQuery1.SQL.Add(`JOIN PROFESE ON Zamestnanec.Idprofese=Profese.Idprofese`);
  IBQuery1.SQL.Add(`WHERE (ohodnoceni=0 AND priplatek=0) OR (not ohodnoceni=0)`);
  IBQuery1.Active := True;
end;

3. Následně ošetřete událost OnClick tlačítko Button2. Reakcí na klepnutí bude právě spuštění naší požadované procedury upravplatyv:

procedure TForm1.Button2Click(Sender: TObject);
begin
  IBQuery1.SQL.Clear;
  IBQuery1.SQL.Add(`SELECT * FROM upravplatyv`);
  IBQuery1.Active := True;
end;

4. Tím je tvorba aplikace hotova. Aplikaci uložte, přeložte a spusťte. Po spuštění se objeví tradiční přihlašovací dialog (viz následující obrázek). Vzápětí si vysvětlíme, jak se jej zbavit, prozatím v něm však vyplňte známé přihlašovací údaje (username SYSDBA, heslo masterkey).

Klepnete-li nyní na první tlačítko (na následujícím obrázku označeno „Vypiš“), vypíší se v mřížce jména a příjmení všech zaměstnanců, jichž se má týkat změna platu:

Klepnete-li následně na tlačítko Změň, dojde ke spuštění uložené procedury. Ta provede svou činnost a vrátí jména a příjmení všech lidí, kterým byl změněn plat. Tato jména jsou následně vypsána do mřížky, takže se lze snadno přesvědčit, že procedura změnila ohodnocení u správných zaměstnanců:

Zákaz zobrazování přihlašovacího dialogu

Několik čtenářů se na mě obrátilo s dotazem, jakým způsobem se zbavit přihlašovacího dialogu (viz obrázek výše) při každém spouštění databázové aplikace. Řešení samozřejmě existuje, avšak je nutné hned zpočátku zdůraznit, že není příliš bezpečné a rozhodně se jej nevyplatí používat u ostrých aplikací, které hodláme šířit mezi běžný uživatelský (tj. uživatelsko-crackerský) lid.

Pokud nechceme, aby se při každém spouštění aplikace zobrazoval přihlašovací dialog, do něhož je nutné zadat uživatelské jméno a heslo, stačí nastavit vlastnost LoginPrompt komponenty IBDatabase na False a vložit přihlašovací údaje do vlastnosti Params téže komponenty. Tyto činnosti je možné provést kdekoliv, například v obsluze události OnCreate hlavního formuláře. Upravíme tedy událost OnCreate předchozí aplikace:

procedure TForm1.FormCreate(Sender: TObject);
begin
  IBDatabase1.Params.Add(`user_name=SYSDBA`);
  IBDatabase1.Params.Add(`password=masterkey`);
  IBDatabase1.LoginPrompt := False;

  IBDatabase1.DatabaseName := `C:\!\platy.gdb`;
  ...

Když nyní aplikaci spustíte, není po přihlašovacím dialogu ani stopy. Je však nutné upozornit, že zadané přihlašovací údaje jsou bez problémů čitelné (tj. není třeba ani zkoumat hexadecimální údaje) ve spustitelném souboru *.exe, viz následující obrázek:

Prozradit takovýmto způsobem heslo (a ještě ke všemu například k administrátorskému účtu SYSDBA) je skutečně krajně nevhodným bezpečnostním opatřením :-)

Spustitelná uložená procedura

Výběrové uložené procedury, které vracejí data a které můžeme spouštět zcela běžným způsobem pomocí SQL příkazu SELECT, máme úspěšně za sebou a můžeme se zabývat druhým typem procedur – výběrovými. Jejich tvorba je nepatrně jednodušší (nemusíme se zabývat navracením dat) a jejich použití v Delphi je nepatrně obtížnější (musíme použít komponentu IBStoredProc).

Začneme podobně jako minule – nejprve vytvoříme spustitelnou uloženou proceduru. Nebudeme přitom vymýšlet žádné světoborné novinky a zcela jednoduše upravíme naši známou proceduru upravující platy zaměstnanců tak, aby upravila platy zcela stejným způsobem jako doposud, ale aby nic nevracela.

Spusťte tedy nástroj IBConsole, připojte se k databázovému serveru a přihlaste se k databázi Platy. Pak z nabídky vyberte Tools – Interactive Query a vložte do okna následující zdrojový kód:

SET TERM !! ;

CREATE PROCEDURE upravplatys AS
  DECLARE VARIABLE IDZAMEST INTEGER;
  DECLARE VARIABLE OHODNOCENI INTEGER;
  DECLARE VARIABLE PRIPLATEK FLOAT;

BEGIN
  FOR SELECT idzamest, ohodnoceni, priplatek
      FROM Zamestnanec Z JOIN Profese P
      ON Z.idprofese = P.idprofese
      INTO :IDZAMEST, :OHODNOCENI, :PRIPLATEK

  DO
  BEGIN
    IF (:OHODNOCENI=0 AND :PRIPLATEK = 0) THEN
    BEGIN
      UPDATE Zamestnanec SET Ohodnoceni=500 WHERE idzamest=:IDZAMEST;
    END
    ELSE
      IF (:OHODNOCENI>0) THEN
      BEGIN
        UPDATE Zamestnanec SET Ohodnoceni=(1.1*:OHODNOCENI) WHERE idzamest=:IDZAMEST;
      END
  END
END!!

SET TERM ; !!

Vzhled nástroje si můžete prohlédnout na následujícím obrázku:

SQL kód spustíte (provedete) volbou Query – Execute. Po úspěšném provedení kódu není hlášeno žádná informační okno.

Proceduru zřejmě není nutné nijak komentovat a vysvětlovat – její činnost je úplně stejná jako činnost předchozí výběrové procedury upravplatyv, jediným rozdílem je absence příkazů suspend a také absence klíčového slova RETURNS, za nímž byly v minulé verzi uvedeny oba výstupní parametry jméno a příjmení. Tyto parametry v nové verzi procedury vůbec nepotřebujeme, proto byly ze zdrojového kódu odstraněny.

Delphi aplikace využívající spustitelnou proceduru

Zbývá poslední, očekávaný krok – vytvořit v Delphi aplikaci, která spustí vytvořenou uloženou proceduru upravplatys.

1. Vytvořte novou aplikaci, na formulář umístěte následující komponenty: IBDatabase, IBQuery, IBTransaction, IBStoredProc, DataSource, DBGrid a dvě tlačítka Button1 a Button2.

2. Nastavte správně všechny vlastnosti. Nastavení všech komponent bude stejné jako v předchozí (výše v tomto dílu uvedené) aplikaci. Rozšířením bude pouze komponenta IBStoredProc, kterou je nutné nastavit takto:

  • vlastnost Database nastavíme na IBDatabase1;
  • vlastnost StoredProcName nastavíme na upravplatys.

Všechna nastavení můžete opět provést i programově, například pomocí následující obsluhy události OnCreate hlavního formuláře:

procedure TForm1.FormCreate(Sender: TObject);
begin
  IBDatabase1.Params.Add(`user_name=SYSDBA`);
  IBDatabase1.Params.Add(`password=masterkey`);
  IBDatabase1.LoginPrompt := False;

  IBDatabase1.DatabaseName := `C:\!\platy.gdb`;
  IBDatabase1.DefaultTransaction := IBTransaction1;
  IBDatabase1.Connected := True;

  IBTransaction1.DefaultDatabase := IBDatabase1;
  IBTransaction1.Active := True;

  IBQuery1.Database := IBDatabase1;
  IBQuery1.Active := False;

  DataSource1.DataSet := IBQuery1;

  DBGrid1.DataSource := DataSource1;

  IBStoredProc1.Database := IBDatabase1;
  IBStoredProc1.StoredProcName := `upravplatys`;
end;

3. Zajistíme, aby po klepnutí na tlačítko Button1 došlo k vypsání jména, příjmení a osobního ohodnocení všech zaměstnanců, kterých se má týkat změna platu (tj. kteří splňují podmínky uvnitř uložené procedury):

procedure TForm1.Button1Click(Sender: TObject);
begin
  IBQuery1.SQL.Clear;
  IBQuery1.SQL.Add(`SELECT jmeno, prijmeni, ohodnoceni FROM ZAMESTNANEC`);
  IBQuery1.SQL.Add(`JOIN PROFESE ON Zamestnanec.Idprofese=Profese.Idprofese`);
  IBQuery1.SQL.Add(`WHERE (ohodnoceni=0 AND priplatek=0) OR (not ohodnoceni=0)`);
  IBQuery1.Active := True;
end;

4. Nyní zařídíme, aby po klepnutí na tlačítko Button2 došlo ke spuštění uložené procedury:

procedure TForm1.Button2Click(Sender: TObject);
begin
  IBStoredProc1.ExecProc;
end;

Vidíte, že zde je zdrojový kód nesmírně jednoduchý – stačí zavolat metodu ExexProc komponenty IBStoredProc. Komponenta zajistí spuštění procedury na databázovém serveru. Pokud následně klepnete znovu na tlačítko Button1, spatříte, že osobní ohodnocení všech vypsaných zaměstnanců se změnila – procedura provedla svou činnost správně.

Závěrem dodejme, že pokud bychom používali v uložené proceduře vstupní parametry, bylo by nutné tyto parametry vložit do vlastnosti Params komponenty IBStoredProc. V tom případě bychom před zavoláním ExecProc museli ještě zavolat metodu Prepare, která slouží pro přípravě parametrů ke spuštění procedury. Ukážeme si to jen velmi stručně na příkladu převzatém z nápovědy k Delphi:

IBStoredProc1.Prepare;
try
  IBStoredProc1.Params[0].AsString := Edit1.Text;
  IBStoredProc1.ExecProc;
  Edit2.Text := IBStoredProc1.ParamByName(`FinalValue`).AsString;
finally
  IBStoredProc1.UnPrepare;
end;

Co tento zdrojový kód znamená? Používáme spustitelnou uloženou proceduru pomocí komponenty IBStoredProc1. Nejprve zavoláme metodu Prepare k přípravě parametrů uložené procedury. V chráněném bloku (try) se následně pokusíme:

  • přiřadit do prvního (a jediného) parametru (Params[0].AsString) hodnotu editačního pole Edit1. Prvním parametrem procedury tedy bude údaj zapsaný v editačním poli Edit1. Z jakého důvodu je v kódu použita vlastnost AsString? Zde je nutný trochu podrobnější popis vlastnosti Params komponenty IBStoredProc. Tato vlastnost je typu TParams, nikoliv např. TStrings, jak bychom možná očekávali. Třída TParams je potomkem třídy TCollection a je specializována pro uchovávání parametrů. Má vlastnost Items, která je „pole prvků typu TParam“. Jak asi víte, název Items se nemusí uvádět, proto je možné namísto Params.Items[5] napsat rovnou Params[5]. Vlastnost Items je tedy pole prvků typu TParam. Třída TParam pak reprezentuje jeden parametr pro databázový dotaz nebo pro databázovou uloženou proceduru. Tato třída dále obsahuje mnoho vlastností jako např. AsAtring, AsBlob, AsBoolean, IsInteger, AsFloat apod. Pomocí těchto vlastností vlastně specifikujeme typ parametru, přesněji řečeno určujeme, jakým způsobem má být parametr reprezentován. Použití některé z těchto vlastností zároveň nastaví správnou hodnotu do vlastnosti DataType.
  • spustit uloženou proceduru (ExecProc),
  • získat výstupní parametr uložené procedury a zobrazit jej v komponentě Edit2. Řekli jsme si, že i spustitelné procedury mohou vracet určité hodnoty, ale nejvýše jednu sadu (řádku) hodnot, nikoliv libovolné množství databázových údajů, jako třeba výběrové procedury. Řekli jsme si též, že tyto návratové hodnoty lze po skončení uložené procedury získat opět prostřednictvím vlastnosti Params. Pomocí metody ParamByName je možné přistupovat k parametrům prostřednictvím jejich jména, nikoliv indexu.

Na závěr (v bloku finally) již jen zavoláme metodu UnPrepare, která uvolní zdroje alokované pro předchozí volání uložené procedury.

A to je vše přátelé

Tolik k uloženým procedurám. V dnešním dílu jsme dokončili popis všech podstatných částí této problematiky. Víme tedy, co uložené procedury jsou, do jakých skupin se rozdělují, jakým způsobem se vytvářejí, jak se ukládají na databázový server a jakými způsoby je využít z aplikací psaných v Delphi.

Diskuze (1) Další článek: Microsoft.com na hodinu vyřazen DoS útokem

Témata článku: , , , , , , , , , , , , , , , , , , , , , ,