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

Kliens-szerver architektúra

Hálózatos környezetben egy munkaállomás a szervert legtöbbször úgy használja, mintha annak erőforrása ténylegesen és lokálisan az övé lenne. Például ha egy fájlt szeretne elérni, a munkaállomás elkéri azt a szervertől. A szerver elküldi a hálózaton keresztül, amit aztán a munkaállomás helyileg feldolgoz. Annyiban azért mégis különbözik a dolog a helyi adatoktól, hogy a szerver adataihoz egyszerre többen is hozzáférnek.

Az adatbázis szerver adatbázis műveletek elvégzésére dedikált szerver, működése egy kicsit különbözik a fent említett szerverek működésétől. Míg egy szerver működése passzív abból a szempontból, hogy pusztán az erőforrásokat biztosítja, és a feldolgozás a munkaállomásokon zajlik, az adatbázisszerver aktív, és az adatok feldolgozásának fontos színtere. Ha a munkaállomásnak szüksége van információra, egy lekérdezést (query) küld az adatbázisszervernek. Az adatbázis maga hajtja ezt végre, és csak a lekérdezés eredményét küldi vissza a kérőnek. Ilyen módon a munkaállomás ügyfele lesz az őt kiszolgáló adatbázis szervernek. Az ügyfél-kiszolgáló vagy kliens-szerver architektúrában az adatbázis szervert számtalan különböző ügyfél bombázhatja kérésekkel egyidejűleg.

Ebben a rendszerben az ügyfél és a kiszolgáló közösen vállalnak részt az adatok kezelésében. A kiszolgáló felel az adatok kezeléséért, az ügyfél pedig megjeleníti a műveletek eredményét.

SQL Links

A Delphi adatbázis szerverek eléréséért felelős komponense a Borland SQL Links. A Delphi telepítésekor ki kell választani, hogy kérjük-e vagy sem. Telepítés után a BDE Administrator programban létre tudunk hozni olyan alias-okat is, amik SQL adatbázisra mutatnak. Ha már nem emlékszünk, hogy hogyan telepítettük a Delphit, a BDE Administrator Configuration fülén könnyedén ellenőrizhetjük: a Configuration/Drivers/Native pontban - többek között - szerepelnie kell az MSSQL bejegyzésnek is.

Az SQL Links nemcsak a Microsoft SQL Server felé kapocs. Szükség van rá InterBase, DB2, Informix, ORACLE és Sybase adatbázisok eléréséhez is.

Az alias vagy álnév egy paraméterhalmaz, amin keresztül hivatkozunk egy adatbázisra. A helyi adatbázisokhoz nem voltak szükségesek, azonban SQL adatbázisok eléréséhez elengedhetetlen.
Az SQL Server adatbázisokra vonatkozó alapértelmezett beállításokat az előbb már említett BDE Administrator Configuration fülén, a Configuration/Drivers/Native pontban tudjuk beállítani. A későbbiekben létrehozott MSSQL aliasok létrehozásuk után az itt beállított értékeket veszik fel.

Nézzük meg a fontosabb paramétereket:

VERSION: Az SQL Links meghajtó verziószámát tartalmazza.
TYPE: Az adatbázis szerver típusát jelzi. Értéke SERVER, ha adatbázis szerverről van szó, pl. MSSQL esetén, vagy FILE, ha helyi adatbázisról van szó, pl. PARADOX esetén.
DLL32: A meghajtó program neve. MSSQL-nél: SQLMS32.DLL.
CONNECT TIMEOUT: Ennyi ideig próbálkozik az alkalmazásunk felépíteni a kapcsolatot az SQL Serverrel. Az értéket másodpercekben kell beírni. Alapértelmezett érték 60 másodperc.
TIMEOUT: Az alkalmazásunk maximum ennyi ideig várakozik egy lekérdezés eredményére. Szintén másodpercben kell beírni az értéket. Alapértelmezett várakozási idő 5 perc. A paraméter beállításánál körültekintéssel kell eljárni, hiszen a leghosszabb ideig futó lekérdezésünknek is bele kell férnie ebbe az időbe.
MAX DBPROCESSES: A nyitott, konkurens MSSQL kapcsolatok száma. Alapértelmezés szerint 31.
SERVER NAME: Az SQL Server neve, amihez csatlakozni szeretnénk.
DATABASE NAME: Az adatbázis neve az SQL Serveren belül.
USER NAME: Az alapértelmezett felhasználói név, amivel bejelentkezünk az SQL Serverbe.
OPEN MODE: Az SQL Server megnyitásának módja. Lehet READ ONLY és READ/WRITE.
LANGDRIVER: Nyelvi drivert állíthatunk be. Alapértelmezés szerint ez a paraméter üres.
SQLQRYMODE: Ez a változó definiálja a lekérdezések kezelésének módját. Értéke és jelentése háromféle lehet. Ha NULL, akkor a mód ún. server-local. Ez azt jelenti, hogy lekérdezések először eljutnak az SQL Serverhez, majd ha a szerver valamiért képtelen ezt feldolgozni, a lekérdezés lokálisan is lefut. A második mód a Server-only mód (a változó értéke SERVER). E szerint a lekérdezés mindenképpen eljut a szerverhez, és semmiképpen nincs helyi feldolgozás. Végül a változó lehet LOCAL, ez a local-only mód, vagyis a lekérdezések mindig helyileg futnak le.
Erre a beállítási módra azért van szükség, mert adódhatnak olyan helyzetek, amikor egy lekérdezés eredménye függ attól, hogy a lekérdezés hol fut le. Ha például egy case-sensitive keresést szeretnénk végrehajtani, és az adatbázisszerverünk ezt nem támogatja, akkor világos, hogy a helyileg futtatott lekérdezést preferáljuk. Ha biztosak akarunk lenni abban, hogy minden lekérdezés eljut a szerverhez, a változó értékét SERVER-re kell állítanunk. Ekkor azok a lekérdezések, amik mindenképpen lokálisan futnának, hibaüzenetet adnak. Ilyenek a heterogén lekérdezések, vagyis azok, amelyek több adatbázisból dolgoznak egyszerre; azok a lekérdezések, amik nem fejezhetők ki egyetlen SQL utasítással, illetve azok, amelyeket a szerver nem támogat.
SQLPASSTHRU MODE: Az egyik legfontosabb paraméter az SQLPASSTHRU MODE. Értéke meghatározza, hogy a Delphi hogyan kommunikáljon az SQL Serverrel a BDE-n keresztül. A lekérdezések alapvetően kétféleképpen juthatnak el a szerverhez. A Delphi beépített adatbázis parancsain keresztül közvetlenül navigálhatunk a táblán belül, törölhetünk vagy beszúrhatunk rekordokat, szűrhetünk, stb. A második metódus - az ún. passthru (passthrough) SQL - esetében az SQL utasítás egy egyszerű sztring formájában jut el a szerverhez, és az értelmezi, optimalizálja, feldolgozza és visszaküldi az eredményt. Ennek megfelelően három különböző értéket állíthatunk be.

SHARED AUTOCOMMIT: Ekkor a passthru SQL és a non-passthru SQL ugyanazt a kapcsolatot megosztva használja, a passthru SQL ekkor úgy viselkedik, mintha non-passthru SQL lenne. Ez azt jelenti, hogy mindaddig, amíg az utasításokat az alkalmazás (illetve a programozó) nem ágyazza explicit módon tranzakcióba, az utasítások automatikusan kommittálódnak. (A tranzakció kezelésről később egy önálló fejezetben részletesebben lesz szó.)
SHARED NOAUTOCOMMIT: A passthru SQL és a non-passthru SQL itt is osztozik a kapcsolaton, de a passthru utasítások ebben az esetben nem kommittálódnak automatikusan.
NOT SHARED: A passthru SQL és a non-passthru SQL nem osztoznak egy kapcsolaton. Ez az alapértelmezés szerinti beállítás.

A SHARED AUTOCOMMIT és SHARED NOAUTOCOMMIT módok nem támogatják az összes passthru SQL utasítást. Továbbá ezekben a módokban nem építhetjük be a tranzakció vezérlést az SQL utasításainkba, azt a BDE-re kell bíznunk.

MAX ROWS: Azon sorok számár maximálja, amelyeket az SQL Server a lekérdezés eredményeként visszaad. Ha az eredményhalmaz ennél a korlátnál nagyobb, egy EOF jellegű üzenet generálódik. Remekül lehet használni ezt a korlátozást olyan nemkívánatos események kivédésére, mint például egy SELECT * FROM ... kiadása a felhasználó részéről. Mégsem célszerű azonban az értéket túl kevésre állítani, mert esetleg az adatbázis szerkezetére vonatkozó metaadatok sem férnek át. Az alapértelmezett érték -1, vagyis nincs sorkorlátozás.
ENABLE SCHEMA CACHE: Boolean érték. TRUE esetén a táblák sémája, vagyis a szerkezetükre vonatkozó információk lokálisan tárolódnak. Kis teljesítményjavulást érhetünk el ezzel a beállítással, hiszen az alkalmazásnak nem kell minden esetben ezeket az információkat a szervertől lekérdeznie. Használata csak statikus adatbázisok esetén tanácsos. Ha ugyanis a szerveren megváltoztatjuk egy már cache-elt tábla bármilyen jellemzőjét (új indexet adunk hozzá, mezőt törlünk belőle stb.), úgy a programfutás közben hibaüzenetet kapunk.
SCHEMA CACHE DIR: Ha az ENABLE SCHEMA CACHE-t TRUE-ra állítjuk, akkor ebben a pontban állíthatjuk be, hogy a tárolandó meta adatok mely könyvtárba kerüljenek. A könyvtár méretét a SCHEMA CACHE SIZE beállításával lehet korlátozni. Azt kell beállítani, hogy legfeljebb hány tábla adatai lehetnek egyszerre tárolva. Az érték 0 és 32 között változhat, alapértelmezés szerint 8. A SCHEMA CACHE TIME beállításával lehet hangolni, hogy meddig tárolódjanak a metaadatok. Az alapértelmezett érték -1, e szerint az adatok az adatbázis bezárásáig tárolódnak. 0 esetén nincs cache-elés, végül bármely 0-nál nagyobb értékkel azt az időt adhatjuk meg másodpercekben, ameddig egy tárolt metaadat fogyasztható. Az idő lejárta után azt újra le kell kérni a szervertől.
TDS PACKET SIZE: Az adatforgalom a szerver és az ügyfél között csomagokban történik. Egy ilyen csomag (TDS packet, tabular data stream packet) méretét lehet itt beállítani. Értéke 0 és 65535 között változhat. Minél nagyobb a csomag mérete, annál nagyobb teljesítményt érhetünk el, főleg ha BLOB-okat is tárolunk. Ajánlott, hogy az érték 4096 és 8192 között legyen. Alapértelmezés szerint 4096 van beállítva.
Hogy ezt az értéket mire állíthatjuk be az SQL Server oldalán, azt az sp_configure tárolt eljárással kérdezhetjük meg tőle. Ez 512 és 65535 között állítható és alapértelmezés szerint 4096 van beállítva.
MAX QUERY TIME: Az SQL Links legfeljebb ennyi ideig vár egy lekérdezés végrehajtására. Ezen túl megszakítja a lekérdezést. Alapértelmezett érték 5 perc.

Optimalizációs tippek

Mindig használjunk schema cache-elést. Ez kizárólag abban az esetben ellenjavallt, ha az adatbázisunk szerkezetét gyakran változtatjuk.

A TDS csomag mérete legyen legalább 4K. Ha változtatjuk a BDE oldalán, mindig állítsuk be szerver oldalon is, különben nem tudunk majd kapcsolatot teremteni.
Explicit begin transaction és end transaction utasítások használata mindig gyorsabb, mintha ezt az SQL Links-re bízzuk és Autocommit-ot állítunk be. Vagyis gyorsabb működést tudunk elérni, ha az SQLPASSTHRU MODE paramétert SHARED NOAUTOCOMMIT-ra állítjuk és a tranzakciók kezelését magunk oldjuk meg.

Kapcsolat az SQL Server felől

Ha az adatbázisszerver és az ügyfélprogram nem ugyanazon a számítógépen fut, az SQL Server a network IPC komponensen keresztül kommunikál. (IPC = Interprocess Communication). Az IPC két részből áll, az eljáráshívási felületből (API) és magából a protokollból. A protokoll definiálja azokat a szabályokat és formátumokat, amik szerint a kommunikáció zajlik a két fél között, az API pedig biztosítja azokat a függvényeket, amelyekkel az IPC programozható.

A szerver és a kliens közti kommunikáció a következőképpen zajlik:

1. Az igény a Delphi alkalmazás szintjén jelentkezik.
2. Az alkalmazás az adatbázis funkciók eléréséhez a BDE-t veszi igénybe.
3. A BDE betölti a szükséges server driver-t, jelen esetben az MSSQL driverét. Ez nem más, mint az SQLMSS32.DLL, amit megnézhetünk a BDE Administrator programban az MSSQL alias prototípus DLL32 paraméterében.

Amikor a BDE továbbadja a hívást, SQL terminológiával élve a DB-Library DLL-jét hívja meg. Ennek két része van. Az egyik része a már említett SQLMSS32.DLL. Eddig a pontig a szükséges fájlokat a Delphi prezentálja és telepítéskor meg is teszi a megfelelő beállításokat. A további szintekről magunknak kell gondoskodnunk.

4. A DB-Library másik felét az SQL Serverben kell keresnünk: ntwdblib.dll.
5. A DB-Library a Net-Library DLL-jét hívja meg. A 2. fejezetben említettük, hogy telepítéskor ki kell választanunk, hogy mely hálózati könyvtárakat kívánjuk használni. Ettől a választástól függ, hogy most melyik DLL-re lesz szükségünk. Ha az alapértelmezés szerinti Names Pipes könyvtárat választottuk, akkor ez most a dbnmpntw.dll.
6. Végül a Net-Library az IPC API-n keresztül fog kommunikálni a szerver oldali IPC API-val.
Most már világos, hogy ha a munkaállomáson nincs SQL Server telepítve, akkor az alkalmazásunk telepítése mellett az ntwdblib.dll és a dbnmpntw.dll fájlokat is fel kell másolnunk, mégpedig a WINDOWS vagy WINNT könyvtárba. Ezeket a dll-eket megtaláljuk a szerveren az MSSQL7/BINN könyvtárban.

Példaprogram

Létrehozunk egy MSSQL aliast és megnyitunk egy táblát. A program feltételezi, hogy Microsoft SQL Server van a számítógépen, és a Delphi is az SQL Links-szel együtt került telepítésre.

Első lépésként hozzunk létre egy MSSQL aliast Test1 néven. A BDE Administrator-ban a következő paramétereket kell mindenképpen beállítani:
1. Server name: Az adatbázis szerver neve, illetve gép neve. (Contorl Panel - Network - Identification -Computer Name)
2. Database name: pubs. (Az SQL Serverrel szállított példa adatbázis neve.)
3. User name: Felhasználói név, amivel az adatbázist el szeretnénk érni. Ha az SQL Server telepítése után nem változtattuk meg az adminisztrátori jelszót, akkor sa névvel és üres jelszóval tudunk belépni.

Mentsük el a változtatásokat, majd próbáljuk meg megnyitni az adatbázist még a BDE Administrator-ból.

Delphi-ben a Form-unkra leteszünk egy tábla, egy grid és egy adatforrás komponenst, és a szokásos módon kötjük össze őket. A tábla DatabasaName property-je legyen Test1, a TablaName property pedig legyen dbo.job. Ha a megnyitás gombra kattintunk, az SQL Links megpróbálja felvenni a kapcsolatot az SQL Serverrel, és jelszót kérdez tőlünk.

Kipróbálhatjuk mi történik, ha a BDE Administrator-ban a max rows paramétert mondjuk 10-re állítjuk. Bekapcsolhatjuk a schema cache-elést, és megnézhetjük, hogy milyen információk jelennek meg a cache dir-ben megadott könyvtárban.
A következő fejezetben megismerkedünk az SQL Server adatbázis objektumának kezelésével, majd ezek után már egy kicsit bonyolultabb programot állítunk össze.

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

4. rész