3. Andmebaasidega suhtlemine ASP-is Microsoft® Data Access Components (MDAC) loob lihtsad programsed vahendid suhtlemiseks andmebaasidega. Võrgul põhinevad klient-server süsteemid (sh. WWW-süsteemid) võivad kasutada MDAC vahendeid vahendamaks andmebaasidel põhinevaid infosüsteeme. MDAC koosneb järgmistest vahenditest: * ActiveXTM Data Objects (ADO) andmebaasi-serveriga suhtlemiseks * Remote Data Service (RDA) loomaks klientarvutis võimalusi andmebaasidega suhtlemiseks serverarvutis * Microsoft® OLE DB Provider for ODBC, mis võimaldab ADO kaudu juurdepääsu andmebaasidele * Open Database Connectivity (ODBC) ja mitmed ODBC draiverid ASP-is kasutatakse andmebaasidega suhtlemiseks tavaliselt ADO vahendeid, mis omakorda kasutab OLE DB-d. 3.1 ADO ülevaade ActiveXTM Data Objects võimaldab luua rakendusi suhtlemaks andmebaasidega. See hõlmab nii andmete vaatamist kui nende muutmist. ADO peamised eelised on kiirus, lihtsus, vähene mälu- ja kettaruumi vajadus. ADO omadused, mis võimaldavad luua klient/server ja WWW süsteeme, sisaldavad järgnevat: * Sõltumatult loodavad objektid. Kui tavaliselt tuleb objekte (ODBC ühendus, päring jt.) luua kindlas järjekorras, siis ADO võimaldab iga sellist objekti sõltumatult luua. See võimaldab kokku hoida programmeerimise aega ja ka suurendab rakenduste efektiivsust. * Uuendatavaid andmeid võimaldatakse hoida (koguda) lokaalselt, et siis kõik korraga serverile saata. * Eelnevalt defineeritud sisend-väljundandmetega protseduuride toetus. * Erineva iseloomuga päringuobjektide loomine. * Päringuparameetrite toetus (nt. kindla ridade arvuga päring). * Mitme kirjeobjekti (recordset) tagastuse toetus eelsalvestatud protseduurides. * Eraldiseisvad (ja töötavad) objektid rakendustes. Vaatamata sellele, et ADO võimaldab neid omadusi kasutada, võivad konkreetsed ODBC draiverid seada omi piiranguid ja sellest tulenevalt ei ole alati võimalik kõiki omadusi kasutada. 3.2 ADO objektid Kuigi ADO objektid on sõltumatult loodavad ja kasutatavad, eksisteerivad nende vahel siiski hierarhilised suhted: Pilt1 Mõnel objektil on määratud atribuudid (properties): Pilt2 ADO-s on defineeritud 7 objekti: * Command käsuobjekt, mis sisaldab infot teatavate käskude kohta (päringustring, parameetrite definitsioonid jne). Selle objektiga saab näiteks defineerida päringuparameetreid või käivitada eelsalvestatud protseduure, mis tagastavad väljundiparameetreid. Samas ei pea seda objekti kasutama päringute tegemiseks. * Connection sisaldab andmeühenduseks vajalikku infot. * Error sisaldab ADO objektide töö käigus tekkivaid veakirjeldusi. Kuna üks käsk võib põhjustada mitu viga, siis kogum Errors võib sisaldada mitut Error-objekti (iga vea jaoks üks). * Field sisaldab konkreetse veeru infot kirjeobjektis (Recordset). Samas sisaldab objekt Recordset kogumit Fields, mis sisaldab kõiki andmehulga Field-objekte. * Parameter parametriseeritud objekti Command üks parameetritest. Samas sisaldab objekt Command kogumit Parameters, milles sisalduvad kõik käsu parameetrid. * Property ADO objekti süsteemselt defineeritud atribuut. * Recordset kirjete hulk, mis on saadud mingi päringu tulemusena. Kirjeobjekti võib luua ka ilma ühenduse objekti (Connection) loomata, aga kui on loodud ühenduse objekt, siis võib luua mitu sellel ühendusel põhinevat kirjeobjekti. Kõik objektid sisaldavad meetodeid ja atribuute, millega saab kergelt objektidega töötada. Objektid Collection, Command ja Recordset toetavad atribuutide kogumit Proterties. Atribuutide kogum sisaldab kõiki atribuute (ka. draiveri-spetsiifilisi), mis pannakse paika mõne ADO (objekti)käsu täitmisel. Selleks, et mõne atribuudi poole pöörduda, võib kasutada meetodit Items, näiteks: Command.Properties.Item(0) Command.Properties.Item(atribuudi nimi) Kuna aga meetod Items on ADO kogumite vaikimisi võetav meetod ja kogum Properties on vaikimisi võetav kogum, siis võib mõlemad ära jätta. Seega on ülaltooduga samaväärne järgnev: Command(0) Command(atribuudi nimi) 3.3 Töö andmetega ASP-is ASP-is andmebaasidega suhtlemiseks võib kasutada eelpoolmainitutest kahte objekti: Connection ja Recordset (ka selle alamobjekt Field). Kuigi objekti Recordset võib kasutada ilma eelnevalt andmebaasiga ühendust loomata (kasutades objekti Connection), on siiski parem eelnevalt luua ühendus andmebaasiga. 3.3.1 Ühendus andmebaasiga Selleks, et andmebaasiga ühendust luua, tuleb kasutada ASP-i objekti Server ja selle meetodit CreateObject. Ühenduse loomise süntaks on järgmine: Set yhendus = Server.CreateObject("ADODB.Connection") Siin luuakse ühenduse objekti esindaja, mille nimeks saab yhendus. Selleks aga, et luua ühendust mingi konkreetse andmebaasiga (kasutades ODBC vahendeid), tuleb kasutada ühenduse objekti meetodit Open, mille süntaks on järgmine: yhendus.Open ühendus-string, kasutajatunnus, parool ühendus-string annab ADO-le (ja sealt edasi ODBC-le) teada, millise andmebaasiga ühendust luua soovitakse. See string võib omakorda täpsustada ODBC draiveri ja muid täpsustavaid andmeid, kuid sellesse võib kirjutada ka lihtsalt ODBC-s defineeritud andmeallika nime (sellega määratakse ära andmebaas ODBC-s). kasutajatunnus ja parool ei ole kohustuslikud; nendega määratakse kasutaja, kelle nime alt andmebaasiga ühendus luuakse. 3.3.2 Päringud andmebaasis Kui ühendus andmebaasiga on loodud, saab andmebaasis päringuid teha (kasutades objekti Recordset). Selleks kasutatakse ühenduse objekti meetodit Execute, mille süntaks on järgmine: Set kirjed = yhendus.Execute(Päringustring, ridadearv, parameetrid) Siin luuakse kirjeobjekt nimega kirjed, mille kaudu edaspidi tagatakse juurdepääs päringu tulemustele. Päringustring on SQL-lause (SQL-päringutest antakse lühiülevaade käesoleva paragrahvi viiendas alajaotuses), tabeli nimi või mõni muu käsk, mida draiver toetab. ridadearv on mittekohustuslik väli ja see määrab, mitu rida päringu tulemusest loetakse kirjeobjekti. parameetrid annab draiverile teada, millist tüüpi käsk sisaldub päringustringis; ka see väli pole kohustuslik. Kui ei taheta kasutada ühenduse objekti, siis võib kirjeobjekti ka otse luua objekti Server meetodiga CreateObject, mille süntaks on järgmine: Set kirjed = Server.CreateObject("ADODB.recordset") Selleks, et loodud objektile vastavusse seada SQL-päringut, tuleb kasutada kirjeobjekti meetodit Open, mille süntaks on järgmine: kirjed.Open Päringustring, andmebaas, kirjetüüp, lukustusetüüp, parameetrid andmebaas kirjeldab ODBC andmeallika nime ja sellega kaasnevaid lisaparameetreid (kasutaja andmeid), kirjetüüp määrab päringu tüübi (kas päring on näiteks dünaamiline, st. kõik muutused kajastuvad kohe ka kirjeobjektis), lukustusetüüp määrab juurdepääsuõigused teistele päringutele/kasutajatele (kui avatakse näiteks tabel, saab keelata teistel sellega tegutsemine). Kohustuslik väli on siin ainult Päringustring. 3.3.3 Päringute kasutamine Kui kirjeobjekt on defineeritud ja sellega on tehtud andmebaasis päring, siis on vajalik ka juurdepääs päringus sisalduvatele andmetele. Selleks on kirjeobjektis loodud kogum Fields ja selles sisalduv alamobjekt Field. Et andmebaasi tabeli väljale vastavaid andmeid kirjeobjektist saada, piisab järgmisest süntaksist: Kirjed(väli) väli on kas string või string-tüüpi muutuja, mis määrab tabeli veeru. Selle meetodiga saab küll konkreetse väärtuse päringust, aga päringus võib olla mitu kirjet ja selleks, et kirjeobjekti kirjetes liikuda, on loodud meetodid MoveFirst (esimesele kirjele liikumiseks), MoveNext (järgmisele kirjele liikumiseks), MovePrevious (eelmisele kirjele liikumiseks) ja MoveLast (viimasele kirjele liikumiseks). Meetodite süntaks on järgmine: Kirjed.meetod Sõltuvalt konkreetsest ODBC draiveri võimalusest võivad meetodid MovePrevious ja MoveLast olla kasutamatud. Kirjete vahel liikumise juures tuleb arvestada järgmiste kirjeobjekti atribuutidega: * BOF saab tõeseks, kui liikumisega on satutud ettepoole esimest kirjet. Kui atribuut on tõene ja üritatakse sellest ettepoole liikuda, tekib viga. * EOF saab tõeseks, kui liikumisega on satutud viimasest kirjest tahapoole. Kui atribuut on tõene ja üritatakse edasi liikuda, tekib viga. Kasulik atribuut kirjetega opereerimisel on RecordCount, mis määrab kirjete arvu kirjeobjektis. Aga sõltuvalt ODBC draiveri võimalustest võib selle atribuudi väärtus valeks osutuda (ADO ei suuda kirjete arvu määrata). Eelneva jutu illustreerimiseks sobib järgmine näide. Kuna Microsoft® Accessi ODBC draiver ei võimalda kasutada atribuuti RecordCount, siis näites arvutatakse kirjete arv: MituKirjet = 0 Algväärtustamine Tsüklit tehakse kuni, jõutakse kirjete lõppu: Do While NOT kirjed.EOF Suurendatakse loendurit: MituKirjet = MituKirjet + 1 Liigutakse järgmisele kirjele: kirjed.MoveNext Loop Tsükli lõpp Kui mõni kirje leidus, tuleb kirjete algusse tagasi minna, et edaspidi kirjetega opereerida: If MituKirjet Then kirjed.MoveFirst 3.3.4 Töö lõpetamine Andmebaasi(de)ga töö lõppedes on kasulik ühendus ja kirjeobjekt kaotada (ressursside säästmise eesmärgil). Objektid hävitatakse küll programmi (protseduuri/skripti) lõppedes, aga kasulikum on objektid ise kaotada. Et objekti kaotada, tuleb objekt kõigepealt sulgeda ja selleks on nii ühenduse objektil kui ka kirjeobjektil meetod Close. Meetodi süntaks on järgmine: Object.Close Meetod vabastab objekti poolt hõivatud ressursid, objekt ise jääb aga alles. Seega võib peale meetodi välja kutsumist objekti atribuute muuta ning objekti uuesti avada (meetod Open). Kui aga soovitakse objekt täielikult hävitada, tuleb objektile omistada spetsiaalväärtus Nothing. Kui kasutada meetodit Close ühenduse objekti sulgemisel, suletakse ka kõik sellel ühendusel baseeruvad kirjeobjektid. Peale ühenduse sulgemist põhjustavad kõik meetodid, mis nõuavad avatud ühenduse olemasolu, vea. Samuti tekib viga, kui ühendust üritatakse sulgeda samal ajal, kui toimub mingi andmete uuendamine. Nagu ühenduse puhul, ei tohi ka kirjeobjekti puhul peale objekti sulgemist kutsuda välja meetodeid, mis nõuavad konkreetse objekti olemasolu. Kui kirjeobjekti üritatakse sulgeda samal ajal, kui toimub andmete uuendamine, tekib viga. 3.3.5 SQL-päringud Eelpoolmainitud päringutes kasutatakse SQL-päringuid (SQL Structured Query Language). SQL on spetsiaalne andmebaasidega suhtlemise keel ja päringute tegemiseks on selles keeles käsk SELECT, mille süntaksit järgnevas tutvustatakse (algmaterjalid saadud allikast [3]). SELECT-käsu üldine süntaks on järgmine: SELECT veeerud FROM tabelid Siin veergudena määratakse need veerud, mida soovitakse tabelitest saada (FROM tabelid). Veergude pärimisel saab nendega teha ka lihtsamaid aritmeetilisi tehteid (+,-,*,/). SELECT-käsuga saab andmetele seada piiranguid; nimelt saab võetud väljadest välja selekteerida korduvad väljad. Selleks on SELECT-käsul lisakäsud: * DISTINCT - ridadest, milles valitud veerud korduvad võetakse ainult üks. * DISTINCTROW - kordumisi ei otsita mitte valitud veergudes, vaid tervetes kirjetes (ehk korduvatest kirjetest jääb ainult üks). Kasutades neid lisakäskusid näeb SELECT-käsu süntaks välja järgmine: SELECT DISTINCT|DISTINCTROW veerud Selleks, et mitmest tabelist andmeid pärida, tuleb tavaliselt tabelid omavahel siduda. Selleks on SQL-is SELECT-käsu osa INNER JOIN, mida kasutatakse koos FROM-käsuosaga. INNER JOIN üldine süntaks on järgmine: FROM tabel1 INNER JOIN tabel2 ON tabel1.väli1 võrdlus tabel2.väli2 Selle käsuosaga antakse teada, et tebel1 on tabel2-ga seotud veergude väli1 ja väli2 kaudu. võrdlus võib olla üks järgnevatest võrdlusoperatsioonidest: "=", "<", ">", "<=", ">=", või "<>". Üks INNER JOIN käsuosa võib sisaldada ka mitut tingimust (eraldatuna üksteisest OR või AND klausliga). INNER JOIN käsuosasid võib üksteise sisse paigutada (mitme tabeli omavahel sidumiseks, sellisel juhul kehtib näiteks kolme tabeli omavahel sidumisel järgmine süntaks: FROM tabel1 INNER JOIN (tabel2 INNER JOIN tabel3 ON tabel2.väli2 võrdlus tabel3.väli3) ON tabel1.väli1 võrdlus tabel2.väli2 Kõigepealt seotakse tabel2 tabel3-ga ja seejärel tabel1 tabel2-ga (ühtlasi ka tabel3-ga läbi tabel2-e). INNER JOIN pole ainus võimalus tabelite omavahel sidumiseks. Tabeleid võib omavahel siduda SELECT-käsu osaga WHERE. Selle lauseosaga saab ka päritavaid andmeid täpsustada. WHERE käsuosa süntaks on järgmine: WHERE tingimused Siin määratakse need tingimused, millele päritavad andmed peavad vastama. Eri tingimusi saab omavahel siduda OR või AND klausliga. Tingimustes saab kontrollida välja eksisteerimist (EXISTS või NOT EXISTS), saab määrata välja kuulumist hulka (IN hulk, kus hulgana võib olla ka teine SELECT-käsk) ja saab seada väljadele sarnasuse tingimusi (LIKE sarnasus, st. väli peab sarnanema antud sarnasusega). Küsitavaid andmeid saab ka grupeerida. Seda tehakse SELECT-käsu osaga GROUP BY, mille süntaks on järgmine: GROUP BY rühm Rühmana antakse veergude nimistu, mida grupeerida soovitakse. Ka grupeerimisele saab tingimusi seada (HAVING tingimused), st. grupeeritakse ainult need väljad, mis vastavad tingimustele. Grupeerimisel saab veergude osas kasutada ka funktsioone summa arvutamiseks (SUM), maksimaalse (MAX), minimaalse (MIN) ja keskmise (AVG) väärtuse leidmiseks; samuti saab leida grupeeritud väljade arvu (COUNT). Funktsioonide süntaks on järgmine: Funktsioon veerunimi Küsitavaid välju saab ka sorteerida, kasutades SELECT-käsu osa ORDER BY, mille süntaks on järgmine: ORDER BY veerunimed Veerunimedele saab ette anda ka tingimused, kas kasvavaks (ASC) või kahanevaks (DESC) sorteerimiseks. Kui tingimust pole määratud, sorteeritakse veerg kasvavas järjekorras. Sorteerimisel arvestatakse veerunimede järjekorda, st. kõigepealt sorteeritakse esimesed veerud. Kokkuvõttes võib SELECT-käsu süntaksi kirja panna järgnevalt: SELECT DISTINCT|DISTINCTROW veerud FROM tabel INNER JOIN tabeln ON tabel.väli1 võrdlus tabeln.välin WHERE tingimus GROUP BY rühm HAVING tingimus ORDER BY veerud Kohustuslikud osad on ainult SELECT ja FROM. Tuleb tähele panna, et stringide edastamisel SQL-päringu kaudu on soovitav nad ümbritseda apostroofidega ja sellest tulenevalt ei tohi stringid ise apostroofe sisaldada.