1 / 98

INFO2120 – INFO2820 – COMP5138 Database Systems

INFO2120 – INFO2820 – COMP5138 Database Systems. Week 8: Database Application Development. (Kifer/Bernstein/Lewis – Chapter 8; Ramakrishnan/Gehrke – Chapter 6; Ullman/Widom – Chapter 9). Outline. Database Application Architectures Client-side DB Application Development

katen
Télécharger la présentation

INFO2120 – INFO2820 – COMP5138 Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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)

More Related