Oracle Database Express Edition® 2 Day Plus PHP Developer Guide 10g Release 2 (10.
Oracle Database Express Edition 2 Day Plus PHP Developer Guide, 10g Release 2 (10.2) B25317-01 Copyright © 2005 Oracle. All rights reserved. Contributing Author: Christopher Jones, Simon Law, Glenn Stokol, Simon Watt. The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws.
Contents Preface ................................................................................................................................................................ v Audience....................................................................................................................................................... Documentation Accessibility ..................................................................................................................... Related Documents ........
Extending the Basic Departments Form............................................................................................ Building the Basic Employee Form.................................................................................................... 5 4-10 4-12 Updating Data Extending the Basic Employee Form .................................................................................................... 5-1 Combining Departments and Employees ..............................................
Preface The Oracle Database Express Edition 2 Day Plus PHP Developer Guide introduces developers to the use of PHP to access Oracle Database Express Edition. This preface contains these topics: Q Audience Q Documentation Accessibility Q Related Documents Q Conventions Audience The Oracle Database Express Edition 2 Day Plus PHP Developer Guide is intended as an introduction to application development using Zend Core for Oracle and Oracle Database Express Edition.
Accessibility of Links to External Web Sites in Documentation This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.
1 Introducing PHP with Oracle Database XE Oracle® Database Express Edition (Oracle Database XE) is a relational database that you can use to store, use, and modify data. Zend Core for Oracle enables application development using PHP.
Overview of the Sample Application 6. Uploads and displays employee photographs Figure 1-1 shows the relationship between the files developed for this application: Figure 1–1 Functionality in the Sample PHP Application The sample application files are: anyco.php This file has the main logic for the AnyCo application. It contains control logic to decide which page is displayed. It manages session data for navigation. The functionality in anyco_cn.inc, anyco_db.inc , and anyco_ui.inc is used by it.
Resources The code for each chapter builds on the files completed in the previous chapter. This tutorial creates files in your $HOME/public_html directory. This is the default location for web access if the Apache web server configuration has the UserDir directive enabled. If you create files in a different location, you need to change the steps for file editing and execution to match your working directory name and URL.
Resources 1-4 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft
2 Getting Started This chapter explains how to install and test your Oracle Database Express Edition (Oracle Database XE) and PHP environment. It has the following topics: Q What You Need Q Testing the Oracle Database XE Installation Q Testing the Apache Installation Q Setting Up Zend Core for Oracle Q Testing the Zend Core for Oracle Installation What You Need Q Oracle Database Express Edition 10gR2 Q Apache 1.3.x or later Q Zend Core for Oracle Q Text editor for editing PHP code.
Testing the Oracle Database XE Installation http://www.oracle.com/technology/tech/php/zendcore/index.html 2. To the right of the "Zend Core for Oracle" Web page, click the Free Download image: 3. Save the downloaded file in a temporary directory, such as /tmp. Testing the Oracle Database XE Installation 1. The PHP application connects to the database as the HR user. You may need to execute the following SQL command, as a user with DBA privileges: alter user hr account unlock identified by hr; 2.
Testing the Apache Installation For further information about unlocking an Oracle Database account, see Chapter 6, "Managing Users and Security", in the Oracle Database Express Edition 2 Day DBA guide. See also: Testing the Apache Installation 1. Start your web browser and enter the following URL: http://localhost Your browser should display a page similar to: 2. In the default Apache Web server configuration file set up a public virtual directory as public_html for accessing your PHP files.
Setting Up Zend Core for Oracle # the following line instead: # UserDir public_html 3. In a command window, to use the new Apache configuration file restart Apache by entering the following commands: su Password: apachectl restart 4.
Setting Up Zend Core for Oracle Installing Zend Core for Oracle on Linux 1. To extract the contents of the downloaded Zend Core for Oracle software as a root user, which is required to install the software. In a command window enter: su Password: cd /tmp tar -zxf ZendCoreForOracle-v1.2.1-Linux-x86.tar.gz By default, unless specified otherwise, files are extracted to a sub-directory called ZendCoreForOracle-v1.2.1-Linux-x86. 2.
Setting Up Zend Core for Oracle 8. When prompted to "Verify the password", enter the same password as specified in the previous step and click OK. 9. In the Zend Core support page, you may optionally enter a Zend network user ID and password. In this case, the assumption is that you have already registered a Zend network user ID and password when you downloaded the software, and therefore click No.
Testing the Zend Core for Oracle Installation 4. In the PHP tab page, which is selected by default, expand the Error Handling and Logging configuration entry by clicking the + icon. 5. In the PHP Configuration page, to enable the display of errors in the HTML script output, set the display_errors directive On. The GUI application is aware that you have unsaved changes. Under the PHP Configuration page header notice the "Unsaved configuration" message. 6.
Testing the Zend Core for Oracle Installation 2-8 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft
3 Getting Connected In this chapter you create HR application files which implement PHP functions to connect and disconnect with the Oracle Database. You also develop a PHP function which enables you to execute a query to validate that a database connection has been successfully established. It guides you through the creation and modification of PHP files that call a function to produce the header and footer for the HR application report pages, where the footer section of the page includes a date and time.
Building the Departments Page $title = htmlentities($title); echo << Any Co.
Building the Departments Page { border-bottom: solid #334B66 4px; font-size: 160%; } table { padding: 5px; } td { border: solid #000000 1px; text-align: left; padding: 5px; } th { text-align: left; padding: 5px; } .footer { border-top: solid #334B66 4px; font-size: 90%; } .company { padding-top: 5px; float: right; } .date { padding-top: 5px; float: left; } 4. To call the user interface functions create anyco.php with the following text:
Connecting to the Database Connecting to the Database 1. To form a database connection in your PHP application, you use the oci_connect() function with three string parameters: $conn = oci_connect($username, $password, $db) The first and second parameters are the database username and password, respectively. The third parameter is the database connection identifier. The oci_connect() function returns a connection resource needed for other OCI8 calls, otherwise it returns FALSE if an error occurs.
Connecting to the Database The oci_parse() function prepares the query for execution, and is supplied the connection identifier and query string as the first and second parameters, respectively. The oci_parse() function returns a statement identifier needed to execute the query and fetch the resulting data rows, otherwise it returns FALSE on error. The oci_execute() function executes the statement associated with the statement identifier provided in the first parameter.
Disconnecting from the Database open simultaneously. The connection performance benefits need to be balanced with the overhead on the database server. Persistent connections are made with the OCI8 oci_pconnect() function. The lifetime of persistent connections can be controlled by several settings in the PHP initialization file. Some the settings include: oci8.max_persistent - controls the number of persistent connections per process. oci8.
4 Querying Data In this chapter you extend the Anyco HR application from chapter 3 by adding additional information to the departments form. You also implement the functionality to query, insert, update, and delete employees in a specific department.
Centralizing the Database Application Logic function db_connect() { // use constants defined in anyco_cn.
Centralizing the Database Application Logic Q Q The null in the fourth parameter for the maximum number of rows to fetch is ignored. In this case, all the rows for the query are returned. For this example where the result set is not large, it is acceptable. The last parameter flag OCI_FETCHSTATEMENT_BY_ROW indicates that the data in the $results array is organized by row, where each row contains an array of column values.
Writing Queries with Bind Variables } } Remember the END; line must not be prefixed with leading spaces otherwise the rest of the document is treated as part of the text to be printed. 5. Edit anyco.php. Include anyco_ui.inc and anyco_db.inc, and call the database functions to query and display information for a department with a department_id of 80 by using the following code. The file becomes:
Writing Queries with Bind Variables FROM WHERE departments department_id = :did'; By using bind variables to parameterize SQL statements: Q Q The statement is reusable with different input values without needing to change the code. The query performance is improved through a reduction of the query parse time in the server, since the Oracle database can reuse parse information from the previous invocations of the identical query string. Q There is protection against "SQL Injection" security problems.
Navigating Through Database Records if (!$stid) { db_error($conn, __FILE__, __LINE__); } // Bind the PHP values to query bind parameters foreach ($bindvars as $b) { // create local variable with caller specified bind value $$b[0] = $b[1]; // oci_bind_by_name(resource, bv_name, php_variable, length) $r = oci_bind_by_name($stid, ":$b[0]", $$b[0], $b[2]); if (!$r) { db_error($stid, __FILE__, __LINE__); } } $r = oci_execute($stid, OCI_DEFAULT); ... } ...
Navigating Through Database Records Q Q Q Detecting if the HTTP request for the page was posted by clicking the next or previous button. Tracking the last row queried by using HTTP session state. A PHP session is started to maintain state information for a specific client between HTTP requests. The first HTTP request will retrieve the first data row and initialize the session state.
Navigating Through Database Records isset($_POST['prevdept']) && $_SESSION['currentdept'] > 1) { $current = $_SESSION['currentdept'] - 1; } elseif (isset($_SESSION['currentdept']) && isset($_POST['nextdept'])) { $current = $_SESSION['currentdept'] + 1; } elseif (isset($_POST['showdept']) && isset($_SESSION['currentdept'])) { $current = $_SESSION['currentdept']; } else { $current = 1; } $query = 'SELECT department_id, department_name, manager_id, location_id FROM departments ORDER BY department_id asc'; $co
Navigating Through Database Records $posturl parameter to set the HTML form's action attribute. So each Next or Previous button click calls anyco.php. 3. Edit anyco_db.inc. Implement the db_get_page_data() function to query a sub set of rows: // Return subset of records function db_get_page_data($conn, $q1, $current = 1, $rowsperpage = 1, $bindvars = array()) { // This query wraps the supplied query, and is used // to retrieve a subset of rows from $q1 $query = 'SELECT * FROM (SELECT A.
Extending the Basic Departments Form 5. To navigate to the next department record (Marketing), click Next: 6. To navigate back to the first department record (Administration), click Previous: You may continue to test and experiment with the application by clicking Next and Previous to navigate to other records in the DEPARTMENTS table, as desired. If you navigate past the last record in the DEPARTMENTS table, an error will occur. Error handling is added in Adding Error Recovery in Chapter 5.
Extending the Basic Departments Form The additional information is obtained by modifying the query to performing a join operation between the DEPARTMENTS, EMPLOYEES, LOCATIONS, and COUNTRIES tables. To extend the department form, perform the following tasks: 1. Edit anyco_ui.inc. Modify the ui_print_departments() function by replacing the Manager ID and Location ID references with the Manager Name and Location, respectively, and insert a Number of Employees field after Department Name.
Building the Basic Employee Form AND l.country_id = c.country_id GROUP BY d.department_id, d.department_name, substr(e.first_name,1,1)||'. '||e.last_name, c.country_name ORDER BY d.department_id ASC"; The query string is enclosed in double quotes to simplify writing the statement which contains SQL literal strings in single quotes. 3. Save the changes to your files, and test the changes by entering the following URL in a Web browser: http://localhost/~/chap4/anyco.
Building the Basic Employee Form require('anyco_ui.inc'); session_start(); construct_employees(); ... ?> 3. Edit anyco_ui.inc.
Building the Basic Employee Form 4-14 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft
5 Updating Data In this chapter you extend the Anyco HR application with forms that enable you to insert, update, and delete an employee record. Q Extending the Basic Employee Form Q Combining Departments and Employees Q Adding Error Recovery Q Further Error Handling Extending the Basic Employee Form To enable employees records to manipulated, perform the following tasks: 1.
Extending the Basic Employee Form else { construct_employees(); } ... 3. Edit anyco.php.
Extending the Basic Employee Form function construct_employees() { $query = "SELECT employee_id, substr(first_name,1,1) || '. '|| last_name as employee_name, hire_date, to_char(salary, '9999G999D99') as salary, nvl(commission_pct,0) as commission_pct FROM employees ORDER BY employee_id asc"; $conn = db_connect(); $emp = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_ROW); ui_print_header('Employees'); ui_print_employees($emp, $_SERVER['SCRIPT_NAME']); ui_print_footer(date('Y-m-d H:i:s')); } 5.
Extending the Basic Employee Form FROM WHERE employees employee_id = :empid"; $conn = db_connect(); $bindargs = array(); array_push($bindargs, array('EMPID', $empid, -1)); $emp = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_ROW, $bindargs); ui_print_header('Modify Employee '); ui_print_modify_employee($emp[0], $_SERVER['SCRIPT_NAME']); ui_print_footer(date('Y-m-d H:i:s')); } 7. Edit anyco.php.
Extending the Basic Employee Form OCI_FETCHSTATEMENT_BY_ROW, in the oci_fetch_all() call with a variable so callers can choose the output type. function db_do_query($conn, $statement, $resulttype, $bindvars = array()) { $stid = oci_parse($conn, $statement); ... $r = oci_fetch_all($stid, $results, null, null, $resulttype); return($results); } 10. Edit anyco_db.inc.
Extending the Basic Employee Form echo '
No Employee found
Extending the Basic Employee Form
Extending the Basic Employee Form $lnm $eml $sal $cpt $eid = = = = = htmlentities($empdetails['LAST_NAME']); htmlentities($empdetails['EMAIL']); htmlentities($empdetails['SALARY']); htmlentities($empdetails['COMMISSION_PCT']); htmlentities($empdetails['EMPLOYEE_ID']); echo << Employee ID $eid First Name Last Name PAGE 47 Extending the Basic Employee Form Scroll to the bottom of the Employees page to view the Modify, Delete and Insert new employee buttons: 16. To insert a new employee, click Insert new employee: 17. When you create or modify employees you will see that the database definitions require the salary to be greater than zero, and the commission to be less than 1. The commission will be rounded to two decimal places. PAGE 48 Extending the Basic Employee Form Click Save. 18. When the new employee is successfully inserted, the web page is refreshed with the form listing all employees. Scroll the web page to the last record and check that the new employee row is present. The employee ID assigned to the new record may be different on your system to the one shown in the following example: 19. To modify the new employee, select the radio button next to the new employee row, click Modify: 20. PAGE 49 Combining Departments and Employees 21. Successfully updating the employee causes the Employee page to be redisplayed. Scroll to the last employee row and confirm that the new employee's salary is now 7,100: 22. To remove the new employee row, select the radio button for the new employee row, click Delete: On successful deletion, the deleted row does not appear in the list of employees records redisplayed in the Employees page: Combining Departments and Employees 1. Edit anyco.php. PAGE 50 Combining Departments and Employees substr(first_name,1,1) || '. '|| last_name as employee_name, hire_date, to_char(salary, '9999G999D99') as salary, nvl(commission_pct,0) as commission_pct FROM employees WHERE department_id = :did ORDER BY employee_id asc"; $deptid = $_SESSION['deptid']; 2. Edit anyco.php. PAGE 51 Combining Departments and Employees function construct_insert_emp() { $deptid = $_SESSION['deptid']; $conn = db_connect(); $query = "SELECT job_id, job_title FROM jobs ORDER BY job_title ASC"; $jobs = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_COLUMN); $query = "SELECT sysdate FROM dual"; $date = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_COLUMN); $emp = array( 'DEPARTMENT_ID' => $deptid, 'HIRE_DATE' => $date['SYSDATE'][0], 'ALLJOBIDS' => $jobs['JOB_ID'], 'ALLJOBTITLES' => $jobs['JOB_TITLE'] ); PAGE 52 Adding Error Recovery 10. Edit anyco_ui.php. In ui_print_employees() change the HTML form to enable it to call the department form: ... PAGE 53 Adding Error Recovery Most production systems would display_errors configuration option in the php.ini file set off, and log_errors set on. PHP's output buffering functionality can be used to trap error text during a function. Using ob_start() prevents text from displaying on the screen. If an error occurs ob_get_contents() allows the previously generated error messages to be stored in a string for later display or analysis. PAGE 54 Adding Error Recovery $bindvars = array()) {...} function db_execute_statement($conn, $statement, &$e, $bindvars = array()) {...} 4. Edit anyco_db.inc. In db_get_page_data() change the call to db_do_query() to pass down the error parameter $e: $r = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_ROW, $e, $bindvars); 5. Edit anyco.php. Add an @ prefix to all oci_* function calls. The @ prefix prevents errors from displaying because each return result is tested. PAGE 55 Adding Error Recovery AND l.country_id = c.country_id GROUP BY d.department_id, d.department_name, substr(e.first_name,1,1)||'. '||e.last_name, c.country_name ORDER BY d. PAGE 56 Adding Error Recovery array_push($bindargs, array('DID', $deptid, -1)); $emp = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_ROW, $err, $bindargs); if (!$emp) { handle_error('Cannot fetch Employees', $err); } else { $deptname = get_dept_name($conn, $deptid); ui_print_header('Employees: '.$deptname); ui_print_employees($emp, $_SERVER['SCRIPT_NAME']); ui_print_footer(date('Y-m-d H:i:s')); } } } 10. Edit anyco.php. Modify construct_insert_emp() to handle errors. PAGE 57 Adding Error Recovery handle_error('Connect Error', $err); } else { $emailid = $newemp['firstname']. PAGE 58 Adding Error Recovery } } } 13. Edit anyco.php. Change modify_emp() to handle errors. PAGE 59 Adding Error Recovery handle_error("Error deleting employee $empid", $err); } else { construct_employees(); } } } } 15. Edit anyco.php. Modify get_dept_name() to handle errors. PAGE 60 Further Error Handling 18. Click Next> to navigate to the last department record, the Accounting department with ID 110. Try to navigate past the last department record by clicking next. The error handling prevents navigation past the last department record. Further Error Handling Specific Oracle errors can be handled individully. PAGE 61 Further Error Handling 4. In the Employees page, click Insert new employee. 5. In the Insert New Employee page, enter employee details as shown setting the Department ID to 99. Click Save. The following error page is displayed: You can click Return to Departments to return to the Administration department page and then click Show Employees to verify that the new employee has not been added to the Administration department. PAGE 62 Further Error Handling 5-24 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft PAGE 63 6 Executing Stored Procedures and Functions This chapter shows you how to run stored procedures and functions using PHP and Oracle Express. It has the following topics: Q Using PL/SQL to Capture Business Logic Q Using PL/SQL Ref Cursors to Return Result Sets The Anyco application is extended with a PL/SQL function to calculate remuneration for each employee, and further extended with a PL/SQL procedure to return a ref cursor of employee records. PAGE 64 Using PL/SQL to Capture Business Logic 4. In the SQL Commands page, to assign the create procedure privilege to the HR user enter the following grant command: grant create procedure to hr; Click Run: A message similar to the following appears in the Results section below the text area where the command was entered: 6-2 5. Click the Logout link to terminate the HTMLDB session. 6. PAGE 65 Using PL/SQL to Capture Business Logic 7. In the Oracle Database XE Login page, enter the Username hr and Password hr. Click Login: 8. In the Home page, click the arrow on the SQL icon, move the mouse over SQL Commands and click Enter Command: 9. PAGE 66 Using PL/SQL to Capture Business Logic In the results window, confirm that the function is created: 10. Create the chap6 directory and copy the application files from chap5: mkdir $HOME/public_html/chap6 cp $HOME/public_html/chap5/* $HOME/public_html/chap6 cd $HOME/public_html/chap6 11. Edit anyco.php. Modify the query in construct_employees() to call the PL/SQL function for each row returned: $query = "SELECT employee_id, substr(first_name,1,1) || '. PAGE 67 Using PL/SQL Ref Cursors to Return Result Sets END; // Write one row per employee foreach ($employeerecords as $emp) { echo ''; echo ''; echo ''.htmlentities($emp['EMPLOYEE_ID']).''; echo ''.htmlentities($emp['EMPLOYEE_NAME']).''; echo ''.htmlentities($emp['HIRE_DATE']).''; echo ''.htmlentities($emp['SALARY']).''; echo ''. PAGE 68 Using PL/SQL Ref Cursors to Return Result Sets 1. In the SQL Commands page, as the HR user, create the following PL/SQL package specification: CREATE OR REPLACE PACKAGE cv_types AS TYPE empinfotyp IS REF CURSOR; PROCEDURE get_employees(deptid in number, employees in out empinfotyp); END cv_types; Click Run: In the Results section, confirm the package specification is successfully created: 2. PAGE 69 Using PL/SQL Ref Cursors to Return Result Sets In the Results section, confirm the package body is successfully created: 3. Edit anyco_db.inc. Create a new PHP function that calls the PL/SQL packaged procedure: // Use ref cursor to fetch employee records // All records are retrieved - there is no paging in this example function db_get_employees_rc($conn, $deptid, &$e) { // Excute the call to the stored procedure $stmt = "BEGIN cv_types. PAGE 70 Using PL/SQL Ref Cursors to Return Result Sets if (!$r) { $e = db_error($refcur, __FILE__, __LINE__); return false; } return ($employeerecords); } The db_get_employees_rc() function executes the following anonymous (unnamed) PL/SQL block: BEGIN cv_types.get_employees($deptid, :rc); END; The PL/SQL statement inside the "BEGIN END" block calls the stored PL/SQL package procedure cv_types.et_employees(). This returns an OCI_B_CURSOR ref cursor bind variable in the PHP variable $refcur. PAGE 71 Using PL/SQL Ref Cursors to Return Result Sets 7. In the Marketing department page, click Show Employees. In the Employees page for the Marketing department, the employee records remuneration is displayed in the last column: 8. In the SQL Commands page, to log out of the HR database session, click the Logout link. PAGE 72 Using PL/SQL Ref Cursors to Return Result Sets 6-10 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft PAGE 73 7 Loading Images This chapter shows you how to change the application to upload a thumbnail picture for new employees and display it on the Employees page. It has the following topics: Q Using Oracle LOBs to Store and Load Employee Images Q Resizing Images Using Oracle LOBs to Store and Load Employee Images 1. Create the chap7 directory and copy the application files from chap6: mkdir $HOME/public_html/chap7 cp $HOME/public_html/chap6/* $HOME/public_html/chap7 cd $HOME/public_html/chap7 2. PAGE 74 Using Oracle LOBs to Store and Load Employee Images 5. In the SQL Commands page, enter the following CREATE TABLE statement: CREATE TABLE employee_photos( employee_id NUMBER, employee_thumbnail BLOB); Click Run: 6. In the Results section below the command text area, confirm that the table is successfully created: The HR user must have the CREATE TABLE privilege to perform this command. PAGE 75 Using Oracle LOBs to Store and Load Employee Images Commission(%) Remuneration Photograph The data for the Photograph column is populated with an tag whose src attribute is defined as a URL reference to a new anyco_im.php file, which will display the image for each employee. 8. Edit anyco_ui.inc. Add code in ui_print_employees() to generate an tag referencing the anyco_im.php file with the employee identifier as a parameter: echo '' . PAGE 76 Using Oracle LOBs to Store and Load Employee Images WHERE employee_id = :eid'; $stid = oci_parse($conn, $query); $r = oci_bind_by_name($stid, ":eid", $empid, -1); if (!$r) { return; } $r = oci_execute($stid, OCI_DEFAULT); if (!$r) { return; } $arr = oci_fetch_row($stid); if (!$arr) { return; } // photo not found $result = $arr[0]->load(); // If any text (or whitespace!) is printed before this header is sent, // the text won't be displayed. The image also won't display properly. PAGE 77 Using Oracle LOBs to Store and Load Employee Images if (!$r) { $e = db_error($stid, __FILE__, __LINE__); return false; } if (!$lob->savefile($imgfile)) { $e = db_error($stid, __FILE__, __LINE__); return false; } $lob->free(); return true; } To tie the new EMPLOYEE_PHOTOS and EMPLOYEES tables together we need to use the same employee id in both. 12. Edit anyco_db.inc. Change the $bindvars parameter in db_execute_statement() to &$bindvars so that OUT bind variable values are returned from the database. PAGE 78 Using Oracle LOBs to Store and Load Employee Images 14. Edit anyco.php. In insert_new_emp(), add a call between the db_execute_statement() and construct_employees() calls to insert the thumbnail picture: $r = db_execute_statement($conn, $statement, $err, $bindargs); if ($r) { $r = db_insert_thumbnail($conn, $bindargs['NEWEID'], $_FILES['empphoto']['tmp_name'], $e); construct_employees(); } 15. In a browser, enter the following application URL: http://localhost/~/chap7/anyco.php 16. PAGE 79 Using Oracle LOBs to Store and Load Employee Images 19. In the File Upload window, browser for and select an image file. Click Open: 20. PAGE 80 Resizing Images On success, the Employees page is displayed with the new employee including the image, which is displayed at its original size: Resizing Images The Employee thumbnails can be resized with PHP's GD graphicsextension. 1. To turn on the graphic extension, enter the following URL in your browser to access the Zend Core for Oracle Console: http://localhost/ZendCore 7-8 2. PAGE 81 Resizing Images 5. In the Extension sub-tab page, expand the Zend Core Extensions tree control. Locate the gd -GD (Image Manipulation) entry and change its switch to on or enabled. 6. In the Extension sub-tab page, to save the configuration changes click the Save Setting link. 7. In the Extension sub-tab page, to restart the web server click the Restart Server link. 8. To logout of the Zend Core for Oracle Console, click the Logout link. 9. Edit anyco_db.inc. PAGE 82 Resizing Images 12. In the Employees page, to insert a new employee click Insert new employee: 13. Enter your new employee details or use the values shown. To browse for an employee image click Browse: 14. Locate and select an image with a size larger than 100 pixels. PAGE 83 Resizing Images 15. PAGE 84 Resizing Images 7-12 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft PAGE 85 8 Building Global Applications This chapter discusses global application development in a PHP and Oracle Database Express environment. It addresses the basic tasks associated with developing and deploying global Internet applications, including developing locale awareness, constructing HTML content in the user preferred language, and presenting data following the cultural conventions of the user's locale. PAGE 86 String Manipulation The language and territory settings control Oracle behaviors such as the Oracle date format, error message language and the rules used for sort order. The character set AL32UTF8 is Oracle's name for UTF-8. Oracle Database Express Edition installation guides for information on the NLS_LANG environment variable. See also: When Zend Core for Oracle is installed on Apache, NLS_LANG can be set in /etc/profile: export NLS_LANG GERMAN_GERMANY. PAGE 87 Developing Locale Awareness Determining User's Locale In a global environment, your application will need to accept users with different locale preferences. The application need to determine the user's preferred locale. Once that is known, the application should construct HTML content in the language of the locale, and follows the cultural conventions implied by the locale. One of the most common methods in determining a user's locale, is based on the default ISO locale setting of the user's browser. PAGE 88 Encoding HTML Pages Table 8–1 (Cont.) Locale Representations in ISO, SQL and PL/SQL Programming Locale Locale ID NLS_LANGUAGE NLS_TERRITORY Portuguese pt PORTUGUESE PORTUGAL Spanish es SPANISH SPAIN Encoding HTML Pages The encoding of an HTML page is important information for a browser and an Internet application. You can think of the page encoding as the character set used for the locale that an Internet application is serving. PAGE 89 Presenting Data following User's Locale Convention This setting does not imply any conversion of outgoing pages. Your application must ensure the server generated pages are encoded in UTF-8. Organizing the Content of HTML Pages for Translation Making the user interface available in the user's local language is one of the fundamental task in globalizing an application. PAGE 90 Presenting Data following User's Locale Convention Oracle Date Formats There are three different date presentation formats in Oracle Database Express Edition, they are standard, short, and long dates. The examples below, illustrate the differences between the short data and long date formats for both United States and Germany. SQL> alter session set nls_territory=america nls_language=american; Session altered. SQL> 2 3 4 5 6* select employee_id EmpID, substr(first_name,1,1)||'. PAGE 91 Presenting Data following User's Locale Convention EMPID ---------100 101 102 103 104 EmpName Salary --------------------------- ---------S.King 24,000.00 N.Kochhar 17,000.00 L.De Haan 17,000.00 A.Hunold 9,000.00 B.Ernst 6,000.00 SQL> alter session set nls_territory=germany; Session altered. SQL> 2 3 4 5* select employee_id EmpID, substr(first_name,1,1)||'. PAGE 92 Presenting Data following User's Locale Convention 3 from employees 4 where last_name like 'C%' 5* order by last_name EMPID ---------187 148 154 119 110 188 Last Name ------------------------Cabrio Cambrault Cambrault Colmenares Chen Chung 6 rows selected. Oracle Error Messages The NLS_LANGUAGE parameter also controls the language of the database error messages being returned from the database. PAGE 93 Index Symbols E $HOME/public_html, 1-3 EMPLOYEES, 1-1 A F AnyCo Corp tutorial application, 1-1 anyco_ui.inc creating, 3-1 Apache creating public_html, 2-4 httpd.conf configuration file, 2-3 obtaining, 2-1 public_html, 2-3 restarting, 2-4 starting, 2-4 testing installation, 2-3 Apache Web server, 1-3 application naming convention, 1-2 files HR application, 3-1 functions ui_print_footer(), 3-1 ui_print_header(), 3-1 C configuration Apache httpd. PAGE 94 prerequisites, 2-1 testing availability, configuration tab, 2-6 configuring, 2-6 GUI password, 2-5 hello.
Extending the Basic Employee Form Scroll to the bottom of the Employees page to view the Modify, Delete and Insert new employee buttons: 16. To insert a new employee, click Insert new employee: 17. When you create or modify employees you will see that the database definitions require the salary to be greater than zero, and the commission to be less than 1. The commission will be rounded to two decimal places.
Extending the Basic Employee Form Click Save. 18. When the new employee is successfully inserted, the web page is refreshed with the form listing all employees. Scroll the web page to the last record and check that the new employee row is present. The employee ID assigned to the new record may be different on your system to the one shown in the following example: 19. To modify the new employee, select the radio button next to the new employee row, click Modify: 20.
Combining Departments and Employees 21. Successfully updating the employee causes the Employee page to be redisplayed. Scroll to the last employee row and confirm that the new employee's salary is now 7,100: 22. To remove the new employee row, select the radio button for the new employee row, click Delete: On successful deletion, the deleted row does not appear in the list of employees records redisplayed in the Employees page: Combining Departments and Employees 1. Edit anyco.php.
Combining Departments and Employees substr(first_name,1,1) || '. '|| last_name as employee_name, hire_date, to_char(salary, '9999G999D99') as salary, nvl(commission_pct,0) as commission_pct FROM employees WHERE department_id = :did ORDER BY employee_id asc"; $deptid = $_SESSION['deptid']; 2. Edit anyco.php.
Combining Departments and Employees function construct_insert_emp() { $deptid = $_SESSION['deptid']; $conn = db_connect(); $query = "SELECT job_id, job_title FROM jobs ORDER BY job_title ASC"; $jobs = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_COLUMN); $query = "SELECT sysdate FROM dual"; $date = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_COLUMN); $emp = array( 'DEPARTMENT_ID' => $deptid, 'HIRE_DATE' => $date['SYSDATE'][0], 'ALLJOBIDS' => $jobs['JOB_ID'], 'ALLJOBTITLES' => $jobs['JOB_TITLE'] );
Adding Error Recovery 10. Edit anyco_ui.php. In ui_print_employees() change the HTML form to enable it to call the department form: ...
Adding Error Recovery Most production systems would display_errors configuration option in the php.ini file set off, and log_errors set on. PHP's output buffering functionality can be used to trap error text during a function. Using ob_start() prevents text from displaying on the screen. If an error occurs ob_get_contents() allows the previously generated error messages to be stored in a string for later display or analysis.
Adding Error Recovery $bindvars = array()) {...} function db_execute_statement($conn, $statement, &$e, $bindvars = array()) {...} 4. Edit anyco_db.inc. In db_get_page_data() change the call to db_do_query() to pass down the error parameter $e: $r = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_ROW, $e, $bindvars); 5. Edit anyco.php. Add an @ prefix to all oci_* function calls. The @ prefix prevents errors from displaying because each return result is tested.
Adding Error Recovery AND l.country_id = c.country_id GROUP BY d.department_id, d.department_name, substr(e.first_name,1,1)||'. '||e.last_name, c.country_name ORDER BY d.
Adding Error Recovery array_push($bindargs, array('DID', $deptid, -1)); $emp = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_ROW, $err, $bindargs); if (!$emp) { handle_error('Cannot fetch Employees', $err); } else { $deptname = get_dept_name($conn, $deptid); ui_print_header('Employees: '.$deptname); ui_print_employees($emp, $_SERVER['SCRIPT_NAME']); ui_print_footer(date('Y-m-d H:i:s')); } } } 10. Edit anyco.php. Modify construct_insert_emp() to handle errors.
Adding Error Recovery handle_error('Connect Error', $err); } else { $emailid = $newemp['firstname'].
Adding Error Recovery } } } 13. Edit anyco.php. Change modify_emp() to handle errors.
Adding Error Recovery handle_error("Error deleting employee $empid", $err); } else { construct_employees(); } } } } 15. Edit anyco.php. Modify get_dept_name() to handle errors.
Further Error Handling 18. Click Next> to navigate to the last department record, the Accounting department with ID 110. Try to navigate past the last department record by clicking next. The error handling prevents navigation past the last department record. Further Error Handling Specific Oracle errors can be handled individully.
Further Error Handling 4. In the Employees page, click Insert new employee. 5. In the Insert New Employee page, enter employee details as shown setting the Department ID to 99. Click Save. The following error page is displayed: You can click Return to Departments to return to the Administration department page and then click Show Employees to verify that the new employee has not been added to the Administration department.
Further Error Handling 5-24 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft
6 Executing Stored Procedures and Functions This chapter shows you how to run stored procedures and functions using PHP and Oracle Express. It has the following topics: Q Using PL/SQL to Capture Business Logic Q Using PL/SQL Ref Cursors to Return Result Sets The Anyco application is extended with a PL/SQL function to calculate remuneration for each employee, and further extended with a PL/SQL procedure to return a ref cursor of employee records.
Using PL/SQL to Capture Business Logic 4. In the SQL Commands page, to assign the create procedure privilege to the HR user enter the following grant command: grant create procedure to hr; Click Run: A message similar to the following appears in the Results section below the text area where the command was entered: 6-2 5. Click the Logout link to terminate the HTMLDB session. 6.
Using PL/SQL to Capture Business Logic 7. In the Oracle Database XE Login page, enter the Username hr and Password hr. Click Login: 8. In the Home page, click the arrow on the SQL icon, move the mouse over SQL Commands and click Enter Command: 9.
Using PL/SQL to Capture Business Logic In the results window, confirm that the function is created: 10. Create the chap6 directory and copy the application files from chap5: mkdir $HOME/public_html/chap6 cp $HOME/public_html/chap5/* $HOME/public_html/chap6 cd $HOME/public_html/chap6 11. Edit anyco.php. Modify the query in construct_employees() to call the PL/SQL function for each row returned: $query = "SELECT employee_id, substr(first_name,1,1) || '.
Using PL/SQL Ref Cursors to Return Result Sets
Using PL/SQL Ref Cursors to Return Result Sets 1. In the SQL Commands page, as the HR user, create the following PL/SQL package specification: CREATE OR REPLACE PACKAGE cv_types AS TYPE empinfotyp IS REF CURSOR; PROCEDURE get_employees(deptid in number, employees in out empinfotyp); END cv_types; Click Run: In the Results section, confirm the package specification is successfully created: 2.
Using PL/SQL Ref Cursors to Return Result Sets In the Results section, confirm the package body is successfully created: 3. Edit anyco_db.inc. Create a new PHP function that calls the PL/SQL packaged procedure: // Use ref cursor to fetch employee records // All records are retrieved - there is no paging in this example function db_get_employees_rc($conn, $deptid, &$e) { // Excute the call to the stored procedure $stmt = "BEGIN cv_types.
Using PL/SQL Ref Cursors to Return Result Sets if (!$r) { $e = db_error($refcur, __FILE__, __LINE__); return false; } return ($employeerecords); } The db_get_employees_rc() function executes the following anonymous (unnamed) PL/SQL block: BEGIN cv_types.get_employees($deptid, :rc); END; The PL/SQL statement inside the "BEGIN END" block calls the stored PL/SQL package procedure cv_types.et_employees(). This returns an OCI_B_CURSOR ref cursor bind variable in the PHP variable $refcur.
Using PL/SQL Ref Cursors to Return Result Sets 7. In the Marketing department page, click Show Employees. In the Employees page for the Marketing department, the employee records remuneration is displayed in the last column: 8. In the SQL Commands page, to log out of the HR database session, click the Logout link.
Using PL/SQL Ref Cursors to Return Result Sets 6-10 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft
7 Loading Images This chapter shows you how to change the application to upload a thumbnail picture for new employees and display it on the Employees page. It has the following topics: Q Using Oracle LOBs to Store and Load Employee Images Q Resizing Images Using Oracle LOBs to Store and Load Employee Images 1. Create the chap7 directory and copy the application files from chap6: mkdir $HOME/public_html/chap7 cp $HOME/public_html/chap6/* $HOME/public_html/chap7 cd $HOME/public_html/chap7 2.
Using Oracle LOBs to Store and Load Employee Images 5. In the SQL Commands page, enter the following CREATE TABLE statement: CREATE TABLE employee_photos( employee_id NUMBER, employee_thumbnail BLOB); Click Run: 6. In the Results section below the command text area, confirm that the table is successfully created: The HR user must have the CREATE TABLE privilege to perform this command.
Using Oracle LOBs to Store and Load Employee Images
Using Oracle LOBs to Store and Load Employee Images WHERE employee_id = :eid'; $stid = oci_parse($conn, $query); $r = oci_bind_by_name($stid, ":eid", $empid, -1); if (!$r) { return; } $r = oci_execute($stid, OCI_DEFAULT); if (!$r) { return; } $arr = oci_fetch_row($stid); if (!$arr) { return; } // photo not found $result = $arr[0]->load(); // If any text (or whitespace!) is printed before this header is sent, // the text won't be displayed. The image also won't display properly.
Using Oracle LOBs to Store and Load Employee Images if (!$r) { $e = db_error($stid, __FILE__, __LINE__); return false; } if (!$lob->savefile($imgfile)) { $e = db_error($stid, __FILE__, __LINE__); return false; } $lob->free(); return true; } To tie the new EMPLOYEE_PHOTOS and EMPLOYEES tables together we need to use the same employee id in both. 12. Edit anyco_db.inc. Change the $bindvars parameter in db_execute_statement() to &$bindvars so that OUT bind variable values are returned from the database.
Using Oracle LOBs to Store and Load Employee Images 14. Edit anyco.php. In insert_new_emp(), add a call between the db_execute_statement() and construct_employees() calls to insert the thumbnail picture: $r = db_execute_statement($conn, $statement, $err, $bindargs); if ($r) { $r = db_insert_thumbnail($conn, $bindargs['NEWEID'], $_FILES['empphoto']['tmp_name'], $e); construct_employees(); } 15. In a browser, enter the following application URL: http://localhost/~/chap7/anyco.php 16.
Using Oracle LOBs to Store and Load Employee Images 19. In the File Upload window, browser for and select an image file. Click Open: 20.
Resizing Images On success, the Employees page is displayed with the new employee including the image, which is displayed at its original size: Resizing Images The Employee thumbnails can be resized with PHP's GD graphicsextension. 1. To turn on the graphic extension, enter the following URL in your browser to access the Zend Core for Oracle Console: http://localhost/ZendCore 7-8 2.
Resizing Images 5. In the Extension sub-tab page, expand the Zend Core Extensions tree control. Locate the gd -GD (Image Manipulation) entry and change its switch to on or enabled. 6. In the Extension sub-tab page, to save the configuration changes click the Save Setting link. 7. In the Extension sub-tab page, to restart the web server click the Restart Server link. 8. To logout of the Zend Core for Oracle Console, click the Logout link. 9. Edit anyco_db.inc.
Resizing Images 12. In the Employees page, to insert a new employee click Insert new employee: 13. Enter your new employee details or use the values shown. To browse for an employee image click Browse: 14. Locate and select an image with a size larger than 100 pixels.
Resizing Images 15.
Resizing Images 7-12 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft
8 Building Global Applications This chapter discusses global application development in a PHP and Oracle Database Express environment. It addresses the basic tasks associated with developing and deploying global Internet applications, including developing locale awareness, constructing HTML content in the user preferred language, and presenting data following the cultural conventions of the user's locale.
String Manipulation The language and territory settings control Oracle behaviors such as the Oracle date format, error message language and the rules used for sort order. The character set AL32UTF8 is Oracle's name for UTF-8. Oracle Database Express Edition installation guides for information on the NLS_LANG environment variable. See also: When Zend Core for Oracle is installed on Apache, NLS_LANG can be set in /etc/profile: export NLS_LANG GERMAN_GERMANY.
Developing Locale Awareness Determining User's Locale In a global environment, your application will need to accept users with different locale preferences. The application need to determine the user's preferred locale. Once that is known, the application should construct HTML content in the language of the locale, and follows the cultural conventions implied by the locale. One of the most common methods in determining a user's locale, is based on the default ISO locale setting of the user's browser.
Encoding HTML Pages Table 8–1 (Cont.) Locale Representations in ISO, SQL and PL/SQL Programming Locale Locale ID NLS_LANGUAGE NLS_TERRITORY Portuguese pt PORTUGUESE PORTUGAL Spanish es SPANISH SPAIN Encoding HTML Pages The encoding of an HTML page is important information for a browser and an Internet application. You can think of the page encoding as the character set used for the locale that an Internet application is serving.
Presenting Data following User's Locale Convention This setting does not imply any conversion of outgoing pages. Your application must ensure the server generated pages are encoded in UTF-8. Organizing the Content of HTML Pages for Translation Making the user interface available in the user's local language is one of the fundamental task in globalizing an application.
Presenting Data following User's Locale Convention Oracle Date Formats There are three different date presentation formats in Oracle Database Express Edition, they are standard, short, and long dates. The examples below, illustrate the differences between the short data and long date formats for both United States and Germany. SQL> alter session set nls_territory=america nls_language=american; Session altered. SQL> 2 3 4 5 6* select employee_id EmpID, substr(first_name,1,1)||'.
Presenting Data following User's Locale Convention EMPID ---------100 101 102 103 104 EmpName Salary --------------------------- ---------S.King 24,000.00 N.Kochhar 17,000.00 L.De Haan 17,000.00 A.Hunold 9,000.00 B.Ernst 6,000.00 SQL> alter session set nls_territory=germany; Session altered. SQL> 2 3 4 5* select employee_id EmpID, substr(first_name,1,1)||'.
Presenting Data following User's Locale Convention 3 from employees 4 where last_name like 'C%' 5* order by last_name EMPID ---------187 148 154 119 110 188 Last Name ------------------------Cabrio Cambrault Cambrault Colmenares Chen Chung 6 rows selected. Oracle Error Messages The NLS_LANGUAGE parameter also controls the language of the database error messages being returned from the database.
Index Symbols E $HOME/public_html, 1-3 EMPLOYEES, 1-1 A F AnyCo Corp tutorial application, 1-1 anyco_ui.inc creating, 3-1 Apache creating public_html, 2-4 httpd.conf configuration file, 2-3 obtaining, 2-1 public_html, 2-3 restarting, 2-4 starting, 2-4 testing installation, 2-3 Apache Web server, 1-3 application naming convention, 1-2 files HR application, 3-1 functions ui_print_footer(), 3-1 ui_print_header(), 3-1 C configuration Apache httpd.
prerequisites, 2-1 testing availability, configuration tab, 2-6 configuring, 2-6 GUI password, 2-5 hello.