Home

Oracle B25-317-01 User's Manual

1. 5 2 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Extending the Basic Employee Form function construct employees Squery 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 Semp db do query conn query OCI FETCHSTATEMENT BY ROW ui print header Employees ui print employees S emp SERVER SCRIPT NAME ui print footer date Y m d H i s Edit anyco php Add insert new emp to insert an employee into the EMPLOYEES table function insert new emp newemp _POST statement INSERT INTO employees employee id first name last name email hire date job id salary commission pct department id VALUES employees seq nextval fnm lnm eml hdt jid sal cpt did conn db connect emailid newemp firstname newemp lastname bindargs array array push bindargs array FNM newemp firstname 1 array push bindargs array LNM newemp lastname 1 array push bindargs array EML emailid 1 array push bindargs array HDT newemp hiredate 1 array push bindargs array JID newemp jobid 1 array push bindargs array SAL newemp salary 1 array push bindargs arr
2. I 7 Editanyco ui inc Adda Photograph column to the EMPLOYEES table in ui print employees 7 2 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Using Oracle LOBs to Store and Load Employee Images lt th gt Commission lt br gt lt th gt lt th gt Remuneration lt th gt lt th gt Photograph lt th gt The data for the Photograph column is populated with an lt img gt 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 codeinui print employees to generate an lt img gt tag referencing the anyco_im php file with the employee identifier as a parameter echo td align right gt htmlentities S emp REMUNERATION lt td gt echo td img src anyco_im php showempphoto emp EMPLOYEE_ID alt Employee photo gt lt td gt 9 Edit anyco_ui inc To enable images to be uploaded when a new employee is created add an enctype attribute to the lt form gt tag in ui print insert employee form method post action posturl enctype multipart form data gt At the bottom of the form add an upload field with input type of file tr td Commission td lt td gt lt input type text name commpct value 0 size 20 gt lt td gt lt tr gt lt tr gt lt td gt Photo lt td gt lt td gt lt input type file name empphot
3. 4 Editanyco db inc Indb 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 Editanyco php Add an 6 prefix to all oci function calls The prefix prevents errors from displaying because each return result is tested Preventing errors from displaying can hide incorrect parameter usage which may hinder testing the changes in this section 6 Editanyco php Create a function to handle the error information function handle error message err ui_print_header message ui print error err SERVER SCRIPT NAME ui print footer date Y m d H i s 7 Edit anyco php Modify all calls to db functions to include the additional error parameter a Change all db connect calls to do connect err Change all db do query calls and insert a err parameter as the fourth parameter Change the db_get_page_data call and insert a err parameter as the fifth parameter Changethedb execute statement calls and insert a err parameter as the third parameter 8 Edit anyco php Modify construct departments to handle errors returned The function becomes function construct departments if isset SESSION currentdept amp amp isset _POST prevdept amp amp SESSION currentdept 1 current SESSION currentdept 1 elseif isset SESSION curre
4. Apache HTTP Server You are free to use the image below on web sites powered by the onf 2 Inthe default Apache Web server configuration file set up a public virtual directory as public html for accessing your PHP files By using your preferred editor open the Apache configuration file etc httpd conf httpd conf and remove the character at the start of the line with the following directive UserDir public html This enables a browser to make a HTTP reque system and to serve files from the users SHOMI example http 1localhost user st using a registered user on the E public html directory For For example your Apache httpd conf file should contain the following lines IfModule mod userdir c UserDir is disabled by default since it can confirm the presence of a username on the system depending on home directory permissions UserDir disable To enable requests to user to serve the user s public_html directory remove the UserDir disable line above and uncomment Beta Draft Getting Started 2 3 Setting Up Zend Core for Oracle the following line instead UserDir public_html lt IfModule gt 3 Ina command window to use the new Apache configuration file restart Apache by entering the following commands su Password lt enter your su root password gt apachectl restart root frodo Eile Edit View Terminal Tabs Help gstokol frodo
5. Department Administration gird E Number of Manager Location Employees Name Administration 1 J Whalen United States of America lt Previous Next gt Show Employees 3 k Any Co 2005 10 10 16 37 29 4 Inthe Employees page click Insert new employee Employees Administration Commission 200 J Whalen 17 SEP 87 4 400 00 Modify Delete Insert new employee X Return to Departments gpa Sir Hiredate Salary Any Co 2005 10 10 16 37 54 5 Inthe Insert New Employee page enter employee details as shown setting the Department ID to 99 Click Save Insert New Employee Last Name Person Hiredate hoocro 05 2005 10 10 16 38 06 The following error page is displayed Any Co Department 99 does not yet exist Error at line 86 of home gstokol public html chap5 anyco db inc Return to Departments 2005 10 10 16 39 15 ORA 02291 integrity constraint HR EMP_DEPT_Fk violated parent key not found Any Co 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 Beta Draft Updating Data 5 23 Further Error Handling 5 24 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft 6 Executing Stored Procedures and Functions
6. This chapter shows you how to run stored procedures and functions using PHP and Oracle Express It has the following topics a Using PL SQL to Capture Business Logic a 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 Oracle PL SQL procedures and functions allow business logic to be stored in the database for any client program to use They also reduce the amount of data that needs to be transferred between the database and PHP To display the total remuneration of each employee perform the following steps to create a PL SQL function stored in the database 1 Ina browser enter the URL for your Oracle Database Express Edition HTMLDB page http localhost 8080 htmldb 2 At the login screen in the Username field enter system and in Password field enter manager or the password you entered at the prompt during configuration of the Oracle Database Express Edition Click Login ORACLE DATABASE Username syste Password O Homepage O Discussion Forum O Documentation o Registration 3 Inthe Home page click the arrow on the SOL icon move the mouse over SOL Commands and click Enter Command Beta Draft Executing Stored Procedures and Functions 6 1 Using PL SQL to Cap
7. td echo td htmlentities S emp EMPLOYEE NAME td echo td htmlentities S emp HIRE DATE td echo td align right gt htmlentities emp SALARY td echo td align right gt htmlentities emp COMMISSION PCT td echo lt tr gt echo END table END Save the changes to anyco php and anyco ui inc Test the result of these changes by entering the following URL in your Web browser http 1localhost username chap4 anyco php Examine the result page and scroll down to view all the employee rows displayed in the page Beta Draft Querying Data 4 13 Building the Basic Employee Form Employees Employee Employee Hiedsie Salary Commission Name psu acc sa com IET V Pataballa 05 FEB 98 N Greenberg i LLLSLLLLE 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 Extending the Basic Employee Form Combining Departments and Employees Adding Error Recovery Further Error Handling Extending the Basic Employee Form To enable employees records to manipulated perform the following tasks 1 Create the chap5 directory and copy application files from chap4 mkdir HOME public_html chap5 cp HOME public_html cha
8. Oracle Database Express Edition Developer Center on the Oracle Technology Network at http www oracle com technology xe PHP Developer Center on the Oracle Technology Network at http www oracle com technology tech php index html Zend Core for Oracle Developer Center at http www oracle com technology tech php zendcore index html Oracle Database Express Edition documentation on the Oracle Technology Network at http www oracle com technology xe documentation a The Oracle Database Documentation Library on the Oracle Technology Network at http www oracle com technology documentation Beta Draft Introducing PHP with Oracle Database XE 1 3 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 What You Need Testing the Oracle Database XE Installation Testing the Apache Installation Setting Up Zend Core for Oracle Testing the Zend Core for Oracle Installation What You Need Oracle Database Express Edition 10gR2 Apache 13 x or later Zend Core for Oracle Texteditor for editing PHP code Obtaining Oracle Database Express Edition Oracle Database XE Oracle Database Express Edition is available from the Oracle Technology Network at http www oracle com technolog
9. SELECT sysdate FROM dual date db do query conn query OCI FETCHSTATEMENT BY COLUMN Semp array DEPARTMENT ID gt deptid HIRE DATE gt Sdate SYSDATE 0 ALLJOBIDS gt jobs JOB ID ALLJOBTITLES jobs JOB TITLE i ui_print_header Insert New Employee ui_print_insert_employee Semp SERVER SCRIPT NAME ui print footer date Y m d H i s Edit anyco php Modify the final else statement in the HTML form handler The handler becomes Start form handler code if isset POST insertemp construct insert emp elseif isset POST saveinsertemp insert new emp elseif isset POST modifyemp construct modify emp elseif isset POST savemodifiedemp modify emp elseif isset _POST deleteemp delete_emp elseif isset _POST showemp isset _POST prevemp isset _POST showemp construct employees elseif isset POST nextdept isset _POST prevdept isset _POST firstdept isset _POST showdept construct departments else construct departments Editanyco ui php Inui print department change the HTML form to enable it to call the employee form form method post action posturl gt lt input type submit value First name firstdept gt lt input type submit value lt Previous name prevdept gt Beta Draft Updating Data
10. mkdir HOME public_html chap6 cp HOME public_html chap5 HOME public_html chap6 cd HOME public_html chap6 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 last_name as employee_name hire_date to_char salary 9999G999D99 as salary nvl commission pct 0 as commission pct to char calc remuneration salary commission pct 9999G999D99 as remuneration FROM employees WHERE department id did ORDER BY employee id ASC Edit anyco ui inc Inui print employees add a Remuneration column to the table and modify the foreach loop to display the remuneration field for each employee echo END form method post action posturl gt lt table gt lt tr gt lt th gt amp nbsp lt th gt lt th gt Employee lt br gt ID lt th gt th Employee br Name th lt th gt Hiredate lt th gt lt th gt Salary lt th gt lt th gt Commission lt br gt th lt th gt Remuneration lt th gt 6 4 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Using PL SQL Ref Cursors to Return Result Sets lt tr gt END Write one row per employee foreach Semployeerecords as Semp echo lt tr gt echo lt td gt lt input type radio name emprec value htmlentities S emp EMPLOYEE ID td echo td align right gt htmlentities Se
11. sess nennen S E S ene ns 8 3 Encoding HIMI Pages tato eu reete dnt iet eres p eoe ettet peer at epa tor etel roue eere P ES 8 4 Specifying the Page Encoding for HTML Pages sess eee eene enne 8 4 Specifying the Page Encoding in PHP sss nennen 8 4 Organizing the Content of HTML Pages for Translation esee 8 5 Stings in PHP assistente a db bd a o e ob boten b Eee d on oreet 8 5 Static EIes tanc LAN UT t En nce a cM EN NU DC KA Reim LE 8 5 pata from the Database nae a ee Se tentia 8 5 Presenting Data following User s Locale Convention see 8 5 Gracle DateXPormatS u teer beoe cadi en cdlseird ise tiet a eb eet ieety 8 6 Oracle INumber Eormalts ee elect etx etie mee a a lebe ete tired eecee mis 8 6 Oracle Einguistc Sorts 2 2 incerto pA eese fea Hn eet tete ah 8 7 Oracle Error M S583g65 cote seeded eie cesi ita ettet e eee eee eere ne 8 8 Index iv Beta Draft Audience 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 Audience Documentation Accessibility Related Documents a Conventions 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 This document assumes a bas
12. 14 Edit anyco php In insert new emp add a call between the statement and construct employees calls to insert the db execute thumbnail picture r db execute statement conn if r r db insert thumbnail conn bindargs NEWEID FILES empphoto tmp name e statement Serr bindargs construct employees 15 Ina browser enter the following application URL http localhost lt username gt chap7 anyco php 16 In the Departments page click Show Employees to navigate to the Employees page Department Administration Number of Manager Location Employees Name Administration 1 J Whalen United States of America lt Previous Next Show Employees yy A OU sag Any Co 2005 10 11 11 18 29 17 In the Employees page to insert a new employee click Insert new employee Employees Administration ors TN Hiredate Salary eg Remuneration Photograph o Employee J Whalen 17 SEP 87 4 400 00 52 800 00 photo Modify Delete Insert per empyes Return to Departments Any Co 2005 10 11 11 19 31 18 The Insert New Employee form allows you to choose a thumbnail image on your system to be uploaded to the database Enter your own values in the fields or use the values as shown Click Browse 7 6 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Using Oracle LOBs to Store and Load Employee Images In
13. O Homep age O Discussion Forum O Documentation o Registration 8 Inthe Home page click the arrow on the SOL icon move the mouse over SOL Commands and click Enter Command ORACLE X E DATABASE User HR Home aa uta qme O Discussion Forum o L Application Builder Object Browser Documentation 5 E 5 O Registration mi J SQL Commands 9 Inthe SQL Commands page enter the following text to create a calc remuneration function create or replace function calc remuneration salary IN number commission pct IN number return number is begin return salary 12 salary 12 nvl commission pct 0 end Click Run Beta Draft Executing Stored Procedures and Functions 6 3 Using PL SQL to Capture Business Logic DATABASE ORACLE X E Q Q User HR Home SQL SQL Commands Autocommit Display 10 create or replace function calc remuneration salary IN number commission pct IN number return number is egin return salary 12 salary 12 nvl commission pct 0 end Results Explain Describe SavedSQL History Enter SQL or PL SQL and click Run to see the results z In the results window confirm that the function is created Results Explain Describe SavedSQL History Function created 0 22 seconds 10 11 12 Create the chap6 directory and copy the application files from chap5
14. Previous Next ote Any Co 2005 10 11 12 29 43 12 In the Employees page to insert a new employee click Insert new employee Employees Administration Hiredate Salary E ID Name 200 J Whalen 17 SEP 87 4 400 00 0 52 800 00 C 1209 8 000 00 96 000 00 Modify Delete Insert new empioyes Return to Departments Employee Employse Remuneration Any Co 2005 10 11 12 30 46 13 Enter your new employee details or use the values shown To browse for an employee image click Browse Insert New Employee Department ID fo First Name hris Last Name oes Hiredate Bfrocro tt Job Marketing Manager gt Salary poo Commission 96 Dam Photo ss Eg Save Cancel D d Any Co 2005 10 11 12 32 04 14 Locate and select an image with a size larger than 100 pixels Click Open Beta Draft Resizing Images v File Upload x TA Home EA Home Desktop Filesystem Modified Floppy Drive e chap2 Sunday CD ROM Drive B chap3 Sunday B chap4 10 03 2005 2 chap5 Yesterday 2 chap6 Yesterday B chap7 Today ia dump array php 09 30 2005 index html 09 27 2005 db Add Remove All Files BD Opes k 15 In the Insert New Image page click Save Insert New Employee Department ID fio First Name Chris Last Name Jones pas
15. db do query conn query OCI FETCHSTATEMENT BY COLUMN err query SELECT sysdate FROM dual date db do query conn query OCI FETCHSTATEMENT BY COLUMN err Semp array DEPARTMENT ID gt Sdeptid HIRE DATE gt S date SYSDATE 0 ALLJOBIDS gt jobs JOB ID ALLJOBTITLES gt jobs JOB TITLE i ui print header Insert New Employee ui print insert employee emp SERVER SCRIPT NAME ui print footer date Y m d H i s 11 Edit anyco php Modify insert new emp to handle errors The function becomes function insert new emp statement INSERT INTO employees employee id first name last name email hire date job id salary commission pct department id VALUES employees seq nextval fnm lnm eml hdt jid sal cpt did newemp _POST conn db connect err if conn 5 18 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Adding Error Recovery handle error Connect Error err else Semailid newemp firstname newemp lastname Sbindargs array array push bindargs array FNM newemp firstname 1 array push bindargs array LNM newemp lastname 1 array push bindargs array EML Semailid 1 array push bindargs array HDT newemp hiredate 1 array push bindargs array JID newemp jobid 1 array push bindargs array SAL newemp sa
16. 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 The connection identifier return is stored in a variable called conn To validate that the oci_connect call returns a usable database connection write a do query function that accepts two parameters the database connection identifier obtained from the call to oci_connect and a query string to select all the rows from the DEPARTMENTS table Edit anyco php to form a database connection with the following parameter values a Username is hr Password for this example is hr Remember to use the actual password of your HR user a Oracle connect identifier is localhost X El The file becomes php File anyco php require anyco ui inc Create a database connection conn oci connect hr hr localhost XE ui print header Departments do query conn SELECT FROM DEPARTMENTS ui print footer date Y m d H i s Execute query and display results function do query conn
17. Job Marketing Manager x sag Cancel 2005 10 11 12 32 04 Any Co The Employees page shows the new uploaded JPEG image with a reduced image size compared to the image loaded prior to the image resize code being included Beta Draft Loading Images 7 11 Resizing Images Employees Administration Employes Employee Hiredate Salary Commission Remuneration Photograph Pa ES Eu seam em mE Whalen 17 seer m e bzem photo LL 210 11 OCT 05 9 000 00 108 000 00 Modify Delete Insert new employee Return to Departments 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 Building a global Internet application that supports different locales requires good development practices A locale refers to a national language and the region in which the language is spoken The application itself must be aware of the user s locale preference and be able to present content following the cultural convention expected by the user It is important to p
18. anyco cn inc require anyco db inc require anyco ui inc query SELECT department id department name manager id location id FROM departments WHERE department id 80 conn db connect dept db do query conn query ui print header Departments ui print department dept 0 ui print footer date Y m d H i s 6 To test the resulting changes to the application in a browser window enter the following URL http 1localhost username chap4 anyco php The page returned in the browser window should resemble the following page Departments X E FRE T d Looston D 80 Sales 145 2500 2005 09 30 11 50 00 Any Co Writing Queries with Bind Variables Using queries with hard coded values in the WHERE clause may be useful for some situations However if the query conditional values need to change it is not appropriate to encode a value into the query Oracle recommends you use bind variables in the query as a placeholder replacing literal values in the query conditions A bind variable is a symbolic name preceded by a colon in the query that acts as a placeholder for literal values in the WHERE clause For example the query string created in the anyco php file could be rewritten with the bind variable did query SELECT department id department name manager id location id 4 4 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Writing Qu
19. illustrate the differences between the short data and long date formats for both United States and Germany SQL gt alter session set nls_territory america nls_language american Session altered SQL gt select employee_id EmpID 2 substr first_name 1 1 last_name EmpName 3 to_char hire_date DS Hiredate 4 to_char hire_date DL Long HireDate 5 from employees 6 where employee_id lt 105 EMPID EmpName Hiredate Long HireDate 100 S King 06 17 1987 Wednesday June 17 1987 101 N Kochhar 09 21 1989 Thursday September 21 1989 102 L De Haan 01 13 1993 Wednesday January 13 1993 103 A Hunold 01 03 1990 Wednesday January 3 1990 104 B Ernst 05 21 1991 Tuesday May 21 1991 SOL alter session set nls territory germany nls language german Session altered SOL select employee id EmpID 2 substr first name 1 1 last name EmpName 3 to char hire date DS Hiredate 4 to char hire date DL Long HireDate 5 from employees 6 where employee id 105 EMPID EmpName Hiredate Long HireDate 100 S King 17 06 87 Mittwoch 17 Juni 1987 101 N Kochhar 21 09 89 Donnerstag 21 September 1989 102 L De Haan 13 01 93 Mittwoch 13 Januar 1993 103 A Hunold 03 01 90 Mittwoch 3 Januar 1990 104 B Ernst 21 05 91 Dienstag 21 Mai 1991 Oracle Number Formats The examples below illustrate the differences in the decimal character and group separator between United States and Germany SOL alter session set nls
20. lt head gt meta http equiv Content Type content text html charset IS0 8859 1 lt link rel stylesheet type text css href style css gt lt title gt Any Co title lt title gt lt head gt lt body gt h1 title h1 END function ui print footer date date htmlentities date echo lt lt lt END lt div class footer gt lt div class date gt date lt div gt lt div class company gt Any Co lt div gt lt div gt END a The design of this application makes use of PHP function definitions to enable modular reusable code a The PHP functions defined in the anyco ui inc file contain parts of the original HTML contents from the first anyco php you created a The functions in anyco_ui inc make use of a PHP language construct called a here document This enables you to place any amount of HTML formatted text between the following two lines echo END END 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 Any PHP parameters appearing inside the body of a here document are replaced with their values for example the titleor date parameters The PHP function htmlentities is used to prevent user supplied text from containing HTML markup 3 The PHP file makes use of a Cascading Style Sheet CSS file called style css to specify presentation style in HTML in the browser
21. query stid oci_parse conn query r oci_execute stid OCI DEFAULT print lt table border 1 gt while row oci fetch array stid OCI RETURN NULLS print lt tr gt foreach row as item print td item htmlentities item amp nbsp td print lt tr gt print table 9 3 4 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft 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 The second parameter specifies the execution mode OCT DEFAULT is used to indicate you do not want to statements to be committed automatically The default execution mode is OCI COMMIT ON SUCCESS The oci execute function returns TRUE on success otherwise it returns FALSE A while loop is used to fetch all the rows for the query executed The oci fetch array returns the next row from the result data otherwise it returns FALSE if there are no more rows The second parameter to oci fetch array ofOCI RETURN NU
22. returns an integer number starting at 1 for each row returned by the query in q1 The set of rows returned by the sub query enclosing query q1 is filtered a second time by the condition in the outermost query WHERE FIRST RNUM This condition ensures that rows prior to the value in FIRST the value in current are excluded from the final set of rows The query enables navigation through a set rows where the first row is determined by the current value and the page size is determined by the rowsperpage value The current value associated to the bind variable called FIRST the expression current rowsperpage 1 sets the value associated with the LAST bind variable To test the changes made to your application save the each file you modified and enter the following URL in your Web browser http 1localhost username chap4 anyco php Since this is the first time you request the anyco php page you see the Administration department displayed Beta Draft Querying Data 4 9 Extending the Basic Departments Form Department Department Department Manager ion ID Name Id 10 Administration 200 1700 Previous Next i 2005 10 02 22 58 19 Any Co 5 To navigate to the next department record Marketing click Next Department Department Department Manager ID Name Id Location ID 20 Marketing 201 1800 X Previous Next gt 2005 10 02 22 5
23. Departments button Experiment by navigating to another department and listing its employees to show the process of switching between the Department and Employee forms Adding Error Recovery Error management is always a significant design decision In production systems you might want to classify errors and handle them in different ways Fatal errors could be redirected to a standard site not available page or home page Data errors for new record creation might return to the appropriate form with invalid fields highlighted 5 14 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Adding Error Recovery Most production systems would display_errors configuration option in the php ini file set of 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 Here we ll change the application so error and database errors are displayed on a new page using a customer error handling function Errors are now returned from the db functions keeping them silent 1 Edit anyco_db inc Change db error to return the error information in an array structure instead of printing and quitting function db error r false file line Serr r oci_error r oci
24. PHP variable of the same name that is b 0 takes the value DID in b 0 and forms a PHP variable called DID whose value is assigned from the second element in the entry The oci bind by name accepts four parameters the stid as the resource a string representing the bind variable name in the query derived from the first element in the array entry the PHP variable containing the value to be associated with the bind variable and the length of the input value 3 To test the results of the preceding modifications save the anyco php and anyco db inc files and enter the following URL http localhost lt username gt chap4 anyco php The page returned in the browser window should resemble the following page Departments Department Department Manager Loostion D ID Name Id 80 Sales 145 2500 2005 09 30 14 42 50 Any Co Navigating Through Database Records Adding navigation through the database requires several important changes to the application logic The modifications require the combination of Including a HTML form to provide Next and Previous navigation buttons to step through data records 4 6 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Navigating Through Database Records 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 starte
25. Shows how to insert update anddelete employee records 5 Handles data exceptions Beta Draft Introducing PHP with Oracle Database XE 1 1 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 anyco php anyco cn inc start page An include file that defines A starter page that provides a default database connection form handler controller to information This includes the manage page requests username password and database name anyco ui inc anyco db inc The model logic an include file The user interface logic an that contains the database logic include file that contains the to create database connections PHP code to display HTML and to execute queries and data forms and database data in manipulation statements HTML tables anyco im php style css A PHP script referenced in an lt IMG gt tag to get an image from a database column for a specified employee and send it to the browser for display 4 Cascading Style Sheet CSS file to manage HTML display 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 anyc
26. error if isset Serr message m htmlentities err message c err code else m Unknown DB error c null rc array MESSAGE gt m CODE gt c FILE gt file LINE gt line return rc 2 Edit anyco_db inc For every call to db error assign the return value to a variable called e and add a return false statement after each call if error test e db error handle FILE LINE return false Make sure to keep the error test and handle parameters the same as they are currently specified for each call Remember the FILE and __LIN constants help pinpoint the location of the failure during development This is useful information to log for fatal errors in a production deployment of an application E 3 Edit anyco_db inc Add a e parameter to every function to enable the return of error information Use the amp reference prefix to ensure that results are returned to the calling function Each function declaration becomes function db connect amp e function db get page data conn q1 currrownum 1 rowsperpage 1 amp e Sbindvars array function db do query conn statement resulttype amp e Beta Draft Updating Data 5 15 Adding Error Recovery bindvars array function db execute statement conn statement amp e bindvars array
27. header has the following form Content Type text html charset utf 8 The charset parameter specifies the encoding for the HTML page The possible values for the charset parameter are the IANA names for the character encodings that the browser supports Specifying the Encoding in the HTML Page Header Use this method primarily for static HTML pages Specify the character encoding in the HTML header as follows lt meta http equiv Content Type content text html charset utf 8 gt The charset parameter specifies the encoding for the HTML page As with the Content Type HTTP Header the possible values for the charset parameter are the IANA names for the character encodings that the browser supports Specifying the Page Encoding in PHP You can specify the encoding of an HTML page in the Content Type HTTP header in PHP by setting the default_charset configuration variable as follows default_charset UTF 8 This can be found in the Zend Core for Oracle Console in the Configuration tab Choose the PHP sub tab and expand the Data Handling tree control After entering a value save the configuration settings and restart the web server 8 4 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft 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 HTM
28. ko KOREAN KOREA Portuguese Brazil pt BR BRAZILIAN BRAZIL PORTUGUESE Beta Draft Building Global Applications 8 3 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 The browser needs to know about the page encoding so that it can use the correct fonts and character set mapping tables to display the HTML pages Internet applications need to know about the HTML page encoding so they can process input data from an HTML form Instead of using different native encodings for the different locales it is recommended to use UTF 8 Unicode encoding for all page encodings Using the UTF 8 encoding not only simplifies the coding for global applications but it allows for multilingual content on a single page Specifying the Page Encoding for HTML Pages There are two ways to specify the encoding of an HTML page one is in the HTTP header and the other is in the HTML page header Specifying the Encoding in the HTTP Header Include the Content Type HTTP header in the HTTP specification It specifies the content type and character set The Content Type HTTP
29. or text that you enter vi Beta Draft 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 This chapter has the following topics Zend Core for Oracle Purpose Overview of the Sample Application a Resources Zend Core for Oracle Purpose Zend Core for Oracle developed in partnership with Zend Technologies provides a seamless out of the box experience delivering a stable high performance easy to install and supported PHP development and production environment fully integrated with Oracle Database Express Edition This guide is a tutorial that shows you how to use Zend Core for Oracle to connect to Oracle Database XE and demonstrates how to use PHP to access and modify data Overview of the Sample Application This document guides you through the development of a simple Human Resources HR application for a fictitious company AnyCo Corp The application manages departmental and employee information stored in the DEPARTMENTS and EMPLOYEES tables in the HR schema provided with Oracle Database XE The complete sample application 1 Establishes a connection to the database using PHP s OCI8 extension 2 Queries the database for department and employee data 3 Displays and navigates through the data 4
30. territory america Session altered SOL select employee id EmpID 2 substr first name 1 1 last name EmpName 3 to char salary 99G999D99 Salary 4 from employees 5 where employee id 105 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Presenting Data following User s Locale Convention EMPID EmpName Salary 100 S King 24 000 00 101 N Kochhar 17 000 00 102 L De Haan 17 000 00 103 A Hunold 9 000 00 104 B Ernst 6 000 00 SOL alter session set nls territory germany Session altered SOL select employee id EmpID 2 substr first name 1 1 last name EmpName 3 to char salary 99G999D99 Salary 4 from employees 5 where employee id 105 EMPID EmpName Salary 100 S King 24 000 00 101 N Kochhar 17 000 00 102 L De Haan 17 000 00 103 A Hunold 9 000 00 104 B Ernst 6 000 00 Oracle Linguistic Sorts Spain traditionally treats ch ll as well as as letters of their own ordered after c and n respectively The examples below illustrate the effect of using a Spanish sort against the employee names Chen and Chung SOL alter session set nls sort binary Session altered SOL select employee id EmpID 2 last name Last Name 3 from employees 4 where last name like C 5 order by last name 7 Cabrio 8 Cambrault 154 Cambrault 0 Chen 8 Chung 9 Colmenares 6 rows selected SOL alter session set nls sort spanish m Session altered SOL select empl
31. 11 Edit anyco db inc Add a new function db insert thumbnail to insert an image into the EMPLOYEE PHOTOS table I function db insert thumbnail conn empid imgfile amp e lob oci_new_descriptor conn OCI D LOB if lob e db_error conn FILE LINE return false insstmt INSERT INTO employee photos employee id employee thumbnail VALUES eid empty blob RETURNING employee thumbnail into etn stmt oci parse conn insstmt r oci bind by name stmt etn lob 1 OCI B BLOB if r e db error stid FILE LINE return false r oci bind by name stmt eid empid 1 if r e db error stid FILE LINE return false r oci execute stmt OCI DEFAULT 7 4 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Using Oracle LOBs to Store and Load Employee Images if r e db error stid FILE LINE return false if lob gt savefile imgfile e db error stid FILE LINE return false 1ob free return true I To tie the new EMPLOYEE PHOTOS and EMPLOY use the same employee id in both ES tables together we need to 12 Edit anyco db inc Change the bindvars parameter in db execute statement to amp bindvars so that OUT bind variable values are returned from the database At the bottom of the functio
32. 12 000 00 07 JUN 94 07 JUN 94 07 JUN 94 07 JUN 94 8 300 00 c 216 f nerk 2005 10 04 13 28 34 10 JUL 04 HHHHHHA 101 00 Modify Delete Insert new employee LLL 16 To insert a new employee click Insert new employee E pen Tere m3 le es ee ee Modify Delete Insert new employees 2005 10 04 13 28 34 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 In the Insert New Employee page the Department ID field contains 10 the default Hiredate contains the current date in default database date format Salary contains 1 Commission contains 0 Enter the following field values First Name James Last Name Bond Job Select Programmer from the drop down box Salary replace the 1 with 7000 Beta Draft Updating Data 5 9 Extending the Basic Employee Form Click Save Insert New Employee Department ID ames Bond Hiredate T 05 First Name I Last Name ul Job Programmer E Commission Il 2005 10 04 13 31 27 Any Co 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 a
33. 16 33 20 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 Department Accounting See eas Number of Manager Location Employees Name Accounting S Higgins United States of America lt Previous Next gt Show Employees Any Co 2005 10 10 16 34 07 The error handling prevents navigation past the last department record Further Error Handling Specific Oracle errors can be handled individully For example if a new employee is created by clicking the Insert new employee button on the Employees page and the Department ID is changed to a department that doesn t exist we can trap this error and display a more meaningful message 1 Edit anyco php Change the error handling in insert_new_emp r db_execute_statement conn statement Serr bindargs if Sr construct employees else if err CODE 2291 Foreign key violated handle_error Department newemp deptid does not yet exist err else handle_error Cannot insert employee Serr 2 Save the changes to your application files Test the changes by entering the following URL http localhost lt username gt chap5 anyco php 3 Inthe Department page click Show Employees 5 22 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Further Error Handling
34. 5 13 Adding Error Recovery lt input type submit value Next gt name nextdept gt amp nbsp amp nbsp amp nbsp lt input type submit value Show Employees name showemp gt lt form gt 10 Edit anyco ui php Inui print employees change the HTML form to enable it to call the department form table input type submit value Modify name modifyemp gt lt input type submit value Delete name deleteemp gt amp nbsp amp nbsp lt input type submit value Insert new employee name insertemp gt amp nbsp amp nbsp lt input type submit value Return to Departments name showdept gt lt form gt 11 Save the changes to your PHP files In your browser test the changes by entering the following URL http localhost lt username gt chap5 anyco php The Department Information page is displayed Department Administration E ane ae Number of Manager Employees Name Administration 1 J Whalen United States of America Previous Next Show Employees Location 2005 10 10 14 20 14 Any Co To display a list of employees in the department click the Show Employees button Employees Administration ey nai Hiredate Salary ges 200 J Whalen 17 SEP 87 4 400 00 Modify Delete Insert new employee Return to Departments 2005 10 10 14 24 45 Any Co You can return to the Department view by clicking the Return to
35. 9 10 Any Co 6 To navigate back to the first department record Administration click Previous Department Department Department Manager ID Name Id Location ID 10 Administration 200 1700 lt Previous Next gt 2005 10 02 22 59 29 Any Co 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 Note 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 department tabular form is extended to include the following additional information a The department s manager name a The number of employees assigned to the department a The country name identifying the location of the department 4 10 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft 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 I To extend the department form perform the following tasks 1 Edit anyco_ui inc Modify theui print departments function by replacing the Manager ID and Location ID references with the Manager Name and Location res
36. ATE for storing an SQL date in the database and 2 The PHP date for display in the footer of each page ui print footer date Y m d H i s The construct insert emp function executes two queries to obtain default data to be used to populate the insert employee form which is displayed by the ui print insert employee function The query of the JOBS table obtains a list of all the existing job ID s and their descriptions in order to build a list for selecting a job type in the HTML form generated by theui print insert employee function The query using SYSDATE obtains the current database date and time for setting the default hire date of the new employee There are two kinds of date used in the application code the PHP date function for printing the date and time in the page footer and the Oracle SYSDATE function to obtain the default date and time for displaying in the employee HTML form s hire date field and to ensure the field text is entered in the correct database format The two db do query function calls provide an additional parameter value OCI FETCHSTATEMENT BY COLUMNS to specify that the return type for query is an array of column values 4 Editanyco php In the construct employees function modify the db do query calltosupply OCT FETCHSTATEMENT BY ROW as the last parameter and provide SERVER SCRIPT NAME as second parameter in the ui print employees call
37. ER SCRIPT NAME to supply the current PHP script name for the 4 8 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Navigating Through Database Records posturl parameter to set the HTML form s action attribute So each Next or Previous button click calls anyco php 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 ROWNUM AS RNUM FROM q1 A WHERE ROWNUM lt LAST WHERE FIRST lt RNUM Set up bind variables array push bindvars array FIRST current 1 array push bindvars array LAST current rowsperpage 1 1 r db do query conn query bindvars return r The structure of the query in db get page data enables navigation through a set or page of database rows The query supplied in q1 is nested as a sub query inside the sub query SELECT A ROWNUM AS RNUM FROM ql WHERE ROWNUM lt LAST Remember the query supplied in q1 retrieves an ordered set of rows which is filtered by its enclosing query to return all the rows from the first row to the next page size Srowsperpage of rows This is possible since the Oracle ROWNUM function or pseudo column
38. IRE DATE echo END form method post action posturl table tr 5 6 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Extending the Basic Employee Form lt td gt Department ID lt td gt lt td gt lt input type text name deptid value deptid size 20 gt lt td gt lt tr gt lt tr gt lt td gt First Name lt td gt lt td gt lt input type text name firstname size 20 gt lt td gt lt tr gt lt tr gt lt td gt Last Name lt td gt lt td gt lt input type text name lastname size 20 gt lt td gt lt tr gt lt tr gt td Hiredate td lt td gt lt input type text name hiredate value hiredate size 20 gt lt td gt lt tr gt lt tr gt lt td gt Job lt td gt lt td gt lt select name jobid gt END Write the drop down list of jobs for i 0 i lt count S emp ALLJOBIDS i echo lt option label htmlentities emp ALLJOBTITLES i valuez htmlentities emp ALLJOBIDS i htmlentities emp ALLJOBTITLES i lt option gt echo lt lt lt END lt select gt lt td gt lt tr gt lt tr gt lt td gt Salary lt td gt lt td gt lt input type text name salary value 1 size 20 gt lt td gt lt tr gt lt tr gt lt td gt Commission lt td gt lt td gt lt input type text name commpct value 0 size 20 gt lt td gt lt tr gt lt table gt lt
39. L Pages for Translation Strings in PHP Static Files Making the user interface available in the user s local language is one of the fundamental task in globalizing an application Translatable sources for the content of an HTML page belong to the following categories a Text strings hard coded in the application code Static HTML files images files and template files such as CSS Dynamic data stored in the database You should externalize translatable strings within your PHP application logic so that the text can be easily available for translation These text messages can be stored in flat files or database tables depending on the type and the volume of the data being translated Static files such as HTML and GIF files are readily translatable When these files are translated they should be translated into the corresponding language with UTF8 as the file encoding To differentiate the languages of the translated files the static files of different languages can be staged in different directories or with different file names Data from the Database Dynamic information such as product names and product descriptions are most likely stored in the database In order to differentiate various translations the database schema holding these information should include a column to indicate the language of the information To select the translated information you need to include the WHERE clause in your query to select the information in
40. L in your browser to access the Zend Core for Oracle Console http localhost ZendCore 2 At the login screen in the Password field enter the password you provided when Zend Core for Oracle was installed and click the login gt gt gt icon 3 Inthe Console page click the Configuration tab 4 Inthe Configuration tab page click the Extensions sub tab 7 8 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Resizing Images 10 11 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 In the Extension sub tab page to save the configuration changes click the Save Setting link In the Extension sub tab page to restart the web server click the Restart Server link To logout of the Zend Core for Oracle Console click the Logout link Edit anyco_db inc To resize the image if it is larger than a thumbnail add the following code before the call to 10b savefile imgfile in db insert thumbnail r oci execute stmt OCI DEFAULT if r e db error stid FILE LINE return false Resize the image to a thumbnail define MAX THUMBNAIL DIMENSION 100 src img imagecreatefromjpeg imgfile list w h getimagesize imgfile if w gt MAX THUMBNAIL DIMENSION h gt MAX THUMBNAIL DIMENSION scale MAX THUMBNAIL DIMENSION h gt
41. LLS indicates that NULL database fields will be returned as PHP NULL values Each row of data is return as an associative or numeric array of column values The code uses a PHP foreach construct to loop through the array and print each column value in a HTML table cell inside a table row element If the item value is NULL then a non breaking space is printed otherwise the item value is printed 2 To test the changes made to SHOME public html chap3 anyco php save the modified anyco php file and in a browser window enter the following URL http localhost lt username gt chap3 anyco php The page returned in the browser window should resemble the following page Departments 20 arketing 201 1800 DIR AN lao Human Resources 03 2400 leo IT 103 1400 Iv Public Relations 204 2700 If you wanted to query the EMPLOYEES data you could change the query in the do query function call to H En ED y o o H do query conn SELECT FROM EMPLOYEES Other Ways to Connect In some applications using a persistent connection improves performance by removing the need to reconnect each time the script is called Depending on your Apache configuration this may cause a number of database connections to remain Beta Draft Getting Connected 3 5 Disconnecting from the Database open simultaneously The connection perfor
42. ORACLE Oracle Database Express Edition 2 Day Plus PHP Developer Guide 10g Release 2 10 2 B25317 01 September 2005 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 Reverse engineering disassembly or decompilation of the Programs except to the extent required to obtain interoperability with other independently created software or as specified by law is prohibited The information contained in this document is subject to change without notice If you find any problems in the documentation please report them to us in writing This document is not warranted to be error free Except as may be expressly permitted in your license agreement for these Programs no part of these Programs may be reproduced or transmitted in any form or by any means electronic or mechanical for any purpose If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government the following notice is applicable U S GOVERNMENT RIGHT
43. S Programs software databases and related documentation and technical data delivered to U S Government customers are commercial computer software or commercial technical data pursuant to the applicable Federal Acquisition Regulation and agency specific supplemental regulations As such use duplication disclosure modification and adaptation of the Programs including documentation and technical data shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement and to the extent applicable the additional rights set forth in FAR 52 227 19 Commercial Computer Software Restricted Rights June 1987 Oracle Corporation 500 Oracle Parkway Redwood City CA 94065 The Programs are not intended for use in any nuclear aviation mass transit medical or other inherently dangerous applications It shall be the licensee s responsibility to take all appropriate fail safe backup redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes and we disclaim liability for any damages caused by such use of the Programs Oracle JD Edwards PeopleSoft and Retek are registered trademarks of Oracle Corporation and or its affiliates Other names may be trademarks of their respective owners Zend is a registered trademark of Zend Technologies Ltd The Programs may provide links to Web sites and access to content products and services from third parties Ora
44. Use your editor to create style css in the chap3 directory with the following CSS text body background CCCCFF color 000000 font family Arial sans serif h1 3 2 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft 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 php require anyco ui inc ui print header Departments ui print footer date Y m d H i s gt The anyco php file uses PHP functions to produce HTML content The require PHP function is used to include the code in anyco_ui inc such that the functions defined in it can be called to produce the desired result If the required file cannot be found PHP will generate an error and stop running the script 5 To test anyco php enter the following URL in your browser http localhost lt username gt chap3 anyco php The resulting Web page produced is Departments 2005 09 27 12 55 02 Any Co The date and time appear in the page footer section Beta Draft Getting Connected 3 3
45. ation see these Oracle resources Oracle Database Express Edition Installation and Licensing Guide for Linux Oracle Database Express Edition 2 Day DBA Guide Oracle Database Express Edition 2 Day Developer Guide a Oracle HTML DB User s Guide Oracle HTML DB 2 Day Developer Oracle Database Express Edition 2 Day Plus Java Developer Guide Oracle Database Express Edition 2 Day Plus NET Developer Guide Oracle Database Express Edition ISV Embedding Guide a SQL Plus User s Guide and Reference a SQL Plus Quick Reference Oracle Database PL SQL User s Guide and Reference Oracle Database SQL Reference Oracle Call Interface Programmer s Guide Oracle Database Concepts Oracle Database Application Developer s Guide Fundamentals Oracle Database Globalization Support Guide Oracle Database Error Messages The examples in this book use the HR sample schema which is installed by default See Oracle Database Sample Schemas for information about this schema Conventions The following text conventions are used in this document Convention Meaning boldface Boldface type indicates graphical user interface elements associated with an action or terms defined in text or the glossary italic Italic type indicates book titles emphasis or placeholder variables for which you supply particular values monospace Monospace type indicates commands within a paragraph URLs code in examples text that appears on the screen
46. ay CPT newemp commpct 1 array push bindargs array DID newemp deptid 1 r db execute statement conn statement bindargs construct employees The return value from db execute statement is ignored and not even assigned to a variable because we don t perform any action on its result until later Edit anyco php Add construct modify emp to build the HTML form for updating an employee function construct modify emp empid POST emprec query SELECT employee id first name last name email hire date salary nvl commission pct 0 as commission pct Beta Draft Updating Data 5 3 Extending the Basic Employee Form FROM employees WHERE employee id empid conn db connect bindargs array array push bindargs array EMPID empid 1 db do query conn query OCI FETCHSTATEMENT BY ROW bindargs emp 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 Editanyco php Add modify emp to update the employee row in the EMPLOYE I ES table using the update form field values function modify emp newemp _POST statement UPDATE employees SET first_name fnm last_name salary sal commission_pct WHERE employee_id eid lnm email eml scpt conn db_connect bindargs array array_push bindargs a
47. b get page data function insert OCI_FETCHSTATEMENT_BY_ROW as the third parameter value in the db do query call function db get page data conn q1 current 1 Srowsperpage 1 bindvars array r db do query conn query OCI FETCHSTATEMENT BY ROW Sbindvars return r Edit anyco_db inc Adda db_execute_statement function to execute data manipulation statements function db execute statement conn statement bindvars array stid oci_parse conn statement if stid db_error conn FILE LINE Bind parameters foreach bindvars as b create local variable with caller specified bind value b 0 b 11 r oci bind by name stid b 0 b 0 b 2 if r db error stid FILE LINE r oci execute stid if r db error stid FILE LINE return r 12 Edit anyco ui inc Changeui print employees to produce a HTML form containing the employee rows The function becomes function ui print employees S employeerecords posturl if Semployeerecords Beta Draft Updating Data 5 5 Extending the Basic Employee Form echo lt p gt No Employee found lt p gt else echo lt lt lt END lt form method post action posturl gt lt table gt lt tr gt th amp nbsp th lt th gt Employee lt br gt ID lt th gt lt th gt Employee lt br gt Name lt th
48. ble It is common to declare a PL SQL type inside a package specification for reuse in other PL SQL constructs such as a package body To create a PL SQL package specification and body with a REF CURSOR to retrieve employees for a specific department perform the following steps Beta Draft Executing Stored Procedures and Functions 6 5 Using PL SQL Ref Cursors to Return Result Sets 1 Inthe SOL 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 ORACLE YE a o 7 DATABASE User HR Home gt SQL gt SQL Commands 7 Autocommit Display 10 gt ICREATE OR REPLACE PACKAGE cv_types AS TYPE empinfotyp IS REF CURSOR PROCEDURE get_employees deptid in number employees in out empinfotyp END cv_types In the Results section confirm the package specification is successfully created Results Explain Describe SavedSQL History Package created 0 05 seconds 2 Inthe SQL Commands page as the HR user create the PL SQL package body implementation CREATE OR REPLACE PACKAGE BODY cv_types AS PROCEDURE get_employees deptid in number employees in out empinfotyp Is BEGIN OPEN employees FOR SELECT employee_id substr first_name 1 1 last_name as employee na
49. cle accept the default or enter your preferred location and click OK The installer begins extracting the files required for the installation 7 When the progress window indicates all the software has been installed you are prompted to Please enter the GUI password In the Password field enter your password for example oracle and click OK The password specified here allows you to log into the Zend Core for Oracle administration Web pages to enable configuration of Zend Core for Oracle engine directives or property values Beta Draft Getting Started 2 5 Setting Up Zend Core for Oracle 10 11 12 13 14 15 16 17 When prompted to Verify the password enter the same password as specified in the previous step and click OK 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 If you have not registered you may still click No and register at a later time using the Zend Core Web page http www zend com The next page prompts you to select the web server for Zend Core installation Select the first entry Apache 2 0 52 etc httpd conf httpd conf the default Apache installed with Linux Click OK If you desire you may continue to install Zend Core with other supported Web servers installed
50. cle is not responsible for the availability of or any content provided on third party Web sites You bear all risks associated with the use of such content If you choose to purchase any products or services from a third party the relationship is directly between you and the third party Oracle is not responsible for a the quality of third party products or services or b fulfilling any of the terms of the agreement with the third party including delivery of products or services and warranty obligations related to purchased products or services Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party Alpha and Beta Draft documentation are considered to be in prerelease status This documentation is intended for demonstration and preliminary use only We expect that you may encounter some errors ranging from typographical errors to data inaccuracies This documentation is subject to change without notice and it may not be specific to the hardware on which you are using the software Please be advised that prerelease documentation in not warranted in any manner for any purpose and we will not be responsible for any loss costs or damages incurred due to the use of this documentation Contents it li lel eae eee RUE E ome eT ERN ERE CREER eee aa v Audiences MR RUN V Doecumentation Accessibility ee eerte ei e irc t teet e iren eb ee ce trier tes V Related Documents 55
51. cle software as a root user which is required to install the software In a command window enter su Password enter the root password cd tmp tar zxf ZendCoreForOracle v1 2 1 Linux x86 tar gz root frodo tmp Eile Edit View Terminal Tabs Help gstokol frodo su Password root frodo cd tmp root frodo tmp tar zxf ZendCoreForOracle v1 2 1 Linux x86 tar gzff By default unless specified otherwise files are extracted to a sub directory called ZendCoreForOracle v1 2 1 Linux x86 2 To start the Zend Core for Oracle installation process enter the following commands cd ZendCoreForOracle v1 2 1 Linux x86 install root frodo tmp ZendCoreForOracle v1 2 1 Linux x86 Eile Edit View Terminal Tabs Help gstokol frodo su Password root frodo cd tmp root frodo tmp cd ZendCoreForOracle v1 2 1 Linux x86 root frodo ZendCoreForOracle v1 2 1 Linux x86 installfj The install command must be executed with root user privileges After the install command is entered the installation process begins as documented in subsequent steps 3 In the initial Zend Core for Oracle Installation page click OK 4 Inthe Zend Core for Oracle V 1 page read the license agreement To continue with the installation click Exit 5 When prompted to accept the terms of the license click Yes 6 When prompted to specify the location for installing Zend Core for Ora
52. command at the SQL prompt SOL exit 2 2 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Testing the Apache Installation See also 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 Testing the Apache Installation 1 Start your web browser and enter the following URL http localhost Your browser should display a page similar to Red Hat Enterprise Linux Test Page This page is used to test the proper operation of the Apache HTTP server after it has been installed If you can read this page it means that the Apache HTTP server installed at this site is working properly If you are a member of the general public The fact that you are seeing this page indicates that the website you just visited is either experiencing problems or is undergoing routine maintenance If you are the website administrator You may now add content to the directory var wm html Note that until you do so people visiting your website will see this page and not your content To prevent this page from ever being used follow the If you would like to let the administrators of this website know that you ve seen this page instead of the page you expected you should send them e mail In general mail sent to the name instructions in the file etc httpd conf d welcome c
53. d 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 A subsequent request initiated with navigation buttons combined with the session state from a previous HTTP request enables the application to set variables that control the next record retrieved by the query Writing a query that returns a sub set of rows based on a set of conditions whose values are determined by the application state To add navigation through database rows perform the following steps 1 Edit anyco_ui inc Add Next and Previous navigation buttons to the departments Web page Change theui print departments function to append a second parameter called postur1 that supplies the value for the form attribute action After printing the lt table gt tag include HTML form tags for the Next and Previous buttons php File anyco ui inc function ui print department dept posturl echo lt lt lt END lt tr gt lt table gt lt form method post action posturl gt lt input type submit value lt Previous name prevdept gt lt input type submit value Next gt name nextdept gt lt form gt END Edit anyco php To detect if the Next or Previous button was used to invoke the page and track session state call the PHP function session start and create a function named construct departments Move and mod
54. e conn deptid ui print header Department deptname ui print department dept 0 SERVER SCRIPT NAME ui print footer date Y m d H i s Edit anyco php Modify construct employees to handle errors The function becomes 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 WHERE department id did ORDER BY employee id asc deptid SESSION deptid conn db connect err if conn handle error Connection Error err else Sbindargs array Beta Draft Updating Data 5 17 Adding Error Recovery array_push bindargs array DID deptid 1 Semp db_do_query conn query OCI FETCHSTATEMENT BY ROW err Sbindargs if emp handle error Cannot fetch Employees err else deptname get dept name conn deptid ui print header Employees deptname ui print employees S emp SERVER SCRIPT NAME ui print footer date Y m d H i s 10 Edit anyco php Modify construct insert emp tohandle errors The function becomes function construct insert emp deptid SESSION deptid conn db connect err if conn handle error Connection Error err else query SELECT job id job title FROM jobs ORDER BY job title ASC jobs
55. eries with Bind Variables FROM departments WHERE department_id did By using bind variables to parameterize SQL statements 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 There is protection against SQL Injection security problems There is no need to specially handle quotes in user input When a query uses a bind variable the PHP code must associate an actual value with each bind variable placeholder used in the query before it is execute This process is known as run time binding To enable you PHP application to use bind variables in the query perform the following changes to your PHP application code 1 Edit anyco php Modify the query to use a bind variable create an array to store the value to be associated with the bind variable and pass bindargs to db do query php File anyco php query SELECT department id department name manager id location id FROM departments WHERE department id did bindargs array In the bindargs array add an array containing the bind variable name used in the query its value a length array_push bindargs array DID 80 1 conn db connect dept db do query conn query bi
56. es 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 ui print header Employees ui print employees emp ui print footer date Y m d H i s Edit anyco php Replace the call to construct departments with a call to construct employees php File anyco php require anyco cn inc require anyco db inc 4 12 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Building the Basic Employee Form require anyco_ui inc session_start construct employees Edit anyco_ui inc Implement the presentation of employee data in a HTML table by addingaui print employees function function ui print employees employeerecords if employeerecords echo lt p gt No Employee found lt p gt else echo lt lt lt END lt table gt lt tr gt lt th gt Employee lt br gt ID lt th gt lt th gt Employee lt br gt Name lt th gt lt th gt Hiredate lt th gt lt th gt Salary lt th gt th Commission br lt th gt lt tr gt END Write one row per employee foreach employeerecords as emp echo lt tr gt echo td align right gt htmlentities emp EMPLOYEE ID
57. es 15 Edit anyco php Modify get dept name to handle errors The function becomes function get dept name conn deptid query SELECT department_name FROM departments WHERE department_id did conn db connect err if conn return Unknown else Sbindargs array array_push bindargs array DID deptid 1 dn db_do_query conn query OCI FETCHSTATEMENT BY COLUMN err Sbindargs if dn false return Unknown else return dn DEPARTMENT_NAME 0 16 Edit afico_ui inc Add anew function ui print errors function ui_print_error message posturl if message echo lt p gt Unknown error lt p gt else echo lt p gt Error at line message LINE of message FILE p Uncomment for debugging echo lt p gt message MESSAGE lt p gt echo lt lt lt END lt form method post action posturl gt lt input type submit value Return to Departments name showdept gt END 17 Save the changes to your application files Test the changes by entering the following URL in your browser http localhost lt username gt chap5 anyco php Beta Draft Updating Data 5 21 Further Error Handling Department Administration owes iss Number of Manager Location Employees Name Administration 1 J Whalen United States of America lt Previous Next EN Show Employees Any Co 2005 10 10
58. fier from the Department page as a session parameter which is used in the Employees page 4 Edit anyco php Create a function get dept name to query the department name for printing in the Department and Employee page titles function get dept name conn deptid query SELECT department name FROM departments WHERE department id did conn db connect bindargs array array push bindargs array DID deptid 1 dn db do query conn query OCI FETCHSTATEMENT BY COLUMN bindargs return dn DEPARTMENT NAME 0 5 Editanyco php Modify construct employees to print the department name in the page heading deptname get dept name conn deptid ui print header Employees deptname 6 Edit anyco php Modify construct departments to print the department name in the page heading deptname get dept name conn deptid ui print header Department deptname 7 Editanyco php Modify construct insert emp so the default department is obtained from the session parameter passed in the emp array to ui print insert employee 5 12 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft 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
59. fnm gt lt td gt name lastname value lnm gt lt td gt name email value eml gt lt td gt name salary value sal gt lt td gt name commpct value cpt gt lt td gt lt table gt lt input type hidden value empdetails EMPLOYEE ID name empid gt lt input type submit value Save name savemodifiedemp gt lt input type submit value Cancel name cancel gt lt form gt END Save the changes to your Anyco application files and test the changes by entering the following URL in you Web browser http locahost lt username gt chap5 anyco php The list of all employees is displayed with a radio button in each row Beta Draft Extending the Basic Employee Form Employees Employee Employee 100 S King Hiredate 17 JUN 87 Salary 24 000 00 Commission me Bc Na 101 102 L De Haan N Kochhar 21 SEP 89 17 000 00 13 JAN 93 17 000 00 alalle 104 103 A Hunold 03 JAN 90 105 D Austin 25 JUN 97 9 000 00 6 000 00 21 MAY 91 4 800 00 ma 107 mm 05 FEB 98 4 800 00 07 FEB 99 HERE LESE Scroll to the bottom of the Employees page to view the Modify Delete and Insert new employee buttons c 201 M Hartstein pn 17 FEB 96 13 000 00 17 AUG 97 6 000 00 c 203 S Mavris s 205 S Higgins 6 500 00 10 000 00
60. 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 data in the results array is organized by row where each row contains an array of column values A value of OCI_FETCHSTATEMENT_BY_COLUMN causes the results array to be organized by column where each column entry contains an array of column values for each row Your choice of value for this flag depends on how you intend to process the data in your logic The last parameter flag OCI_FETCHSTATEMENT_BY_ROW indicates that the To examine the structure of the result array use the PHP var_dump function after the query has been executed This is useful for debugging For example print pre var dump results print pre The db error function accepts three arguments The r parameter can be false or null for obtaining connection errors or a connection resource or statement resource to obtain an error for those contexts The file and line values are populated by using _ FILE and LINE respectively as the actual parameters to enable the error message to display the source file and line from which the database error is reported This enables you to easily track the possible cause of errors The db_ error function calls the oci error function to obtain database error
61. gt lt th gt Hiredate lt th gt lt th gt Salary lt th gt lt th gt Commission lt br gt lt th gt lt tr gt END Write one row per employee foreach employeerecords as Semp echo lt tr gt echo lt td gt lt input type radio name emprec value htmlentities emp EMPLOYEE ID gt lt td gt echo lt td align right gt htmlentities Semp EMPLOYEE_ID lt td gt echo lt td gt htmlentities Semp EMPLOYEE_NAME lt td gt echo lt td gt htmlentities Semp HIRE_DATE lt td gt echo lt td align right gt htmlentities Semp SALARY lt td gt echo lt td align right gt htmlentities S emp COMMISSION PCT td echo lt tr gt echo lt lt lt END lt table gt lt input type submit value Modify name modifyemp gt lt input type submit value Delete name deleteemp gt amp nbsp amp nbsp lt input type submit value Insert new employee name insertemp gt lt form gt END The form prints a radio button in the first column of each row to enable you to select the record to be modified or deleted 13 Edit anyco_ui inc Add ui_print_insert_employee to generate the form to input new employee data function ui print insert employee emp posturl if emp echo p No employee details found lt p gt else deptid htmlentities emp DEPARTMENT ID hiredate htmlentities emp H
62. i schemas sample vi starting Apache 2 4 T testing Apache installation 2 3 Oracle Database XE access 2 2 Zend Core for Oracle installation 2 7 tutorial application AnyCo Corp 1 1 U ui print footer PHP functions 3 1 ui print header PHP functions 3 1 unlocking HR account 2 2 WwW web browser testing Apache installation 2 3 web server Zend Core for Oracle 2 6 Z Zend Core for Oracle 1 1 Index 2 Beta Draft configuration tab 2 6 configuring 2 6 GUI password 2 5 hello php 2 7 installing 2 5 obtaining 2 1 testing installation 2 7 web server 2 6
63. ic understanding of the SQL PL SQL and PHP Documentation Accessibility Our goal is to make Oracle products services and supporting documentation accessible with good usability to the disabled community To that end our documentation includes features that make information available to users of assistive technology This documentation is available in HTML format and contains markup to facilitate access by the disabled community Accessibility standards will continue to evolve over time and Oracle is actively engaged with other market leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers For more information visit the Oracle Accessibility Program Web site at http www oracle com accessibility Accessibility of Code Examples in Documentation Screen readers may not always correctly read the code examples in this document The conventions for writing code require that closing braces should appear on an otherwise empty line however some screen readers may not always read a line of text that consists solely of a bracket or brace Beta Draft V 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 Related Documents For more inform
64. id e db_error conn FILE LINE return false refcur oci new cursor conn if stid e db error conn FILE LINE return false r QGoci bind by name stid RC refcur 1 OCI B CURSOR if r e db error stid FILE LINE return false r Goci execute stid if r e db error stid FILE LINE return false Now treat the ref cursor as a statement resource r oci_execute refcur OCI DEFAULT if r e db error refcur FILE LINE return false r Goci fetch all refcur employeerecords null null OCI FETCHSTATEMENT BY ROW Beta Draft Executing Stored Procedures and Functions 6 7 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 The Srefcur variable is treated as a statement handle that is used for execute and fetch operations 4 Edit anyco php Modify the construct employees function Remove the query text and the bind arguments The function becomes function c
65. ify the database access logic into a new construct departments function which detects if navigation has been performed manages session state defines a sub query for the database access layer to process connects and calls a function db get page data The file becomes php File anyco php require anyco cn inc require anyco db inc require anyco ui inc session start construct departments function construct departments if isset _SESSION currentdept amp amp Beta Draft Querying Data 4 7 Navigating Through Database Records isset POST prevdept amp amp SESSION currentdept 1 current SESSION currentdept 1 elseif isset SESSION currentdept amp amp isset POST nextdept current SESSION currentdept 1 elseif isset POST showdept amp amp 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 conn db connect dept db get page data conn query current 1 deptid dept 0 DEPARTMENT ID SESSION currentdept current ui print header Department ui print department dept 0 SERVER SCRIPT NAME ui print footer date Y m d H i s The if and elseif construct at the start of the construct departments funct
66. ing employee images click the arrow on the SOL icon highlight SOL Commands and click Enter Command Beta Draft Loading Images 7 1 Using Oracle LOBs to Store and Load Employee Images ORACLE X E QO DATABASE Logout Heb User HR Home fp WRIST VER zi v bd O Homepage J j Discussion Forum a exe S fo Application Builder Object Browser Documentation O Registration v v E j QL SQL Commands 5 Inthe SQL Commands page enter the following CREATE TABLE statement CREATE TABLE employee photos employee id NUMBER employee thumbnail BLOB Click Run ORACLE X E DATABASE User HR Home SQL SQL Commands Autocommit Display 10 CREATE TABLE employee photos employee id NUMBER employee thumbnail BLOB E Results Explain Describe SavedSQL History Enter SQL or PL SQL and click Run to see the results 6 Inthe Results section below the command text area confirm that the table is successfully created Results Explain Describe SavedSQL History Table created 0 55 seconds The HR user must have the CREATE TABLE privilege to perform this command If you get an insufficient privileges error message then logout as the HR user login as system with password manager and execute the following GRANT command GRANT create table TO hr Then login as HR again to execute the CREATE TABLE command
67. input type submit value Save name saveinsertemp gt lt input type submit value Cancel name cancel gt lt form gt END 14 Edit anyco ui inc Addui print modify employee to generate the form to update an employee function ui print modify employee empdetails posturl if empdetails echo lt p gt No Employee record selected lt p gt else inm htmlentities empdetails FIRST NAME Beta Draft Updating Data 5 7 Extending the Basic Employee Form 15 5 8 Oracle Database Express Edition 2 Day Plus PHP Developer Guide 1nm eml htmlentities empdetails LAST NAME htmlentities empdetails EMAIL sal htmlentities empdetails SALARY cpt htmlentities empdetails COMMISSION_PCT Seid echo lt lt lt END lt tr gt lt td gt Employee ID lt td gt lt td gt eid lt td gt lt tr gt lt tr gt lt td gt First Name lt td gt lt td gt lt input type text lt tr gt lt tr gt lt td gt Last Name lt td gt lt td gt lt input type text lt tr gt lt tr gt lt td gt Email Address lt td gt lt td gt lt input type text lt tr gt lt tr gt lt td gt Salary lt td gt lt td gt lt input type text lt tr gt lt tr gt lt td gt Commission lt td gt lt td gt lt input type text lt tr gt htmlentities empdetails EMPLOYEE ID lt form method post action posturl lt table gt name firstname value
68. ion is used to detect if a navigation button was with a HTTP post request to process the page and tracks if the currentdept number is set in the session state Depending on the circumstances the variable current is decremented by one when the previous button is clicked current is incremented by one when the Next button is clicked otherwise current is set to the current department or initialized to one for the first time through A query is formed to obtain all the department rows in ascending sequence of the department id The ORDER BY clause is an essential part of the navigation logic The query is used as a sub query inside the db get page data function to obtain a page of a number of rows where the number of rows per page is specified as the fourth argument to the db get page data function After connecting to the database db get page data is called retrieve the set of rows obtained for the specified query The do get page data function is provide with the connection resource the query string a value in current specifying the first row in the next page of data rows required and the number of rows per page in this case one row per page After calling db get page data to obtain a page of rows the value of current is stored in the application session state Between printing the page header and footer the ui print department function is called to display the recently fetched department row and uses SERV
69. ixaus eundo RD enaegeedoelenieila ettet tete teinte vi Conventions uis bein repe onte Harte iet Deed t tati bb Gere e b ba erc ad vi 1 Introducing PHP with Oracle Database XE Zend Core for Oracle haien a iu tanteu nie e E Oed ei deos 1 1 Purpose merat clon E in A e A LA uate OTT et hu LAPIS Laure ele LU 1 1 Overview of the Sample Application ssssssssseseeenene eene nne nen nennen 1 1 Resources ae T RR E NE MER 1 3 2 Getting Started What You Need 5 neta e E E Ee e iter sins lavvotpsshasbostelesseustatuasetoressds 2 1 Obtaining Oracle Database Express Edition Oracle Database XE 2 1 Obtaining Apache eee eben tete asta tee i eere ee ehe roti ec eere eck 2 1 Obtaining Zend Core for Oracle 5 mei eeu ted ede ficio a tetas 2 1 Testing the Oracle Database XE Installation eee e e eene 2 2 Testing the Apache Installation sese een nenne eren nennen 2 3 Setting Up Zend Core for Oracle eiie E Hie eet e etre d ides td rii redis 2 4 Installing Zend Core for Oracle on Linux sessssssseseseee eee nennen 2 5 Configuring Zend Core for Oracle aite esee tee ehe tice ded assess 2 6 Testing the Zend Core for Oracle Installation sess 2 7 3 Getting Connected Building the Departments Page eee ether aerem bee ee hee ee ee 3 1 Connecting to the Database eoe cm eee mte id a et a t dea 3 4 Other Ways to Connect e ete Dee d ee ete tete eee dan ede ee RA ede e eee respe
70. lary 1 array push bindargs array CPT newemp commpct 1 array push bindargs array DID newemp deptid 1 r db execute statement conn statement err Sbindargs if r construct employees else handle error Cannot insert employee err 12 Edit anyco php Modify construct modify emp to handle errors The function becomes function construct modify emp if isset POST emprec User didn t select a record construct employees else Sempid POST emprec query SELECT employee id first name last name email hire date salary nvl commission pct 0 as commission pct FROM employees WHERE employee id empid conn db connect err if conn handle error Connect Error err else Sbindargs array array push bindargs array EMPID Sempid 1 Semp db do query conn query OCI FETCHSTATEMENT BY ROW err Sbindargs if emp handle error Cannot find details for employee empid err else ui print header Modify Employee ui print modify employee emp 0 SERVER SCRIPT NAME ui print footer date Y m d H i s Beta Draft Updating Data 5 19 Adding Error Recovery 13 Edit anyco php Change modify emp to handle errors The function becomes function modify emp newemp _POST statement UPDATE employees SET first name fnm last name lnm email eml
71. le should default to English The following PHP code will retrieve the ISO locale from the Accept Language HTTP header via the _ SERVER Server variable s SERVER HTTP ACCEPT LANGUAGE Developing Locale Awareness Once the user s locale preference has been determined the application can call locale sensitive functions such as date time and monetary formatting to format the HTML pages according to the cultural conventions of the user s locale When writing global applications across different programming environment the user locale settings must be synchronized between environments For example PHP applications that call PL SQL procedures should map the ISO locales to the corresponding NLS LANGUAGE and NLS TERRITORY values and change the parameter values to match the user s locale before calling the PL SOL procedures cleetable below shows how some of the commonly used locales are defined in ISO and Oracle environ Table 8 1 Locale Representations in ISO SQL and PL SQL Programming Environments Locale Locale ID NLS LANGUAGE NLS_TERRITORY Chinese R P C zh CN SIMPLIFIED CHINA CHINESE Chinese Taiwan zh TW TRADITIONAL TAIWAN CHINESE English U S A en US AMERICAN AMERICA English United en GB ENGLISH UNITED KINGDOM Kingdom French Canada fr CA CANADIAN CANADA FRENCH French France fr FR FRENCH FRANCE German de GERMAN GERMANY Italian it ITALIAN ITALY Japanese ja JAPANESE JAPAN Korean
72. mance 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 persistent_timeout specifies the time in seconds that a process maintains an idle persistent connection oci8 ping_interval specifies the time in seconds that must pass before a persistent connection is pinged to check its validity See the PHP reference manual http www php net manual en ref oci8 php for more information Disconnecting from the Database The PHP engine will automatically close the database connection at the end of the script unless a persistent connection was made To explicitly close a database connection you may call the oci c1ose OCI function with the connection identifier returned by the oci connect call For example php conn oci connect hr hr localhost XE oci close conn 3 6 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft 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 depar
73. me hire date to char salary 999G999D99 as salary NVL commission pct 0 as commission pct to char calc remuneration salary commission pct 9999G999D99 as remuneration FROM employees WHERE department id deptid ORDER BY employee id ASC END get employees END cv types Click Run 6 6 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Using PL SQL Ref Cursors to Return Result Sets DATABASE ORACLE WE Q Q User HR IS BEGIN Home SQL SQL Commands Autocommit Display 10 ICREATE OR REPLACE PACKAGE BODY cv types AS PROCEDURE get employees deptid in number OPEN employees FOR SELECT employee id substr first name 1 1 last_name as employee name hire date to_char salary 999G999D99 as salary v EJ employees in out empinfotyp In the Results section confirm the package body is successfully created 0 06 seconds Results Explain Describe SavedSQL History Package Body 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 amp e Excute the call to the stored procedure stmt BEGIN cv_types get_employees deptid rc END stid oci_parse conn stmt if st
74. messages The db error function calls isset function checks if the message component of the database error structure is set before printing the message or indicating that the error is unknown Edit anyco ui inc To format the results of single row from the DEPARTMENTS table query in a HTML table format insert the following function function ui print department dept if dept echo lt p gt No Department found lt p gt else echo lt lt lt END lt table gt lt tr gt lt th gt Department lt br gt ID lt th gt lt th gt Department lt br gt Name lt th gt lt th gt Manager lt br gt Id lt th gt lt th gt Location ID lt th gt lt tr gt lt tr gt END echo td htmlentities dept DEPARTMENT ID td echo td htmlentities dept DEPARTMENT NAME td echo td htmlentities dept MANAGER ID td echo td htmlentities dept LOCATION ID td echo END tr table END Beta Draft Querying Data 4 3 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 lt php File anyco php require
75. ming convention 1 2 C configuration Apache httpd conf 2 3 configuring Zend Core for Oracle 2 6 Zend Core for Oracle configuration tab 2 6 connecting HRuser 2 2 Oracle Database XE 3 1 creating anyco ui inc application user interface 3 1 directory for HR application files 3 1 PHP files HR application report page 3 1 public html 2 4 D debugging 4 3 DEPARTMENTS 1 1 Index E EMPLOYEES 1 1 F files HR application 3 1 functions ui_print_footer 3 1 ui_print_header 3 1 G GUI password Zend Core for Oracle 2 5 H hello php testing Zend Core for Oracle installation HR application files directory 3 1 user interface 3 1 httpd conf Apache configuration file 2 3 Human Resources HR application 1 1 2 7 installation 2 5 installing Zend Core for Oracle 2 5 Zend Core for Oracle install directory 2 5 installing Oracle Database XE 2 1 L location Zend Core for Oracle 2 5 O Oracle Database XE 1 1 connecting 3 1 installing 2 1 obtaining 2 1 Beta Draft Index 1 prerequisites 2 1 testing availability 2 2 P PHP 1 1 creating files 3 1 PHP functions ui_print_footer 3 1 ui_print_header 3 1 Prerequisits Oracle Database XE 2 1 public virtual directory Apache 2 3 public_html Apache 2 3 creating 2 4 R reporting HR application 3 1 Resources 1 3 restarting Apache 2 4 S sample schemas v
76. mn Employees Marketing PEOR uds nium Saary s Remuneration o 17 AUG 97 6 000 00 72 000 00 C 202 P Fay Modify Delete Insert new employee Return to Departments Any Co 2005 10 10 23 30 32 In the SOL Commands page to log out of the HR database session click the 8 Logout link ORACLE YE Q o DATABASE User HR Beta Draft Executing Stored Procedures and Functions 6 9 Using PL SQL Ref Cursors to Return Result Sets 6 10 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft 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 Using Oracle LOBs to Store and Load Employee Images 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 SHOME public html chap6 SHOME public_html chap7 cd SHOME public html chap7 2 Ina browser enter the URL to access the Oracle Database XE HTMLDB Web page http localhost 8080 htmldb 3 Inthe Oracle Database XE Login page enter hr in the Username and Password fields Click Login ORACLE DATABASE Username hr Password 1 O Homepage Login d O Discussion Forum O Documentation O Registration 4 Inthe Home page to create a new table for stor
77. mp EMPLOYEE_ID lt td gt echo td htmlentities S emp EMPLOYEE NAME td echo td htmlentities S emp HIRE DATE td echo td align right htmlentities Semp SALARY td echo td align right htmlentities S emp COMMISSION PCT td echo td align right htmlentities emp REMUNERATION td echo lt tr gt 13 Save the changes to your application files In a browser enter the following URL to test the application http localhost lt username gt chap6 anyco php 14 In the Departments form click Show Employees Department Administration tac ares Number of Manager Employees Name Administration 1 J Whalen United States of America Previous Next Show Employees X Location 2005 10 10 22 12 54 Any Co In the Employees page for the department the employee remuneration is displayed in the last column Employees Administration EE mm Hiredate Salary UIS Remuneration o 200 J Whalen 17 SEP 87 4 400 00 0 52 800 00 Modify Delete Insert new employee Return to Departments 2005 10 10 22 14 31 Any Co Using PL SQL Ref Cursors to Return Result Sets Data sets can be returned as REF CURSORS from PL SQL blocks in a PHP script This can be useful where the dataset requires complex functionality A REF CURSOR in PL SQL is a type definition that is assigned to a cursor varia
78. n add a loop to set any return bind values function db execute statement conn statement amp e amp bindvars array Qoci execute stid if r e db error stid FILE LINE return false outbinds array foreach bindvars as b outbinds b 0 b 0 bindvars outbinds return true r 13 Edit anyco php Change the INSERT statement in insert_new_emp so that it returns the new employee identifier in the bind variable neweid This value is inserted with the image into the new EMPLOYEE PHOTOS table I statement INSERT INTO employees employee id first name last name email hire date job id salary commission pct department id VALUES employees seq nextval fnm lnm eml hdt jid sal cpt did RETURNING employee id into neweid Alsoin insert new emp addan array push to set a new bind variable NEWEID at the end of the list of array push calls array push S bindargs array CPT newemp commpct 1 array push bindargs array DID newemp deptid 1 array push bindargs array NEWEID null 10 Because the value of NEWID is being retrieved with the RETURNING clause in the INSERT statement its initial value is set to NULL The length is set to 10 to allow enough digits in the return value Beta Draft Loading Images 7 5 Using Oracle LOBs to Store and Load Employee Images
79. ndargs In this example the bind variable called DID is an input argument in the parameterized query and it is associated with the value 80 Later the value of the bind variable will be dynamically determined In addition the length component is passed as 1 as the OCI8 layer can determine the length This is not the case for bind variables accepting output results from a query Edit anyco_db inc Modify the db do query function to accept a bindvars array variable as a third parameter Call the oci bind by name OCISB call to associate the PHP values supplied in bindvars parameter with bind variables in the query php File anyco db inc function db do query conn statement bindvars array stid oci_parse conn statement Beta Draft Querying Data 4 5 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 The binding is performed in the foreach loop before the oci execute is done For each entry in bindvars array the first element contains the query bind variable name that is used to create a
80. ntdept amp amp isset POST nextdept Scurrent SESSION currentdept 1 elseif isset POST showdept amp amp isset SESSION currentdept Scurrent SESSION currentdept else Scurrent 1 query SELECT d department id d department name substr e first_name 1 1 e last name as manager name Cc country name count e2 employee id as number of employees FROM departments d employees e locations 1 countries c employees e2 WHERE d manager id e employee id AND d location id l location id AND d department id e2 department id 5 16 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft 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 department id ASC conn db connect err if conn handle error Connection Error err else dept db get page data conn query current 1 err if dept false Use so empty array at end of fetch is not matched handle error Cannot fetch Departments err else if isset dept 0 DEPARTMENT ID amp amp current 1 no more records so go back one current dept db get page data conn query current 1 err deptid dept 0 DEPARTMENT ID SESSION deptid deptid SESSION currentdept current deptname get dept nam
81. o gt lt td gt lt tr gt 10 Create anyco_im php This file accepts an employee identifier as a URL parameter reads the thumbnail from the Photograph column for that employee and returns the thumbnail image to be displayed lt php anyco im php require anyco cn inc require anyco db inc construct image function construct image if isset GET showempphoto return empid GET showempphoto conn db connect err if conn return query SELECT employee thumbnail FROM employee photos Beta Draft Loading Images 7 3 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 1oad 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 Comment out the header line to see the text and debug header Content type image JPEG echo result The construct image function uses the OCI Lob 10ad function to retrieve the Oracle LOB data which is the image data The PHP header function sets the MIME type in the HTTP response header to ensure the browser interprets the data as a JPEG image
82. o ui inc This file contains the functions used for presentation of data and forms in a HTML page anyco cn inc This file contains definitions for database connection information the database username password and database connect identifier anyco db inc This file contains database logic to create connections execute queries and execute data manipulation statements anyco im php This file contains logic to retrieve an image from a database column and send it to a browser for display as a JPEG image style css This file contains Cascading Style Sheet definitions for various HTML tags generated by the application It manages the look and feel of the application Files with the suffix inc are PHP code files included in other PHP files Files with the suffix php can be loaded in a browser You can create and edit the PHP application source files in a text editor or any tool that supports PHP development 1 2 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Resources 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
83. on your system In the page confirming your Web server selection at the Do you wish to proceed prompt click Yes In the next installation page you are prompted to Please select an installation method for Apache 2 0 52 Select the first entry Apache module as the method and click OK In the next installation page prompting Please select a virtual server for the Zend Core GUI select the first entry Main Server click OK In the next installation page after selecting the virtual server at the Would you like to restart the Web Server prompt click Yes When prompted Would you like to configure another Web Server to use Zend Core click No In the final installation page containing Thank you for installing Zend Core for Oracle lists useful configuration commands and Web page for administration of the Zend Core engine Take note of the information and click EXIT When the Zend Core installation pages are terminated a message is displayed in your command window The Zend Core for Oracle installation is now complete Configuring Zend Core for Oracle In this section you configure environment variables and Zend Core directives that control default error reporting in web pages 1 In a web browser enter the following URL to access the Zend Core administration page http 1localhost ZendCore The Zend Core for Oracle welcome page is displayed In the Zend Core for Oracle Welcome page in the Password field en
84. ons open the Zend Core for Oracle console and go to the Configuration tab Navigate to the Extensions sub tab and expand the Zend Core Extensions tree control Your application code should use functions such as mb_strlen to calculate the number of characters in strings This may return different value than strlen which will return the number of bytes in a string Once the mbstring extension has been enabled and the web server restarted several configuration options become available You can change the behavior of the standard PHP string functions by setting mbstring func_overload to one of the Overload settings The PHP mbstring reference manual http www php net mbstring contains more information 8 2 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Developing Locale Awareness Determining User s Locale Ina 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 Every HTTP request sends the default ISO locale of the browser with the Accept Language HTTP header If the Accept Language header is NULL then the loca
85. onstruct employees deptid SESSION deptid conn db connect err if conn handle error Connection Error err else emp db get employees rc conn deptid err if emp handle error Cannot fetch Employees Serr else deptname get dept name conn deptid ui_print_header Employees deptname ui_print_employees Semp SERVER SCRIPT NAME ui print footer date Y m d H i s 5 Save the changes to your application files In a browser enter the following URL to test the application http localhost lt username gt chap6 anyco php 6 Inthe Departments form click Next gt to navigate to the Marketing department page 6 8 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Using PL SQL Ref Cursors to Return Result Sets Department Administration ee E Number of Manager location Employees Name Frente Administration Jewel J Whalen United States of America States of America lt Previous Next gt Show Employees 2005 10 10 23 27 47 In the Marketing department page click Show Employees 7 Department Marketing Mild Eer Number of Manager location Employees Name Marketing M Hartstein Canada Previous Next gt Show d Any Co 2005 10 10 23 28 36 In the Employees page for the Marketing department the employee records remuneration is displayed in the last colu
86. oyee id EmpID 2 last name Last Name Beta Draft Building Global Applications 8 7 Presenting Data following User s Locale Convention 3 from employees 4 where last name like C 5 order by last name EMPID Last Name 187 Cabrio 148 Cambrault 154 Cambrault 119 Colmenares 110 Chen 88 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 Setting this parameter prior to submitting your SQL statement will ensure that the language specific database error messages will be returned to the application Consider the following server message ORA 00942 table or view does not exist When the NLS LANGUAGE parameter is set to French the server message appears as follows ORA 00942 table ou vue inexistante Seealso The Working in a Global Environment chapter in the Oracle Database Express Edition 2 Day Developer Guide for more discussion of globalization support features within Oracle Database Express Edition 8 8 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Symbols HOME public_html 1 3 A 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 na
87. p4 SHOME public_html chap5 cd HOME public_html chap5 Edit anyco php Add form handler control logic to manage the requests for showing inserting updating and deleting employees lt php File anyco php require anyco_cn inc require anyco_db inc require anyco_ui inc session_start Start form handler code if isset POST insertemp construct_insert_emp elseif isset POST saveinsertemp insert new emp elseif isset POST modifyemp construct modify emp elseif isset POST savemodifiedemp modify emp elseif isset POST deleteemp delete emp Beta Draft Updating Data 5 1 Extending the Basic Employee Form else construct employees 3 Editanyco php Add the construct insert emp function function construct insert emp 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 gt 10 Default to department 10 HIRE DATE gt date SYSDATE 0 ALLJOBIDS jobs JOB ID ALLJOBTITLES gt jobs JOB TITLE ui_print_header Insert New Employee ui_print_insert_employee emp SERVER SCRIPT NAME Note The two kinds of date used 1 SYSD
88. pectively and insert a Number of Employees field after Department Name Make the necessary changes in the table header and data fields The function becomes function ui print department dept posturl if dept echo p No Department found lt p gt else echo lt lt lt END lt table gt lt tr gt lt th gt Department lt br gt ID lt th gt lt th gt Department lt br gt Name lt th gt lt th gt Number of lt br gt Employees lt th gt lt th gt Manager lt br gt Name lt th gt lt th gt Location lt th gt lt tr gt lt tr gt END echo td htmlentities dept DEPARTMENT ID td echo td htmlentities dept DEPARTMENT NAME td echo td htmlentities dept NUMBER OF EMPLOYEES td echo td htmlentities dept MANAGER NAME td echo td htmlentities dept COUNTRY NAME td echo END lt tr gt lt table gt lt form method post action posturl gt lt input type submit value lt Previous name prevdept gt lt input type submit value Next gt name nextdept gt lt form gt END There is no need to pass a bindargs parameter to the db_do_query call because we are not using bind variables The db do query declaration will provide a default value of an empty array automatically PHP allows functions to have variable numbers of parameters 2 Edit anyco php Replace the query string in construct departmen
89. resent data with appropriate locale characteristics such as using the correct date and number formats Oracle Database Express is fully internationalized to provide a global platform for developing and deploying global applications This chapter has the following topics a Establishing the Environment between Oracle and PHP String Manipulation Determining User s Locale Developing Locale Awareness Encoding HTML Pages Organizing the Content of HTML Pages for Translation Presenting Data following User s Locale Convention Establishing the Environment between Oracle and PHP Correctly setting up the connectivity between the PHP engine and the Oracle database is first step in building a global application it guarantees data integrity across all tiers Most internet based standards support Unicode as a character encoding in this chapter we will focus on using Unicode as the character set for data exchange PHP is an Oracle OCI application and rules that apply to OCI also apply to PHP Oracle locale behavior including the client character set used in OCI application are defined by an environment variable NLS LANG which has the form language territory character set For example for a German user in Germany running their application in Unicode NLS LANG should be set to GERMAN GERMANY AL32UTF8 Beta Draft Building Global Applications 8 1 String Manipulation The language and territory settings cont
90. rol Oracle behaviors such as the Oracle date format error message language and the rules used for sort order The character set AL32UTFS is Oracle s name for UTF 8 See also Oracle Database Express Edition installation guides for information on the NLS_LANG environment variable When Zend Core for Oracle is installed on Apache NLS_LANG can be set in etc profile export NLS LANG GERMAN GERMANY AL32UTF8 If Zend Core for Oracle is installed on Oracle HTTP Server NLS LANG needs to be set as an environment variable in SORACLE HOME opmn con f opmn xml ias component id HTTP Server process type id HTTP Server module id 0HS gt environment variable id PERL5LIB value D oracle 1012J32EE Apache Apache mod_perl site 5 6 1 lib gt variable id PHPRC value D oracle 1012J2EE Apache Apache conf gt variable id NLS_LANG value german germany al32utf8 environment module data category id start parameters gt data id start mode value ssl disabled gt lt category gt lt module data gt lt process set id HTTP_Server numprocs 1 gt lt process type gt lt ias component gt The web listener will need to be restarted to pick up the change String Manipulation PHP was designed to work with the ISO 8859 1 character set To handle other character sets specifically multi byte character sets a set of Multi Byte String Functions is available To enable these functi
91. rray_push bindargs array_push bindargs array_push bindargs array_push bindargs array_push bindargs array EID newemp empid 1 array FNM newemp firstname 1 array LNM newemp lastname 1 array EML newemp email 1 array SAL newemp salary 1 array CPT newemp commpct 1 r db execute statement conn statement bindargs construct employees 8 Editanyco php Add delete emp to delete an employee row from the EMPLOYE I ES table function delete emp empid POST emprec statement DELETE FROM employees WHERE employee id empid conn db connect bindargs array array push bindargs array EMPID empid 10 r db execute statement conn statement bindargs construct employees 9 Edit anyco_db inc Add resulttype asa third parameter to db do query Replace the last parameter value 5 4 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Extending the Basic Employee Form 10 11 OCI FETCHSTATEMENT BY ROW intheoci 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 Edit anyco_db inc Inside the d
92. rt and PHP Developer Center updates for Zend Core for Oracle will be made available directly s 3 from Zend _PHP Discussion Forum Podcast A Quick Tour of Zend Download Zend Core for Oracle Beta from Zend com Core for Oracle gt Product Information Related Technologies Oracle Database 10g Data Sheet PDF i ide PDF Oracle Application Server 10g Installation Guide PDF Oracle Application Server 10 3 Savethe 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 SOL command as a user with DBA privileges alter user hr account unlock identified by hr 2 To test that Oracle Database XE is accessible connect to the database using the HTML DB web interface or enter the following commands in a command window export ORACLE HOME export PATH S ORACLE HOME bin PATH sqlplus hr hr localhost gstokol frodo export ORACLE HOME usr lib oracle xe app oracle prod uct 10 2 0 server gstokot frodo export PATH 0RACLE_HOME bin PATH gstokol frodo sqlplus hr hr XE SQL Plus Release 10 2 0 1 0 Production on Tue Sep 27 11 04 26 2005 Copyright c 1982 2005 Oracle All rights reserved Connected to Oracle Database 10g XE Edition Release 10 2 0 1 0 Beta sor ff Terminate the SQL Plus session by entering the exit
93. rt pages where the footer section of the page includes a date and time This chapter has the following topics Building the Departments Page Connecting to the Database Disconnecting from the Database Note For simplicity the username and password are written into this sample application code For applications that will be deployed coding the username and password strings directly into your application source code is not recommended Some other technique such as implementing a dialog that prompts the end user for the username and password is recommended See the Oracle Database Security Guide and documentation for your development environment for details on security features and practices Building the Departments Page 1 To create a directory for the application files in a command window enter the following command mkdir HOME public_html chap3 cd SHOME public html chap3 2 To start creating the PHP application user interface framework create a file called anyco ui inc that contains the two functions ui print header and ui print footer with their parameters to enable web pages with consistent header and footer sections php function ui print header title Beta Draft Getting Connected 3 1 Building the Departments Page title htmlentities title echo lt lt lt END lt DOCTYPE HTML PUBLIC W3C DTD HTML 4 01 EN http www w3 org TR htm14 strict dtd gt html
94. salary sal commission pct cpt WHERE employee id eid conn db connect err if conn handle error Connect Error err else Sbindargs array array push bindargs array EID newemp empid 1 array push bindargs array FNM newemp firstname 1 array push bindargs array LNM newemp lastname 1 array push bindargs array EML newemp email 1 array push bindargs array SAL newemp salary 1 array push bindargs array CPT newemp commpct 1 r db execute statement conn statement err bindargs if r handle error Cannot update employee newemp empid err else construct employees 14 Edit anyco php Modify delete emp to handle errors The function becomes function delete emp if isset POST emprec User didn t select a record construct employees else 5 20 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Sempid POST emprec conn db connect err if conn handle error Connection Error err else statement DELETE FROM employees WHERE employee id empid bindargs array array push bindargs array EMPID r db execute statement conn Sempid 1 statement err Sbindargs if r Beta Draft Adding Error Recovery handle error Error deleting employee empid err else construct employe
95. sert New Employee Save Cancel 2005 10 11 11 21 05 Any Co 19 In the File Upload window browser for and select an image file Click Open v File Upload x Home TA Home public htm Desktop Filesystem Name Moi Floppy Drive 2 chap2 Sunday CD ROM Drive Bi chaps Sunday chap4 10 03 2005 2 chaps Yesterday 2 chap6 Yesterday 3 chap7 Today dump array php 09 30 2005 index html 09 27 2005 db Add Remove All Files 9g 20 In the Insert New Employee page click Save Beta Draft Loading Images 7 7 Resizing Images Insert New Employee Department ID Last Name oes Hiredate hrocro 05 E Manager Commission 96 EL Photo Sag Cancel 2005 10 11 12 32 04 On success the Employees page is displayed with the new employee including the image which is displayed at its original size Employees Administration ied cra Hiredate Salary gommission Remuneration Photograph Employee s T Whalen 17 separ 4 400 00 52 800 00 photo 0 m G Stokol 11 OCT 05 8 000 00 Modify Delete Insert new employee Return to Departments 2005 10 11 12 27 16 96 000 00 Any Co Resizing Images The Employee thumbnails can be resized with PHP s GD graphicsextension 1 To turn on the graphic extension enter the following UR
96. ssful deletion the deleted row does not appear in the list of employees records redisplayed in the Employees page j 206 E Gietz pes 07 JUN 94 cm 8 300 00 3 o Modify eov Delete aie Insert new employee 2005 10 04 13 52 19 Any Co Combining Departments and Employees 1 Edit anyco php Modify the query in construct employees to include a WHERE clause to compare the department_id with a value in a bind variable called did This makes the page display employees in one department at a time Get the deptid session parameter value to populate the bind variable query SELECT employee_id Beta Draft Updating Data 5 11 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 Editanyco php In construct employees update the call to db do query to pass the bind information conn oci connect bindargs array array push bindargs array DID deptid 1 Semp db do query conn query OCI FETCHSTATEMENT BY ROW bindargs 3 Edit anyco php In construct departments save the department identifier in a session parameter SESSION currentdept current SESSION deptid deptid This saves the current department identi
97. ssigned to the new record may be different on your system to the one shown in the following example 206 W Gietz 07 JUN 94 8 300 00 Ez 0 eT cene eres m 5 Modify Delete Insert new employee 2005 10 04 13 40 42 Any Co 19 To modify the new employee select the radio button next to the new employee row click Modify 206 W Gietz 07 JUN 94 8 300 00 c 0 CIENT TN 77777 E777 NN Modi X Delete Insert new employee 2005 10 04 13 40 42 Any Co 20 In the Modify Employee page modify the Email Address field to JBOND and increase the Salary to 7100 click Save 5 10 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Combining Departments and Employees Modify Employee Employee ID First Name Jams Last Name Bod Email Address BoD Salary 7100 p 2005 10 04 13 45 04 Any Co 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 js 216 f nerk m eu tm 101 00 o Modify Delete EL Insert new employee 2005 10 04 13 47 38 Any Co 22 To remove the new employee row select the radio button for the new employee row click Delete 216 line e nerk 10 JUL 04 101 00 Modify Lum De ete TN Insert new employee 2005 10 04 13 47 38 Any Co On succe
98. su Password root frodo apachectl restart root frodo 4 Inthe command window login as a normal non root user and create a public html sub directory in the users HOME directory by using the following commands su gstokol Password for gstokol enter the password mkdir S HOME public html gstoko frodo File Edit View Terminal Tabs Help gstokol frodo su gstokol Password gstokol frodo mkdir HOME public html gstokol frodo exit 5 If Apache is not running and you get an error page or do not get the expected results In a command window start the Apache Web server on your machine using the following commands su Password lt enter your su root password gt apachectl start If the Apache Web server does not start you may need to check the error log files to determine the cause It may be a configuration error Setting Up Zend Core for Oracle This tutorial is specific to PHP in Zend Core for Oracle For detailed setup information for Zend Core for Oracle see the Installation Guide under Product Information on the Zend Core for Oracle web page at http www oracle com technology tech php zendcore index html 2 4 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Setting Up Zend Core for Oracle Installing Zend Core for Oracle on Linux 1 To extract the contents of the downloaded Zend Core for Ora
99. te 3 5 Disconnecting from the Database sse entente 3 6 4 Querying Data Centralizing the Database Application Logic sese eee eee 4 1 Writing Queries with Bind Variables sse eee ee en e nenne 4 4 Navigating Through Database Records esee eee e nenne nenne 4 6 Beta Draft iii Extending the Basic Departments Form sss enne 4 10 Building the Basic Employee Form eee eene eene enne 4 12 5 Updating Data Extending the Basic Employee Form 1 eene nn n enne neni 5 1 Combining Departments and Employees sse ee eee 5 11 Adding Error Recovery neniarn nin erea aan eene n enne en nennen neret nnne 5 14 Further Error Handling onore o duet ere ERI RR en ee e a re nre t enn 5 22 6 Executing Stored Procedures and Functions Using PL SQL to Capture Business Logic sess tena 6 1 Using PL SQL Ref Cursors to Return Result Sets sese 6 5 7 Loading Images Using Oracle LOBs to Store and Load Employee Images sess 7 1 Resizing Imagess endisse nh dta P a a Bd te a deste 7 8 8 Building Global Applications Establishing the Environment between Oracle and PHP 1 eee 8 1 String Manipulation tenens d ierat iv diea ati a nd ai o EO he Een 8 2 Determining User s Locale ooo pecore nana vede adotta 8 3 Developing Locale Awareness
100. ter the GUI password which you provided during Zend Core for Oracle installation Click the login gt gt gt icon In the Zend Core for Oracle administration GUI page the main Control Center tab page is displayed with the System Overview tab page selected To display the configuration options click the Configuration tab 2 6 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Testing the Zend Core for Oracle Installation In the PHP tab page which is selected by default expand the Error Handling and Logging configuration entry by clicking the icon 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 In the PHP Configuration page to save the configuration change click the Save Settings link Saving configuration changes typically requires the Apache server to be restarted Under the PHP Configuration page header notice the Please Restart Apache message In the PHP Configuration page to restart the Apache server click the Restart Server link The PHP Configuration page is refreshed when the Apache server has been restarted In the Zend Core for Oracle administration page to exit the GUI page click the Logout link Now that the basic configuration changes have been made yo
101. the desired language of the query Presenting Data following User s Locale Convention Data in the application needs to be presented in a way that conforms to the user s expectation if not the meaning of the data can sometimes be mis interpreted For example the date 12 11 05 implies 11th December 2005 in the United States whereas in the United Kingdom it means 12th November 2005 Similar confusion exists for number and monetary formats the symbol dot is a decimal separator in the United States in Germany this symbol is recognized as a thousand separator Different languages have their own sorting rules some languages are collated according to the letter sequence in the alphabet some according to the number of stroke counts in the letter and there are some languages which are ordered by the pronunciation of the words Presenting data not sorted in the linguistic sequence that your users are accustomed to can make searching for information difficult and time consuming Oracle Database Express offers many features that help to refine the presentation of data when the user s locale preference is known Here are some examples of locale sensitive operations in SOL Beta Draft Building Global Applications 8 5 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
102. tment This chapter has the following topics Centralizing the Database Application Logic Writing Queries with Bind Variables Navigating Through Database Records Extending the Basic Departments Form Building the Basic Employee Form Centralizing the Database Application Logic Modify your application code by moving the database access logic into separate files for inclusion in the PHP application Create new files in the HOME public html chapA directory Copy the files completed in chapter 3 to a new chap4 directory mkdir S HOME public html chap4 cp SHOME public html chap3 SHOME public_html chap4 cd SHOME public html chap4 Using your preferred editor create a file called anyco cn inc which defines named constants for the database connection information This file enables to change connection information in one place php File anyco cn inc define ORA CON UN hr Username define ORA CON PW hr Password define ORA CON DB localhost XE Connection identifier Create a file called anyco_db inc that declares functions for creating a database connection executing a query and disconnecting from the database Use the following logic which includes some error handling that is managed by calling an additional function called dob error php File anyco db inc Beta Draft Querying Data 4 1 Centralizing the Database Application Logic function db connect
103. ts with query SELECT d department id d department name substr e first name 1 1 e last name as manager name C country name count e2 employee id as number of employees FROM departments d employees e locations 1 countries c employees e2 WHERE d manager id e employee id AND d location id l location id AND d department id e2 department id Beta Draft Querying Data 4 11 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 1last 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 Savethe changes to your files and test the changes by entering the following URL in a Web browser http 1localhost username chap4 anyco php The Web page result should resemble the following output Department Department Department Number of Manager Location Employees Name ID Name 10 Administration 4 J Whalen United States of America Previous Next 2005 10 03 10 56 55 Any Co Building the Basic Employee Form To display employees perform the following tasks 1 Edit anyco php Add a function construct employees which constructs the employee query calls db do query to execute the query and prints the results using u print employe
104. ture Business Logic ORACLE x Sakae AE Q f Logout Het User SYSTEM Home I Qe vj dy mi O Homepage J J Discussion Forum Application Builder Object Browser Documentation Registration s us SQL fe SQL Commands SQL Commands Query Builder gt 4 Inthe SOL Commands page to assign the create procedure privilege to the HR user enter the following grant command grant create procedure to hr Click Run ORACLE DATABASE XE Home ogout Hek User SYSTEM Home SQL SQL Commands Autocommit Display 10 grant create procedure to hr E Results Explain Describe SavedSQL History Enter SQL or PL SQL and click Run to see the results A message similar to the following appears in the Results section below the text area where the command was entered Results Explain Describe SavedSQL History Procedure created 0 27 seconds 5 Click the Logout link to terminate the HTMLDB session ORACLE a DATABASE XE e b Home Lafgut Heb User SYSTEM 6 Inthe Logout Confirmation page click the Login link 6 2 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Using PL SQL to Capture Business Logic ORACLE X E DATABASE You are now logged out 7 Inthe Oracle Database XE Login page enter the Username hr and Password hr Click Login ORACLE X E DATABASE Username hr
105. u may now proceed to the next section to test the Zend Core for Oracle installation Testing the Zend Core for Oracle Installation 1 To get started create a directory called chap2 as a child of your HOME public html directory and change directory to HOME public html chap2 by entering the following commands mkdir S HOME public html chap2 cd SHOME public html chap2 gstokol frodo cd HOME public html chap2 gstokol frodo chap2 gstokol frodo mkdir HOME public html chap2 To check that PHP works with your preferred editor create a file called hello php which contains the following HTML text lt php echo Hello world gt Open a web browser and enter the following URL http localhost lt username gt chap2 hello php The result in the browser is Hello world Beta Draft Getting Started 2 7 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 repo
106. use constants defined in anyco_cn inc conn oci connect ORA CON UN ORA CON PW ORA CON DB if conn db error null FILE LINE return conn function db do query conn statement stid oci_parse conn statement if stid db_error conn FILE LINE r oci execute stid OCI DEFAULT if r db error stid FILE LINE r oci fetch all stid results null null OCI FETCHSTATEMENT BY ROW return results r is the resource containing the error Pass no argument or false for connection errors function db error r false file line err r oci error r oci error if isset err message m htmlentities err message else m Unknown DB error echo lt p gt lt b gt Error lt b gt at line line of file lt p gt echo lt pre gt m lt pre gt exit The db do query has been written to use theoci fetch all OCI8 function instead of oci fetch array Theoci fetch all function accepts five parameters a Sstid the statement identifier for the statement executed Sresults the output array variable containing the data returned for the query a The nul1 in the third parameter for the number of initial rows to skip is ignored 4 2 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft Centralizing the Database Application Logic The null inthe
107. w h w nw w scale nh h scale dest img imagecreatetruecolor nw nh imagecopyresampled dest img src img 0 0 0 0 nw nh w h imagejpeg dest img imgfile overwrite file with new thumbnail imagedestroy src img imagedestroy dest img if lob gt savefile Simgfile The imagecreatefromjpeg function reads the JPEG file and creates an internal representation used by subsequent GD functions Next new dimensions are calculated with the longest side no larger than 100 pixels A template image with the new size is created using imagecreatetruecolor Data from the original image is sampled into it with imagecopyresampled to create the thumbnail The thumbnail is written back to the original file and the internal representations of the images are freed The existing code in db insert thumbnail uploads the image file to the database as it did in the previous section Enter the following URL in your browser to test the changes in your application http 1localhost username chap7 anyco php In the Departments page navigate to the employees page by clicking Show Employees Beta Draft Loading Images 7 9 Resizing Images 7 10 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Department Administration Department Department Numberof Manager Location Employees Name ID Name 10 Administration 2 J Whalen United States of America
108. y xe See also a Oracle Database XE discussion Forum at http www oracle com technology xe forum a Oracle Database XE documentation at http www oracle com technology xe documentation Obtaining Apache Apache is normally a standard part of the Linux environment If Apache is not available on your Linux platform you can download it from http httpd apache org Obtaining Zend Core for Oracle 1 To obtain Zend Core for Oracle for the Linux Platform enter the following URL in your Web Browser Beta Draft Getting Started 2 1 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 Downloads Documentation Discussion Forums Articles Sample Code Training RSSEXM Resources For Zend Core Zend Core for Oracle FREE DOWNLOAD Oracle Zend Core for Oracle developed i in partnership with Zend Downkad Technologies supports businesses using PHP with Oracle Database for mission critical Web applications It provides a seamless out of the box experience delivering a stable high performance easy to install and supported PHP development and production environment fully integrated with the Oracle Database Zend Core for Oracle Beta from Zend com Zend Core for Oracle will be available as a free download from LERETUMOFE Zend in late 2005 itis currently available in Beta Suppo

Oracle B25-317-01 User's Manual

Contents

Download Pdf Manuals

Related Search

Related Contents