Database Application Development

Chpt 6 Xin Zhang Database Management Systems Raghu Ramakrishnan 1

Embedded SQL

  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 a statement to

connect

database.

to the right SQL relations are set-oriented. – SQL supports a mechanism called

cursor

this.

to handle Raghu Ramakrishnan Database Management Systems 2

Cursors

   Can declare a cursor on a relation or query statement (which generates a relation).

Can

move open

a cursor, and repeatedly

fetch

a tuple then the cursor, until all tuples have been retrieved.

– Can use a special clause, called which tuples are returned.

ORDER BY , in queries that are accessed through a cursor, to control the order in  Fields in ORDER BY clause must also appear in SELECT clause.

– The ORDER BY clause, which orders answer tuples, is

only

allowed in the context of a cursor.

Can also modify/delete tuple pointed to by a cursor.

Database Management Systems Raghu Ramakrishnan 3

Cursor Declaration Find names, and ages of those sailors who’s rating is above 7, in alphabetical order

EXEC SQL DECLARE sinfo CURSOR FOR SELECT S.sname

FROM WHERE Sailors S rating >7 ORDER BY S.sname

Raghu Ramakrishnan Database Management Systems 4

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’); EXEC SQL CLOSE sinfo;

Database Management Systems Raghu Ramakrishnan 5

Database APIs: Alternative to embedding

   DBMS-independent ODBC & JDBC Database can be across a network Database Management Systems Raghu Ramakrishnan 6

JDBC steps

 Create the database in your dbms  Register the database as a data source  Import java.sql.* at the beginning of your java file.

 Connect to a JDBC source Connection con = DriverManager.getConnection(“jdbc:oracle:thin:@coit ora01:1521:class”,”user”,”passwd”)  Create an SQL statement Statement stmt = con.createStatement(); stmt.executeUpdate("INSERT INTO sailor VALUES(22,'dustin',7,45.0)"); Database Management Systems Raghu Ramakrishnan 7

JDBC steps cont.

   Execute the statement – ResultSet rs = stmt.executeQuery(“Select * from …”) Parse the result – rs.next(), rs.getFloat

– ResultSetMetaData contains the information about column Close the statement and connection – stmt.close() – con.close

Database Management Systems Raghu Ramakrishnan 8

Useful resources

   JDBC tutorial http://java.sun.com/docs/books/tutorial/jd bc/index.html

UNCC COIT http://coit-servlet01.uncc.edu:8080/support/?

Example http://webpages/~xinzhang/Teaching/Sampl eJDBC.java

Raghu Ramakrishnan Database Management Systems 9

SQL API in Java (JDBC)

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 ()); } Database Management Systems Raghu Ramakrishnan 10