Moznost pristupu k databazi oracle z ruznych prostredi Pro*C SQL z jazyka C, priklady v ${ORACLE_HOME}/precomp/demo/proc/ Uvod do Pro*C je napriklad zde. Dokumentace Oracle je zde. Princip: Vlozeni SQL prikazu pomoci: EXEC SQL prikaz; Napr: EXEC SQL INSERT INTO TEST VALUES (3,'test',2.5); Integrace s lokalni promennou C: prefixovat nazev promenne dvojteckou (analogie k triggerum) Napr: int prom; EXEC SQL FETCH kursor1 INTO :prom; Rozdil v retezcich: C ma nulou ukoncene retezce -- DB ma delku retezce Napr: char jmeno[21]; // Typ v DB je VARCHAR2(20) nebo CHAR(20) EXEC SQL FETCH kursor1 INTO :jmeno; Takove reseni nerespektuje realnou delku retezce z DB, ale doplni jej vzdy mezerami. Reseni: char jmeno[21]; // Typ v DB je VARCHAR2(20) EXEC SQL VAR jmeno IS STRING(21); // Toto zajisti, ze retezec je vzdy ukoncen nulou a neni doplnen mezerami. Podpora kurzoru EXEC SQL DECLARE muj_kurzor CURSOR FOR SELECT ...; EXEC SQL OPEN ...; EXEC SQL FETCH ...; EXEC SQL CLOSE ...; Obsluha chyb Deklarativnim zpusobem pomoci: EXEC SQL WHENEVER <chyba> <akce>; <chyba> can be any of the following: SQLWARNING - sqlwarn[0] is set because Oracle returned a warning SQLERROR - sqlcode is negative because Oracle returned an error NOT FOUND - sqlcode is positive because Oracle could not find a row that meets your WHERE condition, or a SELECT INTO or FETCH returned no rows <akce> can be any of the following: CONTINUE - Program will try to continue to run with the next statement if possible DO - Program transfers control to an error handling function GOTO <label> - Program branches to a labeled statement STOP - Program exits with an exit() call, and uncommitted work is rolled back Napr: EXEC SQL WHENEVER SQLWARNING DO print_warning_msg(); NULL hodnoty Jazyk C nema NULL hodnotu pro kazdy typ (pouze pro ukazatele, coz je stejne nula). Reseni v Pro*C: Pouziti indikatorovych promennych Napr: int id; short id_null; EXEC SQL FETCH ... INTO :id:id_null; EXEC SQL INSERT INTO r VALUES(:id INDICATOR :id_null, ...); Hodnoty indikatoru: -1 The column value is NULL, so the value of the host variable is indeterminate. 0 Oracle assigned an intact column value to the host variable. >0 Oracle assigned a truncated column value to the host variable. The integer returned by the indicator variable is the original length of the column value. -2 Oracle assigned a truncated column variable to the host variable, but the original column value could not be determined. Hodnoty indikatoru v INSERT/UPDATE: -1 Oracle will assign a NULL to the column, ignoring the value of the host variable. >=0 Oracle will assign the value of the host variable to the column. -- Ukol c.1: Napiste program, ktery vyhleda knihy s nazvem obsahujicim zadany retezec -- Pro nalezene zaznamy vypise jejich nazev, isbn a id. -- Tip: Pouzijte nasledujici kostru: zdrojak Kompilace zdrojaku do binarky pro spusteni -- Vygenerovani platneho C zdrojaku: $ proc soubor.pc -- kontrola na chyby - vizte soubor.lis -- Preklad C do binarky $ gcc -m32 -o soubor soubor.c -L$ORACLE_HOME/lib -I$ORACLE_HOME/precomp/public -lclntsh -- Spusteni binarky $ ./soubor Perl Perl ma k dispozici univerzalni rozhrani pro praci s databazemi: DBI -- Ukol c.2: Stejne zadani, ale v Perlu -- Tip: Pouzijte nasledujici kostru: zdrojak Dalsi priklady, ktery realizuji vkladani do tabulky, jsou zde. Tam najdete i blizsi informace ke kompilaci Pro*C.