DOC PREVIEW
Duke CPS 116 - SQL: Programming

This preview shows page 1 out of 4 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 4 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 4 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

1SQL: ProgrammingCPS 116Introduction to Database Systems2Announcements (September 29) Homework #2 due today Sample solution available next Tuesday Homework #1 graded Please verify your score on Blackboard See me or Ming if you have further questions Sample midterm (from last year) available Solution available next Tuesday Midterm in class next Thursday Format similar to the sample midterm Covers everything up to next Tuesday’s lecture Emphasizes on materials exercised in homeworks3Motivation 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: cursor Open (a table or a result table): position the cursor just 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 cursor positioning and movement options, modification at the current cursor position (analogous to the view update problem), etc.5Augmenting SQL: SQL/PSM PSM = Persistent Stored Modules CREATE PROCEDURE proc_name ( parameter_declarations )local_declarationsprocedure_body; 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 studentCursor CUSOR 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-- 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, RESIGNAL9Interfacing 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 … {…static {// Load the JDBC driver:Class.forName(”COM.ibm.db2.jdbc.net.DB2Driver”);…}…}11Connections…// Connection URL is a DBMS-specific string:String url =”jdbc:db2://rack40.cs.duke.edu/dbcourse”;// Making a connection:Connection con =DriverManager.getConnection(url);…// Closing a connection:con.close();…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();…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 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 problem15Prepared statements: motivation…Statement 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, even though all strings are essentially the same query (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 parameter values:stmt.setInt(1, age);stmt.setInt(2, age+10);ResultSet rs = stmt.executeQuery();// Work on the results:…}… The DBMS performs parsing, semantic analysis, optimization, and compilation only once, when it prepares the statement At execution time, the DBMS only needs to check parameter types and validate the compiled execution plan17Transaction processing Set isolation level for the current transaction con.setTransactionIsolationLevel(l); Where l is one of TRANSACTION_SERIALIZABLE (default), TRANSACTION_REPEATABLE_READ, TRANSACTION_READ_COMITTED, and TRANSACTION_READ_UNCOMMITTED Set the transaction to be read-only


View Full Document

Duke CPS 116 - SQL: Programming

Documents in this Course
Part I

Part I

8 pages

XSLT

XSLT

4 pages

XSLT

XSLT

8 pages

Part I

Part I

8 pages

XSLT

XSLT

8 pages

Load more
Download SQL: Programming
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view SQL: Programming and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view SQL: Programming 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?