PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax: A practical guide to combining the power, performance, ... development time, and high performance of PHP

392 Pages·2007·7.1 MB·English

by  Yuli Vasiliev

PHP Oracle Web Development
Data Processing, Security, Caching, XML,
Web Services, and AJAX
A practical guide to combining the power, performance,
scalability, and reliability of Oracle Database with
the ease of use, short development time, and high
performance of PHP
Yuli Vasiliev
BIRMINGHAM - MUMBAI
PHP Oracle Web Development
Data Processing, Security, Caching, XML, Web Services, and AJAX
Copyright © 2007 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval
system, or transmitted in any form or by any means, without the prior written
permission of the publisher, except in the case of brief quotations embedded in
critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of
the information presented. However, the information contained in this book is sold
without warranty, either express or implied. Neither the author, Packt Publishing,
nor its dealers or distributors will be held liable for any damages caused or alleged to
be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all the
companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information.
First published: August 2007
Production Reference: 1240707
Published by Packt Publishing Ltd.
32 Lincoln Road
Olton
Birmingham, B27 6PA, UK.
ISBN 978-1-847193-63-6
www.packtpub.com
Cover Image by Vinayak Chittar ([email protected])
Credits
Author Project Manager
Yuli Vasiliev Patricia Weir
Reviewer Indexer
Anup Nanda Bhushan Pangaonkar
Acquisition Editor Proofreader
Priyanka Baruah Chris Smith
Technical Editor Production Coordinator
Akshara Aware Shantanu Zagade
Code Testing Cover Designer
Bansari Barot Shantanu Zagade
Editorial Manager
Dipali Chittar
About the Author
Yuli Vasiliev is a software developer, freelance author, and a consultant currently
specializing in open-source development, Oracle technologies, and service-oriented
architecture (SOA). He has over 10 years of software development experience as well
as several years of technical writing experience. He has written a series of technical
articles for Oracle Technology Network (OTN) and Oracle Magazine.
About the Reviewer
Arup Nanda ([email protected]) has been an Oracle DBA since 1993, when
the world was slowly turning its attention to a big force to reckon with—Oracle7. But
he was not so lucky; he was entrusted with a production Oracle database running
Oracle 6. Since then, he has never been out of the Oracle DBA career path—weaving
several interesting situations from modeling to performance tuning to backup/
recovery and beyond, with lots of gray hairs to document each ORA-600. He has
written several articles for publications such as Oracle Magazine and for Oracle Tech
Net, he has presented at conferences such as Oracle World and IOUG Live, and he
has coauthored four books. In 2003, Oracle chose him as the DBA of the Year. He
lives in Danbury, Connecticut, with his wife, Anu, and their son, Anish.

Table of Contents
Preface 1
Chapter 1: Getting Started with PHP and Oracle 5
Why PHP and Oracle? 6
Simplicity and Flexibility 6
Performance 7
Robustness 7
Exploring PHP and Oracle Alternatives 8
PHP and MySQL 8
JSF and Oracle 8
What You Need to Start 9
Pieces of Software Required 9
Oracle Database Considerations 10
Understanding the Oracle Database 10
Choosing Between Oracle Database Editions 11
Obtaining Oracle Database Software 12
PHP Considerations 13
Apache HTTP Server 13
Why PHP 5 14
Obtaining PHP 15
Making PHP and Oracle Work Together 15
Oracle Instant Client 15
Zend Core for Oracle 16
Using Oracle SQL*Plus 17
Why Use SQL*Plus in PHP/Oracle Development? 17
Connecting to a Database with SQL*Plus 18
Running Scripts in SQL*Plus 20
Putting It All Together 21
Creating Your First PHP/Oracle Application 23
Connecting to a Database 26
Using the Local Naming Method 26
Using the Easy Connect Method 27
Table of Contents
Issuing an SQL Statement Against the Database 28
Fetching and Displaying Results 28
Summary 29
Chapter 2: PHP and Oracle Connection 31
Introducing the PHP OCI8 Extension 31
Why Use OCI8 Extension? 32
Processing SQL Statements with OCI8 32
Connecting to Oracle with OCI8 36
Defining a Connection String 37
OCI8 Functions for Connecting to Oracle 38
Parsing and Executing SQL Statements with OCI8 39
Preparing SQL Statements for Execution 39
Using Bind Variables 40
Executing SQL Statements 42
Handling Errors 42
Using the oci_error Function 42
Using the trigger_error Function 43
Using Exceptions 44
Fetching Results with OCI8 44
OCI8 Functions for Fetching 45
Fetching the Next Row 46
Fetching All the Rows 47
Alternatives to PHP OCI8 Extension 49
Using PEAR DB 49
Using ADOdb 51
Using PDO 52
Creating Your Own Library on Top of OCI8 53
Summary 54
Chapter 3: Data Processing 57
Implementing the Business Logic of a PHP/Oracle Application 58
When to Move the Data to the Processing 58
Advantages of Moving the Processing to the Data 59
Ways of Implementing Business Logic Inside the Database 60
Interaction between Components Implementing Business Logic 61
Using Complex SQL Statements 62
Employing Oracle SQL Functions in Queries 62
Oracle SQL Functions versus PHP Data Processing 62
Aggregate Functions 65
The GROUP BY Clause 66
Using Join Queries 66
Taking Advantage of Views 69
[ ii ]
Table of Contents
The Key Benefits of Using Views 69
Hiding Data Complexity with Views 70
Using the WHERE Clause 71
Using Stored Subprograms 73
What are Stored Subprograms? 73
Advantages of Stored Subprograms 75
An Example of When to Use a Stored Subprogram 76
Creating Stored Subprograms 80
Calling Stored Subprograms from PHP 82
Using Triggers 83
Creating Triggers 84
Firing Triggers 85
Calling Stored Procedures from a Trigger 85
Summary 86
Chapter 4: Transactions 89
Overview of Transactions 89
What is a Transaction? 90
What are ACID Rules? 91
How Transactions Work in Oracle 92
Using Transactions in PHP/Oracle Applications 93
Structuring a PHP/Oracle Application to Control Transactions 96
Developing Transactional Code 99
Controlling Transactions from PHP 99
Moving Transactional Code to the Database 105
Using Triggers 105
Dealing with Statement-Level Rollbacks 106
Transaction Isolation Considerations 109
What OCI8 Connection Function to Choose 110
Concurrent Update Issues 113
Locking Issues 114
Lost Updates 115
Autonomous Transactions 118
Summary 121
Chapter 5: Object-Oriented Approach 123
Implementing PHP Classes to Interact with Oracle 123
Building Blocks of Applications 124
Creating a Custom PHP Class from Scratch 125
Testing the Newly Created Class 127
Taking Advantage of PHP 5's Object-Oriented Features 128
Functionality and Implementation 130
Reusability 132
Handling Exceptions 132
[ iii ]