Programiranje baza podataka

5. Implementiranje transakcija

Transakcije predstavljaju izuzetno važan alat za svakog programera koji koristi baze podataka u svojim aplikacijama. Svaka iole kompleksnija operacija nad podacima zahteva korišćenje transakcija da bi se takva operacija uspešno implementirala. Pritom, postoji veliki broj pitanja i potencijalnih problema koji se otvaraju prilikom korišćenja transakcija. U ovoj sekciji ćemo se upoznati sa različitim naredbama za rad sa transakcijama u DB2 sistemu za upravljanje relacionim bazama podataka. Započnimo ovaj deo teksta narednom definicijom.

Transakcija (engl. transaction) predstavlja logičku jedinicu posla pri radu sa podacima.

Transakcija predstavlja niz radnji koji ne narušava uslove integriteta. Sa stanovišta korisnika, izvršavanje transakcije je atomično. Po izvršenju kompletne transakcije stanje baze treba da bude konzistentno, tj. da su ispunjeni uslovi integriteta. Dakle, posmatrana kao jedinica posla, transakcija prevodi jedno konzistentno stanje baze u drugo takvo stanje baze, dok u međukoracima transakcije konzistentnost podataka može biti i narušena. Transakcija na taj način predstavlja i bezbedno sredstvo za interakciju korisnika sa bazom.

5.1 Operacije potvrđivanja i poništavanja izmena u bazi podataka

Pre nego što započnemo detaljnu diskusiju o implementaciji transakcija, obratićemo pažnju na jedan realan primer izvršavanja programa koji implementira prebacivanje novca sa jednog računa na drugi. Ovaj primer će nam služiti kao glavna motivacija zašto je potrebno da razumemo rad sa transakcijama. Pre nego što demonstriramo primer, dajmo narednu definiciju.

Atomična (engl. atomic) operacija je ona operacija koju nije moguće podeliti na više manjih operacija (tj. u pitanju je nedeljiva operacija).

Premeštaj novca sa jednog računa na drugi se može implementirati narednim nizom atomičnih operacija (radi čuvanja prostora, iz narednog niza eliminišemo razne provere, kao što su provera da li korisnik može da prebaci novac između računa, provera da li ima dovoljno sredstava na prvom računu, itd.):

1. Dohvati red u tabeli koji predstavlja prvi račun.
2. Umanji iznos u tom redu za traženu sumu.
3. Dohvati red u tabeli koji predstavlja drugi račun.
4. Uvećaj iznos u tom redu za traženu sumu.

Pretpostavimo da SUBP operacije iz ovih koraka implementira na takav način da se neposredno nakon njihovog izvršavanja sve izmene trajno upisuju u bazu podataka. Naredna slika ilustruje jedno moguće izvršavanje programa koji implementira ove korake. U tom izvršavanju, program prvo naredbom SELECT dohvata red u tabeli koji predstavlja prvi račun (koji je na početku imao 100 000 jedinica valute). Zatim, u drugom koraku, naredbom UPDATE umanjuje iznos u tom redu za traženu sumu (10 000 jedinica valute). Iz nekog razloga (nestanak struje, prekid mrežne komunikacije, itd.), nakon 2. koraka, program prijavljuje grešku i operativni sistem ga prekida. Međutim, kao što je prikazano na slici, stanje baze je takvo da je prvom računu umanjen iznos i informacija o tome da novac nije uspešno prebačen na drugi račun se izgubila. Drugim rečima, baza se nalazi u nekonzistentnom stanju. Zbog toga, važno je zapamtiti da SUBP nikada ne implementira operacije izmena tako da njihovi efekti budu trajno upisani u bazu podataka neposredno nakon njihovog izvršavanja.

Sada je validno postaviti pitanje - u kom trenutku se informacije o izmenama zaista upisuju u bazu podataka? Db2 baza podataka definiše naredne dve operacije za rad sa izmenama u bazi podataka.

Potvrđivanje (engl commit) predstavlja trajno upisivanje izmena u bazu podataka koje su do tada bile izvršene nad tom bazom podataka. Sve načinjene izmene se trajno pamte u bazi podataka i svi ostali procesi dobijaju mogućnost da vide načinjene izmene.

Poništavanje (engl rollback) predstavlja vraćanje stanja baze podataka u ono u kojem se baza podataka našla pre izvršavanja izmena koje su do tada bile izvršene nad tom bazom podataka. Izvršavanjem ove naredbe možemo poništiti sve one akcije koje do trenutka poništavanja nisu prethodno bile potvrđene.

Da bismo dodatno razumeli koje su to izmene u bazi podataka koje se potvrđuju, odnosno, poništavaju ovim operacijama, potrebno je da definišemo pojam jedinice posla.

Jedinica posla (engl. unit of work, skr. UOW) predstavlja nadoknadivu sekvencu operacija u okviru aplikacionog procesa.

Jedinica posla se inicijalizuje prilikom pokretanja aplikacionog procesa ili kada se prethodna jedinica posla završila posledicom operacije koja nije prekid aplikacionog procesa. Jedinica posla se završava operacijom potvrđivanja ili poništavanja izmena ili završetkom aplikacionog procesa. Operacije potvrđivanja i poništavanja izmena utiču samo da one promene u bazi podataka koje su izvršene tokom te jedinice posla koja se završava.

Inicijalizacija i završetak jedinice posla definišu tačke konzistentnosti u okviru aplikacionog procesa. Razmotrimo prethodni primer bankarske transakcije u kojoj se vrši premeštaj sredstava sa jednog računa na drugi račun. Kao što smo rekli, nakon drugog koraka (oduzimanja sredstava) podaci su nekonzistentni. Tek nakon izvršavanja četvrtog koraka (uvećavanje sredstava) konzistentnost je obnovljena, što je prikazano na narednoj slici:

Kada se oba koraka izvrše, može se iskoristiti operacija potvrđivanja izmena da bi se završila jedinica posla. Ako dođe do greške pre nego što se jedinica posla uspešno završi, SUBP će poništiti sve nepotvrđene izmene da bi vratio stanje baze podataka u konzistentno, što je prikazano na narednoj slici:

Dakle, rešenje problema prenosa novca bismo implementirali narednim koracima:

1. (Implicitno) Započni novu jedinicu posla (bilo započinjanjem novog procesa, prethodnim izvršavanjem naredba potvrđivanja ili poništavanja, itd.).
2. Dohvati red u tabeli koji predstavlja prvi račun.
3. Umanji iznos u tom redu za traženu sumu.
4. Dohvati red u tabeli koji predstavlja drugi račun.
5. Uvećaj iznos u tom redu za traženu sumu.
6. Potvrdi izmene u bazi podataka.

Naravno, nakon svakog koraka je neophodno izvršiti proveru grešaka. U slučaju greške u bilo kom trenutku, program mora da izvrši poništavanje izmena, čime se stanje baze vraća u ono koje je bilo pre 1. koraka, dakle, u konzistentno stanje.

SQL jezik definiše dve naredbe koje odgovaraju opisanim operacijama:

• Naredba COMMIT implementira operaciju potvrđivanja izmena.

• Naredba ROLLBACK implementira operaciju poništavanja izmena.

O ovim naredbama i njihovim bočnim efektima ćemo detaljnije govoriti u sekcijama o potvrđivanju izmena i o poništavanju izmena. Međutim, diskusija koju smo izložili do sada je dovoljna za demonstraciju najosnovnijeg efekta ovih naredbi.

Rešenje:

Datoteka: vezbe/primeri/poglavlje_5/zadatak_5_1.sqc:

#include <stdio.h>
#include <stdlib.h>

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
sqlint32 hIndex;
EXEC SQL END DECLARE SECTION;

void checkSQL(const char *str) {
    if(SQLCODE < 0) {
        fprintf(stderr, "Greska %d: %s\n", SQLCODE, str);

        // U funkciju za obradu greske dodajemo naredbu ROLLBACK
        // da bismo ponistili eventualne izmene u bazi podataka
        // ukoliko zaista dodje do greske.
        EXEC SQL ROLLBACK;

        EXEC SQL CONNECT RESET;
        exit(EXIT_FAILURE);
    }
}

int main() {
    EXEC SQL CONNECT TO stud2020 USER student USING abcdef;
    checkSQL("Connect");
    
    EXEC SQL 
        SELECT  MAX(INDEKS) 
        INTO    :hIndex 
        FROM    DA.ISPIT;
    checkSQL("Select 1");
    
    // Ako nema studenata u tabeli ISPIT, zavrsi program
    if(SQLCODE == 100) {
        printf("Tabela je prazna!\n");
        
        EXEC SQL CONNECT RESET;
        checkSQL("Connect reset - SQLCODE 100 - 1");
        
        return 0;
    }
    
    // Inace, ispisi maksimalni index, pa ga obrisi iz tabele ISPIT
    printf("Maksimalni index je %d.\n", hIndex);

    EXEC SQL 
        DELETE  FROM DA.ISPIT 
        WHERE   INDEKS = :hIndex;
    checkSQL("Delete");

    printf("Uspesno je obrisan podatak");

    // Ispisivanje najveceg index iz tabele ISPIT
    EXEC SQL 
        SELECT  MAX(INDEKS) 
        INTO    :hIndex 
        FROM    DA.ISPIT;
    checkSQL("Select 2");
    
    if(SQLCODE == 100) {
        printf("Tabela je prazna!\n");
    }
    else {
        printf("Maximalni index je %d.\n", hIndex);
    }
    
    // Pitamo korisnika za dalju akciju
    printf("Izaberite jednu od dve akcije:\n"
        "1. Potvrdjivanje izmena\n"
        "2. Ponistavanje izmena\n");

    short userResponse;
    scanf("%hd", &userResponse);

    if (userResponse == 1) {
        // Potvrdjujemo izmene naredbom COMMIT
        EXEC SQL COMMIT;
        checkSQL("Commit");
    }
    else {
        // Ponistavamo izmene naredbom ROLLBACK
        EXEC SQL ROLLBACK;
        checkSQL("Rollback");
    }

    printf("Vasa akcija je izvrsena!\n");
    
    // Ispisujemo najveci index ponovo
    EXEC SQL 
        SELECT  MAX(INDEKS) 
        INTO    :hIndex 
        FROM    DA.ISPIT;
    checkSQL("Select 3");
    
    if(SQLCODE != 100) {
        printf("Maximalni index je %d.\n", hIndex);
    }
    else {
        printf("Tabela je prazna!\n");
    }   

    // U slucaju uspesnog izvrsavanja programa,
    // potvrdjujemo sve akcije koje je nas program izvrsio
    // pre nego sto zatvorimo konekciju.
    EXEC SQL COMMIT;
    checkSQL("Commit - kraj programa");
    
    // Iako u ovom slucaju nismo imali nikakvu izmenu od poslednje COMMIT ili ROLLBACK naredbe do ovde, 
    // potvrdjivanje izmena tik pred raskidanje konekcije se smatra dobrom praksom
    // tako da cemo ovo ponasanje usvojiti u svim nasim aplikacijama nadalje!

    EXEC SQL CONNECT RESET;
    checkSQL("Connect reset");

    return 0;
}

Vrlo je bitno primetiti naredne dve stvari u kodu:

• U slučaju uspešnog izvršavanja programa, pre nego što zatvorimo konekciju sa bazom podataka, izvršavamo SQL naredbu COMMIT kako bismo potvrdili sve izmene koje je naša aplikacija eventualno izvršila nad bazom podataka.

• U slučaju da dođe do greške prilikom izvršavanja programa, pre nego što izađemo iz programa i prijavimo neuspeh, u funkciji checkSQL izvršavamo naredbu ROLLBACK kako bismo poništili sve izmene koje je naša aplikacija eventualno izvršila nad bazom podataka.

Ovo je dobra praksa i mi ćemo usvojiti ovaj način rada u našim C/SQL programima nadalje.

5.2 Složena SQL naredba

U ovoj sekciji ćemo diskutovati o načinima za izvršavanje više SQL naredbi kao jedne naredbe, tzv. složene SQL naredbe. Složene SQL naredbe predstavljaju osnovu rada sa transakcijama, o čemu će biti detaljnije reči u nastavku teksta.

Složena SQL naredba (engl. compound SQL) predstavlja sekvencu SQL naredbi ograđenu odgovarajućim ključnim rečima kojim se definiše jedan blok izvršavanja.

Postoje tri tipa složenih SQL naredbi:

1. Linijske (engl. inline) - Složena SQL linijska naredba je složena SQL naredba koja je umetnuta linijski tokom faze izvršavanja u okviru druge SQL naredbe. Složene SQL linijske naredbe imaju svojstvo atomičnosti; ako izvršavanje bilo koje pojedinačne SQL naredbe podigne grešku, cela naredba se poništava.

2. Ugnežđene (engl. embedded) - Ovaj tip naredbi kombinuje jednu ili više SQL naredbi (odnosno, podnaredbi) u jedan izvršivi blok.

3. Kompilirane (engl. compiled) - Predstavlja sekvencu SQL naredbi koje se izvršavaju sa lokalnim opsegom za promenljive, uslove, kursore i pokazivače na slogove (engl. handle).

Mi ćemo u daljem tekstu razmatrati isključivo složene SQL ugnežđene naredbe, tako da podrazumevamo ovaj tip kada kažemo “složena SQL naredba”. Sintaksa ovih naredbi je data u nastavku:

BEGIN COMPOUND (ATOMIC|NOT ATOMIC) STATIC
[STOP AFTER FIRST <MATICNA_PROMENLJIVA> STATEMENTS]

<SQL_NAREDBA>;
<SQL_NAREDBA>;
-- ...
<SQL_NAREDBA>;

END COMPOUND

U zavisnosti od odabranih vrednosti narednih opcija prilikom deklaracije složene SQL naredbe, ta naredba može imati različite varijante koji utiču na način izvršavanja:

• Atomičnost - tačno jedna od naredne dve opcije se navode

  • ATOMIC - Specifikuje da, ako bilo koja od podnaredbi u okviru složene SQL naredbe bude izvršena neuspešno, onda se sve izmene u bazi podataka koje su nastale efektom bilo koje druge podnaredbe, bilo one uspešne izvršene ili ne, poništavaju.

  • NOT ATOMIC - Specifikuje da, bez obzira na neuspešno izvršavanje podnaredbi, složena SQL naredba neće poništiti izmene u bazi podataka koje su nastale efektom bilo koje druge podnaredbe.

• Statičnost - navodi se naredna naredba

  • STATIC - Specifikuje da će sve matične promenljive za sve podnaredbe zadržati njihove originalne vrednosti. Na primer, ukoliko se u SQL složenoj naredbi nađe naredba:

SELECT  MIN(OCENA) 
INTO    :ocena 
FROM    DA.ISPIT

koja je praćena naredbom

DELETE  FROM DA.ISPIT 
WHERE   OCENA = :ocena

onda će naredba DELETE koristiti vrednost matične promenljive ocena koju je ta promenljiva imala na početku bloka koji je definisan složenom SQL naredbom, a ne vrednost koju je naredba SELECT INTO upisala u tu matičnu promenljivu. Time je transakcija koja se ostvaruje tom SQL složenom naredbom, za koju bismo možda očekivali da bude korektna, zapravo neispravno implementirana, zato što se oslanja na međuvrednost koju matična promenljiva ocena dobija naredbom SELECT INTO. Dodatno, ako se vrednost iste promenljive postavlja od strane više SQL podnaredbi, onda će na kraju bloka ta promenljiva sadržati vrednost koju je postavila poslednja SQL podnaredba.

Napomenimo da u DB2 sistemu nestatičko ponašanje nije podržano. To znači da bi trebalo posmatrati kao da se podnaredbe izvršavaju nesekvencijalno i podnaredbe ne bi trebalo da imaju međuzavisnosti, kao u datom primeru.

• Opcionom klauzom STOP AFTER FIRST specifikujemo da će se izvršiti samo određeni broj podnaredbi. Matična promenljiva <MATICNA_PROMENLJIVA> tipa short sadrži ceo broj N kojim se specifikuje koliko prvih N podnaredbi će biti izvršeno.

Nakon opisanih opcija, potrebno je navesti nula ili više SQL naredbi <SQL_NAREDBA>. SQL naredbe koje je moguće navesti kao deo složenih SQL naredbi su sve izvršive naredbe, osim narednih 14 naredbi:

1. CALL
2. CLOSE
3. CONNECT
4. Složena SQL naredba
5. DESCRIBE
6. DISCONNECT
7. EXECUTE IMMEDIATE
8. FETCH
9. OPEN
10. PREPARE
11. RELEASE (Connection)
12. ROLLBACK
13. SET CONNECTION
14. SET variable

Važe i neka dodatna pravila. Na primer, ukoliko se naredba COMMIT koristi kao jedna od podnaredbi, onda se ona mora naći kao poslednja podnaredba. Ukoliko je COMMIT nađe na ovoj poziciji, onda će ona biti izvršena, čak i u situaciji da klauza STOP AFTER FIRST indikuje da se neće sve podnaredbe u okviru složene SQL naredbe izvršiti. Na primer, pretpostavimo da je COMMIT poslednja podnaredba u okviru složene SQL naredbe koja ima 100 podnaredbi. Ukoliko se klauzom STOP AFTER FIRST specifikuje da se izvršava prvih 50 podnaredbi, onda će COMMIT podnaredba biti izvršena kao 51. podnaredba.

Neka dodatne napomene koje treba imati u vidu prilikom rada sa složenih SQL naredbama u DB2 sistemu su sledeće:

• Nije dozvoljeno ugnežđavati kod iz matičnog jezika unutar bloka koji definiše složena SQL naredba.

• Nije dozvoljeno ugnežđavati složene SQL naredbe u okviru drugih složenih SQL naredbi.

• Pripremljena COMMIT naredba nije dozvoljena u ATOMIC složenoj SQL naredbi.

• Jedna SQLCA struktura se postavlja za celu složenu SQL naredbu. Važe naredna pravila:

  • Vrednosti SQLCODE i SQLSTATE koje se postavljaju na kraju složene SQL naredbe su podrazumevano postavljene na osnovu poslednje podnaredbe koja se izvršila u okviru složene SQL naredbe, osim u slučaju opisanom narednom tačkom.

  • Ako je sistem signalizirao upozorenje da “nije pronađen podatak” (SQLSTATE 02000, odnosno, SQLCODE +100), onda se tom upozorenju daje prednost u odnosu na ostala upozorenja da bi se naredbom WHENEVER NOT FOUND moglo dejstvovati. U ovoj situaciji se polja iz SQLCA strukture koja se eventualno vraćaju aplikaciji postavljaju na osnovu podnaredbe koja je okinula upozorenje da “nije pronađen podatak”. Ukoliko u okviru složene SQL naredbe postoji više podnaredbi koje okidaju ovo upozorenje, onda se polja iz SQLCA strukture postavljaju na osnovu poslednje od tih podnaredbi.

Sada slede primeri korišćenja složenih SQL naredbi. Primetimo da smo, kao i u prethodnom zadatku, koristili naredbu ROLLBACK u definiciji funkcije checkSQL da poništimo izmene u bazi podataka u slučaju da dođe do greške, odnosno, naredbu COMMIT za potvrđivanje izmena pre raskidanja konekcije.

Rešenje:

Datoteka: vezbe/primeri/poglavlje_5/zadatak_5_2.sqc:

#include <stdio.h>
#include <stdlib.h>

EXEC SQL INCLUDE SQLCA;

void checkSQL(const char *str) {
    if(SQLCODE < 0) {
        fprintf(stderr, "Greska %d: %s\n", SQLCODE, str);

        EXEC SQL ROLLBACK;

        EXEC SQL CONNECT RESET;
        exit(EXIT_FAILURE);
    }
}

int main() {
    EXEC SQL CONNECT TO stud2020 USER student USING abcdef;
    checkSQL("Connect");
    
    EXEC SQL 
        BEGIN COMPOUND ATOMIC STATIC
            INSERT  INTO DA.ISPITNIROK
            VALUES  (YEAR(CURRENT_DATE), 'maj', CONCAT('Maj ', YEAR(CURRENT_DATE)), CURRENT_DATE, CURRENT_DATE + 20 days);
            
            UPDATE  DA.ISPITNIROK
            SET     DATKRAJA = DATKRAJA - 10 days
            WHERE   SKGODINA = YEAR(CURRENT_DATE) AND
                    OZNAKAROKA = 'maj';
        END COMPOUND;
    checkSQL("Compound");
    
    printf("Sve naredbe su uspesno izvrsene!\n");
    
    EXEC SQL COMMIT;
    checkSQL("Commit");
    
    EXEC SQL CONNECT RESET;
    checkSQL("Connect reset");
    
    return 0;
}

5.3 Tačke čuvanja u okviru transakcija

U prethodnim primerima smo videli da možemo da predstavimo transakcije kao složene SQL naredbe, a zatim da koristimo specifičnosti složenih SQL naredbi da bismo preciznije definisali ponašanje u zavisnosti od toga da li se pojavila greška tokom rada transakcije ili ne. Preciznije, videli smo kako je moguće poništiti efekat celog izvršavanja programa kao jedinice posla u slučaju pojave greške u nekoj od podnaredbi.

Vrlo često nam je neophodno da imamo precizniju kontrolu nad time šta se tačno poništava, na primer, ukoliko želimo da samo jedan deo transakcije bude poništen, umesto cele transakcije. Da bismo postigli takav efekat, potrebne su nam sofisticiranije metode upravljanja transakcijama. Jedan od takvih metoda podrazumeva korišćenje tačke čuvanja.

Tačka čuvanja (engl. savepoint) predstavlja mehanizam za poništavanje precizno definisanog skupa izvršenih naredbi.

Ukoliko se desi neka greška prilikom izvršavanja transakcije, tačka čuvanja se može koristiti da poništi dejstvo naredbi od trenutka kada je tačka čuvanja započeta do trenutka kada je poništenje akcija zahtevano.

Samim tim, tačka čuvanja omogućava konstruisanje grupe od nekoliko SQL naredbi, grupisanih u jedan izvršivi blok, koji se može izvršavati, kao deo jedne transakcije. Ukoliko se neka od podnaredbi izvrši sa greškom, taj definisani blok će biti poništen.

5.3.1 Kreiranje tačke čuvanja

Kreiranje tačke čuvanja u izvornom kodu se može izvršiti pozivanjem naredbe SAVEPOINT, čija je sintaksa data sa:

SAVEPOINT <NAZIV_TACKE_CUVANJA> [UNIQUE]
ON ROLLBACK RETAIN CURSORS
[ON ROLLBACK RETAIN LOCKS]

Ovom naredbom se kreira nova tačka čuvanja naziva <NAZIV_TACKE_CUVANJA>. Ukoliko specifikujemo opcionu klauzu UNIQUE, onda navodimo da aplikacija ne želi da iskoristi ovo ime tačke čuvanja dok je tačka čuvanja aktivna u okviru trenutnog nivoa čuvanja. DB2 sistem će prijaviti grešku ako pokušamo da kreiramo tačku čuvanja kao jedinstvenu ako već postoji tačka čuvanja sa istim imenom, kao i ako pokušamo da kreiramo tačku čuvanja sa imenom koje je prethodno bilo proglašeno za jedinstveno.

Obaveznom klauzom ON ROLLBACK RETAIN CURSORS se specifikuje ponašanje sistema tokom operacija poništavanja izmena do ove tačke čuvanja u odnosu na otvorene kursore nakon ove SAVEPOINT naredbe. Ovom klauzom se indikuje da, kada god je to moguće, kursori bivaju van uticaja operacije poništavanja do tačke čuvanja. Za više detalja o tome kako poništavanje izmena utiče na kursore, pogledati sekciju o poništavanju izmena.

Opcionom klauzom ON ROLLBACK RETAIN LOCKS se specifikuje ponašanje sistema tokom operacija poništavanja izmena do ove tačke čuvanja u odnosu na katance koje je aplikacija dobila nakon ove SAVEPOINT naredbe. Ukoliko je navedena, katanci koje je aplikacija dobila neće biti oslobođeni prilikom takve operacije poništavanja.

Neka dodatna pravila i napomene koje treba imati u vidu prilikom kreiranja tačaka čuvanja u DB2 sistemu su sledeće:

• Novi nivo čuvanja se kreira kada se naredni događaji okinu:

  • Nova jedinica posla je započela.

  • Pozvana je procedura koja je definisana klauzom NEW SAVEPOINT LEVEL.

  • Započeta je atomična složena SQL naredba.

• Nivo čuvanja se završava kada događaj koji je inicirao njegovo kreiranje je završen ili uklonjen. Kada se nivo čuvanja završi, sve tačke čuvanja koje se nalaze na tom nivou se oslobađaju. Svi otvoreni kursori, DDL akcije ili modifikacije podataka su nasleđeni od strane roditeljskog nivoa čuvanja (odnosno, nivoa čuvanja u okviru kojeg se trenutni nivo čuvanja završio) i pod uticajem su bilo koje naredbe koja se tiče nivoa čuvanja i koja važi u okviru roditeljskog nivoa čuvanja.

5.4 Potvrđivanje izmena

Kao što smo videli, jedna od dve osnovne operacije za upravljanje izmenama koje je napravila jedna transakcija jeste potvrđivanje izmena (engl. commit), koja se izvršava naredbom COMMIT. Sintaksa ove naredbe je data u nastavku:

COMMIT [WORK]

Iako naredba pohranjivanja izmena ima jednostavnu sintaksu, njeni efekti su mnogobrojni. Osnovna upotreba naredbe podrazumeva da se jedinica posla, u kojoj je COMMIT naredba izvršena, završava i nova jedinica posla se inicira. Sve izmene koje su izvršene nekom od narednih naredbi se potvrđuju u bazi podataka: ALTER, COMMENT, CREATE, DROP, GRANT, LOCK TABLE, REVOKE, SET INTEGRITY, SET Variable, kao i naredbe za izmenu podataka: INSERT, DELETE, MERGE, UPDATE, uključujući i one naredbe koje su ugnežđene u upitima.

Svi katanci koje je jedinica posla dobila nakon njenog iniciranja se oslobađaju, osim neophodnih katanaca za otvorene kursore koji su deklarisani klauzom WITH HOLD. Svi otvoreni kursori koji nisu deklarisani klauzom WITH HOLD se zatvaraju. Otvoreni kursori koji jesu deklarisani klauzom WITH HOLD ostaju otvoreni, i takvi kursori se pozicioniraju ispred narednog logičkog reda rezultujuće tabele (drugim rečima, naredba FETCH se mora izvršiti pre nego što se izvrši pozicionirana naredba UPDATE ili DELETE).

Sve tačke čuvanja postavljene u okviru transakcije se oslobađaju.

Neka dodatna pravila i napomene koje treba imati u vidu prilikom poništavanja izmena u DB2 sistemu su sledeće:

• Snažno se preporučuje da svaki aplikacioni proces eksplicitno završi svoju jedinicu posla pre nego li se završi. Ako se aplikacioni program završi bez COMMIT ili ROLLBACK naredbe, onda će SUBP sam pokušati da izvrši operaciju pohranjivanja ili poništavanja u zavisnosti od okruženja aplikacije. Iako su SUBP sistemi napredni, ipak se ne bi trebalo osloniti na njih da rade posao programera.

5.5 Poništavanje izmena

Ukoliko želimo da poništimo izmene koje je napravila jedna transakcija, možemo koristiti SQL naredbu ROLLBACK. Sintaksa ove naredbe je data u nastavku:

ROLLBACK [WORK]
[TO SAVEPOINT [<IME_TACKE_CUVANJA>]]

Efekat ove naredbe je da se prekida jedinica posla u kojoj je izvršena naredba ROLLBACK i nova jedinica posla se inicijalizuje. Sve promene koje su se ostvarile u bazi podataka tokom jedinice posla su poništene. U zavisnosti od odabranih vrednosti narednih opcija prilikom deklaracije naredbe ROLLBACK, ta naredba može imati različite varijante koje utiču na način izvršavanja:

• Navođenjem opcione klauze TO SAVEPOINT, poništavanje se izvršava parcijalno, odnosno, do poslednje tačke čuvanja. Ukoliko nijedna tačka čuvanja nije aktivna na trenutnom nivou čuvanja, podiže se greška (SQLSTATE 3B502). Nakon uspešnog poništavanja, navedena tačka čuvanja <IME_TACKE_CUVANJA> nastavlja da postoji, ali svaka ugnežđena tačka čuvanja se oslobađa i nadalje ne postoji. Ugnežđene tačke čuvanja, ako postoje, smatraju se za poništene i oslobođene kao deo poništavanja do navedene tačke čuvanja. Ukoliko <IME_TACKE_CUVANJA> nije navedeno, onda se poništavanje vrši do poslednje postavljene tačke čuvanja na tekućem nivou čuvanja.
  
  Ako se klauza TO SAVEPOINT ne postavi, onda naredba ROLLBACK poništava čitavu transakciju. Dodatno, sve tačke čuvanja u okviru te transakcije se oslobađaju. Ukoliko se navede <IME_TACKE_CUVANJA>, onda će se poništavanje izvršiti do te imenovane tačke čuvanja. Nakon uspešne operacije poništavanja, navedena imenovana tačka čuvanja nastavlja da postoji. Ukoliko ne postoji imenovana tačka čuvanja sa datim nazivom, podiže se greška (SQLSTATE 3B001).

Neka dodatna pravila i napomene koje treba imati u vidu prilikom poništavanja izmena u DB2 sistemu su sledeće:

• Svi katanci koji se čuvaju se oslobađaju prilikom izvršavanja naredbe ROLLBACK za tu jedinicu posla. Svi otvoreni kursori se zatvaraju.

• Izvršavanje naredbe ROLLBACK neće uticati na naredbu RELEASE.

• Ukoliko se izvršavanje program ne završi normalno, onda je jedinica posla implicitno poništena.

• Uticaj na kursore koji rezultuje iz naredbe ROLLBACK TO SAVEPOINT zavisi od naredbi u okviru tačke čuvanja:

  • Ako tačka čuvanja sadrži DDL za koji je kursor zavisan, kursor se označava za nevalidan. Pokušaji da se takav kursor koristi rezultuju podizanjem greške (SQLSTATE 57007).

  • Inače:

    • Ako je kursor referenciran u tački čuvanja, kursor ostaje otvoren i biva pozicioniran ispred narednog logičkog reda rezultujuće tabele. U tom slučaju je potrebno izvršiti naredbu FETCH pre nego što se izvrši pozicionirajuća naredba UPDATE ili DELETE.

    • Inače, kursor ne potpada pod uticaj naredbe ROLLBACK TO SAVEPOINT (ostaje otvoren i pozicioniran).

• Naredba ROLLBACK TO SAVEPOINT će obrisati sve privremeno kreirane ili deklarisane privremene tabele u okviru tačke čuvanja.

• Svi katanci su sadržani nakon ROLLBACK TO SAVEPOINT naredbe.

Naredni primeri ilustruju napredno implementiranje transakcija korišćenjem naredbi COMMIT i ROLLBACK.

Rešenje:

Datoteka: vezbe/primeri/poglavlje_5/zadatak_5_3.sqc:

#include <stdio.h>
#include <stdlib.h>

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
short hSchoolYear;
char hFinalsName[31],
     hFinalsLabel[21];
EXEC SQL END DECLARE SECTION;

void checkSQL(const char *str) {
    if(SQLCODE < 0) {
        fprintf(stderr, "Greska %d: %s\n", SQLCODE, str);

        EXEC SQL ROLLBACK;

        EXEC SQL CONNECT RESET;
        exit(EXIT_FAILURE);
    }
}

int main() {
    EXEC SQL CONNECT TO stud2020 USER student USING abcdef;
    checkSQL("Connect");

    // Primetimo da smo morali da navedemo klauzu WITH HOLD ovde
    // zato sto vrsimo potvrdjivanje izmena tokom obrade kursora.
    EXEC SQL 
        DECLARE cFinals CURSOR WITH HOLD FOR 
        SELECT  NAZIV,
                SKGODINA,
                OZNAKAROKA
        FROM    DA.ISPITNIROK IR
        WHERE   EXISTS (
                    SELECT  *
                    FROM    DA.ISPIT I
                    WHERE   I.SKGODINA = IR.SKGODINA AND
                            I.OZNAKAROKA = IR.OZNAKAROKA
                );
    checkSQL("Declare");

    EXEC SQL OPEN cFinals;
    checkSQL("Open");

    for(;;) {
        EXEC SQL 
            FETCH   cFinals 
            INTO    :hFinalsName,
                    :hSchoolYear,
                    :hFinalsLabel;
        checkSQL("Fetch");

        if(SQLCODE == 100) {
            break;
        }

        printf("Obradjujem ispitni rok %s u %hd. godini\n"
            "Da li zelite da obrisete polaganja u ovom ispitnom roku? [d/n] ", hFinalsName, hSchoolYear);
            
        char userResponse;
        scanf("%c", &userResponse);
        getchar(); // Za citanje novog reda

        if (userResponse == 'n') {
            printf("Preskacem obradu za ovaj ispitni rok\n\n");
            
            continue;
        }
        
        EXEC SQL
            DELETE  FROM DA.ISPIT
            WHERE   SKGODINA = :hSchoolYear AND
                    OZNAKAROKA = :hFinalsLabel;
        checkSQL("Delete");
        
        // Da nismo naveli klauzu WITH HOLD pri deklaraciji kursora, 
        // onda bi naredni poziv naredbe COMMIT zatvorio kursor,
        // pa bi poziv FETCH naredbe u narednoj iteraciji petlje prijavio gresku 
        // (jer dohvatamo podatak nad zatvorenim kursorom).
        EXEC SQL COMMIT;
        checkSQL("Commit");
        
        printf("Podaci su uspesno obrisani\n\n");
    }

    EXEC SQL CLOSE cFinals;    
    checkSQL("Close");

    EXEC SQL COMMIT;
    checkSQL("Potvrdjivanje izmena");
    
    EXEC SQL CONNECT RESET;
    checkSQL("Connect reset");

    return 0;
}
 

Za naredni program je potrebno kreirati tabelu OBRADJENIPREDMETI sa narednom strukturom:

DROP TABLE DA.OBRADJENIPREDMETI;

CREATE TABLE DA.OBRADJENIPREDMETI (
    IDPREDMETA INTEGER NOT NULL,
    PRIMARY KEY (IDPREDMETA),
    FOREIGN KEY (IDPREDMETA) REFERENCES DA.PREDMET
);

Rešenje:

Datoteka: vezbe/primeri/poglavlje_5/zadatak_5_4.sqc:

#include <stdio.h>
#include <stdlib.h>

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
sqlint32 hCourseId;
short hCourseESPB;
char hCourseName[201];
EXEC SQL END DECLARE SECTION;

void checkSQL(const char *str) {
    if(SQLCODE < 0) {
        fprintf(stderr, "Greska %d: %s\n", SQLCODE, str);

        EXEC SQL ROLLBACK;

        EXEC SQL CONNECT RESET;
        exit(EXIT_FAILURE);
    }
}

int main() {
    EXEC SQL CONNECT TO stud2020 USER student USING abcdef;
    checkSQL("Connect");

    EXEC SQL 
        DECLARE cUnprocessedCourses CURSOR WITH HOLD FOR 
        SELECT  ID,
                RTRIM(NAZIV),
                ESPB
        FROM    DA.PREDMET
        WHERE   ID NOT IN (
                    SELECT  IDPREDMETA
                    FROM    DA.OBRADJENIPREDMETI
                )
        FOR     UPDATE OF ESPB;
    checkSQL("Declare");

    EXEC SQL OPEN cUnprocessedCourses;
    checkSQL("Open");

    unsigned broj_obradjenih = 0;
    
    for(;;) {
        EXEC SQL 
            FETCH   cUnprocessedCourses 
            INTO    :hCourseId,
                    :hCourseName,
                    :hCourseESPB;
        checkSQL("Fetch");

        if(SQLCODE == 100) {
            break;
        }

        printf("\nPredmet %s ima broj bodova: %hd.\n"
            "Da li zelite da povecate broj bodova za 1? [d/n] ", hCourseName, hCourseESPB);
            
        char userResponse;
        scanf("%c", &userResponse);
        getchar(); // Za citanje novog reda

        if (userResponse == 'd') {
            EXEC SQL 
                UPDATE  DA.PREDMET
                SET     ESPB = ESPB + 1
                WHERE   CURRENT OF cUnprocessedCourses;
            checkSQL("Update");            
            
            printf("Podaci su uspesno azurirani\n");
        }
        
        // Ubelezavamo u BP da smo obradili tekuci predmet
        EXEC SQL
            INSERT  INTO DA.OBRADJENIPREDMETI
            VALUES  (:hCourseId);
        checkSQL("Insert into");
        
        // Uvecavamo broj obradjenih predmeta        
        ++broj_obradjenih;
        
        // Proveravamo da li je kraj jedne transakcije
        if (broj_obradjenih == 5) {
            EXEC SQL COMMIT;
            checkSQL("Commit");
            
            printf("\nTRANSAKCIJA JE IZVRSENA\n"
                "Da li zelite da nastavite obradu? [d/n] ");
            char userResponse;
            scanf("%c", &userResponse);
            
            getchar(); // Za citanje novog reda

            if (userResponse == 'n') {
                break;
            }

            broj_obradjenih = 0; 
        }
    }

    EXEC SQL CLOSE cUnprocessedCourses;
    checkSQL("Close");

    EXEC SQL COMMIT;
    checkSQL("Potvrdjivanje izmena");
    
    EXEC SQL CONNECT RESET;
    checkSQL("Connect reset");

    return 0;
}

Naredni zadatak ilustruje jednostavan rad sa tačkama čuvanja u okviru transakcija.

Za naredni program je potrebno izvršiti naredne SQL naredbe:

DELETE  FROM DA.ISPITNIROK
WHERE   SKGODINA = 2021;

DELETE  FROM DA.SKOLSKAGODINA
WHERE   SKGODINA = 2021;

INSERT  INTO DA.SKOLSKAGODINA
VALUES  (2021, '01/01/2021', '12/31/2021');

Rešenje:

Datoteka: vezbe/primeri/poglavlje_5/zadatak_5_5.sqc:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

EXEC SQL INCLUDE SQLCA;

// U ovom primeru ilustrujemo kako je moguce smestiti jedan red iz tabele u C strukturu.
// S obzirom da ova struktura predstavlja jedan ceo red u tabeli DA.ISPITNIROK,
// neophodno je da redosled polja u strukturi odgovara redosledu kolona u tabeli
// (iako imena polja i kolona ne moraju da budu identicna).
EXEC SQL BEGIN DECLARE SECTION;
struct final {
    sqlint32 schoolYear;
    char finalsLabel[21];
    char finalsName[31];
    char finalsStartDate[11];
    char finalsEndDate[11];
} hFinal;
EXEC SQL END DECLARE SECTION;

void checkSQL(const char *str) {
    if(SQLCODE < 0) {
        fprintf(stderr, "Greska %d: %s\n", SQLCODE, str);

        EXEC SQL ROLLBACK;

        EXEC SQL CONNECT RESET;
        exit(EXIT_FAILURE);
    }
}

// Funkcija za pripremanje niza struktura
// koje sadrze podatke o ispitnim rokovima.
void prepareFinalsArray(struct final finalsArr[], const int n);

int main() {
    EXEC SQL CONNECT TO stud2020 USER student USING abcdef;
    checkSQL("Connect");

    // Pripremamo niz ispitnih rokova.
    // Naredni niz ce cuvati generisane ispitne rokove za tekucu godinu.
    struct final finalsArr[12];
    const int n = sizeof(finalsArr)/sizeof(struct final);
    prepareFinalsArray(finalsArr, n);
    
    // S obzirom da se podaci o jednom ispitnom roku smestaju u strukturu struct final,
    // potrebno je da redosled kolona u projekciji odgovara redosledu polja u strukturi.
    EXEC SQL 
        DECLARE cFinals CURSOR WITH HOLD FOR
        SELECT  SKGODINA,
                OZNAKAROKA,
                NAZIV,
                DATPOCETKA,
                DATKRAJA
        FROM    DA.ISPITNIROK;
    checkSQL("Declare");
    
    printf("Unesite obavezan broj ispitnih rokova: ");
    short numOfFinals;
    scanf("%hd", &numOfFinals);
    getchar(); // novi red

    int i;
    for (i = 0; i < numOfFinals; ++i) {
        // Pripremamo maticnu promenljivu tako sto kopiramo vrednosti iz jedne-po-jedne strukture u nizu
        hFinal.schoolYear = finalsArr[i].schoolYear;
        strcpy(hFinal.finalsLabel, finalsArr[i].finalsLabel);
        strcpy(hFinal.finalsName, finalsArr[i].finalsName);
        strcpy(hFinal.finalsStartDate, finalsArr[i].finalsStartDate);
        strcpy(hFinal.finalsEndDate, finalsArr[i].finalsEndDate);
            
        // Maticna promenljiva tipa struct final se moze koristiti kao i bilo koja druga maticna promenljiva.
        EXEC SQL 
            INSERT  INTO DA.ISPITNIROK
            VALUES  (:hFinal);
        checkSQL("Unosenje obaveznih ispitnih rokova");
    }

    // Kreiranje tacke cuvanja
    EXEC SQL SAVEPOINT tacka_cuvanja ON ROLLBACK RETAIN CURSORS;
    checkSQL("Savepoint");
    
    for (; i < n; ++i) {
        hFinal.schoolYear = finalsArr[i].schoolYear;
        strcpy(hFinal.finalsLabel, finalsArr[i].finalsLabel);
        strcpy(hFinal.finalsName, finalsArr[i].finalsName);
        strcpy(hFinal.finalsStartDate, finalsArr[i].finalsStartDate);
        strcpy(hFinal.finalsEndDate, finalsArr[i].finalsEndDate);
        
        // Maticna promenljiva tipa struct final se moze koristiti i na drugi nacin:
        // navodjenjem polja te strukture direktno.
        // INSERT naredba ispod je ekvivalentna INSERT naredbi iznad.
        EXEC SQL 
            INSERT  INTO DA.ISPITNIROK
            VALUES  (:hFinal.schoolYear, :hFinal.finalsLabel, :hFinal.finalsName, :hFinal.finalsStartDate, :hFinal.finalsEndDate);
        checkSQL("Unosenje ostalih ispitnih rokova");
    }

    printf("----------------------------------------\n");
    
    EXEC SQL OPEN cFinals;
    checkSQL("Open");

    for(;;) {
        // Prva verzija koriscenja strukture, ovoga puta u FETCH naredbi.
        EXEC SQL 
            FETCH   cFinals 
            INTO    :hFinal;
        checkSQL("Fetch");

        if(SQLCODE == 100) {
            break;
        }

        printf("%5.5d  %7.7s  %18.18s  %10.10s  %10.10s\n", 
               hFinal.schoolYear, hFinal.finalsLabel, hFinal.finalsName, hFinal.finalsStartDate, hFinal.finalsEndDate);
    }

    EXEC SQL CLOSE cFinals;    
    checkSQL("Close");
    
    printf("----------------------------------------\n");

    printf("Da li zelite da ponistite unos neobaveznih ispitnih rokova? \n");

    char userResponse = getchar();
    if (userResponse == 'd' || userResponse == 'D') {
        // Ponistavanje izmena samo do prethodno kreirane tacke cuvanja
        EXEC SQL ROLLBACK TO SAVEPOINT tacka_cuvanja;
        checkSQL("Rollback");
    }
    else {
        EXEC SQL COMMIT;
        checkSQL("Commit");
    }

    printf("----------------------------------------\n");
    
    EXEC SQL OPEN cFinals;
    checkSQL("Open");

    for(;;) {
        // Druga verzija koriscenja strukture, ovoga puta u FETCH naredbi.
        EXEC SQL 
            FETCH   cFinals 
            INTO    :hFinal;
        checkSQL("Fetch");

        if(SQLCODE == 100) {
            break;
        }

        printf("%5.5d  %7.7s  %18.18s  %10.10s  %10.10s\n", 
               hFinal.schoolYear, hFinal.finalsLabel, hFinal.finalsName, hFinal.finalsStartDate, hFinal.finalsEndDate);
    }

    EXEC SQL CLOSE cFinals;    
    checkSQL("Close");
    
    printf("----------------------------------------\n");

    EXEC SQL COMMIT;
    checkSQL("Potvrdjivanje izmena");
    
    EXEC SQL CONNECT RESET;
    checkSQL("Connect reset");

    return 0;
}

void prepareFinalsArray(struct final finalsArr[], const int n) {
    const char *monthNames[] = {"Januar", "Februar", "Mart", "April", "Maj", "Jun", "Jul", "Avgust", "Septembar", "Oktobar", "Novembar", "Decembar"};
    int i;
    for (i = 0; i < n; ++i) {
        finalsArr[i].schoolYear = 2021;
        sprintf(finalsArr[i].finalsLabel, "%.3s 21", monthNames[i]);
        sprintf(finalsArr[i].finalsName, "%s 2021", monthNames[i]);
        sprintf(finalsArr[i].finalsStartDate, "%2.2d/01/2021", i+1);
        sprintf(finalsArr[i].finalsEndDate, "%2.2d/10/2021", i+1);
    }
}

Naredni zadaci ilustruju kompleksniju upotrebu tačaka čuvanja u okviru transakcija.

Za naredni program je potrebno izvršiti naredne SQL naredbe:

DROP    TABLE DA.STATISTIKAPOLAGANJA;

CREATE  TABLE DA.STATISTIKAPOLAGANJA (
    SKGODINA    SMALLINT NOT NULL,
    OZNAKAROKA  VARCHAR(20) NOT NULL,
    IDPREDMETA  INTEGER NOT NULL,
    USPESNOST   DOUBLE,
    PRIMARY KEY (SKGODINA, OZNAKAROKA, IDPREDMETA),
    FOREIGN KEY (SKGODINA, OZNAKAROKA) 
                REFERENCES DA.ISPITNIROK,
    FOREIGN KEY (IDPREDMETA)
                REFERENCES DA.PREDMET
);

Rešenje:

Datoteka: vezbe/primeri/poglavlje_5/zadatak_5_6.sqc:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
short hSchoolYear;
char hFinalsLabel[21];
sqlint32 hCourseId;
double hSuccessPercentage;
EXEC SQL END DECLARE SECTION;

void checkSQL(const char *str) {
    if(SQLCODE < 0) {
        fprintf(stderr, "Greska %d: %s\n", SQLCODE, str);

        EXEC SQL ROLLBACK;

        EXEC SQL CONNECT RESET;
        exit(EXIT_FAILURE);
    }
}

int main()  {
    EXEC SQL CONNECT TO stud2020 USER student USING abcdef;
    checkSQL("Connect");

    EXEC SQL 
        DECLARE cExamStatistics CURSOR WITH HOLD FOR
        SELECT      SKGODINA,
                    OZNAKAROKA,
                    IDPREDMETA,
                    SUM(
                        CASE 
                            WHEN OCENA > 5 AND STATUS='o' THEN 1.0 
                            ELSE 0.0 
                        END
                    ) / COUNT(*) * 100.0 AS USPESNOST
        FROM        DA.ISPIT I
        WHERE       NOT EXISTS (
                        SELECT  *
                        FROM    DA.STATISTIKAPOLAGANJA SP
                        WHERE   SP.SKGODINA = I.SKGODINA AND
                                SP.OZNAKAROKA = I.OZNAKAROKA AND
                                SP.IDPREDMETA = I.IDPREDMETA
                    )
        GROUP BY    SKGODINA,
                    OZNAKAROKA,
                    IDPREDMETA
        ORDER BY    IDPREDMETA ASC,
                    SKGODINA ASC,
                    OZNAKAROKA DESC;
    checkSQL("Declare cExamStatistics");
    
    EXEC SQL OPEN cExamStatistics;
    checkSQL("Open cExamStatistics");
    
    for (;;) {
        EXEC SQL 
            FETCH   cExamStatistics
            INTO    :hSchoolYear,
                    :hFinalsLabel,
                    :hCourseId,
                    :hSuccessPercentage;
        checkSQL("Fetch cExamStatistics");
        
        if (SQLCODE == 100) {
            break;
        }
        
        EXEC SQL
            INSERT  INTO DA.STATISTIKAPOLAGANJA
            VALUES  (:hSchoolYear, :hFinalsLabel, :hCourseId, NULL);
        checkSQL("Insert");
        
        EXEC SQL SAVEPOINT tacka_cuvanja ON ROLLBACK RETAIN CURSORS;
        checkSQL("Savepoint");
        
        EXEC SQL 
            UPDATE  DA.STATISTIKAPOLAGANJA
            SET     USPESNOST = :hSuccessPercentage
            WHERE   SKGODINA = :hSchoolYear AND
                    OZNAKAROKA = :hFinalsLabel AND
                    IDPREDMETA = :hCourseId;
        checkSQL("Update");
        
        printf("\nUneta je statistika: %5.2lf%% hSuccessPercentagei za predmet %d u roku %s %hd.\n"
            "Da li zelite da ponistite unos statistike? [d/n] ", hSuccessPercentage, hCourseId, hFinalsLabel, hSchoolYear);
        char userResponse = getchar();
        getchar(); // novi red
        
        if (userResponse == 'd' || userResponse == 'D') {
            EXEC SQL
                ROLLBACK TO SAVEPOINT tacka_cuvanja;
            checkSQL("Rollback to savepoint");
            
            printf("Statistika o polaganju nije sacuvana, ali je zabelezeno da je izracunata!\n");
        }
        
        EXEC SQL COMMIT;
        checkSQL("Commit");
    }
    
    EXEC SQL CLOSE cExamStatistics;
    checkSQL("Close cExamStatistics");
    
    EXEC SQL COMMIT;
    checkSQL("Commit");
    
    EXEC SQL CONNECT RESET;
    checkSQL("Connect reset");
    
    return 0;
}

Za naredni program je potrebno izvršiti naredne SQL naredbe:

DROP TABLE DA.OBRADJENAPOLAGANJA;

CREATE TABLE DA.OBRADJENAPOLAGANJA (
    INDEKS INTEGER NOT NULL,
    GODINA SMALLINT NOT NULL,
    PRIMARY KEY (INDEKS, GODINA),
    FOREIGN KEY (INDEKS)
        REFERENCES DA.DOSIJE
);

INSERT INTO DA.OBRADJENAPOLAGANJA
VALUES (20180050, 2018);

Rešenje: Da bismo lakše modulirali naše rešenje, implementirajmo naredne funkcije:

1. Funkcija void deklarisi_kursor_za_godine(sqlint32 indeks) vrši deklaraciju čitajućeg kursora sa nazivom c_godine koji pronalazi godine onih ispitnih rokova u kojem student, čiji je indeks jednak vrednosti parametra indeks (koja predstavlja matičnu promenljivu koja je globalno deklarisana), ima položene ispite.

2. Funkcija unsigned godine_polozenih_ispita() ispisuje sve godine iz kursora c_godine. Funkcija takođe i vraća broj pronađenih godina.

3. Funkcija void deklarisi_kursor_za_polaganja(sqlint32 indeks, short godina) vrši deklaraciju ažurirajućeg kursora sa nazivom c_polozeni kojim se može ažurirati vrednost kolone STATUS_PRIJAVE u tabeli ISPIT i koji pronalazi naziv predmeta i ocenu za sve položene ispite za studenta sa indeksom indeks u godini roka godina (oba parametra predstavljaju matične promenljive). Ovaj kursor mora biti deklarisan klauzom WITH HOLD zato što se koristi kao deo transakcije.

4. Funkcija void polaganja_za_studenta_u_godini(sqlint32 indeks, short godina) implementira neophodne operacije koje čine deo transakcije za jednog studenta. Funkcija redom:

• Unosi informacije (INSERT) o indeksu i godini roka u tabelu OBRADJENAPOLAGANJA.
• Kreira tačku čuvanja, kako bismo mogli da eventualno poništimo sve izmene, osim unosa u tabelu iz prethodne tačke.
• Prolazi kroz kursor c_polozeni. Za svaki red ispisuje informacije iz kursora i ukoliko korisnik potvrdi poništavanje ispita, izvršava odgovarajuće ažuriranje (UPDATE).
• Postavlja pitanje korisniku da li želi da potvrdi izmene. Ukoliko je odgovor negativan, funkcija poništava sve izmene, ali ne i izmenu koja je učinjena u prvoj tački (tj. naredbu INSERT iznad), pošto bez obzira na poništavanje ažuriranja, želimo da informacija o obrađivanju bude trajno upisana.

Funkcija int main() je sada poprilično jednostavna. Nakon povezivanja na bazu podataka i deklarisanja kursora (pozivom funkcija pod 1 i 3 iznad), započinje se iterativni proces koji predstavlja jednu transakciju. Svaka transakcija se sastoji od opisanih operacija iz teksta zadatka, tako što se pozivaju odgovarajuće funkcije (pod 2 i 4 iznad). Naravno, da bi svaka iteracija predstavljala jednu transakciju, potrebno je da se na kraju iteracije izvrši naredba COMMIT.

Datoteka: vezbe/primeri/poglavlje_5/zadatak_5_7.sqc:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
sqlint32 hIndex;
short hSchoolYear;
char hCourseName[201];
short hGrade;
EXEC SQL END DECLARE SECTION;

void checkSQL(const char *str) {
    if(SQLCODE < 0) {
        fprintf(stderr, "Greska %d: %s\n", SQLCODE, str);

        EXEC SQL ROLLBACK;

        EXEC SQL CONNECT RESET;
        exit(EXIT_FAILURE);
    }
}

void declareSchoolYearsCursor(sqlint32 hIndex);
void declareExamsCursor(sqlint32 hIndex, short hSchoolYear);
unsigned iterOverSchoolYearsCursor();
void iterOverExamsCursor(sqlint32 hIndex, short hSchoolYear);

char userResponse[3];

int main() {
    EXEC SQL CONNECT TO stud2020 USER student USING abcdef;
    checkSQL("Connect");

    declareSchoolYearsCursor(hIndex);
    declareExamsCursor(hIndex, hSchoolYear);
    
    for(;;) {
        printf("\n----------POCETAK TRANSAKCIJE----------\n\n");
        
        printf("Unesite indeks studenta: ");
        scanf("%d", &hIndex);
        
        unsigned numOfSchoolYears = iterOverSchoolYearsCursor();
        
        if (numOfSchoolYears == 0u) {
            printf(
                "Za datog studenta ne postoji obradjena godina "
                "u kojoj su polagali neke ispite\n");
            continue;
        }
        
        printf("Unesite jednu od ponudjenih godina polaganja: ");
        scanf("%hd", &hSchoolYear);
        
        iterOverExamsCursor(hIndex, hSchoolYear);
        
        EXEC SQL COMMIT;
        checkSQL("Commit transakcije");
        
        printf("\n----------KRAJ TRANSAKCIJE----------\n");
        
        printf("\nDa li zelite da nastavite dalje? [da/ne] ");
        scanf("%s", userResponse);
        
        if (strcmp(userResponse, "da") != 0) {
            break;
        }
    }
    
    EXEC SQL COMMIT;
    checkSQL("Commit");
    
    EXEC SQL CONNECT RESET;
    checkSQL("Connect reset");
    
    return 0;
}

void declareSchoolYearsCursor(sqlint32 hIndex) {
    EXEC SQL 
        DECLARE cSchoolYears CURSOR FOR
        SELECT  DISTINCT 
                SKGODINA
        FROM    DA.ISPIT I
        WHERE   INDEKS = :hIndex AND
                OCENA > 5 AND
                STATUS = 'o' AND
                NOT EXISTS (
                    SELECT  *
                    FROM    DA.OBRADJENAPOLAGANJA
                    WHERE   INDEKS = :hIndex AND
                            GODINA = I.SKGODINA
                )
        FOR     READ ONLY;
    checkSQL("Declare cSchoolYears");
}

void declareExamsCursor(sqlint32 hIndex, short hSchoolYear) {
    EXEC SQL 
        DECLARE cExams CURSOR WITH HOLD FOR
        SELECT  (
                    SELECT  TRIM(P.NAZIV)
                    FROM    DA.PREDMET P
                    WHERE   I.IDPREDMETA = P.ID
                ) AS NAZIV_PREDMETA,
                I.OCENA
        FROM    DA.ISPIT I
        WHERE   I.INDEKS = :hIndex AND
                I.SKGODINA = :hSchoolYear AND
                OCENA > 5 AND
                STATUS = 'o'
        FOR     UPDATE OF STATUS;
    checkSQL("Declare cExams");
}

unsigned iterOverSchoolYearsCursor() {
    unsigned numOfSchoolYears = 0u;
    
    EXEC SQL OPEN cSchoolYears;
    checkSQL("Open cSchoolYears");
    
    for(;;) {
        EXEC SQL 
            FETCH   cSchoolYears
            INTO    :hSchoolYear;
        checkSQL("Fetch cSchoolYears");
        
        if (SQLCODE == 100) {
            break;
        }
        
        printf("\t%hd\n", hSchoolYear);
        ++numOfSchoolYears;
    }
    
    EXEC SQL CLOSE cSchoolYears;
    checkSQL("Close cSchoolYears");
    
    return numOfSchoolYears;
}

void iterOverExamsCursor(sqlint32 hIndex, short hSchoolYear) {
    EXEC SQL 
        INSERT  INTO DA.OBRADJENAPOLAGANJA
        VALUES  (:hIndex, :hSchoolYear);
    checkSQL("Insert into");
    
    EXEC SQL
        SAVEPOINT s_obradjeni ON ROLLBACK RETAIN CURSORS;
    checkSQL("Savepoint");
    
    EXEC SQL OPEN cExams;
    checkSQL("Open cExams");
    
    for (;;) {
        EXEC SQL 
            FETCH   cExams
            INTO    :hCourseName,
                    :hGrade;
        checkSQL("Fetch cExams");
        
        if (SQLCODE == 100) {
            break;
        }
        
        printf("%s, %d\n", hCourseName, hGrade);
        printf("Da li zelite da ponistite ispit? [da/ne] ");
        scanf("%s", userResponse);
        
        if (strcmp(userResponse, "da") != 0) {
            continue;
        }
        
        EXEC SQL
            UPDATE  DA.ISPIT
            SET     STATUS = 'x'
            WHERE   CURRENT OF cExams;
        checkSQL("Update");
    }
    
    EXEC SQL CLOSE cExams;
    checkSQL("Close cExams");
    
    printf("Da li zelite da potvrdite sve izmene za tekuceg studenta? [da/ne] ");
    scanf("%s", userResponse);
    
    if (strcmp(userResponse, "da") != 0) {
        EXEC SQL ROLLBACK TO SAVEPOINT s_obradjeni;
        checkSQL("Rollback to savepoint");
    }
}

5.6 Zadaci za vežbu

CREATE TABLE DA.PRAKSA2020 (
    INDEKS INTEGER NOT NULL,
    IDPREDMETA INTEGER NOT NULL,
    PRIMARY KEY (INDEKS, IDPREDMETA),
    FOREIGN KEY (INDEKS)
        REFERENCES DA.DOSIJE,
    FOREIGN KEY (IDPREDMETA)
        REFERENCES DA.PREDMET(ID)
)