1SQL: ProgrammingCPS 116Introduction to Database Systems2Announcements (Tue. Sep. 23) Homework #2 due next Thursday Please start early—you can do all of it now! Homework #1 sample solution available Only in hardcopies; see me if you did not get one in class Project milestone #1 due in 3½ 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 ilprogramming languages• E.g.: SQL/PSM Use SQL together with general-purpose programming languages• E.g.: JDBC, embedded SQL Extend general-purpose programming languages with SQL-like constructs• E.g.: LINQ (Language Integrated Query for .NET), HQL (Hibernate Query Language)24Impedance 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;END37SQL/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 controlGOTOGOTO Exceptions SIGNAL, RESIGNAL… For more DB2-specific information, search for “SQL routines” in DB2 v9.5 Information Center Link available from course website (under Programming Notes: DB2 SQL Notes)9Interfacing SQL with another language API approach SQL commands are sent to the DBMS at runtime Examples: JDBC, ODBC (C/C++/VB), Python DB API 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)410Example 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) {…}}…} Not very nice since it ties your code to a particular DBMS Best if you load it from a properties file Or, for web apps, use a JNDI DataSource (see course website: Programming Notes: Tomcat Notes)11Connections// Connection URL is a DBMS-specific string:String url =”jdbc:db2://cps116.cod.cs.duke.edu: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 here Again, in practice you should avoid hard-coding DBMS-specific things (see previous slide)12Statements// 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();513Query 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, or insert a row into the database Possible only when there is a clear 1-1 correspondence between the change and a row in the underlying table Analogous to the view update problem• Covered in the lecture on SQL views 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
View Full Document
Unlocking...