SQL statemen ts b ecome pro cedure calls. 1 Shared V - - PDF document

sql statemen ts b ecome pro cedure calls 1 shared v
SMART_READER_LITE
LIVE PREVIEW

SQL statemen ts b ecome pro cedure calls. 1 Shared V - - PDF document

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


slide-1
SLIDE 1 Em b edded SQL Add to a con v en tional programming language (C in
  • ur
examples) certain statemen ts that represen t SQL
  • p
erations.
  • Eac
h em b edded SQL statemen t in tro duced with EXEC SQL.
  • Prepro
cessor con v erts C + SQL to pure C.

SQL statemen ts b ecome pro cedure calls. 1
slide-2
SLIDE 2 Shared V ariables A sp ecial place for C declarations
  • f
v ariables that are accessible to b
  • th
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
  • ptional.
  • In
C, v ariables used normally; in SQL, they m ust b e preceded b y a colon. 2
slide-3
SLIDE 3 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
slide-4
SLIDE 4 Cursors Similar to PL/SQL cursors, with some syn tactic dierences. 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
slide-5
SLIDE 5 Oracle Vs. SQL F eatures
  • SQL
exp ects FROM in fetc h-statemen t.
  • SQL
denes an arra y
  • f
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 sqlca.h that declares a c
  • mmunic
ation ar e a and denes macros to access it.

In particular, NOT FOUND is a macro that sa ys \the no-tuple-found signal w as set." 5
slide-6
SLIDE 6 Dynamic SQL Motiv atio n:
  • Em
b edded SQL is ne for xed applicati
  • ns,
e.g., a program that is used b y a sales clerk to b
  • k
an airline seat.
  • It
fails if y
  • u
try to write a program lik e sqlplus, b ecause y
  • u
ha v e compiled the co de for sqlplus b efore y
  • u
see the SQL statemen ts t yp ed in resp
  • nse
to the SQL> prompt.
  • Tw
  • sp
ecial statemen ts
  • f
em b edded SQL:

PREPARE turns a c haracter string in to an SQL query .

EXECUTE executes that query . 6
slide-7
SLIDE 7 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" =
  • ptimize
the query , e.g., nd a w a y to execute it using few disk-page I/O's.
  • Alternativ
el y , PREPARE and EXECUTE can b e com bined in to: EXEC SQL EXECUTE IMMEDIATE :query; 7
slide-8
SLIDE 8 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
  • ther
language) program creates SQL statemen ts as c haracter strings and passes them to functions that are part
  • f
a library .
  • Similar
to what really happ ens in em b edded SQL implemen tations.
  • Tw
  • ma
jor approac hes: SQL/CLI (standard
  • f
ODBC =
  • p
en datab ase c
  • nne
ctivity) and JDBC (Ja v a database connectivit y). 8
slide-9
SLIDE 9 CLI
  • In
C, library calls let y
  • u
create a statement hand le = struct in whic h y
  • u
can place an SQL statemen t.

See text.
  • Use
SQLPrepare(myHandle, <statement>,...) to mak e myHandle represen t the SQL statemen t in the second argumen t.
  • Use
SQLExecute(myHandle) to execute that statemen t. Example SQLPrepare(handle1 , "SELECT beer, price FROM Sells WHERE bar = 'Joe''s Bar'",...); SQLExecute(handle1 ); 9
slide-10
SLIDE 10 F etc hing Data T
  • btain
the data returned b y an executed query , w e: 1. Bind v ariables to the comp
  • nen
t n um b ers
  • f
the returned query .

SQLBindCol applies to a handle, column n um b er, and v ariable, plus
  • ther
argumen ts (see text). 2. F etc h, using the handle
  • f
the query's statemen t.

SQLFetch applies to a handle. Example SQLBindCol(handle 1, 1, SQL CHAR, &theBar,...) SQLBindCol(handle 1, 2, SQL REAL, &thePrice,...) SQLExecute(handle 1); ... while(SQLFetch(ha ndle1 ) != SQL NO DATA) f ... 10
slide-11
SLIDE 11 JDBC
  • Start
with a Conne ction
  • b
ject,
  • btained
from the DBMS (see text).
  • Metho
d cr e ateStatement() returns an
  • b
ject
  • f
class Statement (if there is no argumen t)
  • r
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'" );
  • myCon
is a connection, stat1 is an \empt y" statemen t
  • b
ject, and stat2 is a (prepared) statemen t
  • b
ject that has an SQL statemen t asso ciated. 11
slide-12
SLIDE 12 Executing Statemen ts
  • JDBC
distinguishes queries (statemen ts that return data) from up dates (statemen ts that
  • nly
aect the database).
  • Metho
ds exe cuteQuery() and exe cuteUp date () are used to execute these t w
  • kinds
  • f
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
  • b
ject
  • f
class R esultSet. Example stat1.executeUpdat e( "INSERT INTO Sells " + "VALUES('Brass Rail', 'Bud', 3.00)" ); ResultSet Menu = stat2.executeQuery (); 12
slide-13
SLIDE 13 Getting the T uples
  • f
a R esultSet
  • Metho
d Next() applies to a R esultSet and mo v es a \cursor" to the next tuple in that set.

Apply Next()
  • nce
to get to the rst tuple.

Next() returns FALSE if there are no more tuples.
  • While
a giv en tuple is the curren t
  • f
the cursor, y
  • u
can get its ith comp
  • nen
t b y applying to a R esultSet a metho d
  • f
the form getX (i), where X is the name for the t yp e
  • f
that comp
  • nen
t. Example while(Menu.Next() ) f theBeer = Menu.getString(1 ); thePrice = Menu.getFloat(2); ... g 13