Embedded SQL Vtzslav Imrek Embedded SQL je dobrm

Embedded SQL Vítězslav Imrýšek

Embedded SQL je dobrým jazykem pro definování databází a vytváření dotazů nad daty databáze SQL není ideálním jazykem pro vytváření sofistikovaných databázových aplikací Potřeba využití vyššího programovacího jazyka (například C / C++)

Embedded SQL Základní myšlenkou Embedded SQL je možnost využití SQL výrazů v programu napsaném v nějakém vyšším jazyce. . printf(); EXEC SQL. . . scanf(); Preprocesor zanalyzuje kód, přeloží SQL výrazy do systémových volání C/C++ a nakonec vygeneruje samotný program.

Hostitelské proměnné SQL výrazy jsou zabudované v programu Potřeba pro mechanismus, který bude předávat hodnoty mezi prostředím C programu a SQL výrazy, které komunikují s SQL serverem. Speciální proměnné určené pro tento účel nazýváme Hostitelské proměnné. Definovány v bloku mezi begin declare section a end declare section Musí být kompatibilní s datovými typy sloupců tabulky v DB EXEC SQL begin declare section int cno; varchar cname[31]; varchar street[31]; int zip; char phone[31]; EXEC SQL end declare section

Hostitelské proměnné /* varchar cname[31] */ struct { unsigned short len; unsigned char arr[31]; } cname; Při zasílání varchar hodnoty do databáze je prací programátora správně vypnit hodnoty len a arr. Při získání varchar hodnoty z databáze jsou obě pole správně vyplněny systémem

Konverze datových typů Oracle Data Type C Data Type char(N) varchar(N) date number(6) number(10) number(6, 2) char array[N+1] varchar array[N+1] char array[10] int long int float

Hostitelské proměnné jsou v jazyce C používány stejně jako standardní proměnné. Pouze v případě, kdy jsou použity v SQL dotazu, je nutné před tyto proměnné umístit dvojtečku „: “. Pozn. : Přiřazení hodnoty k proměnné pomocí příkazu select into lze použít pouze v případě, můžeme zaručit, že dotaz vrátí právě jednu, či žádnou položku. (řeší tzv. Kurzory, více později) scanf(“%d“, &cno); EXEC SQL select into from where cname : cname customers cno = : cno; scanf(“%d%s%s%d%s“, &cno, cname. arr, street. arr, &zip, phone); cname. len street. len = strlen(cname. arr); = strlen(street. arr); EXEC SQL insert into customers values(: cno, : cname, : street, : zip, : phone);

Indikátorové proměnné SQL obsahuje hodnotu null, tato hodnota ovšem nemá v jazyce C odpovídající protějšek. Příklad: V SQL může mít datový typ integer hodnotu null. V C toto není možné. Pro vyřešení problému komunikace null hodnoty Embedded SQL poskytuje tzv. Indikátorové proměnné. Pomocná proměnná typu integer Každý atribut má jednu Možné hodnoty: -1: položka má hodnotu null 0: položka nemá hodnotu null

Indikátorové proměnné - příklad EXEC SQL begin declare section; struct { int ono; int cno; int eno; char received[12]; char shipped[12]; } order_rec; struct { short ono_ind; short cno_ind; short eno_ind; short received_ind; short shipped_ind; } order_rec_ind; int onum; EXEC SQL end declare section; scanf(“%d“, &onum); EXEC SQL select * into : order_rec indicator : order_rec_ind from orders where ono = : onum; if(order_rec_ind. shipped_ind == -1) printf("SHIPPED is Nulln"); else printf("SHIPPED is not Nulln");

Indikátorové proměnné Pro vložení hodnoty null do databáze: nastaví se hodnota -1 do příslušné indikátorové proměnné indikátorová dotazu. proměnná se využije v insert|update onum = 1021; order_rec_ind. shipped_ind = -1; EXEC SQL update orders set shipped = : order_rec. shipped indicator : order_rec_ind. shipped_ind where ono = : onum;

SQL Communications Area (sqlca) Oznámení o stavu zpracování dotazu. Pro použití sqlca je nutné brzy v programu použít následující příkaz: EXEC SQL include sqlca; Okamžitě po zpracování dotazu dojde k vyplnění proměnné „sqlca“. Možné hodnoty proměnné sqlca: sqlca. sqlcode Interpretace 0 SQL dotaz vykonán úspěšně >0 Žádná další data nebo hodnoty nenalezeny <0 Vyskytla se chyba během vykonávání SQL dotazu.

SQL Communications Area Příklad 1 Kontrola chyby EXEC SQL set transaction read write; EXEC SQL insert into customers values (custseq. nextval, : customer_rec. cname, : customer_rec. street, : customer_rec. zip, : customer_rec. phone); if(sqlca. sqlcode < 0) { printf("CUSTOMER (%s) DID NOT GET ADDEDn", customer_rec. cname. arr); EXEC SQL rollback work; return; } EXEC SQL commit;

SQL Communications Area Příklad 2 Kontrola existence dat EXEC SQL select into from where zip, city : zipcode_rec zipcodes zip = : customer_rec. zip; if(sqlca. sqlcode > 0) { zipcode_rec. zip = customer_rec. zip; printf("Zip Code does not exist; Enter City: "); scanf("%s", zipcode_rec. city. arr); zipcode_rec. city. len = strlen(zipcode_rec. city. arr); EXEC SQL set transaction read write; EXEC SQL insert into zipcodes (zip, city) values (: zipcode_rec); EXEC SQL commit; }

Připojení k Oracle databázi Klíčové příkazy: EXEC SQL connect [user] identified by [password] – připojení k Db EXEC SQL commit release – odpojení od Db EXEC SQL begin declare section; varchar userid[10]; varchar password[15]; EXEC SQL end declare section; strcopy(userid. arr, "Admin"); userid. len = strlen(userid. arr); strcopy(password. arr, "Password"); password. len = strlen(password. arr); EXEC SQL connect : userid identified by : password; if(sqlca. sqlcode == 0) { // Connected }

Kurzory Pokud příkaz select vrací více než jeden výsledek, jednoduchý příkaz select into nelze použít V takovém případě se používá koncept Kurzorů Mechanismus, umožňující C programu přistupovat k výsledným řádkům SQL dotazu po jednom. EXEC SQL declare <cur-name> cursor for <select-statement> [for {read only | update [of <column-list>]}]; for read only select Nedovoluje mazání a úpravu nalezených dat for update [of <column-list>] Používáno v případech pozičního mazání a úpravě dat

Kurzory – použití 1) Otevřít Kurzor 2) Několikanásobné volání příkazu fetch, pomocí kterého postupně získáme všechny řádky zadaného dotazu. 3) Uzavřít Kurzor EXEC SQL open <cur-name>; EXEC SQL fetch <cur-name> into <host-var>, … , <host-var>; EXEC SQL close <cur-name>;

Kurzory Příklad 1 Vypsání všech řádků tabulky{ print_customers() void EXEC SQL declare customers_cur cursor for select cno, cname, street, zip, phone from customers; EXEC SQL set transaction read only; EXEC SQL open customer_cur; EXEC SQL fetch customer_cur into : customer_rec indicator : customer_rec_ind; while(sqlca. sqlcode == 0) { customer_rec. cname. arr[customer_rec. cname. len] = ''; customer_rec. street. arr[customer_rec. street. len] = ''; printf("%6 d %10 s %20 s %6 d %15 sn", customer_rec. cno, customer_rec. cname. arr, customer_rec. street. arr, customer_rec. zip, customer_rec. phone); EXEC SQL fetch customer_cur into : customer_rec indicator : customer_rec_ind; } EXEC SQL close customer_cur; EXEC SQL commit; }

Kurzory Příklad 2 Poziční mazání a úpravy EXEC SQL declare del_cur cursor for select * from employees where not exists (select 'a' from orders where orders. eno = employees. eno) for update; EXEC SQL set tranaction read write; EXEC SQL open del_cur; EXEC SQL fetch del_cur into : employee_rec; while(sqlca. sqlcode == 0) { EXEC SQL delete from employees where current of del_cur; EXEC SQL fetch del_cur into : employee_rec; } EXEC SQL commit release;

Rekurzivní dotazy S Embedded SQL je možné snadno vytvářet rekurzivní dotazy nad daty databáze SQL má své historické limitace Ve starších verzích SQL nemožné vyjádřit rekurzivní dotazy V novějších verzích SQL již toto možné je

Rekurzivní dotazy Příklad EID (employee ID integer) MGRID (Manager ID integer) Smith Jones Blake Jones Brown Smith Green Smith White Brown Adams White Chceme najít všechny zaměstnance pracující pod určitým manažerem na všech úrovních. Nebo-li: query(X) : - emps(X, ‘Jones’); query(X) : - emps(X, Y), query(Y). tabulka emps algoritmus insert into query select eid from emps where mgrid = 'Jones'; repeat insert into query select eid from query, emps where mgrid = a; until (no more changes to query);

Rekurzivní dotazy Příklad #include <stdio. h> EXEC SQL begin declare section; int eid, a; EXEC SQL end declare section; EXEC SQL include sqlca; main() { int newrowadded; EXEC SQL declare c 1 cursor for select eid from emps where EXEC SQL declare c 2 cursor for select eid from emps, query EXEC SQL declare c 3 cursor for select a from query; do { newrowadded = FALSE; EXEC SQL open c 2; EXEC SQL fetch c 2 into : a; while(sqlca. sqlcode == 0) { EXEC SQL insert into query values (: a); if(sqlca. sqlcode == 0) newrowadded = TRUE; EXEC SQL fetch c 2 into : a; } EXEC SQL close c 2; } while(newrowadded); EXEC SQL commit work; //Initial answers mgrid = : eid; //Transitivity where mgrid = a; //Print answers EXEC SQL create table query( a integer not null, primary key(a)); printf("Answer isn"); EXEC SQL open c 3; EXEC SQL fetch c 3 into : a; while(sqlca. sqlcode == 0) { printf("%dn", a); EXEC SQL fetch c 3 into : a; } EXEC SQL close c 3; EXEC SQL commit work; printf("Type in employee id: "); scanf("%d", &eid); EXEC SQL open c 1; EXEC SQL fetch c 1 into : a; while(sqlca. sqlcode == 0) { EXEC SQL insert into query values (: a); EXEC SQL fetch c 1 into : a; } EXEC SQL close c 1; EXEC SQL commit work; EXEC SQL drop table query; EXEC SQL commit work; }

Zpracovávání chyb Oracle oznamuje všechny chyby v sqlca Dva způsoby zpracování: Explicitní Kontrola hodnot sqlca Implicitní Pomocí příkazu whenever struct sqlca { char sqlcaid[8]; long sqlabc; long sqlcode; struct { unsigned short sqlerrml; char sqlerrmc[70]; } sqlerrm; char sqlerrp[8]; long sqlerrd[6]; char sqlwarn[8]; char sqlext[8]; }; struktura sqlca v Oracle

Explicitní zpracovávání chyb Chyby jsou zkontrolovány po každém volání Db využívá se SQL communications area (sqlca) Programátor za běhu programu reaguje na stavy hodnot Je. Položka možné inicializovat pomocí #define SQLCA_INIT Popis sqlcaid znakový řetězec reprezentující sqlca; ‘SQLCA ’ sqlabc velikost struktury sqlca; sizeof(struct sqlca) sqlcode stav poslední Db operace sqlerrm podstruktura, obsahuje chybovou hlášku sqlerrp zatím bez využití sqlerrd zaznamenává chybové diagnostické informace sqlwarn pole varování sqlext zatím bez využití

Implicitní zpracovávání chyb Explicitní zpracovávání vyžaduje mnoho obslužného kódu Implicitní zpracovávání umožňuje definovat obslužné akce na jednom místě příkaz whenever EXEC SQL whenever <condition> <action>; /* conditions */ sqlerror : an error situation (sqlcode < 0) sqlwarning : a warning situation (sqlcode > 0) not found : an error situation (sqlcode = 1403) /* actions */ continue : ignore condition and continue do function: call a function do break : perform a break out of a control structure do return : perform a return statement goto label : branch to a label stop : stop program and roll back uncommitted changes Příkaz whenever je možné použít libovolně krát Aplikováno pro všechny následující příkazy Nastavení pro danou <condition> je účinné do doby, kdy tuto <condition> předefinujeme jinou akcí

Implicitní zpracovávání Příklad Typické použití /* Placed somewhere at the beginning of the source file */ EXEC SQL whenever sqlerror do sql_error("ORACLE error--n"); /* Error handling procedure */ void sql_error(char msg[]) { char error_message[512]; long buffer_length, message_length; EXEC SQL whenever sqlerror continue; buffer_length = sizeof(error_message); sqlglm(error_message, &buffer_length, &message_length); printf("%. *sn", message_length, error_message); EXEC SQL rollback release; exit(1); }

Kontrola transakcí Transakce Soubor dílčích operací Vykonány jako celek Pokud jedna dílčí operace neuspěje, k žádné změně Db nedojde Následující příkazy slouží ke správě transakcí: /* Used before select or fetch statements. Begins read-only transaction */ EXEC SQL set transaction read only /* Used before updates or deletes in Db. Terminated by either commit or rollback */ EXEC SQL set transaction read write /* Commit all the changes made in the transaction and release any locks on tables */ EXEC SQL commit /* Undo all the changes made in the transaction and release all locks on tables */ EXEC SQL rollback

Dynamické SQL Dosavadní příkazy statické fixované v době kompilace Existují případy, kdy SQL dotazy v době kompilace nejsou známy Tyto příkazy musejí být sestaveny v době běhu programu Příkazy Dynamic SQL

Dynamické SQL – příkazy EXEC SQL execute immediate <host-var> Provede SQL dotaz uložený v poskytnuté proměnné Proměnná typu řetězec znaků Nemůže obsahovat odkaz na žádnou Hostitelskou proměnnou Může obsahovat pouze jediné z: create table, alter table, drop table, insert, delete, update Při každém zavolání je nutné dotaz zkompilovat Pomalé

Dynamické SQL – příkazy EXEC SQL prepare s from : sql_stmt; EXEC SQL execute s using : var 1, … , : varn; Provede SQL dotaz uložený v poskytnuté proměnné Proměnná typu řetězec znaků Umožňuje Dvě použít v dotazu Hostitelskou proměnnou fáze: Prepare – zkompilování a uložení připraveného SQL dotazu Execute – provedení zkompilovaného SQL dotazu, za využití poskytnutých proměnných

Dynamické SQL Příklad 1 execute immediate #include <stdio. h> EXEC SQL begin declare section; char sql_stmt[256]; varchar username[10], password[15]; EXEC SQL end declare section; EXEC SQL include sqlca; void main() { strcpy(username. arr, "book"); username. len = strlen(username. arr); strcpy(password. arr, "book"); password. len = strlen(password. arr); EXEC SQL connect : username identified by : password; strcopy(sql_stmt, "update employees set hdate=sysdate where eno = 1001"); EXEC SQL set transaction read write; EXEC SQL execute immediate : sql_stmt; EXEC SQL commit release; exit(0); }

Dynamické SQL Příklad 2 prepare & execute #include <stdio. h> EXEC SQL begin declare section; char sql_stmt[256]; int num; varchar username[10], password[15]; EXEC SQL end declare section; EXEC SQL include sqlca; void main() { strcpy(username. arr, "book"); username. len = strlen(username. arr); strcpy(password. arr, "book"); password. len = strlen(password. arr); EXEC SQL connect : username identified by : password; strcopy(sql_stmt, "update employees set hdate=sysdate where eno = : n"); EXEC SQL set transaction read write; EXEC SQL prepare s from : sql_stmt ; do { printf("Enter eno to update (0 to stop): >"); scanf("%d", &num); if(num > 0) { EXEC SQL execute s using : num; EXEC SQL commit; } } while(num > 0); EXEC SQL commit release; exit(0); }

Závěr Embedded SQL umožňuje přístup k databázím a zde uloženým datům v prostředí C/C++ programů a to vše za použití známých SQL dotazů Embedded SQL umožňuje snadné psaní mocnějších rekurzivních dotazů Embedded SQL poskytuje vývojáři aplikace různé prostředky na zjištění a diagnostiku chyb Embedded SQL podporuje princip transakcí Embedded SQL umožnuje vyhodnocování SQL výrazů zadaných až v době běhu programu