Aplikativni (ugnježdeni) SQL, karakteristike i primeri

1. Aplikativni (ugnježdeni) SQL, karakteristike i primeri Marko Marković 334/06 Nikola Ranković 310/06

2. Koncept • 1.ugradjeni SQL • 2.direktni pozivi DB2 funkcija (CLI – Call Level Interface) • 3.upotreba ODBC standarda ( Open Database Connectivity)

3. Ugradjen SQL • po mogućnostima jednak CLI sistemu • Sve SQL komande : • 1) Statičke • 2) Dinamičke • Prema komandama dele se i programi: • 1) Statičke • 2) Dinamičke

4. program sa dinamičkim SQL-om • 1) korisnik koji pokreće paket mora imati pravo da pokrene paket i prava da izvši svaku pojedinačnu SQL komandu iz paketa • 2) korisnik koji ugradjuje paket mora imati prava da ugradi paket (BINDADD)

5. Programa sa statičkim SQL-om • 1.Korisnik koji pokreće paket mora imati pravo da pokrene paket; • 2.Korisnik koji ugradjuje paket mora imati pravo da ugradi paket (BINDADD) i prava da izvrši svaku pojedinačnu SQL komandu i to ne implicitno preko grupnog ili PUBLIC prava nego eksplicitno, preko pojedinačnog prava.

6. Ugradjivanje SQL komandi • U okviru osnovnog programskog paketa DB2 sistema isporučuju se biblioteke i alati za ugradjivanje SQL komandi u: • C, • C++, • COBOL, • FORTRAN • REXX • a pored toga se mogu nabaviti i alati za SMALLTALK, JAVA i druge jezike

7. Primer 1. Napisati C program, sa ugradjenim SQL-om, koji ispisuje najveći redni broj snabdevača iz tabele S: • #include <stdlib.h> • #include <stdio.h> • EXEC SQL INCLUDE SQLCA; • EXEC SQL BEGIN DECLARE SECTION; • char d_srbr[5]; • EXEC SQL END DECLARE SECTION; • int is_error (char eString[], struct sqlca *caPointer) • { • if (saPointer->sqlcode<0){ • printf (“----GRESKA----\n”); • printf (“GRESKA: %s.\SQLCODE:%ld\n”, eString, caPointer->sqlcode); • printf (“---------------------”); • return 1; • } • return 0; • }

8. #define CHECKERROR(str) if(is_error(str,&sqlca)!=0) return 1; • main() • { • EXEC SQL connect to spj user studentb using abcde; • CHECKERROR(“Connect to SPJ”); • EXEC SQL select max(srbr) into :d_srbr • from s; • CHECKERROR(“Select”) • if(!SQLCODE) printf(“MAX SRBR = %.5s\n\r”, d_srbr); • EXEC SQL connect reset • CHECKERROR(“Connect reset”); • return 0; • }

9. Fragment ugnježdenog SQL upita u PL/I(u primeru tražimo težinu proizvoda šifre ‘P2’): • EXEC SQL BEGIN DECLARE SECTION ; • DCL SQLSTATE CHAR(5) ; • DCL P# CHAR(6) ; • DCL TEZINA FIXED DECIMAL(5,1) ; • EXEC SQL END DECLARE SECTION ; • P# = ‘P2’ ; • EXEC SQL SELECT P.TEZINA • INTO : TEZINA • FROM P • WHERE P.P# = P# ( :P# ) ; • IF SQLSTATE = ‘00000’ • THEN … ; /* TEZINA = vraćena vrednost */ • ELSE … ; /* došlo je do neke greške */

10. Karakteristike ugnježdavanja • 1.Ugnježdeni SQL iskazi se unosi sa prefiksom EXEC SQL da bi se razlikovali od iskaza jezika u kome ugradjujemo te komande, i završavaju se posebnim simbolom kao npr. ; • 2. Izvršni iskaz SQL-a se pojavljuje kada god se pojavljuje izvršni iskaz programa. Za razliku od interaktivnog SQL-a ugnježdeni SQL uključuje iskaze koji su čisto deklarativni a ne izvršni. Npr. DECLARE CURSOR nije izvršni iskaz kao ni BEGIN i END DECLARE SECTION.

11. 3. SQL iskazi mogu sadržati reference na promenljive programa na kome se radi; ove reference moraju sadržati prefiks : da bi se razlikovali od imena kolona. Promenljive se mogu pojavljivati u ugnježdenom SQL-u kada god se literal može pojavljivati u interaktivnom SQL-u. Promenljive se takodje mogu pojavljivati u INTO klauzama pri SELECT-u ili FETCH-u da bi označavale ciljeve operacija prikupljanja podataka. • U C-u, ako se pokazivačke promenljive koriste u SQL komandama, tada se moraju upotrebljavati sa tačno onoliko simbola ‘*’ sa koliko su definisani. • Ako se u neku promenljivu želi upisati neki atribut rezultata upita koji može biti NULL, tada je neophodna upotreba indikatora (promenljiva tipa short int, negativan broj ako je rezultat NULL) • Indikatorska promenljiva se navodi u obliku: • …:var INDICATOR :ivar… ili …:var:ivar….

12. 4. INTO klauza u SELECT iskazu se može uneti da bi odredili promenljive u kojima se upisuju vrednosti koje će biti vraćene ovim iskazom. • SELECT ... INTO <host-promenljive> • FROM … WHERE … GROUP BY … HAVING … • Vrednosti svih atributa iz SELECT klauzule redom se upisuju u host promenljive, koje moraju biti odgovarajućeg tipa. Host promenljive se medjusobno razdvajaju zapetama, a ako je dopušteno da vrednost atributa bude NULL, onda je neophodno da neposredno iza host promenljive sledi odgovarajuća indikatorska promenljiva. • 5. Sve programske promenljive na koje referišemo u SQL iskazima moraju biti prethodno deklarisane u okviru deklarativnog dela ugnježdenog SQL-a, koji je odredjen sa BEGIN i END DECLARE SECTION iskazom.

13. 6. Svaki program koji sadži iskaze ugnježdenog SQL-a mora takodje da sadrži promenljivu zvanu SQLSTATE. Posle svakog izvršenog SQL iskaza u PL/I-u programu se vraća šifra stanja. -00000 – iskaz se uspešno završio -02000 – se izvršio ali nije vraćen rezultat • Promenljiva SQLCODE u C-u je celobrojna i može biti: =0, uz SQLWARN0=’’, sve je u redu =100, ako nema zahtevanih podataka >0, izvršenje je prošlo bez greške, ali sa upozorenjem =0, uz SQLWARN0=’W’, sve je u redu, ali sa upozorenjem <0,greška i komanda nije izvršena

14. 7. Svaka programska promenljiva mora biti odgovarajućeg tipa zavisno od upotrebe. npr., • programska promenljiva koja će biti korišćenja kao cilj smeštanja podataka mora biti onog tipa podataka koji bi bio kompatibilan sa iskazom koji pruža vrednost koja će biti pridružena ovoj lokaciji. • Takodje, programska promenljiva koja je koričćena kao izvor podataka (u INSERT iskazu) mora biti onog tipa koji je kompatabilan sa SQL kolonom u kojoj se upisuju vrednosti. • 8.Programske promenljive i SQL kolone imaju isto ime

15. 9. U jeziku PL/I, iskaz WHENEVER se koristi da bi se pojednostavio proces testiranja pomoću vrednosti promenljive SQLSTATE. Ona ima formu: • EXEC SQL WHENEVER <condition> <action> ; • <condition> ::= NOT FOUND | SQLWARNING | SQLEXCEPTION | SQLSTATE • <action> ::= CONTINUE | GO TO

16. “WHENEVER <condition> GO TO <label>” • “IF <condition> THEN GO TO <label>…” • “WHENEVER <condition> CONTINUE” • <condition>-i NOT FOUND, SQLWARNING I SQLEXCEPTION su definišu na sledeći način: • NOT FOUND - podatak nije nadjen (SQLSTATE= 02xxx) • SQLWARNING - prijavljeno je upozorenje(SQLSTATE = 01xxx ) • SQLEXCEPTION - došlo je do greške

17. Primer obrade greške u C-u: • … • int is_error (char eString[], struct sqlca *caPointer) • { • if (saPointer->sqlcode<0){ • printf (“----GRESKA----\n”); • printf (“GRESKA: %s.\SQLCODE:%ld\n”, eString, caPointer->sqlcode); • printf (“---------------------”); • return 1; • } • return 0; • } • #define CHECKERROR(str) if(is_error(str,&sqlca)!=0) return 1; • main() • { • … • CHECKERROR(“Connect to spj”); • … • }

18. 10.Ugnježdavanje SQL-a se zasniva na loose coupling (labavo uparivanje) izmedju SQL-a i programskog jezika.

19. Host (programske) promenljive

21. Kursori • Problem sa operacijama prikupljanja podataka je taj da generalno vraćaju veći broj vrsta kao rezultat, a ne samo jednu. • Treba premostiti razlike izmedju skupovnih operacija i mogućnosti SQL-a i metoda prihvatanja podataka programskog jezika u kome se radi, koji operišu sa vrstama. • Kursori se uvode kao pokazivači na jedan red rezultata ispita, koji se mogu postepeno pomerati unapred po rezultujućoj tabeli. Kursori se ne mogu pomerati unazad. • DECLARE <ime kursora> CURSOR FOR <upit> • OPEN <ime kursora> • FETCH <ime kursora> INTO <host promenljive> • CLOSE <ime kursora>

22. Primer u C-u. Napisati program koji pomoću kursora ispisuje sadržaj S: • … • EXEC SQL BEGIN DECLARE SECTION; • char d_srbr[5]; • char d_sime[20]; • short id_sime; • short d_status; • short id_status; • char d_grad[15]; • short id_grad; • EXEC SQL END DECLARE SECTION; • … • EXEC SQL declare scur cursor for • select srbr, sime, status, grad from s; • CHECKERROR(“Declare cursor scur”); • EXEC SQL open scur; • ...

23. ... • CHECKERROR(“Open scur”); • printf(“SRBR SIME STAT.GRAD\n\r ”); • printf(“============================\n\r”); • while(1){ • EXEC SQL fetch scur into :d_srbr, :d_sime:id_sime, d:status:id_status, :d_grad:id_grad; • if(SQLCODE!=0) break; • if(id_sime<0) d_sime[0]=’\0’; • if(id_status<0) d_sime[0]=’\0’; • if(id_grad<0) d_sime[0]=’\0’; • printf(“%.5s %.20s %.5d %15s\n\r”, d_srbr, (id_sime<0 ? “NULL”:d_sime), (id_status<0 ? -1:d_status), (id_grad<0 ? “NULL”:d_grad)); • } • CHECKERROR(“Fetch scur”); • EXEC SQL close scur; • CHECKERROR(“Close scur”);

24. Kursori i promena podataka • Kursor može biti definisan samo za čitanje, za čitanje i brisanje, ili za čitanje, brisanje i menjanje podataka. • FOR READ ONLY • FOR UPDATE OF <lista atributa> • Kursor se implicitno ograničava ako: • 1.FROM klauzula upita, ili nekog podupita, sadrži više od jedne tabele, ili sadrži pogled koji služi za čitanje; • 2.upit sadrži DISTINCT, VALUES ili ORDER BY klauzulu; • 3.upit sadrži agregatne funkcije ili GROUP BY ili HAVING klauzulu; • 4.upit sadrži neki skupovni operator (UNION, INTERSECT, EXCEPT); • DELETE FROM <ime tabele> WHERE CURRENT OF <ime kursora>

25. Operacije koje ne zahtevaju korišćenje kursora: • Jedinični (singleton) SELECT • INSERT • DELETE (osim u CURRENT obliku, videti pri kraju) • UPDATE (osim u CURRENT obliku, videti pri kraju)

26. Jedinični (singleton)SELECT : • naći status i grad snabdevača čija šifra je data programskom promenljivom GIVENS# • EXEC SQL SELECT STATUS, GRAD INTO :RANK, :MESTO FROM S WHERE S# = S# ( :GIVENS# ) ;

27. INSERT : • uneti novi deo (šifra dela, ime i težina su dati redom programskim promenljivama P#, PIME, PTEZ; boja i grad nisu poznati) • EXEC SQL INSERT INTO P ( P#, PIME, PTEZ) VALUES ( :P#, :PIME, :PTEZ) ;

28. DELETE : • izbrisati sve pošiljke snabdevača čiji je grad dat programskom promenljivom GRAD. • EXEC SQL DELETE FROM SPJ WHERE :GRAD = ( SELECT GRAD FROM S WHERE S.S# = SPJ.S# ) ;

29. UPDATE : • povećati status svi snabdevača iz Londona za količinu datu programskom promenljivom POVISICA • EXEC SQL UPDATE S SET STATUS = STATUS + :POVISICA WHERE GRAD = ‘London’ ;

30. Operacije koje zahtevaju korišćenje kursora • potreban je mehanizam za sekvencijalan pristup vrstama u skupu, jedna-po-jedna, te kursori pružaju takav mehanizam

31. Ovaj proces je ilustovan na prehodno prikazanom primeru u kome je dato da se pronadju podaci S#, SNAME i STATUS za sve proizvodjače u gradu zadatom programskom promenljivom Y. • DECLARE X CURSOR… <tabelarni izraz> • Procenjivanje tabelarnog izraza tek pri OPEN X • FETCH X INTO… <host promenljiva> • CLOSE X

32. Postoje tri izvršna iskaza za rad sa kursorima: OPEN, FETCH i CLOSE: • OPEN: EXEC SQL OPEN <ime kursora> ; • FETCH: EXEC SQL FETCH <ime kursora> INTO <referenca na programsku promenljivu zarez-lista> ; • CLOSE: EXEC SQL CLOSE <ime kursora> ;

33. CURRENT oblici iskaza DELETE i UPDATE • Ukoliko je naprimer X trenutno pozicioniran na nekoj vrsti, onda je moguće primeniti DELETE i UPDATE na “trenutnu poziciju X”. • EXEC SQL UPDATE S SET STATUS = STATUS + :POVISICA WHERE CURRENT OF X ; • CURRENT oblici DELETE i UPDATE iskaza su zabranjeni ukoliko <tabelarni izraz> u deklaraciji kursora definiše pogled bez mogućnosti ažuriranja, ukoliko je deo CREATE VIEW iskaza.