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.