INFO2120 – INFO2820 – COMP5138 Database Systems - PowerPoint PPT Presentation

info2120 info2820 comp5138 database systems n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
INFO2120 – INFO2820 – COMP5138 Database Systems PowerPoint Presentation
Download Presentation
INFO2120 – INFO2820 – COMP5138 Database Systems

play fullscreen
1 / 98
INFO2120 – INFO2820 – COMP5138 Database Systems
159 Views
Download Presentation
katen
Download Presentation

INFO2120 – INFO2820 – COMP5138 Database Systems

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. INFO2120 – INFO2820 – COMP5138Database Systems Week 8: Database Application Development (Kifer/Bernstein/Lewis – Chapter 8; Ramakrishnan/Gehrke – Chapter 6; Ullman/Widom – Chapter 9)

  2. Outline • Database Application Architectures • Client-side DB Application Development • Call-level Database APIs: PHP/PDO and JDBC • Database Application Design Principles • Server-side DB Application Development • Stored Procedures Based on slides from Kifer/Bernstein/Lewis (2006) “Database Systems” and from Ramakrishnan/Gehrke (2003) “Database Management Systems”,and also including material from Fekete and Röhm. INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  3. Database Applications INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  4. Data-intensive Systems • Three types of functionality: • The system architecture determines whether these three components reside on a single system (1-tier) or whether they are distributed across several tiers • Presentation Logic • Input – keyboard/mouse • Output – monitor/printer GUI Interface Processing Logic- Business rules - I/O processing Procedures, functions, programs Data Management(Storage Logic) - data storage and retrieval DBMS activities INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  5. Possible System Architectures • 1-Tier Architectures: Centralised Systems • 2-Tier Architectures: Client-Server Systems • 3-Tier Architectures • Client - Server - Middleware • Internet Applications • Web Databases INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  6. Centralized System • Presentation Services - displays forms, handles flow of information to/from screen • Application Services - implements user request, interacts with DBMS • Transactional properties automatic (isolation is trivial) or not required (this is not really an enterprise) • DBMS runs within the user process • Examples: • Access; any application with an integrated DB (e.g. SQLite) – from smartphones to PCs centralized system DBMS API presentation application services services user module INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  7. Client/Server Model of TPS database server machine client machines presentation application services services DBMS • • • presentation application services services communication /network INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  8. Three-Tiered Model of TPS application / webserver machine database server machine client machines presentation server DBMS • • • application server presentation server communication (IPC or network) Data ManagementTier Middle Tier Presentation Tier INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  9. Interactive vs. Non-Interactive SQL • Interactive SQL: SQL statements input from terminal; DBMS outputs to screen • Inadequate for most uses • It may be necessary to process the data before output • Amount of data returned not known in advance • SQL has very limited expressive power (not Turing-complete) • Non-interactive SQL: SQL statements are included in an application program written in a host language, like C, Java, COBOL • Nowadays also: as embedded in dynamic webpages • Client-side vs. Server-side application development • Server-side: Stored Procedures and Triggers INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  10. Outline • Database Application Architectures • Client-side DB Application Development • Call-level Database APIs: PHP/PDO • Call-level Database API for Java: JDBC • Database Application Design Principles • Server-side DB Application Development • Stored Procedures Based on slides from Kifer/Bernstein/Lewis (2006) “Database Systems” and from Ramakrishnan/Gehrke (2003) “Database Management Systems”,and also including material from Fekete and Röhm. INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  11. SQL in Application Code • 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. • Two main integration approaches: • Statement-level interface (SLI) • Embed SQL in the host language (Embedded SQL in C, SQLJ) • Application program is a mixture of host language statements and SQL statements and directives • Call-level interface (CLI) • Create special API to call SQL commands (JDBC, ODBC, PHP, …) • SQL statements are passed as arguments to host language (library) procedures / APIs INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  12. Call-level Interfaces and Database APIs • Rather than modify compiler, add library with database calls (API) • Special standardized interface: procedures/objects • Pass SQL strings from language,present result sets in language-friendly way • Supposedly DBMS-neutral • a “driver” executes the calls and translates them into DBMS-specific code • database can be across a network • Several Variants • SQL/CLI: “SQL Call-Level-Interface” • Part of the SQL-92 standard; • “The assembler under the APIs” • ODBC: “Open DataBase Connectivity” • Side-branch of early version of SQL/CLI • Enhanced to: OLE/db, and further ADO.NET • JDBC: “Java DataBase Connectivity” • Java standard • PDO • Persistency standard for PHP Data Objects JDBC, ODBC, PDO, … Native Interface CLI DBMS INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  13. PDO – PHP Data Objects INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  14. PHP • PHP is a scripting language for dynamic websites • PHP – original recursive acronym for "PHP: Hypertext Preprocessor” • embedded into HTML • Indicated by <?phpPHP-code?> • There are several different approacheson how to connect in PHP scripts to databases • Vendor-specific database extensions • e.g. pgsql (PostgreSQL) or pci8 (Oracle) => Outdated! • Some abstraction layers on top (typically for PHP 5.1 onwards) • e.g. PDO (“PHP Data Objects”) • Generic DB library also via PEAR (PHP Extension&Application Repository) INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  15. PHP 101 • A dynamically-typed scripting language • Embedded in normal HTML page • Offers the usual programming constructs: • Variable • Condition statements • Loops • Input/output • Example (example.php):<html><head><title>PHP Test</title></head><body> <h1>This is a PHP test</h1> Today is <?php echo "a just normal day" ?>, the <?php echo date("F j, Y") ?>.</body></html> INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  16. PHP 101: Variables in PHP • Must begin with $ • Dynamically typed – it is OK to not declare a type for a variable. • But you give a variable a value that belongs to a “class,” in which case, methods of that class are available to it. • String Variables: • PHP solves a very important problem for languages that commonly construct strings as values: • How do I tell whether a substring needs to be interpreted as a variable and replaced by its value? • PHP solution: Double quotes means replace; single quotes means don’t. $100 = ”one hundred dollars”; $sue = ’You owe me $100.’; $joe = ”You owe me $100.”; • Value of $sue is ’You owe me $100’, while the value of $joe is ’You owe me one hundred dollars’. INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  17. PHP 101: Array Variables in PHP • Two kinds: numeric and associative. • Numeric arrays are ordinary indexed 0,1,… • Example: $a = array("Paul", "George", "John", "Ringo"); • Then $a[0] is "Paul", $a[1] is "George", and so on. • Elements of an associative array $a are pairs x => y, where x is a key string and y is any value. • If x => y is an element of $a, then $a[x] is y. • Example: $a = array("bass" => "Paul", "guitar" => "George", "guitar2"=>"John", "drums" => "Ringo"); • Then $a[‘bass’] is "Paul", $a[‘drums’] is "Ringo", and so on. INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  18. PDO – PHP Data Objects • Introduced since PHP 5.1 (in 2005) • Object-oriented extension to PHP for database programming that provides a database abstraction layer • Generic driver model to connect to different database engines via the same API • Significant improvement over the previous proprietary APIs • URL: http://www.php.net/manual/en/intro.pdo.php INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  19. PDO Example <?phpfunction printClassList ($unit_of_study, $user, $pwd) { try {/* connect to the database */ $conn=new PDO('pgsql:host=localhost:port=5432:dbname=unidb", $user, $pwd);/* prepare a dynamic query */$stmt = $conn->prepare('SELECT name FROM Student NATURAL JOIN Enrolled WHERE uosCode = :uos'); $stmt->bindValue( ':uos', $unit_of_study, PDO::PARAM_STR, 8);/* execute the query and loop through the resultset */ $results = $stmt->execute(); while ( $row = $results->fetch() ) { print " student: ", $row['name']; } /* clean up */ $stmt->closeCursor(); $conn = null; } catch (PDOException $sqle) { /* error handling */ print "SQL exception : ", $sqle->getMessage(); } } ?> INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  20. Core Problems with SQL Interfaces (1) Establishing a database connection (2) Static vs. Dynamic SQL (3) Mapping of domain types to data types of host • Concept of host variable • How to treat NULL values? (4) Impedance Mismatch: • SQL operates on sets of tuples • Host languages like C do not support a set-of-records abstraction, but only a one-value-at-a-time semantic • Solution: Cursor ConceptIteration mechanism (loop) for processing a set of tuples (5) Error handling INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  21. (1): PDO Run-Time Architecture Oracle database Oracle driver PHP code PDO PostgreSQL driver PostgreSQL database MySQL driver MySQL database DBMS • PDO is DBMS independent • PDO functions are generic • PDO allows to connect to specific driver • Using parameters of PDO constructor • Even to different databases from the same program • Database drivers are loaded and used at run-time . . . INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  22. PDO Connections • Session with a data source started by creating a PDO object: $conn = new PDO( DSN, $userid, $passwd [,$params] ); • Data Source Name (DSN) of the form <driver>:<connectionParameter1>;<connectionParameter2>;… • For example with PostgreSQL: $conn = new PDO( "pgsql:host=postgres.it.usyd.edu.au;dbname=unidb",$user,$pw); connectionParameters driver db login Details: http://www.php.net/manual/en/pdo.construct.php INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  23. PDO Connection Drivers • Driver support for variety of DBMSs • MySQL (prefix: mysql) • PostgreSQL (prefix: pgsql) • Oracle (prefix: oci) • IBM DB2 (prefix: ibm) • SQL Server (prefix: sqlsrv) • sqlite (prefix: sqlite) • … • DSN syntax and additional DB parameters vary for each driver • Check manuals: http://www.php.net/manual/en/pdo.drivers.php • Example for Oracle: $conn = new PDO( "oci:dbname=oracle10g.it.usyd.edu.au:1521/ORCL", $user, $pwd ); Note:drivers need to be installed first as part of the PHP server's configuration… INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  24. PDO Connection Example <?php try {/* connect to the database */ $conn = new PDO('pgsql:host=localhost:port=5432:dbname=unidb", $user, $pw);/* query database */$stmt = $conn->query('SELECT name FROM Student WHERE studID=4711'); • … Do Actual Work …. • /* clean up */ • $stmt->closeCursor(); • $conn = null; }/* error handling */ catch (PDOException $sqle) { • print "SQL exception : ", $sqle->getMessage(); }?> INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  25. PDO Objects PDO __construct(…)query()prepare() beginTransaction() commit() rollBack() … PDOStatement string $queryString bindValue() bindParam() bindColumn() execute() fetch() fetchColumn() fetchAll() nextRowset() closeCursor() errorCode() … prepare(stmt) query(stmt) PDOException array $errorInfo getMessage() getPrevious() getCode() getFile() getLine() … INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  26. PDO Class Interface • Start SQL statements • query() for static SQL, or • prepare() for parameterized SQL queries • exec() for immediately executing some SQL; returns num rows • Transaction control • beginTransaction() starts a database transaction (otherwise: autocommit) • commit() successfully finishes current transaction • rollBack() aborts current transaction • inTransaction() checks whether there's an active transaction • Sets/gets connection parameters (often driver specific) • getAttribute(…) • setAttribute(…) • Error Handling • errorCode() • errorInfo() [cf. http://www.php.net/manual/en/class.pdo.php] INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  27. Side Note on DB Connections • Establishing a database connection takes some time… • Network communication, memory allocation, dbs authorization • So do this only once in your program • … but not for individual SQL queries • Modern, multi-threaded applications will typically want to have a pool of connections that are re-used • Might be handled by your runtime library(that's what happens in PHP) • But for, e.g., Java programs better be mindful of connection costs! INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  28. (2) Executing SQL Statements • Three different ways of executing SQL statements: • PDOStatementPDO::query(sql) semi-static SQL statements • PDOStatementPDO::prepare(sql) parameterized SQL statements • num_rowsPDO::exec(sql) immediately run SQL command • PDOStatementclass:Precompiled, parameterized SQL statements: • Structure is fixed after call to PDO::prepare() • Values of parameters are determined at run-time • Fetch and store routines are executed when PDOStatement::execute() is executed to communicate argument values with DBMS • PDOStatement::execute() can be invoked multiple times with different values of in parameters • Each invocation uses same query execution plan INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  29. PDOStatement with Semi-static SQL • Simplest way to execute some static SQL query: <?php try {/* connect to the database */ … /* query database */$stmt = $conn->query('SELECT name FROM Student WHERE studID=4711'); $name = $stmt->fetchColumn(); /* just fetch the single return value */ print $name; /* clean up */ $stmt->closeCursor(); }/* error handling */ catch (PDOException $sqle) { print "SQL exception : ", $sqle->getMessage(); }?> This is 'semi-static' because one could construct the SQL string during runtime. Warning: DON'T DO THIS! Use parameterized queries instead! (cf. SQL Injection problem later) INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  30. Static vs. Dynamic SQL • SQL constructs in an application can take two forms: • Standard SQL statements (staticembeddedSQL): Useful when SQL portion of program is known at compile time • Only available with Embedded SQL in compiled language… • Directives (dynamic SQL): Useful when SQL portion of program not known at compile time. Application constructs SQL statements at run time as values of host language variables that are manipulated by directives. • Problem is: PHP is not a compiled language;So everything in PHP/PDO is by definition dynamic SQL… • Still: Try to avoid constructing SQL strings in the program from user input, rather use fixed query structures with parameters (parameterized queries) INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  31. Approach 2: Preparing and Executing a parameterized Query • $query = "SELECT E.studId FROM Enrolled E WHERE E.uosCode = ? AND E.semester = ?"; • $stmt = $conn->prepare ( $query ); • Prepares the statement • Creates a prepared statement object, $stmt, containing the prepared statement • Placeholders (?) mark positions of in parameters; • special API is provided to plug the actual values in • positions indicated by the ?’s placeholders INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  32. Preparing & Executing a Query (cont’d) • var $uos_code, $semester; • ……… • $stmt->bindValue(1, $uos_code); // set value of first in parameter • $stmt->bindValue(2, $semester); // set value ofsecond in parameter • $stmt->execute (); • Evaluates parameters bound with setParameter() only now • Executes the query • Associates a result set with the same PDOStatement • while ( $row = $stmt->fetch () ) { // advance the cursor • $j = $row['studId']; // fetch output int-value • …process output value… • } INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  33. (3) Host Variables • Data transfer between DBMS and application • Mapping of SQL domain types to data types of host language • PHP PDO: • Host variables are normal mixed PHPvariables that are dynamically typed and accessed during runtime:$studid = 12345;$stmt = $conn->prepare( "SELECT name FROM Student WHERE sid=?");$stmt->bindValue(1, $studid); • Note: in statement-level APIs such as ESQL/C: Host variables must be declared before usage EXEC SQL BEGIN DECLARE SECTION; int studid = 12345; char sname[21];EXEC SQL END DECLARE SECTION; Variables shared by host and SQL INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  34. PDO: Parameterized Queries • Two Approaches for specifying query parameters: • Anonymous Placeholders $studid= 12345;$stmt = $conn->prepare( "SELECT name FROM Student WHERE sid=?"); $stmt->bindValue(1, $studid); 2. Named Placeholders $studid= 12345;$stmt = $conn->prepare( "SELECT name FROM Student WHERE sid=:s"); $stmt->bindValue(':s', $studid); INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  35. PDO: Binding Host Variables • Two Approaches for binding host variables as inputparams: • PDOStatement::bindValue() binds value of host variable at call • PDOStatement::bindParam() binds host variable by reference • Example $studid= 12345;$stmt = $conn->prepare( "SELECT name FROM Student WHERE sid=:s"); $stmt->bindParam(':s', $studid); $studid= 56789;$stmt->execute(); INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  36. PDO: Typing Host Variables • Host variables can be dynamically typed $stmt = $conn->prepare( "SELECT name FROM Student WHERE sid=:s"); $stmt->bindValue(':s', 12345); • or type-safe with (optional) third type parameter • PDO::PARAM_INT represents an SQL INTEGER • PDO::PARAM_STR represents a SQL CHAR or VARCHAR • PDO::PARAM_BOOL represents a boolean • PDO::PARAM_LOB represents a SQL large object data type • PDO::PARAM_NULL represents SQL NULL • Example: $studid= 12345;$stmt = $conn->prepare( "SELECT name FROM Student WHERE sid=:s"); $stmt->bindValue(':s', $studid, PDO::PARAM_INT); INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  37. PDO: Binding Output Variables • For binding output parameters: PDOStatement::bindColumn() binds a output column to a PHP varPDOStatement::fetch(PDO::FETCH_BOUND) fetches values into vars • Can also be strongly typed during bindColumn() call • Example: $sql= "SELECT name,gender,address FROM Student WHERE sid=4711"; $stmt= $conn->prepare($sql);$stmt->execute(); /* option 1: bind by column number */ $stmt->bindColumn(1, $name, PDO::PARAM_STR); $stmt->bindColumn(2, $gender, PDO::PARAM_STR); /* option 2: bind by column name */ $stmt->bindColumn('address', $addr); $row = $stmt->fetch(PDO::FETCH_BOUND); print $name, '\t',$gender, '\t',$addr, '\n'; INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  38. Preparing & Executing Dynamic Updates $sql="INSERT INTO Student VALUES(?,?,?,?)"; $pstmt = $conn->prepare($sql); $pstmt.bindValue(1, $sid, PDO::PARAM_INT); $pstmt.bindValue(2, $sname, PDO::PARAM_STR); $pstmt.bindValue(3, $birthdate, PDO::PARAM_STR); $pstmt.bindValue(4, $country, PDO::PARAM_STR); /* execute with latest values from host variables */ $pstmt.execute(); $numRows1 = $pstmt.rowCount(); /* execute again with dynamically bound values */ $pstmt.execute( array(1234,'Obama',NULL,'USA') ); $numRows2 = $pstmt.rowCount(); INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  39. (4) Buffer Mismatch Problem(also: Impedance Mismatch) • Problem: SQL deals with tables (of arbitrary size); host language program deals with fixed size buffers • How is the application to allocate storage for the result of a SELECT statement? • Solution: Cursor concept • Fetch a single row at a time cursor SELECT Result set (or pointers to it) application Base table INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  40. Mapping of Sets: Cursor Concept • Result set – set of rows produced by a SELECT statement • Cursor – pointer to a row in the result set. • Cursor operations: • Declaration • Open – execute SELECT to determine result set and initialize pointer • Fetch – advance pointer and retrieve next row (JDBC: next() call) • Close – deallocate cursor INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  41. Cursor in PDO – via PDOStatement • Cursor concept with PHP/PDO: $stmt = $conn->prepare("SELECT title,name,address FROM Emp");$stmt->execute();while ( $row = $stmt->fetch() ) { $data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\n"; print $data; }$stmt->closeCursor(); • PHP language natively supports arrays; good for small results $stmt->execute();$resultset = $stmt->fetchAll(); foreach ( $resultsetas $row ) { print_r($row); } • just be mindful that this can be VERYmemoryhungry for large results You can address result columns either by name or position INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  42. PDOStatement::fetch() mixedPDOStatement::fetch ( [ int$fetch_style [, int$cursor_orientation = PDO::FETCH_ORI_NEXT [, int$cursor_offset = 0 ]]] ) • where • $fetch_styleControls how new result row will be returned to caller • PDO::FETCH_ASSOC as an associative array • PDO::FETCH_NUM as numerically-index array, starting at 0 • PDO::FETCH_BOTH both of above (DEFAULT) • PDO::FETCH_BOUND fetch in bound output column variables • … • $cursor_orientationWhether it is a scrollable cursor, or not (DEFAULT) • $cursor_offsetfor a scrollable cursor, the absolute row number to fetch first INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  43. NULL Handling in PDO • Remember: Null values mean neither 0 nor empty string • Hence special indication of unknown values needed. • In PHP this is quite natural, as PHP supports NULL: $stmt = $conn->query("SELECT gender FROM Student …");$row = $stmt->fetch(); if ( is_null($row['gender']) ){ /* null value */ }else{ /* no null value */} • Other languages require a special indicator variable. Eg. C: EXEC SQLselect gender into:gender:indicatorfrom Student where sid=4711;if ( indicator == -1 ){ /* null value */ }else{ /* no null value */} INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  44. PHP: isset() vs. empty() vs. is_null() • is_null(var) • Returns TRUEif var === NULL,otherwise FALSE [http://php.net/manual/en/function.is-null.php] • isset(var) • Returns TRUE if var exists and is not NULL, otherwise returns FALSE.[http://php.net/manual/en/function.isset.php] • empty(var) • Returns FALSE if var exists and has a non-empty, non-zero value, otherwise TRUE. [http://php.net/manual/en/function.empty.php] INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm) http://techtalk.virendrachandak.com/php-isset-vs-empty-vs-is_null/

  45. NULL Handling in PDO (cont'd) • In PDO, the NULL behaviour can be further configured • PDO connection attribute PDO::ATTR_ORACLE_NULLS (available with all drivers, not just Oracle): • PDO::NULL_NATURAL no conversion. • PDO::NULL_EMPTY_STRING empty string is converted to NULL. • PDO::NULL_TO_STRING NULL is converted to an empty string. INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  46. (5) Error Handling • Multitude of potential problems • No database connection or connection timeout • Wrong login or missing privileges • SQL syntax errors • Empty results • NULL values • … • Hence always check database return values, • Provide error handling code, resp. exception handlers • Gracefully react to errors or empty results or NULL values • NEVER show database errors to end users • Not only bad user experience, but huge security risk… INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  47. You should avoid this! Also cf. error #... Of http://www.sans.org/top25-software-errors/ INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  48. Error Handling with PDO Two mechanism: 1. Explicitly testing for error codes after each statement • Both PDO and PDOStatement objects provide error status functions: • errorCode() fetches the SQLSTATE of last statement • errorInfo()fetches extended error information of last stmt. 2. Error handling via normal exception mechanism of PHP • This has to be configured on a connection (PDO) object via PDO::setAttribute() • PDO::ATTR_ERRMODE: Error reporting. • PDO::ERRMODE_SILENT: Just set error codes. • PDO::ERRMODE_WARNING: Raise E_WARNING. • PDO::ERRMODE_EXCEPTION: Throw exceptions. • Example:try { …} catch ( PDOException $ex ) { print ex.getMessage();} INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  49. SQLSTATE • a five characters alphanumeric identifier defined in SQL-92 • Two characters error class value • Followed by a three characters sub-class value • Examples: • 00000 successful completion • Class 01 indicates a warning • eg. 01004 Warning: string data, right truncation • or 01007 Warning: privilege not granted • Class 02: no data error (SQLSTATE: 02000) • Class 08: connection error • eg. 08001 Error: unable to establish SQL connection • … • List of available SQLSTATEs:http://docstore.mik.ua/orelly/java-ent/jenut/ch08_06.htm INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)

  50. Exception Handling with PDO • Class PDOException • PDOException::getMessage() returns exception message • PDOException::getCode() returns the exception code • … • Example: • 1. Configure to have thrown exceptions on SQL errrors$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); • 2. Catch-Try block around PDO statements:try { …} catch ( PDOException $ex ) { print ex.getMessage();} INFO2120/INFO2820/COMP5138 "Database Systems" - 2013 (U. Röhm)