Alison Holloway's Blog

It's easy to get confused as to how to specificy your Oracle database connection string, and there's a handy new feature in Oracle 10g that makes this a whole lot easier. So here's a little rundown of the three ways to connect to Oracle databases. You can use the:

• tnsnames.ora file
• Full connection string
• Easy connect string

tnsnames.ora File

The tnsnames.ora file is a client side file that maps an alias used by client programs to a database service. It is used to connect to a non-default database. Here you have to have an entry in the tnsnames.ora file, and reference the alias to that entry in your connection code.

PHP code:

oci_connect($un, $pw, 'MYDB');

tnsnames.ora entry

MYDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = mymachine.mydomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYDB.AU.ORACLE.COM)) )

Full Connection String

The full connection string does not require the use of a tnsnames.ora file. You need to enter the full connection string when you connect to the database in your code.

PHP code:

oci_connect($un, $pw, '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mymachine.mydomain)(PORT=1521)) (CONNECT_DATA=(SERVER=DEDICATED) (SERVICE_NAME = MYDB)))');

Easy Connect String

This is one Oracle 10g feature that I use daily. As I constantly connect to so many different databases in my day, this has saved me so much time as I don't have to configure anything, just know the machine name and the database alias and I'm off.

The easy connect string does not require the use of a tnsnames.ora file, and is an abbreviated version of the full connection string. you must have the Oracle 10g client-side libraries to use the easy connect string.

PHP code:

oci_connect($un, $pw, '//mymachine.mydomain:port/MYDB');

Introduction

In November 2005 I attended the PHP Conference in Frankfurt. At that conference I did a demonstration which showed how to integrate PHP Web Services using Oracle BPEL, and the Oracle JDeveloper PHP Extension. I have written up that demo for others to get an overview of what I talked about and showed to the conference attendees.

If you have PHP web services you want to integrate into a new web service, or business process, then BPEL is your answer. BPEL lets you orchestrate your disparate web services into new web services. You can even use the BPEL web services as part of another BEPL web service. The beauty of web services is that they can be in a variety of languages, so you can reuse your PHP web services and ones in other languages.

This article gives an overview of the tools you could use to create BPEL processes:

• Oracle JDeveloper 10g PHP Extension - an extension to Oracle JDeveloper 10g to create and edit PHP scripts, with syntax highlighting and sample code to create connections to an Oracle database.
• Oracle BPEL Designer - an extension to Oracle JDeveloper 10g to graphically create and orchestrate BPEL processes.
• Oracle BPEL Console - a web service to start, stop, monitor and debug BPEL processes.
• Oracle BPEL Worklist - a web service to interract with BPEL processes that require user intervention.

These tools help you to quickly get started creating BPEL processes to incorporate your PHP web services into new business processes.

Let's suppose you have a departmental application to approve employee leave requests. A PHP developer has written this application as a web service and managers log into the application to manage the leave requests. The employee leave request table contains a row with the identifier of 1004, which is a leave request for an employee, CJONES. You can see this leave request in the PHP web service used by department managers to approve employee leave requests.

Figure 1, PHP Web Service Consumer Interface

This is a standalone application that you now want to incorporate into a new company-wide business process as a web service. You can use BPEL to do this. Let's now run through the Oracle BPEL and PHP tools.

Oracle JDeveloper 10g PHP Extension

This PHP web service was written using the Oracle JDeveloper 10g PHP Extension. This extension was written by Oracle and gives you, amongst other things, PHP code syntax highlighting, and some snippets of code that help you get started creating connections to an Oracle Database. Here's a look at the PHP Extension in action.

Figure 2, Oracle JDeveloper 10g PHP Extension

The code snippets shown in the Component Palette are included in the PHP Extension and enable to to quickly create database connections and run SQL statements. The Structure pane includes a list of PHP functions and their respective variables.

Although the PHP Extension doesn't give you PHP debugging, it may be useful if you want to write or edit PHP files while creating a BPEL application.

To create a BPEL process which will incorporate this PHP web service, you would use the Oracle BPEL Designer.

Oracle BPEL Designer

The Oracle BPEL Designer is another extension to Oracle JDeveloper 10g. I would suggest installing the Oracle BPEL Designer (Oracle JDeveloper 10g and the BPEL Designer Extension) first, and then add the PHP Extension. There is also an Oracle BPEL Designer extension available for Eclipse, although this doesn't include the Oracle-specific features.

Here's a look at the BPEL Designer interface.

Figure 3, Oracle BPEL Designer Interface

The BPEL Designer gives you a graphical tool to create and orchestrate BPEL processes. For example, you can click and drag in PartnerLinks (links to WSDL files for web services), User Tasks (interraction by a user), add decision making elements such as While loops, Waits, Switches, and throw errors.

The BPEL process shown here includes a Partner Link to a database adapter, called DBRead in Figure 3. This enables you to make direct calls to an Oracle Database to query and update data. This BPEL process queries the database using the leave request ID and returns the row associated with that ID. The BPEL process then manipulates the results using XSLT and XQuery functions to format the query results, and create new variables required by the process.

There is also some decision making in the process. In this case, there is a switch statement which enables different actions to be performed depending on whether the leave request is approved, denied, or an error is generated.

If the employee leave request is approved, the BPEL process will use the PHP web service, called PHPUpdateDB in Figure 3, to delete the row from the departmental table. The BPEL process can then update the main human resources database with the employee leave request information using another web service, or a direct database update using a database adapter.

When the BPEL process is ready to deploy, the BPEL Designer will generate all the required deployment information and deploy it to the BPEL Process Manager Server.

BPEL Process Manager

The BPEL Process Manager is a J2EE application which allows you to start, stop, monitor, debug, and kill BPEL processes. You can monitor the state of all the BPEL processes and drill down to individual SOAP messages. This is useful for debugging processes while you're developing them, as well as monitoring them when they have been deployed.

The BPEL Process Manager can be deployed to a standalone OC4J instance (which is the default deployment option and included in the BPEL Process Manager), or to another J2EE compliant server.

When the BPEL process is deployed, use the BPEL Work Console to start and stop, monitor, and debug BPEL processes.

BPEL Work Console

The BPEL Work Console is used to start, monitor and debug BPEL processes. The BPEL Work Console is a web service and sends a SOAP message to the BPEL process. Using the LeaveRequestID of 1004 and sending the process an XML message will start the process.

Figure 4, Starting a BPEL process using the Oracle BPEL Console Interface

The BPEL process verifies that the LeaveRequestID exists by using a Database PartnerLink (web service) to select a row from the table and retrieve the contents of that row. This is a direct database query and does not use the PHP web service.

When the BPEL process is started, the BPEL Console displays a screen with options to visually track the flow, audit it using XML, or to debug it.

Figure 5, BPEL process monitoring options using the Oracle BPEL Console Interface

The Visual Flow is a very easy way to see what state the process is in, what variables have been passed, what decisions have been made, and what errors may have been generated. In this case, the BPEL process has halted and is waiting for manual intervention at the receiveUpdate... User Task.

Figure 6, BPEL process waiting for user input using the Oracle BPEL Console Interface

You could write an application yourself which would allow you to interract with the BPEL process (web service), or you can use the Oracle BPEL Worklist. The BPEL Worklist is a supplied web service which enables you to interract with BPEL processes that require user intervention.

The BPEL Worklist shows a list of the User Tasks assigned to a manager. In this case, it is the approval of the employee leave request.

Figure 7, Acquiring a task using the BPEL Worklist Interface

The task must be acquired by the manager before any action or decision can be made on it. Once the task is acquired, the manager can approve, reject or escalate the task to a supervisor.

The escalation heirarchy is managed by JAZN (Oracle's implementation of Java Authentication and Authorization Service (JAAS)) and can be either set up using the JAZN interface, or connected to an LDAP or SSO Server.

Figure 8, Approving a task using the BPEL Worklist Interface

When the task is approved, the BEPL process receives a message from the Worklist web service and continues processing. The BPEL process continues through its decision making and processing, assigning variables to whatever is required using simple transformations, or XSLT.

Figure 9, BPEL process execution completed using the Oracle BPEL Console Interface

You can click on any of the events in the process to see the SOAP messages being generated For example, the result of invoking the GetLeaveDetails Partner Link generates the following SOAP message.

Figure 10, DBRead Database Adapter SOAP messages displayed using the Oracle BPEL Console Interface

The SOAP message displays the input and output variables of the message. You can see that the input variable is lrequest=1004, and the output variables are employeeId=CJONES, id=1004, leaveTypeId=7 and noOfDays=4. This message represents a request to the database Partner Link to retrieve the row with the leave request identifier of 1004.

Now that the BPEL process has finished, and the database updated to remove the leave request with an ID of 1004, logging into the PHP web service shows that the database has been updated. The leave request with an ID of 1004 is no longer displayed.

Figure 10, PHP Web Service Interface showing ID 1004 removed from the database

This is a very simple BPEL process and PHP web service, but it shows that you can use BPEL to integrate existing PHP web services with other web services to create new business processes.

Further Information

For further information on BPEL and using PHP with Oracle, go to the following URLs.

Oracle BPEL Process Manager

http://otn.oracle.com/bpel

Service-Oriented Architecture Technology Center

http://otn.oracle.com/webservices

PHP Developer Center

http://otn.oracle.com/php

Zend Core for Oracle

http://www.oracle.com/technology/tech/php/zendcore

Oracle JDeveloper 10g PHP Extension

http://www.oracle.com/technology/products/jdev/htdocs/partners/addins/exchange/php

Chris Jones and I recently travelled to Sydney's first LinuxWorld, and delivered a three hour training course, with colleague Aslam Edah-Tally. We wrote a small guide on installing Oracle XE, Zend Core for Oracle, and using the new PHP OCI8 driver to connect to Oracle. You may find some technical tips in here that you won't find anywhere else, so here's a link to this little gem of a document.

PHPfest Tutorial: Oracle Database 10g Express Edition and Zend Core for Oracle

PHP Coders, Get Busy: The Underground PHP Oracle Manual is Here

Chris Jones and I have just finished and released version 1.2 of The Underground PHP and Oracle Manual. This book gives you help installing the Oracle, Apache, and PHP on Linux and Windows. It will help you understand and start working with the new OCI8 (Oracle's API) changes for PHP.

We still need to do more work on this, and there'll probably be another version coming out soon, but it's a good start. Take a look, and let me know what you think about it by leaving comments on this posting.

PHP for the Enterprise
By Rich Schwerin

I just read Cal Evans review on the Zend Developer Zone of the book Chris Jones and myself recently published on the Oracle Technology Network, the Underground PHP and Oracle Manual. I have to say I feel quite flattered, and pleased that Cal went to so much trouble to write up the review in such detail. Kudos to you Cal. So if you want an independent review of this book, go read Cal's review, and hopefully then go and read the book!

Chris Jones and I have just released an updated version of the Underground PHP and Oracle Manual
on OTN. We've numbered it as 1.2.1, and it has some bug fixes, and some
extra information on Oracle SQL Developer. If you have any feedback on
the book, feel free to leave a message here and I'll do my best to get
the fixes and suggestions in the next release.

Zend have just released a new version of Zend Core for Oracle, the prebuilt stack of Oracle Instant Client, Apache and PHP. It's version 1.5 and now has an optional installation of Apache 2.0.59. It used to automatically install Apache 2 whether you wanted it or not, but now you can use an existing web server, either Apache, IIS, or Oracle's HTTP Server. It's now using PHP 5.1.6, the latest OCI8 extension (Oracle's PHP driver), and Oracle Instant Client. This stack has a native installer, and it's available on Linux, AIX, Solaris and Windows. If you haven't seen it before, it's great for getting an easy prebuilt PHP/Oracle stack, and mix it with Oracle Database Express Edition, which is super easy and fast to install, and you've got a very easy PHP/Oracle stack. Phew, that's enough nerdy promotional stuff for the moment.

I'm busy writing a paper for Oracle Openworld, San Francisco, 22-24 October. It'll be on integrating PHP web services using BPEL. It will cover Oracle's tools for SOA development, and BPEL orchestration and deployment. Hopefully I'll have a small demo working, but that's yet to be seen as I'm running out of time. Chris Jones and I will also be doing a two hour hands on demo on working with PHP and Oracle. I hope to see you there.

I'm also helping out on the Oracle Developer Days about to be run in Australia and New Zealand. I'll let you know more on these as the dates and locations are finalised. They are expected to run early October, but I believe we're still working out the details.

Back to it.

I know this is nothing to do with PHP or Oracle, but something that's been bugging me for a while and I've just found the answer to it.

I'm working on the 1.3 version of the Underground PHP and Oracle Manual that Chris Jones and myself have been writing over the last year. I'm writing it in MS Word. I used to be a technical writer many years ago and I'm used to working with long, complex documents, using the appropriate tools. This time I'm using MS Word. It is just awful for this type of job.

One thing that was driving me mad is that I couldn't figure out how to remove paragraph or character styles. Most of the time I could just change them to another style, but sometimes they wouldn't budge. After some research, I found out that to totally remove a paragraph style, change it to Normal, then to the new style you need. Who'd'a thunk?

A similarly strange method is required for character styles. You need to select it and then hit Ctr + Spacebar. That removes it and you can apply another one.

This has been a thorn in my side for the last two releases of this book. Hopefully I can spend some time reformatting it so each chapter looks a little more consistent now I've worked out how to do it.

Moral of the story ... don't use Word for anything other than a fax!

Now if anyone can tell me how to get the bookmarks and links working from a Word master document to Acrobat, I'll send you a special present.

If you hadn't worked it out from this blog, I'm working on version 1.3 of The Underground PHP and Oracle Manual. So in a long and roundabout way what I'm really saying is stay tuned for more news on the upcoming release of the new and improved version of this book.

Richard Rendell, my manager, and leader of all things PHP at Oracle, has an article in this month's International PHP Magazine. Here's a reprint of the original article.

It has info on connection options with Oracle databases, and the new connection pooling feature. Nice work Richard.

After a few weeks of furious writing, Chris Jones and I have released the latest version of The Underground PHP and Oracle Manual. This is version 1.4, and includes updates on:

• PHP PDO_OCI extension

• Testing the OCI8 extension

• Tracing OCI8 internals

• Comaprison of old and new OCI8 function names

• and lots more to keep you busy
  

I've just been setting up PHP 5.2.2 on Windows XP Pro, with Apache 2.0.59. I couldn't get Apache to find the correct php.ini file. It was looking in C:\Windows, instead of where I installed PHP. The httpd.conf file told Apache to look in C:\Program Files\PHP, but it wasn't. So none of the extensions were loading.

I figured out that changing the back slashes to forward slashes fixes the problem. I wanted to blog about this as The Underground PHP and Oracle Manual that we've just released doesn't mention this problem, and you may encounter it yourselves. So if you do, you know the fix. And yes, I've logged a bug with PHP for this. :-)

Zend have just released a new version of Zend Core for Oracle. This is a prebuilt and tested stack of Apache, PHP and Oracle Instant Client. If you haven't used it before, you will like the easy setup. I promise.

Try this with an existing Oracle database install, or download and install Oracle Database XE, our free database.

Zend Core for Oracle is also supported with Oracle Enterprise Linux.

Zend Core for Oracle release 2.0 includes PHP 5.2.1, the refactored OCI8 driver, Oracle Instant Client, and an optional Apache HTTP Server 2.2.2.

Zend Core for Oracle is supported by Zend on the following operating systems: 

* Oracle Enterprise Linux 
* X86 running SLES9 or RHEL3 or RHEL4 
* X86 running Windows XP/2003/Vista 
* X86-64 running Windows Vista in 32bit mode 
* X86-64 running SLES9 or RHEL3 or RHEL4 
* pSeries running AIX 5.2 or 5.3 
* Sun Solaris Sparc 8, 9 10

The web servers that are supported are: 

* Apache 1.3.x (except on Windows Vista), Apache 2.x 
* Oracle HTTP Server 10.1.2.0.0 (on Linux and Windows x86) 
* Microsoft IIS 5, 6, 7

Zend Core for Oracle is supported (by Zend) against Oracle Database 10g and 9i. That means that you can have a fully supported stack of database, web server and PHP.

If you aren't sure how to use it, or want more information on install, configuration, and use, go and have a read of the Underground PHP and Oracle Manual. Yet another handy resource for all things PHP and Oracle. And another shameless plug from me.

Don't forget there are lots of articles, FAQs, downloads and links at the OTN PHP Developer Center.

Since I've started blogging about little idiosyncracies I've been experiencing in PHP, I've been getting feedback that you want more. Okay. I'll start adding little tid-bits that might help out someone out there. So here's one I found (again) today.

While testing the bug fix I mentioned in an blog entry last week, I found that trying to load the phpinfo() script caused my browser to try loading the file as an application. That is, it tried to download the file, rather than execute the script. This happened in Firefox and IE. For those not familiar with phpinfo(), it's a function that displays the setup of PHP, including loaded extensions, environment variables, PHP variables, and so on. Here's what you would use:

phpinfo.php

<?php

phpinfo();

?>

Good news everyone. We've just released a set of RPMs for PHP which include OCI8 and the Oracle PDO driver, as well as many other PHP extensions. These are for development testing only, as Oracle doesn't support them. The RPMs are based on PHP 5.2.3.

You can download the PHP RPMs from the oss.oracle.com site.

To install them:

1. As root, run:
   

   rpm -ivh php-common-5.2.3-1.i386.rpm 
   php-cli-5.2.3-1.i386.rpm php-5.2.3-1.i386.rpm

   
   

2. To verify the PHP RPMs have been installed, run rpm -qa |grep php. You should see:

   
   

   php-common-5.2.3-1
   php-5.2.3-1
   php-cli-5.2.3-1
   

   
   

1. Download oracle-instantclient-basic-10.2.0.3-1.i386.rpm and install it with:

   
   

   rpm -ivh oracle-instantclient-basic-10.2.0.3-1.i386.rpm

2. Install PHP's PDO extension with:

   
   

   rpm -ivh php-pdo-5.2.3-1.i386.rpm

   
   

3. Install PHP's Oracle OCI8 and PDO_OCI extensions with:

   
   

   rpm -ivh php-oci8-5.2.3-1.i386.rpm