[PDF] - SQL statemen ts b ecome pro cedure calls. 1 Shared V PDF Document

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 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 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 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 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 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 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 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 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 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 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 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 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