Em b edded SQL Add to a con v en tional programming language (C in our examples) certain statemen ts that represen t SQL op erations. � Eac h em b edded SQL statemen t in tro duced with SQL . EXEC � Prepro cessor con v erts C + SQL to pure C. ✦ SQL statemen ts b ecome pro cedure calls. 1
Shared V ariables A sp ecial place for C declarations of v ariables that are accessible to b oth SQL and C. � Brac k eted b y EXEC SQL BEGIN/END DECLARE SECTION; � In Oracle Pro/C (not C++) the \brac k ets" are optional. � In C, v ariables used normally; in SQL, they m ust b e preceded b y a colon. 2
Example Find the price for a giv en b eer at a giv en bar. Sells(bar , beer , price) EXEC SQL BEGIN DECLARE SECTION; char theBar[21], theBeer[21]; float thePrice; EXEC SQL END DECLARE SECTION; . . . /* assign to theBar and theBeer */ . . . EXEC SQL SELECT price INTO :thePrice FROM Sells WHERE beer = :theBeer AND bar = :theBar; . . . 3
Cursors Similar to PL/SQL cursors, with some syn tactic di�erences. Example Prin t Jo e's men u. Sells(bar , beer , price) EXEC SQL BEGIN DECLARE SECTION; char theBeer[21]; float thePrice; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE c CURSOR FOR SELECT beer, price FROM Sells WHERE bar = 'Joe''s Bar'; EXEC SQL OPEN CURSOR c; while(1) { EXEC SQL FETCH c INTO :theBeer, :thePrice; if(NOT FOUND) break; /* format and print beer and price */ } EXEC SQL CLOSE CURSOR c; 4
Oracle Vs. SQL F eatures � SQL exp ects in fetc h-statemen t. FROM � SQL de�nes an arra y of c haracters SQLSTATE that is set ev ery time the system is called. ✦ Errors are signaled there. ✦ A failure for a cursor to �nd an y more tuples is signaled there. ✦ Ho w ev er, Oracle pro vides us with a header �le that declares a sqlca.h c ommunic ation ar e a and de�nes macros to access it. ✦ In particular, is a macro that NOT FOUND sa ys \the no-tuple-found signal w as set." 5
Dynamic SQL Motiv atio n: � Em b edded SQL is �ne for �xed applicati ons, e.g., a program that is used b y a sales clerk to b o ok an airline seat. � It fails if y ou try to write a program lik e sqlplus , b ecause y ou ha v e compiled the co de for b efore y ou see the SQL sqlplus statemen ts t yp ed in resp onse to the SQL> prompt. � Tw o sp ecial statemen ts of em b edded SQL: ✦ turns a c haracter string in to an PREPARE SQL query . ✦ executes that query . EXECUTE 6
Example: Sqlplus Sk etc h EXEC SQL BEGIN DECLARE SECTION; char query[MAX QUERY LENGTH]; EXEC SQL END DECLARE SECTION; /* issue SQL> prompt */ /* read user's text into array query */ EXEC SQL PREPARE q FROM :query; EXEC SQL EXECUTE q; /* go back to reissue prompt */ � Once prepared, a query can b e executed man y times. ✦ \Prepare" = optimize the query , e.g., �nd a w a y to execute it using few disk-page I/O's. � Alternativ el y , and can b e PREPARE EXECUTE com bined in to: EXEC SQL EXECUTE IMMEDIATE :query; 7
Call-Lev el In terfaces A more mo dern approac h to the host- language/SQL connection is a c al l-level interfac e , in whic h the C (or other language) program creates SQL statemen ts as c haracter strings and passes them to functions that are part of a library . � Similar to what really happ ens in em b edded SQL implemen tations. � Tw o ma jor approac hes: SQL/CLI (standard of ODBC = op en datab ase c onne ctivity ) and JDBC (Ja v a database connectivit y). 8
CLI � In C, library calls let y ou create a statement hand le = struct in whic h y ou can place an SQL statemen t. ✦ See text. � Use SQLPrepare(myHandle, to mak e <statement>,...) myHandle represen t the SQL statemen t in the second argumen t. � Use to execute that SQLExecute(myHandle) statemen t. Example SQLPrepare(handle1 , "SELECT beer, price FROM Sells WHERE bar = 'Joe''s Bar'",...); SQLExecute(handle1 ); 9
F etc hing Data T o obtain the data returned b y an executed query , w e: 1. Bind v ariables to the comp onen t n um b ers of the returned query . ✦ applies to a handle, column SQLBindCol n um b er, and v ariable, plus other argumen ts (see text). 2. F etc h, using the handle of the query's statemen t. ✦ applies to a handle. SQLFetch Example SQLBindCol(handle 1, 1, SQL CHAR, &theBar,...) SQLBindCol(handle 1, 2, SQL REAL, &thePrice,...) SQLExecute(handle 1); ... while(SQLFetch(ha ndle1 ) != f SQL NO DATA) ... 10
JDBC � Start with a Conne ction ob ject, obtained from the DBMS (see text). � Metho d cr e ateStatement() returns an ob ject of class Statement (if there is no argumen t) or Pr ep ar e dStatement if there is an SQL statemen t as argumen t. Example Statement stat1 = myCon.createState ment( ); PreparedStatement stat2 = myCon.createState ment( "SELECT beer, price " + "FROM Sells " + "WHERE bar = 'Joe''s Bar'" ); � is a connection, is an \empt y" myCon stat1 statemen t ob ject, and is a (prepared) stat2 statemen t ob ject that has an SQL statemen t asso ciated. 11
Executing Statemen ts � JDBC distinguishes queries (statemen ts that return data) from up dates (statemen ts that only a�ect the database). � Metho ds exe cuteQuery() and exe cuteUp date () are used to execute these t w o kinds of SQL statemen ts. ✦ They m ust ha v e an argumen t if applied to a Statement , nev er if applied to a Pr ep ar e dStatement . � When a query is executed, it returns an ob ject of class R esultSet . Example stat1.executeUpdat e( "INSERT INTO Sells " + "VALUES('Brass Rail', 'Bud', 3.00)" ); ResultSet Menu = stat2.executeQuery (); 12
R esultSet Getting the T uples of a � Metho d Next() applies to a R esultSet and mo v es a \cursor" to the next tuple in that set. ✦ Apply Next() once to get to the �rst tuple. ✦ Next() returns if there are no more FALSE tuples. � While a giv en tuple is the curren t of the cursor, y ou can get its i th comp onen t b y applying to a R esultSet a metho d of the form (i) , where is the name for the t yp e of get X X that comp onen t. Example f while(Menu.Next() ) theBeer = Menu.getString(1 ); thePrice = Menu.getFloat(2); ... g 13
Recommend
More recommend
