Práce s databázovými dotazy ve skriptu

1. Co je databázový dotaz ve skriptu

• Databázový dotaz je samostatný konfigurovatelný objekt aplikace, definovaný mimo skript.
• Skript databázový dotaz pouze používá, nikoliv definuje.
• Databázový dotaz ve skriptu neurčuje, jak se data vybírají, ale jak se s vybranými daty dále pracuje.
• Databázový dotaz není SQL příkaz psaný ve skriptu.

2. Načtení hodnot z databázového dotazu

• Skript může z databázového dotazu načíst jednu nebo více hodnot.
• Načtením hodnot z dotazu vznikají skriptové proměnné.
• Datový typ proměnné se automaticky převezme z databázového sloupce.
• Pokud databázový dotaz vrací více záznamů, proměnná se stává polem hodnot.
• Pokud databázový dotaz nevrátí žádný záznam, proměnná se stává polem s nulovým počtem prvků.

3. Načtení jedné hodnoty

• Příkaz „Přiřadit hodnotu z dotazu“ načítá z databázového dotazu jeden sloupec.
• Do proměnné se načtou hodnoty ze všech vrácených záznamů – vznikne pole hodnot. U sloupce prvku „MultiListBox“ jsou prvky pole jednotlivé položky ze všech vrácených záznamů, bez duplicit.
• Do databázového ovládacího prvku se zapíše hodnota z prvního vráceného záznamu; nevrátí-li dotaz žádný záznam, zapíše se databázová hodnota „null“.
• Pro dotaz, který logicky vrací právě jednu hodnotu (např. součet, minimum, maximum), je výsledkem pole s jedním prvkem.

4. Načtení více hodnot

• Při načtení více hodnot z databázového dotazu se načítají všechny řádky výsledné množiny.
• Každý načtený sloupec se uloží do samostatné proměnné.
• Všechny proměnné mají stejný počet prvků a jsou navzájem synchronizované.
• Do databázového ovládacího prvku se zapíše hodnota z prvního vráceného záznamu. Nevrátí-li dotaz žádný záznam, ovládací prvek zůstane beze změny.
• Ke každé načítané hodnotě lze volitelně připojit výpočet – operátor a druhý operand, kterým je hodnota databázového ovládacího prvku aktuálního záznamu, hodnota jiného sloupce téhož záznamu dotazu, nebo hodnota zapsaná do pole hodnoty.

5. Databázový dotaz a cyklus

• Databázový dotaz se velmi často používá ve spojení se skriptovým příkazem „Cyklus“.
• Cyklus prochází jednotlivé prvky proměnných načtených z databázového dotazu.
• V každé iteraci cyklu se jako jednoprvková chová řídící proměnná a proměnné načtené týmž příkazem jako ona. Ostatní proměnné s polem hodnot zůstávají i v cyklu polem hodnot.
• Po ukončení cyklu se proměnné opět chovají jako pole hodnot. Hodnota, kterou skript během iterace přiřadí řídící proměnné nebo jiné proměnné načtené týmž příkazem jako ona, se po dokončení iterace (příkazem „Další...“) uloží do příslušného prvku jejího pole, takže ji příkaz „Uložit proměnnou zpět do databáze“ za cyklem zapíše do databáze. Neplatí to pro cyklus nad proměnnou, která nebyla načtena z databázového dotazu, a pro iteraci, kterou skript opustí příkazem „Skok na řádek“ – změny z takové iterace se ztratí.
• Jako řídící proměnná cyklu se doporučuje použít primární klíč „id“.

6. Uložení hodnot zpět do databáze

• Hodnoty načtené z databázového dotazu je možné uložit zpět do databáze třemi příkazy, které se chovají rozdílně:
  • „Uložit proměnnou zpět do databáze“ zapíše každý prvek proměnné do záznamu, ze kterého byl načten. Dotaz se při tom znovu nespouští a počet prvků proměnné se od načtení nesmí změnit. Zpět lze uložit jen proměnnou načtenou ze sloupce formuláře, nad kterým je dotaz postavený; proměnná ze sloupce „(ID)“, ze sloupce připojeného formuláře nebo ze sloupce prvku „MultiListBox“ skončí chybou. Záznamy, které skript dříve smazal příkazem „Smazat záznam“, se přeskočí.
  • „Uložit hodnotu do databáze“ zapíše jednu hodnotu do všech záznamů, které vrátí databázový dotaz tohoto příkazu.
  • „Uložit více hodnot do databáze“ zapíše každé své přiřazení (proměnnou, databázový ovládací prvek nebo hodnotu zapsanou do pole hodnoty) do zvoleného sloupce všech záznamů, které vrátí databázový dotaz tohoto příkazu.
  • Proměnnou s polem hodnot tyto dva příkazy po záznamech nerozdělují: do všech záznamů zapíší její první prvek, do sloupce prvku „MultiListBox“ všechny její prvky jako jeho položky. Každý prvek do vlastního záznamu zapíše příkaz „Uložit proměnnou zpět do databáze“.

• Zapisující příkazy skriptu („Uložit proměnnou zpět do databáze“, „Uložit hodnotu do databáze“, „Uložit více hodnot do databáze“, „Nový záznam“ a „Smazat záznam“, včetně jejich podmíněných variant) se v databázi provedou až po úspěšném dokončení celého skriptu, ve skriptech „OnBeforeSave“ a „OnBeforeDelete“ až v téže transakci po uložení, resp. smazání záznamu. Přeskočí-li smazání funkce „SKIPDELETE()“, nebo uložení funkce „SKIPSAVE()“ ve skriptu „OnBeforeSave“ spuštěném jinak než uložením editačního formuláře (například hromadnou změnou nebo importem z Excelu), neprovedou se vůbec.
• Zapisující příkaz zapíše jen sloupce, jejichž nová hodnota se liší od výchozího stavu záznamu. U záznamu, se kterým pracuje editační formulář, je výchozím stavem hodnota načtená z databáze na začátku zpracování formuláře (kromě skriptů „OnAfterSave“ a „OnAfterDelete“), jinak aktuální stav v databázi; zapsal-li skript do téhož záznamu už dříve, porovnává se s tímto dosud nepotvrzeným zápisem. Zapíše-li proto skript „OnBeforeSave“ do právě ukládaného záznamu hodnotu shodnou s jeho původní hodnotou v databázi, zápis se vynechá a uloží se hodnota, se kterou se záznam ukládá (z editačního formuláře, hromadné změny, importu a podobně).
• Do té doby pozdější příkazy skriptu, které pracují s databázovým dotazem, dostanou u vrácených záznamů už nové hodnoty sloupců formuláře, nad kterým je dotaz postavený, a záznamy určené ke smazání vynechají. Nové záznamy ale nevidí a podmínky, řazení i agregace dotazu vyhodnotí nad původními hodnotami.
• Serverová funkce „SQL()“ a externí funkce vidí stav databáze před zápisem.
• Zapisující příkazy skriptu nespouštějí skripty ani validaci formuláře, do jehož záznamů zapisují. Změny zapíší do historie změn a předají k synchronizaci stejně jako uložení záznamu ve formuláři. Zpracování záznamu ukládacími skripty jeho formuláře zajistí serverová funkce „OBSOAS“.
• Okamžité provedení zápisů zajistí serverová funkce „COMMIT()“ – detailní popis je uveden v samostatné příručce Serverové funkce.

7. Typické scénáře použití

• Načtení hodnot z databáze do proměnných a jejich další zpracování.
• Hromadná úprava databázových záznamů pomocí cyklu.
• Vytváření nových záznamů na základě hodnot z jiných databázových tabulek.

8. Typické chyby při práci s dotazy

• Očekávání jedné hodnoty, zatímco dotaz vrací více záznamů.
• Zapomenutí použít cyklus při práci s polem hodnot.
• Nejednoznačný databázový dotaz bez omezujících podmínek.
• Neuvážené použití dotazu s velkým množstvím záznamů.

9. Příklady

9.1. Mazání velkého množství záznamů pomocí skriptu

• Při mazání velkého počtu záznamů (orientačně více než 10 000) přímo z datagridu může operace selhat. Hranice není pevná a závisí na velikosti záznamů, historii, synchronizaci a databázovém serveru.
• Jeden požadavek datagridu musí načíst a zpracovat velké množství záznamů a pro každý z nich může vytvořit záznam historie. To zvyšuje riziko timeoutu a vyčerpání paměti nebo databázových prostředků.
• Bez historie se použije optimalizovaná funkce „DELETEDATA“. Má-li se současně odstranit i dosavadní historie záznamů, lze záznamy a jejich historii mazat iterativně po jednom; volání „DELETEDATA“ a „DELETEHISTORY“ se provádějí okamžitě mimo odloženou transakci příkazů skriptu.

9.1.1. Varianta mazání bez záznamů historie

DELETEDATA(ng_abc, id > 10)

9.1.2. Varianta mazání včetně záznamů historie

A = SELECT id FROM ng_abc WHERE id > 10
Cyklus (A)
  DELETEDATA(ng_abc, id = #A#)
  DELETEHISTORY(ng_abc, changedid = #A#)
Konec cyklu