Outline Embedded SQL. CS 235: Call-Level Interface (CLI). Java - PDF document

Outline • Embedded SQL. CS 235: • Call-Level Interface (CLI). • Java Database Connectivity (JDBC). Introduction to Databases Svetlozar Nestorov Lecture Notes #18 Embedded SQL Shared Variables • Standard for combining SQL with a host • The interface between SQL and the host language. language is through shared variables. EXEC SQL BEGIN DECLARE SECTION; • SQL statements are converted to procedure calls in the host language by a declarations of shared variables in host language syntax preprocessor. EXEC SQL END DECLARE SECTION; • Begin SQL statements with EXEC SQL . Use of Shared Variables Example • Look up the price that a given bar charges • In SQL, shared variables are preceded by for a given beer. a colon. EXEC SQL BEGIN DECLARE SECTION; – Can be used as constants in SQL statements. char aBeer[21], aBar[21]; – Can get values from SQL statements and float aPrice; EXEC SQL END DECLARE SECTION; pass values to host language. /* read in the beer and the bar */ • In the host language, shared variables are EXEC SQL SELECT price INTO :aPrice used as any other variables. FROM Sells WHERE beer = :aBeer AND bar = :aBar; /* print the price */ 1

Embedded Queries Cursors • Modification queries. • Declare a cursor. – Return no results; can be used anywhere. EXEC SQL DECLARE c CURSOR FOR <query>; • Single-row select queries. • Open a cursor. – Return a single tuple; can be read into shared variables. EXEC SQL OPEN c; • Multiple-row select queries. • Fetch a tuple. – Return many tuples; can be used with EXEC SQL FETCH c INTO <vars>; cursors. Example (1/2) Example (2/2) EXEC SQL OPEN CURSOR spoonBeers; • Find the prices of all beers sold in Spoon. while(1) { EXEC SQL BEGIN DECLARE SECTION; EXEC SQL FETCH spoonBeers char aBeer[21]; INTO :aBeer, :aPrice; float aPrice; if (NO_MORE_TUPLES) break; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE spoonBeers CURSOR FOR /* print out the beer and the price */ SELECT beer, price } FROM Sells EXEC SQL CLOSE CURSOR spoonBeers; WHERE bar = ‘Spoon’; Modifying Base Relations Dynamic SQL • A cursor can range over a base relation. • So far, fixed queries with possibly some parameters. EXEC SQL DECLARE c CURSOR FOR Sells; • What if we want run ad-hoc queries? • Modifications can be made only to the current tuple. • Dynamic SQL EXEC SQL DELETE FROM Sells – Prepare statement (not known at compile WHERE CURRENT OF c; time.) • Any condition can be applied in the host – Execute statement. language. 2

Dynamic SQL Syntax Example • Read a query and run it. • Prepare a query. EXEC SQL BEGIN DECLARE SECTION; EXEC SQL PREPARE <query-name> char query[255]; FROM <query>; EXEC SQL END DECLARE SECTION; • Execute a query. while (1) EXEC SQL EXECUTE <query-name>; /* read query */ EXEC SQL PREPARE q FROM :query; EXEC SQL EXECUTE q; Execute-Immediate SQL/CLI • If the query is to be executed only once • Call-Level Interface: call library functions the prepare and execute statements can and procedures within a host language. be combined. • Data types: EXEC SQL EXECUTE IMMEDIATE <query>; – Environments: DBMS installation. – Connections: logins to DBMS. – Statements: SQL statements. – Descriptions: query results or parameters. Data Type Instances Example • Create environment, connection, and SQLHENV myEnv; statement handles with SQLHDBC myCon; SQLAllocHandle(T,I,O) SQLAllocHandle(SQL_HANDLE_ENV, – T is the type, e.g.SQL_HANDLE_ENV. SQL_NULL_HANDLE, &myEnv); – I is the input handle (higher-level handle): SQLAllocHandle(SQL_HANDLE_DBC, • statement < connection < environment myEnv, &myCon); – O is the output handle. 3

Processing Statements Example SQLPrepare(myStmt, “SELECT bar, beer • Prepare and execute. FROM Sells WHERE price < 3.00”, SQLPrepare(<statement-handle>, SQL_NTS) <statement>, SQLExecute(myStmt) <length of statement>) or SQLExecDirect(myStmt, “SELECT bar, beer FROM Sells WHERE price < 3.00”, SQLExecute(<statement-handle>) SQL_NTS) Fetching Tuples Binding Variables • Before fetching we need to indicate where • Every statement has an implied cursor the tuple attributes should be stored. associated with it. SQLBindCol(<stmt-handle>, • SQLFetch(<stmt-handle>) returns the next <attribute-pos>, tuple from the result of the executed <attribute-type>, statement. <var-ptr>, <var-size>, <var-info-ptr>); Example Parameterized Queries SQLExecDirect(myStmt, “SELECT bar, beer • Bind variables to query parameters, so FROM Sells WHERE price < 3.00”, SQL_NTS); you can execute the same statement SQLBindCol(myStmt, 1, SQL_CHAR, &aBar, several times with different parameters. size(aBar), &aBarInfo); SQLPrepare(myStmt, “INSERT(bar, beer) SQLBindCol(myStmt, 2, SQL_CHAR, &aBeer, size(aBeer), &aBeerInfo); VALUES(?,?)”, SQL_NTS); while (SQLFetch(myStmt) != SQL_NO_DATA) SQLBindParameter(myStmt, 1,…,aBar,…); { SQLBindParameter(myStmt, 2,…,aBeer,…); /* Cheers! */ SQLExecute(myStmt); } 4

JDBC JDBC Connection • Java Database Connectivity (JDBC) • Connect with DriverManager by specifying the DBMS URL, username, and password. – Similar to SQL/CLI and ODBC but adapted to object-oriented Java. Connection myCon = • JDBC drivers are similar to environments DriverManager.getConnection( in CLI. <DB URL>, <username>, <psswd>); – Platform, implementation, and installation dependent. • DriverManager object. Statements Executing Statements • Two types of statements: • JDBC distinguishes between queries and modifications. – Statement can accept any string that is an SQL statement and execute it. • Both Statement and PreparedStatement – PreparedStatement has a fix SQL statement. have two methods: Statement s1 = myCon.createStatement(); – executeQuery PreparedStatement s2 = – executeUpdate myCon.createStatement(<SQL-stmt>); • For Statement the methods take a parameter. Example Accessing Results PreparedStatement s2 = • ResultSet class objects are similar to myCon.createStatement(“SELECT cursors. bar,beer FROM Sells WHERE price < • Method next() gets the next tuple. 3.0”); – Must be called once to get the first tuple. ResultSet cheapBeers = s2.executeQuery(); – Returns FALSE when tuples are exausted. Statement s1 = myCon.createStatement(); cheepBeers.next() s1.executeUpdate(“INSERT INTO Sells Values(‘Spoon’, ‘Bud’, 3.0)”); 5

Accessing Attributes Example • Call an appropriate method, depending on while (cheepBeers.next()) { the type of attribute, on the ResultSet aBar = cheepBeers.getString(1); object. aBeers = cheepBeers.getString(2); – Position of the attribute is a aprameter /* print out a map to the bar */ • getInt(i), getString(i), getFloat(i). } Parameterized Queries • PreparedStatements can be parameterized – Use ? to denote a parameter. • Use methods setString, setInt, setFloat. • Then run executeQuery or update. 6