1SQL: ProgrammingCPS 116Introduction to Database Systems2Announcements (September 25) Homework #2 due this Thursday Submit to Yi—not through Jun’s office door Solution available this weekend No class this ThursdayMidtermin class next Thursday (October 4)Midtermin class next Thursday (October 4) Open book, open notes Format similar to the sample midterm• Solution available this weekend Covers everything up to next Tuesday’s lecture Emphasizes materials exercised in homeworks Project milestone #1 due in 2½ weeks3Motivation Pros and cons of SQL Very high-level, possible to optimize Not intended for general-purpose computation Solutions Augment SQL with constructs from general-purpose programming languages (SQL/PSM) Use SQL together with general-purpose programming languages (JDBC, embedded SQL, etc.)4Impedance mismatch and a solution SQL operates on a set of records at a time Typical low-level general-purpose programming languages operates on one record at a time) Solution: cursorO(lbl)iih bfhfiOpen(a result table): position the cursor before the first row Get next: move the cursor to the next row and return that row; raise a flag if there is no such row Close: clean up and release DBMS resources)Found in virtually every database language/API• With slightly different syntaxes)Some support more positioning and movement options, modification at the current position(analogous to view update), etc.5Augmenting SQL: SQL/PSM PSM = Persistent Stored Modules CREATE PROCEDURE proc_name ( parameter_declarations )local_declarationsprocedure_body;CREATE FUNCTIONfunc name(parameter declarations)CREATE FUNCTION func_name( parameter_declarations)RETURNS return_typelocal_declarationsprocedure_body; CALL proc_name ( parameters ); Inside procedure body:SET variable = CALL func_name ( parameters );6SQL/PSM exampleCREATE FUNCTION SetMaxGPA(IN newMaxGPA FLOAT)RETURNS INT-- Enforce newMaxGPA; return number of rows modified.BEGINDECLARE rowsUpdated INT DEFAULT 0;DECLARE thisGPA FLOAT;-- A cursor to range over all students:DECLARE t d tC CURSOR FORDECLARE studentCursor CURSOR FORSELECT GPA FROM StudentFOR UPDATE;-- Set a flag whenever there is a “not found” exception:DECLARE noMoreRows INT DEFAULT 0;DECLARE CONTINUE HANDLER FOR NOT FOUNDSET noMoreRows = 1;… (see next slide) …RETURN rowsUpdated;END27SQL/PSM example continued-- Fetch the first result row:OPEN studentCursor;FETCH FROM studentCursor INTO thisGPA;-- Loop over all result rows:WHILE noMoreRows <> 1 DOIF thisGPA > newMaxGPA THEN--EnforcenewMaxGPA:Enforce newMaxGPA:UPDATE Student SET Student.GPA = newMaxGPAWHERE CURRENT OF studentCursor;-- Update count:SET rowsUpdated = rowsUpdated + 1;END IF;-- Fetch the next result row:FETCH FROM studentCursor INTO thisGPA;END WHILE;CLOSE studentCursor;8Other SQL/PSM features Assignment using scalar query results SELECT INTO Other loop constructs FOR, REPEAT UNTIL, LOOP Flow control GOTO Exceptions SIGNAL, RESIGNAL… For more DB2-specific information, check out Developing SQL and External Routines Available as part of DB2 v9 manual collection, or directly as ftp://ftp.software.ibm.com/ps/products/db2/info/vr9/pdf/letter/en_US/db2a3e90.pdf9Interfacing SQL with another language API approach SQL commands are sent to the DBMS at runtime Examples: JDBC, ODBC (for C/C++/VB), Perl DBI These API’s are all based on the SQL/CLI (Call-Level Interface) standard Embedded SQL approach SQL commands are embedded in application code A precompiler checks these commands at compile-time and converts them into DBMS-specific API calls Examples: embedded SQL for C/C++, SQLJ (for Java)10Example API: JDBC JDBC (Java DataBase Connectivity) is an API that allows a Java program to access databases// Use the JDBC package:import java.sql.*;…public class … {p{…static {// Load the JDBC driver:try {Class.forName(”com.ibm.db2.jcc.DB2Driver”);} catch (ClassNotFoundException e) {…}}…}11Connections// Connection URL is a DBMS-specific string:String url =”jdbc:db2://localhost:50000/dbcourse”;// Making a connection:Connection con =DriverManager getConnection(url user password);DriverManager.getConnection(url, user, password);…// Closing a connection:con.close();For clarity we are ignoring exception handling for now12Statements// Create an object for sending SQL statements:Statement stmt = con.createStatement();// Execute a query and get its results:ResultSet rs =stmt.executeQuery(”SELECT SID, name FROM Student”);// Work on the results:…// Execute a modification (returns the number of rows affected):int rowsUpdated =stmt.executeUpdate(”UPDATE Student SET name = ’Barney’ WHERE SID = 142”);// Close the statement:stmt.close();313Query results// Execute a query and get its results:ResultSet rs =stmt.executeQuery(”SELECT SID, name FROM Student”);// Loop through all result rows:while (rs.next()) {//Get col mn al es:// Get column values:int sid = rs.getInt(1);String name = rs.getString(2);// Work on sid and name:…}// Close the ResultSet:rs.close();14Other ResultSet features Move the cursor (pointing to the current row) backwards and forwards, or position it anywhere within the ResultSet Update/delete the database row corresponding to the current result row Analogous to the view update problem Insert a row into the database Analogous to the view update problem Obtain metadata: rs.getMetaData() returns a ResultSetMetaData object describing the output table schema (number, order, names, types of columns, etc.)15Prepared statements: motivationStatement stmt = con.createStatement();for (int age=0; age<100; age+=10) {ResultSet rs = stmt.executeQuery(”SELECT AVG(GPA) FROM Student” +” WHERE age >= ” + age + ” AND age < ” + (age+10));// Work on the results:…}} Every time an SQL string is sent to the DBMS, the DBMS must perform parsing, semantic analysis, optimization, compilation, and then finally execution These costs are incurred 10 times in the above example A typical application issues many queries with a small number of patterns (with different parameter values)16Prepared statements: syntax// Prepare the statement, using ? as placeholders for actual parameters:PreparedStatement stmt = con.prepareStatement(”SELECT AVG(GPA) FROM Student WHERE age >= ? AND age < ?”);for (int age=0; age<100; age+=10) {// Set actual
View Full Document
Unlocking...