Database Application Development

1. Database Application Development Chapter 6

2. Overview • SQL in application code • Embedded SQL • Cursors • Dynamic SQL • JDBC

3. SQL in Application Code • SQL commands can be called from within a host language (e.g., C++ or Java) program. • SQL statements can refer to host variables (including special variables used to return status). • Must include statement to connect to right database. • Two main integration approaches: • Embed SQL in the host language (e.g., Pro*C, Embedded SQL, SQLJ) • Create special API (Call Level Interface) to call SQL commands (e.g., JDBC, ODBC, PHP …)

4. SQL in Application Code (Contd.) Impedance mismatch issues: • Type mismatch • Data type casting (declare variables) • Set-oriented • SQL relations are (multi-) sets of records, with no a priori bound on the number of records. • Usually no such data structure exists in procedural programming languages such as C++. • SQL supports a mechanism called a cursor to handle this.

5. Embedded SQL

6. Embedded SQL • Approach: Embed SQL in host language. • Given host language with embedded SQL • A preprocessor converts SQL statements into special function calls. • Then regular compiler used to compile the host language+function class into executable. • Final executable works for one DBMS only (not portable)

7. Embedded SQL: Main Constructs • Connect to DB EXEC SQL CONNECT • Declare variables that can be used by both SQL and host language EXEC SQL BEGIN DECLARE SECTION … EXEC SQL END DECLARE SECTION • Executing SQL statements EXEC SQL …

8. Embedding SQL in C: Oracle #include <stdio.h> #include <stdlib.h> #include <string.h> #include <sqlca.h> EXEC SQL BEGIN DECLARE SECTION; VARCHAR userid[20]; VARCHAR passwd[20]; int value; EXEC SQL END DECLARE SECTION; void sql_error (char *msg) { printf (“%s”, msg); exit (1); }

9. Embedding SQL in C: Oracle int main () { strcpy (userid.arr, “me”); userid.len = strlen (userid.arr); strcpy (passwd.arr, “no-me”); passwd.len = strlen (passwd.arr); EXEC SQL WHENEVER SQLERROR DO sql_error (“Oracle Error\n”); EXEC SQL CONNECT :userid IDENTIFIED BY :passwd; EXEC SQL CREATE TABLE Test (a int); EXEC SQL INSERT INTO Test VALUES (1); EXEC SQL SELECT MAX (a) INTO :value from Test; printf (“Max value=%d\n”,value); }

10. Cursors • Can declare a cursor on a relation or query statement (which generates a relation). • Can open a cursor, repeatedly fetch a tuple, move the cursor, until all tuples have been retrieved. • Control order: ORDER BY, in queries that are accessed through a cursor • Can also modify/delete tuple pointed to by cursor. • Must close cursor at end.

11. Cursor that gets names of sailors who’ve reserved a red boat, in alphabetical order EXEC SQL DECLARE sinfo CURSOR FOR SELECT S.sname FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ ORDER BY S.sname

12. Cursors EXEC SQL DECLARE myCursor CURSOR FOR SELECT bid from Reservations; EXEC SQL OPEN myCursor; EXEC SQL WHENEVER NOT FOUND DO break; while (1) { EXEC SQL FETCH myCursor INTO :num; … } EXEC SQL CLOSE myCursor;

13. Embedding SQL in C: An Example char SQLSTATE[6]; EXEC SQL BEGIN DECLARE SECTION char c_sname[20]; short c_minrating; float c_age; EXEC SQL END DECLARE SECTION c_minrating = random(); EXEC SQL DECLARE sinfo CURSOR FOR SELECT S.sname, S.age FROM Sailors S WHERE S.rating > :c_minrating ORDER BY S.sname; do { EXEC SQL FETCH sinfo INTO :c_sname, :c_age; printf(“%s is %d years old\n”, c_sname, c_age); } while (SQLSTATE != ‘02000’); -- empty cursor EXEC SQL CLOSE sinfo;

14. Dynamic SQL • SQL queries are not always known at compile time • Example: spreadsheet, graphical DBMS frontend, web access. • Allow construction of SQL statements (query strings) on-the-fly

15. Dynamic SQL Example char c_sqlstring[] ={“DELETE FROM Sailors WHERE rating>5”}; -- parse, compile and bind to variable: EXEC SQL PREPARE readytogo FROM :c_sqlstring; EXEC SQL EXECUTE readytogo;

16. Embedding vs Database APIs • Embedding: Modify compiler (see discussion so far) • API : Provide library with standard database call interface

17. Database APIs

18. Database APIs • Special standardized interface to libraries of functions provided explicitly for SQL statements: • No preprocessor, instead host language compiler compiles code. • Pass SQL strings from PL language • Presents result sets in language-friendly way • Examples : JDBC: Java API MS ODBC – Open DB Connection • Supposedly DBMS-neutral • “driver” traps calls & translates them into DBMS-specific code • Database can be across a network • Same executable works on different DBMSs without recompiling • Independent both at source code and at executable level

19. JDBC Architecture: 4 Components • Application • initiates and terminates connections, submits SQL statements • Driver manager • load JDBC driver at run-time • Driver • Registers with manager • Connects to data source, transmits requests and returns/translates results and error codes into DBMS specific calls • Data source • processes SQL statements

20. JDBC Classes and Interfaces Steps to submit a database query: • Load JDBC driver • Connect to data source • Execute SQL statements

21. JDBC Driver Management • All drivers managed by DriverManager class • Options for Loading JDBC driver: • In Java code (dynamic loading of class in java):Class.forName(“oracle/jdbc.driver.Oracledriver”); • When starting Java application:-Djdbc.drivers=oracle/jdbc.driver

22. Connections in JDBC We interact with data source through sessions. Each connection identifies a logical session. • JDBC URL:jdbc:<subprotocol>:<otherParameters> Example: String url=“jdbc:oracle:www.bookstore.com:3083”; Connection con; try{ con = DriverManager.getConnection(url,usedId,password); } catch SQLException except { …}

23. Executing SQL Statements String sql=“INSERT INTO Sailors VALUES(?,?,?,?)”; PreparedStatment pstmt=con.prepareStatement(sql); pstmt.clearParameters(); pstmt.setInt(1,sid); pstmt.setString(2,sname); pstmt.setInt(3, rating); pstmt.setFloat(4,age); // since no rows are returned, use executeUpdate() int numRows = pstmt.executeUpdate(); Where numRows is # of rows modified.

24. ResultSets • PreparedStatement.executeUpdate only returns number of affected records • PreparedStatement.executeQueryreturns data, encapsulated in a ResultSet object (a cursor) ResultSet rs=pstmt.executeQuery(sql); // rs is now a cursor While (rs.next()) { // process the data}

25. ResultSets A ResultSet is a powerful cursor: • previous(): moves one row back • absolute(int num): moves to the row with the specified number • relative (int num): moves forward or backward • first() and last()

26. SQL Type Java class ResultSet get method BIT Boolean getBoolean() CHAR String getString() VARCHAR String getString() DOUBLE Double getDouble() FLOAT Double getDouble() INTEGER Integer getInt() REAL Double getFloat() DATE java.sql.Date getDate() TIME java.sql.Time getTime() TIMESTAMP java.sql.TimeStamp getTimestamp() Matching Java and SQL Data Types

27. An Example Connection con = // connect DriverManager.getConnection ( url, ”login", ”pass“ ); Statement stmt = con.createStatement(); // set up stmt String query = "SELECT name, rating FROM Sailors"; ResultSet rs = stmt.executeQuery(query); try { // handle exceptions // loop through result tuples while (rs.next()) { String s = rs.getString(“name"); Int n = rs.getFloat(“rating"); System.out.println(s + " " + n); } } catch (SQLException ex) { System.out.println(ex.getMessage () + ex.getSQLState () + ex.getErrorCode ()); }

28. Summary of Whirlwind Tour • Embedded SQL allows execution of parameterized static queries within host language • Dynamic SQL allows execution of completely ad-hoc queries within a host language • Cursor mechanism allows retrieval of one record at a time and bridges impedance mismatch between HL and SQL • APIs such as JDBC introduce a layer of abstraction between application and DBMS