Why Is This Important? So far, accessed DBMS directly through client - PDF document

Why Is This Important?  So far, accessed DBMS “directly” through client tools  Great for interactive use Database Application Development  How can we access the DBMS from a program?  Need an interface between programming language and DBMS Chapter 6  Many different options  Our focus: JDBC 1 2 Overview SQL in Application Code  SQL in application code  SQL commands can be called from within a host language (e.g., C++ or Java) program.  Embedded SQL  SQL statements can refer to host variables (including  Cursors special variables used to return status).  JDBC  Must include a statement to connect to the right database.  Stored procedures  Two main integration approaches:  Embed SQL in the host language (Embedded SQL, SQLJ)  Create special API to call SQL commands (JDBC) 3 4 SQL in Application Code (Contd.) Embedded SQL  Impedance mismatch:  Approach: Embed SQL in the host language.  SQL relations are (multi-) sets of records, with no a priori  A preprocessor converts SQL statements into special API bound on the number of records. No such data structure calls. existed traditionally in procedural programming languages  Then a regular compiler is used to compile the code. such as C.  Language constructs:  SQL supports a mechanism called a cursor to handle this.  Connecting to a database: • Cursor essentially is a more powerful iterator EXEC SQL CONNECT  Declaring variables: EXEC SQL BEGIN (END) DECLARE SECTION  Statements: EXEC SQL Statement; 5 6

Embedded SQL in C: Variables Cursors EXEC SQL BEGIN DECLARE SECTION  Can declare a cursor on a relation or query char c_sname[20]; statement (which generates a relation). long c_sid;  Can open a cursor and repeatedly fetch a tuple, then short c_rating; move the cursor until all tuples have been retrieved.  Can use a special clause, called ORDER BY, in queries that float c_age; are accessed through a cursor, to control the order in EXEC SQL END DECLARE SECTION which tuples are returned. • Fields in ORDER BY clause must also appear in SELECT clause.  Two special “error” variables:  SQLCODE (long, is negative if an error has occurred)  Can also modify/delete tuple pointed to by a cursor.  SQLSTATE (char[6], predefined codes for common errors) 7 8 Cursor: Get names of sailors who reserved a red boat, in alphabetical Embedding SQL in C: An Example order char SQLSTATE[6]; EXEC SQL BEGIN DECLARE SECTION EXEC SQL DECLARE sinfo CURSOR FOR SELECT S.sname char c_sname[20]; short c_minrating; float c_age; EXEC SQL END DECLARE SECTION FROM Sailors S, Boats B, Reserves R c_minrating = random(); WHERE S.sid=R.sid AND R.bid=B.bid AND B.color =‘red’ EXEC SQL DECLARE sinfo CURSOR FOR ORDER BY S.sname SELECT S.sname, S.age FROM Sailors S WHERE S.rating > :c_minrating  Cannot replace S.sname by, say, S.sid in the ORDER ORDER BY S.sname; do { BY clause above (Why?) EXEC SQL FETCH sinfo INTO :c_sname, :c_age;  Can we add S.sid to the SELECT clause and replace printf (“%s is %d years old \ n”, c_sname, c_age); S.sname by S.sid in the ORDER BY clause? } while (SQLSTATE != ‘02000’); EXEC SQL CLOSE sinfo; 9 10 Database APIs: Alternative to JDBC Architecture Components embedding  Rather than modify compiler, add library with database  Application calls (API)  Initiates and terminates connections, submits SQL  Advantage: executable is also DBMS-independent statements  Embedded is SQL DBMS-independent only at source-code level  Driver manager  Pass SQL strings from language, present result sets in a  Loads JDBC driver, passes JDBC calls from app to correct language-friendly way driver  Sun’s JDBC: Java API  Driver  Supposedly DBMS-neutral  Connects to data source, transmits requests and  A driver traps the calls and translates them into DBMS-specific returns/translates results and error codes code  Data source (DBMS)  Driver loaded dynamically and on-demand  Processes SQL statements  Database can be across a network 11 12

JDBC Architecture (Pure Java) JDBC Architecture (Not Pure Java)  Left side: type 4 driver  Left side: type 1 driver  Allows direct call from client  JDBC access via ODBC to DBMS, pure Java drivers  Converts JDBC calls into  Each client using the network protocol used by bridge must have ODBC DBMS binary code  Right side: type 3 driver  Right side: type 2 driver  Translates JDBC calls into  Converts JDBC calls into middleware protocol calls on the DBMS client  Middleware translates this to API DBMS protocol  Needs binary code on  Useful when connecting to client machine many different DBMSes Source: java.sun.com Source: java.sun.com 13 14 JDBC Classes and Interfaces Connecting to A DBMS private Connection getDBConnection() {  Steps to submit a database query: Connection con = null; try {  Load the JDBC driver // Load the driver Class.forName(myDbDriver).newInstance();  Connect to the data source } catch (InstantiationException e) { e.printStackTrace();  Execute SQL statements } catch (IllegalAccessException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } try {  Important: make sure you include the driver in the String connectionURL = “ myURL ”; classpath con = DriverManager.getConnection(connectionURL);  Driver jar file sqljdbc4.jar needs to be in the classpath } catch (SQLException e) { e.printStackTrace(); }  Should be there by default on Windows lab machines return con; } 15 16 Connection Data Connections in JDBC  MSFT JDBC driver for SQL Server  Notice: We interact with a data source through  dbDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver “; sessions.  connectionURL = “ jdbc:sqlserver ://address:1433;” + “ databaseName=XYZ;user=YOU;password =SECRET;”;  Each connection identifies a logical session.  In the JDBC API 4.0, the DriverManager.getConnection  JDBC URL: jdbc:<subprotocol>:<otherParameters> method is enhanced to load JDBC drivers automatically.  Multiple users: each has his/her own session(s)  Do not need to call the Class.forName method to register or load the driver when using the sqljdbc4.jar class library.  When the getConnection method of the DriverManager class is called, an appropriate driver is located from the set of registered JDBC drivers.  sqljdbc4.jar file includes "META-INF/services/java.sql.Driver" file, which contains the com.microsoft.sqlserver.jdbc.SQLServerDriver as a registered driver. 17 18

Important Imports For JDBC Running A Simple SQL Query public List getSpeciesNames() { Connection con = getDBConnection();  import java.sql.Connection; List species = new ArrayList();  import java.sql.DriverManager; try { Statement S = con.createStatement();  import java.sql.ResultSet; // Get query results ResultSet rs = S.executeQuery(  import java.sql.SQLException; "SELECT DISTINCT " + speciesColName + " FROM " + scoresTableName); // Copy results into list  import java.sql.Statement; while (rs.next()) { String speciesName = rs.getString(speciesColName); species.add(speciesName); } rs.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } return species; } 19 20 Connection Interface Connection Interface (Contd.)  Can set auto-commit mode  Better performance possible for read-only access  Auto-commit on: each statement considered its own  boolean isReadOnly (), transaction, no need for explicit commit() void setReadOnly (boolean readOnly)  boolean getAutoCommit (), void setAutoCommit (boolean autoCommit)  Check whether connection is still open  Can set transaction isolation level  boolean isClosed (),  Connection.TRANSACTION_READ_UNCOMMITTED, void close () Connection.TRANSACTION_READ_COMMITTED,  Commit or abort transaction Connection.TRANSACTION_REPEATABLE_READ, or Connection.TRANSACTION_SERIALIZABLE  Use only when autoCommit is false  int getTransactionIsolation (),  void commit (), void setTransactionIsolation (int level) void rollback ()  Isolation, auto-commit covered later, for now use default 21 22 Statement Interface SQL Stored Procedures  Used to execute SQL statement and return its results  What is a stored procedure?  execute(String sql) to execute any SQL statement  Program executed through a single SQL statement  executeQuery(String sql) to obtain single ResultSet object  Executed in the process space of the server  executeUpdate(String sql) for INSERT, UPDATE, or DELETE  Sub-interface PreparedStatement  Advantages:  Precompiled SQL statement for efficiently executing a  Can encapsulate application logic while staying “close” to statement multiple times. the data  Structure fixed, parameters determined at runtime • PreparedStatement pstmt = connection.prepareStatement("UPDATE  Reuse of application logic by different users EMPLOYEES SET SALARY = ? WHERE ID = ?");  Avoid tuple-at-a-time return of records through cursors • pstmt.setBigDecimal(1, 153833.00); pstmt.setInt(2, 110592);  Sub-interface CallableStatement • Only final result is returned to Java app • For calling SQL stored procedures through standard way for all RDBMSes 23 24