Database Application Development Programming With Databases 1 Overview

Database Application Development Programming With Databases 1

Overview Concepts covered in this lecture: v SQL in application code v Embedded SQL v Cursors v Dynamic SQL v Stored procedures 2

Example: Course Enrolment Enters request: add course, drop course User/Client Sends query: Course availability, student info, … Application • Checks constraints • returns confirmation for display Database Returns data 3

Programming With SQL 4

SQL statements as Part of a larger software system v So far: § interactive SQL interface, § pure “SQL programs”. v In practice often: § queries are not ad-hoc, but programmed once and executed repeatedly. Ø need the greater flexibility of a general-purpose programming language: Ø for complex calculations Ø graphic user interfaces. 5

Key Questions How do we send SQL commands to a database management system from within an application program? v How do we get the answer back in a way that can be processed by the application program? v Rather than extending a programming language with SQL capability, how about extending SQL with programming capabilities? v 6

SQL in Application Code v 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 to the right database. 7

Overview Static Queries: Dynamic Queries Query form known at compile time Execution in Application Space Embedded SQLJ Server Execution Stored Procedure SQL/PSM API: Dynamic SQL ODBC, JDBC Could also have dynamic stored procedures but we won’t discuss it. 8

SQL in Application Code (Contd. ) Impedance mismatch: v SQL relations are (multi-) sets of records, with no a priori bound on the number of records. v No such data structure exists traditionally in procedural programming languages such as C++ or Java. v SQL supports a mechanism called a cursor to handle this. 9

Cursors 10

Cursors Can declare a cursor on a relation or query statement (which generates a relation). v Can open a cursor, and repeatedly fetch a tuple then move the cursor, until all tuples have been retrieved. v Can use a special clause, called ORDER BY, to control the order in which tuples are returned. § Fields in ORDER BY clause must also appear in SELECT clause. § v Can also modify/delete tuple pointed to by a cursor. 15

Terminology “Cursor” is the official SQL term. v Many programming languages have their term for the same concept. v Language Term SQL Standard Cursor Stored Procedures PSM Cursor Java Result. Set Visual Studio (C##, Visual Basic) Data. Reader 16

Cursor that gets names of sailors who’ve reserved a red boat, in alphabetical order EXEC SQL DECLARE sinfo CURSOR FOR SELECT S. sname FROM Sailors S, Boats B, Reserves R WHERE S. sid=R. sid AND R. bid=B. bid AND ORDER BY S. sname B. color=‘red’ Most implementations differ from SQL standard: v Can use order by without cursor. v Can have many expressions for order criteria. 17

Database APIs Add library with database calls (API) v Special standardized interface: procedures/objects v Pass SQL strings from language, presents result sets in a language-friendly way v Sun’s JDBC: Java API v Supposedly DBMS-neutral § § § a driver traps the calls and translates them into DBMSspecific code database can be across a network. Source code and executable is independent of DBMS. 19

Dynamic SQL 20

Dynamic SQL v Often, the concrete SQL statement is known not at compile time, but only at runtime. § Example 1: a program prompts user for parameters of SQL query, reads the parameters and executes query. § Example 2: a program prompts user for an SQL query, reads and executes it. v Construction of SQL statements on-the-fly: PREPARE: parse and compile SQL command. EXECUTE: execute command. 21

Dynamic SQL: Example char c_sqlstring[]= {“DELETE FROM Sailors WHERE rating > 5”}; EXEC SQL PREPARE readytogo FROM : c_sqlstring; EXEC SQL EXECUTE readytogo; 22

CONNECTING TO DATABASES 23

Connection Flow Chart Load Driver Manager do this once Driver Manager create Connection Statement Query = string create execute return repeat this part Cursor, Result. Set 24

Python Database Connectivity See CSIL website for how to connect. v Python has the built-in concept of a tuple, so fetching one row from a cursor returns a tuple! v Python Database Tutorial v 25

Python Example >>> import pymssql >>> conn = pymssql. connect(host='cypress. csil. sfu. ca', user='s_oschulte', password=’J****’, database='oschulte 354') >>> mycursor = conn. cursor() >>> tablename = 'sailors' >>> mycursor. execute('SELECT * from '+tablename) >>> row = mycursor. fetchone() >>> print row (22, u'Dustin', 7, 45. 0) >>> print row[0: 2] (22, u'Dustin') >>> while row: print "Sailor ID and Sailor Name are", row[0: 2] row = mycursor. fetchone() Sailor ID and Sailor Name are (22, u'Dustin') Sailor ID and Sailor Name are (29, u'Brutus'). . . Sailor ID and Sailor Name are (32, u'Andy') >>> conn. close() 26

Visual Studio Example Visual Studio Connection Example see course website. 27

JDBC: Architecture v Four architectural components: § Application (initiates and terminates connections, submits SQL statements) § Driver manager (load JDBC driver) § Driver (connects to data source, transmits requests and returns/translates results and error codes) § Data source (processes SQL statements) 28

JDBC Example Connection con = Driver. Manager. get. Connection(url, ”login", ”pass"); // connection object created by Driver Manger Statement stmt = con. create. Statement(); // set up statement for the connection String query = "SELECT name, rating FROM Sailors"; Result. Set rs = stmt. execute. Query(query); try { // handle exceptions // loop through result tuples while (rs. next()) { // while there is a next tuple String s = rs. get. String(“name"); // get value of “name” in current tuple Int n = rs. get. Float(“rating"); // get value of “rating” in current tuple System. out. println(s + " " + n); } } catch(SQLException ex) { System. out. println(ex. get. Message () + ex. get. SQLState () + ex. get. Error. Code ()); } 29

JDBC Driver Management All drivers are managed by the Driver. Manager class v Loading a JDBC driver: v § In the Java code: Class. for. Name(“oracle/jdbc. driver. Oracledriver”); Class. for. Name(“com. mysql. jdbc. Driver”); § When starting the Java application: -Djdbc. drivers=oracle/jdbc. driver v Check CSIL documentation! 30

Connections in JDBC We interact with a data source through sessions. Each connection identifies a logical session. v JDBC URL: jdbc: <subprotocol>: <other. Parameters> Example: String url=“jdbc: oracle: www. bookstore. com: 3083”; Connection con; try{ con = Driver. Manager. get. Connection(url, used. Id, password); } catch SQLException except { …} 31

Connection Class Interface v v v public boolean get. Read. Only() and void set. Read. Only(boolean b) Specifies whether transactions in this connection are read -only public boolean get. Auto. Commit() and void set. Auto. Commit(boolean b) If autocommit is set, then each SQL statement is considered its own transaction. Otherwise, a transaction is committed using commit(), or aborted using rollback(). public boolean is. Closed() Checks whether connection is still open. 32

Result. Sets A Result. Set is a very powerful cursor: v previous(): moves one row back v absolute(int num): moves to the row with the specified number v relative (int num): moves forward or backward v first() and last() v Record. Set, Data. Reader in Visual Basic 33

Call Result. Sets v v Prepared. Statement. execute. Update only returns the number of affected records Prepared. Statement. execute. Query returns data, encapsulated in a Result. Set object (a cursor) Result. Set rs=pstmt. execute. Query(sql); // rs is now a cursor While (rs. next()) { // process the data } 34

Stored Procedures 35

Stored Procedures A stored procedure is a function / procedure written in a general-purpose programming language that is executed within the DBS. v Performs computations that cannot be expressed in SQL. v Procedure executed through a single SQL statement. v Executed in the process space of the DB server. v SQL standard: PSM (Persistent Stored Modules). Extends SQL by basic concepts of a general-purpose programming language. v 36

Advantages of Stored Procedures Can encapsulate application logic while staying close to the data. v Reuse of application logic by different users. v Avoid tuple-at-a-time return of records through cursors. v Provides data security (like a view). v 37

Stored Procedures: Examples CREATE PROCEDURE Show. Num. Reservations SELECT S. sid, S. sname, COUNT(*) FROM Sailors S, Reserves R WHERE S. sid = R. sid GROUP BY S. sid, S. sname Stored procedures can have parameters: v Three different modes: IN, OUT, INOUT CREATE PROCEDURE Increase. Rating( IN sailor_sid INTEGER, IN increase INTEGER) UPDATE Sailors SET rating = rating + increase WHERE sid = sailor_sid 38

Stored Procedures: Examples (Contd. ) Stored procedure does not have to be written in SQL: CREATE PROCEDURE Top. Sailors( IN num INTEGER) LANGUAGE JAVA EXTERNAL NAME “file: ///c: /stored. Procs/rank. jar” 39

Main SQL/PSM Constructs (Contd. ) v v Local variables (DECLARE) RETURN values for FUNCTION Assign variables with SET Branches and loops: § IF (condition) THEN statements; ELSEIF (condition) statements; … ELSE statements; END IF; § LOOP statements; END LOOP v v Queries can be parts of expressions Can use cursors without “EXEC SQL” 40

Calling Stored Procedures EXEC SQL BEGIN DECLARE SECTION Int sid; Int rating; EXEC SQL END DECLARE SECTION // now increase the rating of this sailor EXEC SQL CALL Increase. Rating(: sid, : rating); 41

SQL/PSM Most DBMSs allow users to write stored procedures in a simple, general-purpose language (close to SQL) SQL/PSM standard is a representative Declare a stored procedure: CREATE PROCEDURE name(p 1, p 2, …, pn) local variable declarations procedure code; Declare a function: CREATE FUNCTION name (p 1, …, pn) RETURNS sql. Data. Type local variable declarations function code; 42

Main SQL/PSM Constructs CREATE FUNCTION rate Sailor (IN sailor. Id INTEGER) RETURNS INTEGER DECLARE rating INTEGER DECLARE num. Res INTEGER SET num. Res = (SELECT COUNT(*) FROM Reserves R WHERE R. sid = sailor. Id) IF (num. Res > 10) THEN rating =1; ELSE rating = 0; END IF; RETURN rating; 43

SQL Server Version CREATE FUNCTION rate. Sailor (@sailor. Id INT) RETURNS INT AS BEGIN DECLARE @num. Res INT DECLARE @rating INT SET @num. Res = (SELECT COUNT(*) FROM Reserves R WHERE R. sid = @sailor. Id) IF @num. Res > 10 SET @rating = 1 ELSE SET @rating = 0 RETURN @rating END GO; SELECT dbo. rate. Sailor(22); go 44

My. SQL Version DELIMITER $$ CREATE FUNCTION rate. Sailor(sailor. Id INT) RETURNS INT BEGIN DECLARE num. Res INT; DECLARE rating INT; SET num. Res = (SELECT COUNT(*) FROM Reserves R WHERE R. sid = sailor. Id); IF num. Res > 10 THEN SET rating = 1; ELSE SET rating = 0; END IF; RETURN rating; END $$ select rate. Sailor(22); 45

Calling Stored Procedures (Contd. ) JDBC: Callable. Statement cstmt= con. prepare. Call(“{call Show. Sailors}); Result. Set rs = cstmt. execute. Query(); while (rs. next()) { … } 46

Summary 47

SQL Connectivity Embedded SQL allows execution of parametrized static queries within a host language v Dynamic SQL allows execution of completely adhoc queries within a host language v Cursor mechanism allows retrieval of one record at a time and bridges impedance mismatch between host language and SQL v APIs such as JDBC introduce a layer of abstraction between application and DBMS v 48

Stored Procedures Stored procedures execute application logic directly at the server v SQL/PSM standard for writing stored procedures v 49