Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
This course is about database management systems. Main topics covered in this course are: manipulate data, use standard query language, creating a database and logical query language. Embedded SQL, Procedure Calls, Shared Variables, Cursors, Oracle Vs. SQL Features, Communication Area, SQL State, Dynamic SQL, Sqlplus Sketch, Call-level Interfaces, SQL/CLI, JDBC, Fetching Data, Executing Statements
Typology: Slides
1 / 13
This page cannot be seen from the preview
Don't miss anything!
Emb edded SQL
Add to a conventional programming language (C in our examples) certain statements that represent SQL op erations.
Each emb edded SQL statement intro duced with EXEC SQL.
Prepro cessor converts C + SQL to pure C.
F SQL statements b ecome pro cedure calls.
Shared Variables
A sp ecial place for C declarations of variables that are accessible to b oth SQL and C.
Bracketed by
EXEC SQL BEGIN/END DECLARE SECTION;
In Oracle Pro/C (not C++) the \brackets" are optional.
In C, variables used normally; in SQL, they must b e preceded by a colon.
Cursors
Similar to PL/SQL cursors, with some syntactic di erences.
Example
Print Jo e's menu.
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;
Oracle Vs. SQL Features
SQL exp ects FROM in fetch-statement.
SQL de nes an array of characters SQLSTATE that is set every time the system is called. F Errors are signaled there.
F A failure for a cursor to nd any more tuples is signaled there.
F However, Oracle provides us with a header le sqlca.h that declares a communication area and de nes macros to access it. F In particular, NOT FOUND is a macro that says \the no-tuple-found signal was set."
Example: Sqlplus Sketch
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 many times.
F \Prepare" = optimize the query, e.g., nd a way to execute it using few disk-page I/O's.
Alternativel y, PREPARE and EXECUTE can b e combined into: EXEC SQL EXECUTE IMMEDIATE :query;
Call-Level Interfaces
A more mo dern approach to the host- language/SQL connection is a cal l-level interface, in which the C (or other language) program creates SQL statements as character strings and passes them to functions that are part of a library.
Similar to what really happ ens in emb edded SQL implementations.
Two ma jor approaches: SQL/CLI (standard of ODBC = open database connectivity) and JDBC (Java database connectivity).
Fetching Data
To obtain the data returned by an executed query, we:
F 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 ...
JDBC
Start with a Connection ob ject, obtained from the DBMS (see text).
Metho d createStatement() returns an ob ject of class Statement (if there is no argument) or PreparedStatement if there is an SQL statement as argument.
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 \empty" statement ob ject, and stat2 is a (prepared) statement ob ject that has an SQL statement asso ciated.
Getting the Tuples of a ResultSet
Metho d Next() applies to a ResultSet and moves a \cursor" to the next tuple in that set. F Apply Next() once to get to the rst tuple. F Next() returns FALSE if there are no more tuples.
While a given tuple is the current of the cursor, you can get its ith comp onent by applying to a ResultSet a metho d of the form getX (i), where X is the name for the typ e of that comp onent.
Example
while(Menu.Next() ) f theBeer = Menu.getString(1 ); thePrice = Menu.getFloat(2); ... g
