Pozrime sa, ako sa, ako sa môže pracovať so storovanými procedúrami Firebirdu v Delphi. Článok je určený stredne pokročilým začiatočníkom, ktorým netreba vysvetľovať terminológiu ako je transakcia, „commit“, property apod.
Pri väčších projektoch, je dobré separovať databázu od programového kódu. Skôr nájdeme chybu, skôr ju opravíme a na projekte môžu spolupracovať aj špecialisti na databázy bez toho, aby ovládali programovací jazyk, v ktorom sa aplikácia vyvíja.
Tento článok voľne nadväzuje na predchádzajúci v ktorom sme sa dozvedeli základ o Firebirde (všetko to, čo potrebujeme teraz). Dneska sa pozrieme, ako môžeme prepojiť náš program so storovanými procedúrami. Najprv si vytvorme skúšobnú databázu:
CREATE DATABASE 'C:\!Ernest2G\Data\Ernest.fdb' USER 'sysdba' PASSWORD 'masterkey';
CREATE EXCEPTION Ex_ChybnyBoolean 'Očakáva sa boolean.';
CREATE TABLE Stat (
Stat INTEGER NOT NULL PRIMARY KEY,
Nazov NATIONAL CHARACTER(70) NOT NULL,
KodStatu NATIONAL CHARACTER(5),
ClenEU INTEGER);
CREATE GENERATOR Seq_Stat;
SET TERM |;
CREATE PROCEDURE Stat_Insert(inNazov NATIONAL CHARACTER(70), inKod NATIONAL CHARACTER(5), inClenEU INTEGER)
RETURNS (Result INTEGER)
AS
BEGIN
Result = GEN_ID(Seq_Stat, 1);
IF ((inClenEU IS NOT NULL) AND ((inClenEU < 0) OR (inClenEU > 1))) THEN
EXCEPTION Ex_ChybnyBoolean '(Člen EU)';
INSERT INTO Stat
(Stat, Nazov, KodStatu, ClenEU)
VALUES
(:Result, :inNazov, :inKod, :inClenEU);
SUSPEND;
END;|
SET TERM ;|
Musíme mať vytvorenú cestu C:\!Ernest2G\Data\, zapnutú databázovú službu a ide sa nato. Vidíme tu tabuľku, ktorá má primárny kľúč, dve textové polia a jeden číselný, ktorý nám bude suplovať boolean. Naša procedúrka nebude robiť nič iné, len vkladať záznam do tabuľky. Nato potrebujeme sekvenciu v podobe generátora (pre autoinkrementáciu – viď predchádzajúci článok) a samotný kód procedúry.
Pozn.: Už vieme, že tento skript si môžeme uložiť napríklad na C:\a.sql a po spustení ISQL Firebirdu ho načítame príkazom edit C:\a.sql;. Takto vytvoríme databázu na spomínanej ceste.
Teraz ako celý Firebird prepojiť s Delphi? Bacnime si na formulár komponent TIBTransaction (ktorý nám vytvára transakčný charakter) a ku nemu pridajme aj TIBDatabase (všetky komponenty sa nachádzajú v záložke InterBase – aspoň v Delphi EE 7). Databáze priraďme transakciu (DefaultTransaction), nastavme cestu k súboru. Ešte sa môžeme zbaviť otravného LoginPromptu (false) a natvrdo nastaviť prihlasovacie údaje v Params:
user_name=sysdba
password=masterkey
Teraz otvorme spojenie Connect (true) na databáze a na transakcii analogicky property Active.
Prvý spôsob práce s procedúrami je najjednoduchší a to priamo použitím komponentu TIBStoredProc. Pridajme si teda aj tento ten na formulár, priraďme mu databázu (Database), napíšme meno procedúry z databázy (StoredProcName), a naklikajme parametre:
Po správne zadanom názve, by sa mali dotiahnuť názvy parametrov automaticky. Vidíme nultý parameter výstupu a zvyšné vstupné argumenty. Kliknime na názov, zvoľme mu typ hodnoty (Value-Type) na string, hodnotu vyplňme nejakým textom. Rovnako postupujeme aj pri kóde (len pozor na max. dĺžku 5 znakov – viď definícia parametra v storovanej procedúre), a posledný parameter nastavme na integer s hodnotou 0 (t.j. Integer(False)).
Takto nastavenú procedúru potrebujeme už len spustiť. Výstupom je vždy parameter (ako sme si všimli, aj výstupný parameter figuruje medzi parametrami), ktorého hodnota sa nastaví po spustení:
if (not IBStoredProc1.Transaction.Active) then
IBStoredProc1.Transaction.StartTransaction;
try
IBStoredProc1.Prepare;
try // FINALLY
IBStoredProc1.Close;
IBStoredProc1.ExecProc;
ShowMessage(VarToStr(IBStoredProc1.Params.ParamValues['Result']));
finally
IBStoredProc1.UnPrepare;
end; // TRY FINALLY
finally
IBStoredProc1.Transaction.Commit;
end; // TRY FINALLY
Metóda Prepare pripravuje procedúru na komunikáciu s databázou (overuje názov, sačkuje parametre apod.).
Niekedy je však potrebné databázové objekty nastavovať procedúre za chodu programu. Napríklad, ak máme komponent na forme, ktorý nemá priamo prístup k týmto objektom. Typická situácia môže nastať pri MDI aplikácii, kde sa zobrazí po štarte modálne login okno, informácie sa predajú MDI aplikácií, tá ich vyhodnotí – konektne sa na databázu, kde drží reláciu. Pri práci s programom však užívateľ pracuje s MDI child formami. Tým teda napríklad vytvoríme property, ktorá odkazuje na potrebný komponent na ich rodičovi, teda na MDI aplikácii:
...
TMainForm = class(TForm)
…
protected
…
function DajDatabazu: TIBDatabase;
procedure NastavDatabazu(const Hodnota: TIBDatabase);
...
property Databaza: TIBDatabase read DajDatabazu write NastavDatabazu;
...
end; // TMainForm
...
// *****************************************************************************
function TMainForm.DajDatabazu: TIBDatabase;
begin
if Owner is TFormErnest then
Result := TFormErnest(Owner).Databaza
else
Result := nil;
end;
// *****************************************************************************
procedure TMainForm.NastavDatabazu(const Hodnota: TIBDatabase);
begin
if Owner is TFormErnest then
TFormErnest(Owner).Databaza := Hodnota;
end;
...
Teda nastavme si komponent presne podľa toho, ako sme údaje naklikávali. Priradíme ukazovateľ na databázu a transakciu cez udalosti Database a Transaction (možný príklad ako sa ku ním dostať bol načrtnutý hore). Zvyšok kódu je analogický, s tým, že už nastavujeme aj vstupné parametre (podobne ako sme získavali hodnotu z výstupného parametra):
if Assigned(DB) and Assigned(Trans) then
begin
Proc.Database := Db;
Proc.Transaction := Trans;
if (not Proc.Transaction.Active) then
Proc.Transaction.StartTransaction;
try // FINALLY
Proc.Params.Clear;
Proc.StoredProcName := 'STAT_INSERT';
Proc.Prepare;
try // FINALLY
Proc.Close;
Proc.ParamByName('inNazov').AsString := 'Tadzikistan';
Proc.ParamByName('inKod').AsString := 'TDZ';
Proc.ParamByName('inClenEU').AsInteger := Integer(False);
Proc.ExecProc;
ShowMessage(VarToStr(Proc.Params.ParamValues['Result']));
finally
Proc.UnPrepare;
end; // TRY FINALLY
finally
Proc.Transaction.Commit;
end; // TRY FINALLY
end; // IF
Najčastejšie sa v praxi ale určite stretneme s potrebou dynamického použitia, kedy potrebujeme v nejakej časti kódu procedúru použiť, nikde inde už nie. Tu pribúda vytvorenie a uvoľnenie komponentu:
if Assigned(DB) and Assigned(Trans) then
with TIBStoredProc.Create(nil) do
try // FINALLY
Database := Db;
Transaction := Trans;
if (not Transaction.Active) then
Transaction.StartTransaction;
try
Params.Clear;
StoredProcName := 'STAT_INSERT';
Prepare;
try // FINALLY
Close;
ParamByName('inNazov').AsString := 'Tadzikistan';
ParamByName('inKod').AsString := 'TDZ';
ParamByName('inClenEU').AsInteger := Integer(False);
ExecProc;
ShowMessage(VarToStr(Params.ParamValues['Result']));
finally
UnPrepare;
end; // TRY FINALLY
finally
Transaction.Commit;
end; // TRY FINALLY
finally
Free;
end; // TRY FINALLY
// IF
Keď si chceme uľahčiť prácu (časté využívanie procedúr), tak si trocha celé zautomatizujme. Vytvorme si procedúru, ktorá na vstupe dostane „hotovú“ procedúru a dynamický počet hodnôt. Nastaví transakciu, vstupné parametre popridáva v poradí zo svojich vstupných hodnôt a vykoná všetko potrebné:
procedure StorovanaProcedura(var AProc: TIBStoredProc; const AParametre: array of Variant);
var
Index, AktualnyParameter: Integer;
begin
if Assigned(AProc) and (Assigned(AProc.Database)) and (Assigned(AProc.Database.DefaultTransaction)) then
begin
if (not Assigned(AProc.Transaction)) then
AProc.Transaction := AProc.Database.DefaultTransaction; // Ak nie je nasetovana Transakcia na procedure, poziciame si z databazy
if (not AProc.Transaction.Active) then
AProc.Transaction.StartTransaction;
try // FINALLY
AktualnyParameter := 0;
AProc.Params.Clear;
AProc.Close;
AProc.Prepare;
try // FINALLY
// Vyskladame parametre
for Index := 0 to Pred(AProc.ParamCount) do
if AProc.Params[Index].ParamType in [ptInput, ptInputOutput] then
begin
if AktualnyParameter > High(AParametre) then // Nezadany parameter nech je NULL
AProc.Params[Index].Value := Null
else
begin
AProc.Params[Index].Value := AParametre[AktualnyParameter];
Inc(AktualnyParameter);
end; // ELSE IF
end; // IF
AProc.ExecProc;
// FOR
finally
AProc.UnPrepare;
end; // TRY FINALLY
finally
AProc.Transaction.Commit;
end; // TRY FINALLY
end // IF
else
raise Exception.Create(‘Zlý vstup!’);
end;
Zasa sme si až tak nepomohli, teraz však vytvorme druhú procedúru, ktorá dostane vstupné parametre práve ukazovatele na databázu a transakciu, nám už známe dynamické hodnoty (budúce parametre storovanej procedúry), názov storovanej procedúry a prípadne aj procedúru. Ak ju nezadáme, telo vykoná len spustenie procedúry s parametrami. Ak ju zadáme, tak rovno máme prístup aj ku výstupným parametrom, ktoré mohla procedúra navrátiť:
procedure StorovanaProcedura2(ADatabaza: TIBDatabase; ATransakcia: TIBTransaction;
const AParametre: array of Variant; const NazovProc: String; Procedurka: TIBStoredProc = nil);
var
UvolnitProc: Boolean;
begin
if Assigned(ADatabaza) and Assigned(ATransakcia) then
begin
if (not Assigned(Procedurka)) then
begin
UvolnitProc := True;
Procedurka := TIBStoredProc.Create(nil);
end
else
UvolnitProc := False;
try // FINALLY
Procedurka.Database := ADatabaza;
Procedurka.Transaction := ATransakcia;
Procedurka.StoredProcName := NazovProc;
StorovanaProcedura(Procedurka, AParametre);
finally
if UvolnitProc then
FreeAndNil(Procedurka);
end; // TRY FINALLY
end // IF
else
raise Exception.Create(‘Zlý vstup!‘);
end;
No a na záver ešte jedná funkcia, ktorá bude emulovať „storovanú funkciu“ práve z tej vlastnosti druhej procedúry, že ak bude jedným z argumentov aj procedúra, tak sa nastaví na výstupné hodnoty:
function StorovanaFunkcia(ADatabaza: TIBDatabase; ATransakcia: TIBTransaction;
const AParametre: array of Variant; const VystupnePole: String = 'Result'): Variant;
var
InternaProc: TIBStoredProc;
begin
InternaProc := TIBStoredProc.Create(nil);
try // FINALLY
StorovanaProcedura2(ADatabaza, ATransakcia, AParametre, 'STAT_INSERT', InternaProc);
Result := InternaProc.Params.ParamValues[VystupnePole];
finally
FreeAndNil(InternaProc);
end; // TRY FINALLY
end;
Toto je už celkom nápomocná konštelácia nástrojov. Napríklad pre náš doterajší príklad už stačí len volať:
ShowMessage(VarToStr(StorovanaFunkcia(DB, Trans, ['Velka Britania', 'UK', Integer(True)])));
Ak chceme pristupovať ku databáze cez dbExpress, na formulár si umiestníme TSQLConnection komponent, na ňom nastavíme properties:
TSQLDataSet <- TDataSetProvider (DataSet) <- TClientDataSet (ProviderName)
Príklad implementácie:
resourcestring
// ---------------------------------------------------------------------------
sNeplatnyNazovProc = 'Interná chyba: Neplatný názov storovanej procedúry!';
sNeplatnyPocetParametrov = 'Interná chyba: Neplatný poèet parametrov storovanej procedúry!';
// ---------------------------------------------------------------------------
TFormXY = class(TForm)
…
public
…
class function SpustiStorovanuProceduru(ASQLConnection: TSQLConnection;
const ANazov: String; const AParametre: TVarArray): TVarArray;
…
end; // TFormXY
...
// *****************************************************************************
// Zavola storovanu proceduru s parametrami
class function TFormXY.SpustiStorovanuProceduru(ASQLConnection: TSQLConnection;
const ANazov: String; const AParametre: TVarArray): TVarArray;
var
Index, IndexVPoli: Integer;
begin
SetLength(Result, 0);
Assert(Assigned(ASQLConnection));
if Assigned(ASQLConnection) and ASQLConnection.Connected then
begin
if Trim(ANazov) = '' then
raise Exception.CreateRes(@sNeplatnyNazovProc);
// Dynamicke pole zacina 0 a High vracia posledny parameter, nie pocet. Pre
// nas pripad potrebujeme posledny index parametra neparny, teda bude parny
// pocet parametrov (s nulou)
if High(AParametre) mod 2 = 0 then
raise Exception.CreateRes(@sNeplatnyPocetParametrov);
// Samotne zavolanie procedury
with TSQLStoredProc.Create(nil) do
try // FINALLY
SQLConnection := ASQLConnection;
StoredProcName := UpperCase(ANazov);
Prepared := True;
// Nastavime vstupne parametre
Index := 0;
while Index < High(AParametre) do // je < nie <= pretoze sa rata s predposlednym a poslednym parametrom naraz (nazov + hodnota)
begin
ParamByName(UpperCase(VarToStr(AParametre[Index]))).Value := AParametre[Succ(Index)];
Inc(Index, 2); // Skocime na dalsi parameter
end; // WHILE
// Zavolame proceduru
ExecProc;
// Nastavime vystupne parametre
IndexVPoli := 0;
for Index := 0 to Pred(ParamCount) do
begin
Result[IndexVPoli] := Params[Index].Name;
Result[Succ(IndexVPoli)] := Params[Index].Value;
Inc(IndexVPoli, 2);
end; // FOR
finally
Free;
end; // FINALLY
end; // IF
end;
...
Môžeme použiť aj „klasickú“ metódu (bez CLASS), ak máme napr. na forme SQLConnection potom, ho nemusíme posielať argumentom. Ja som si zvolil statickú metódu preto, lebo tieto metódy sa dajú volať z objektu, aj keď objekt nie je vytvorený. Typickým príkladom je napr. ClassType, môže sa zavolať niečo ako TPersistnent.ClassType a ono to nezdochne, pričom sa vráti niečo ako „class of TPersistent;”.
Teda ak pracujeme s dialógmi, zásuvnými modulmi apod. potom nemusíme predávať smerník na objekt (najčastejšie sa to robí Ownerom), ale stačí mať len v USES referenciu na MainForm a potom analogicky stačí zavolať:
TFormJukebox.SpustiStorovanuProceduru(…)
Príklad:
ClientDataSetGlobalneParametre.DisableControls;
try // FINALLY
StorovanaProcedura('ParameterSekcieUpdate', VarArrayOf([
'inSekcia', SekciaInterna,
'inNazov', Nazov,
'inHodnota', NavratovaHodnota,
'inHodnotaZobraz', ZobrazovaciaHodnota,
'inReadOnly', 0, // Logicky sa musi dat editovat
'inTyp', ClientDataSetGlobalneParametre.FieldByName('Typ').AsInteger]));
finally
ClientDataSetGlobalneParametre.EnableControls;
end; // FINALLY
Pre procedúru:
CREATE PROCEDURE ParameterSekcieUpdate (inSekcia NCHAR VARYING(70),
inNazov NCHAR VARYING(70),
inHodnota NCHAR VARYING(70),
inHodnotaZobraz NCHAR VARYING(70),
inReadOnly INT,
inTyp INT)
RETURNS (Result INT) AS
DECLARE SekciaID INT;
BEGIN
-- Najdeme parameter ak existuje
SELECT
PS.ParameterSekcie,
PS.Sekcia
FROM ParameterSekcie AS PS
INNER JOIN Sekcia AS S ON S.Sekcia = PS.Sekcia
WHERE
(S.Nazov = :inSekcia) AND
(PS.Nazov = :inNazov)
INTO
:Result,
:SekciaID;
-- Vlozime novy zaznam, pripadne stary updatneme
IF (Result IS NULL) THEN
EXECUTE PROCEDURE ParameterSekcieInsert(:inSekcia, :inNazov, :inHodnota, :inHodnotaZobraz, :inReadOnly, :inTyp) RETURNING_VALUES Result;
ELSE
UPDATE ParameterSekcie SET
Sekcia = :SekciaID,
Nazov = :inNazov,
Hodnota = :inHodnota,
HodnotaZobraz = :inHodnotaZobraz,
ReadOnly = :inReadOnly
WHERE
(ParameterSekcie = :Result);
END;
Ak potrebujeme v selekte použiť nejakú funkciu, nedá sa použiť storovaná procedúra (ako napr. v Sybase). Ale veľmi jednoducho vieme napísať modul do Firebirdu, ktorým si pomôžeme, tieto moduly sa volajú UDF:
Nezabúdajme, že InterBase nedokáže spracovať NULL ani ho vrátiť v UDF, teda musíme zabezpečiť NOT NULL argumenty (v selekte napr. cez príkaz COALESCE). Delphi príklad:
Nezabúdaj písať optimálne UDF. Predsa len sa púšťajú pri každom zázname a môj príklad je ináč fekálne neoptimálny!