/******************************************************/ /* A quick intro on how to access Oracle from pHp. */ /* version: 1.0 */ /* Date : 19/06/2009 */ /* By : Albert van der Sel */ /******************************************************/ Remark: This note just lists just a few examples, and hints, on how to use pHp to access Oracle. Hopefully, it might be a nice note for beginners. But for the more experienced programmers, it's probably too simplistic. WARNING: Especially on the subject of "authentication" options and encryption (all neccessary stuff in order to build secure sites), it contains no info. So, you should only regard this note as "introductionary" and it only contains some hints and examples for a beginner, in order to orientate on the subject. It's only purpose is to establish some basic understanding, in a short time. Section 1: A few possible Client stacks to Oracle (general discussion): ======================================================================= ------------------- ----------- -------- |Application (web) |some sort |Database | |client| ---->|Server |-------------------> |Server | -------- | |of connectivity | | ------------------- software needed ----------- May be only a browser is needed In a "tree tier architecture", as sketched above, then if a client connects to an Application Server, which connects to an Oracle Database Server, then the client might only need a browser. But ofcourse, the Application Server itself then, needs some additional software to connect to Oracle. ----------- -------- |Database | |client| ------------------------>|Server | -------- | | Here, some sort of Oracle ----------- connectivity software is needed And, suppose the client connects "directly" to the Database Server (like in a traditional Client/Server setup), then ofcourse that client needs that "connectivity" software. So, there is always "something" (your PC, or an Application Server), that needs some sort of Oracle connectivety software. And, what actually is the "client" to Oracle, can thus be your webserver, or just your PC or Workstation. pHp at the Web/Application Server: ---------------------------------- pHp is often used in "Server side scripting", which means that some sort of pHp engine runs at a Web/Application Server. Inside "html-like" .php pages, we have pHp code stored, which is executed at the time the page is requested by a client. So, in a dynamic way, a html page is generated, which can, for example, contain data that the pHp code has retrieved real-time from a database (such as Oracle). This is how it works. The client request an .php page. The Server processes any script in this page and returns a dynamically constructed html reponse to the client. Server Client ----------------------------------------------------------------- ------- request getdata.php | | | |---------------------> | connecting |browser| getdata.html returned| .... | to Oracle | | <------------------ | ?> | to get the ------ | ----------------------------------------------------------------- Customer data | | | | | V There are several ways for your Application Server (with the pHp engine) to connect to an Oracle Database. You can for example, install the "Oracle client" software, which installs the OCI layer and a set of other base Oracle "Net" software. In general, below are a few figures, that depict some popular ways, of what a client uses to connect to Oracle: ------------- --------------- ----------------- |Application| | Application | | Application | ------------- --------------- ----------------- | OCI | | "Thick" JDBC| (also called OCI JDCB) | "Thin" JDBC | ------------- --------------- ----------------- | Oracle NET| | OCI | | JAVA Net stack| ------------- --------------- ----------------- |TCP IP | | Oracle NET | | TCP IP | ------------- --------------- ----------------- | TCP IP | --------------- Note that what I have called "Oracle Net" is sometimes also denoted by "SQL*Net". Ofcourse, this is not a JDBC note, but to complement the upper figures, I think I put in this as well: --------------------------------------------- | JDBC Class Libary | --------------------------------------------- | ODBC based| Oracle OCI | Oracle | | driver | based driver| Pure Java driver| ------------|-------------|-----------------| |might use | OCI |Java Net, | |OCI Library| Library |java sockets | --------------------------------------------- But be aware that upper figures are not the whole story. In JDBC, people distinguish multiple "types", and some of that is covered by the above figures. Anyway, you see how often OCI is used? OCI is short for the "Oracle Call Interface". It's quite old, but still heavily used at many sites. OCI is a software component that provides an interface between the client application and the SQL language the database server understands. As you might expect, especially Java applications, would use JDBC, either Thick or Thin. If you would use the Thick JDBC interface, you still need a number of Oracle libraries, like OCI. In case of a Web Server which uses the pHp engine to generate dynamic webpages, and if pHp code is supposed to interface to Oracle, then the use of OCI is common way to do that. Remark: Since pHp and the Database engine "MySQL" are (more or less) open products, in the past, pHp was mainly used to access MySQL databases. Ofcourse, especially in the Internet, pHp and MySQL are still incredably popular. But in more "closed" environments, like companies, chances are that you come across other databases like MS SQL Server, Oracle, DB2 etc.. pHp will work great with those databases as well. But for certain Database, you need to install certain additional packages, in order to to create Tabular Data Stream- (TDS) and Database Abstraction Layers. Undoubtly, if you go deeper into the subject, you will certainly come across PEAR modules. This note is about Oracle and pHp. You can encounter various versions of Oracle Databases, like 9i, 10g, 11g. Also, if you take a look at one such version only, like 10g, there are even with that version, several socalled "Editions", like "standard edition", "enterprise edition". A special edition, called XE or "Express Edition", is especially meant for use for developers. If you need more info, you might want to check: http://www.oracle.com/technology/products/database/xe/index.html Section 2: How to setup your pHp engine so that it can talk to Oracle: ====================================================================== This section just gives some general clues on the subject. On your specific platform, and Server components, you need to find the exact instructions on how to setup the system. --> Oracle client software: If you have installed the Oracle client on your Web Server, you have the Oracle Net components, with OCI included. - You can install the "regular" Oracle client software, which has many options on how to install it, varying from a light-weight install, to a full-blown install with many libraries and tools. Even the "smallest" install should work. It's available for almost all unix platforms (e.g. aix, hp, solaris), linux, and Windows. - A very interresting type of client is "Oracle's instant client". Also this one can be installed with varying options, ranging from "basic" to more complex installations. Again, even the basic +SDK install should work. The instant client generally has a smaller footprint, compared to the standard client. If you google on "Oracle instant client", you get many good links back. You can also look at this url: http://www.oracle.com/technology/tech/oci/instantclient/index.html --> Now we need to let pHp know, that it can use OCI: Depending on what you already have installed (and downloaded), and on which OS your Server is installed, it can vary between "really easy" to "relatively hard". On Windows, one usual step consists of editing "php.ini" and uncommenting the following record: "extension = php_oci8.dll" On some Linux distro's, you do something similar, like enabling "extension=oci8.so" in php.ini. But on your specific distro, it's possible that many other steps are neccessary, like installing additional packages (like from PEAR, ZEND), and creating a number of symlinks, etc.. So installation might be easy, but sometimes it is not always good news here. --> a few remarks on "connect strings": If you have used the standard Oracle client, it's customary that the Oracle Net software uses a few ascii configuration files, that specify on how to connect (or find) a certain Database Instance on a remote Host (or even locally on this host). The most important file is "tnsnames.ora". On most occasions, you can find it in a subdir of where you have installed the client software, like for example "/path_to_oracle_client/network/admin". You can see (and edit) the connect strings in that file. A typical example looks like this: DBTEST= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=STARBOSS)(PORT=1521) ) (CONNECT_DATA= (SERVICE_NAME=DBTEST) ) ) The "DBTEST" item, is actually an "alias" if you want to connect to the database, located on Server STARBOSS, using TCP/IP, where on STARBOSS a listener is "listening" on port 1521. Actually, an alias can be any string of your choice (albeit that there are some constraints ofcourse, like the length). If you want to connect to the database, using a prompt tool like SQLplus, you could do it like this: $ sqlplus username/password@DBTEST And the Oracle Net software resolves the alias to the connect description as specified in the tnsnames.ora file. You see how in the "Description" part, we have completely specified, on "how" to get to that database (which host, which protocol, which port). So, it's quite likely that on your Webserver, where the Oracle client was installed, you need to take a closer look on the tnsnames.ora file. But, if you have used the graphical installer of this Oracle client, somewhere in the install process, you might have come across several setup screens where you can enter this type of configuration information, like the remote Database name, the protocol and port etc.. If you use the "Oracle's instant client", it can use a tnsnames.ora file as well. But also something called "EZconnect" can be used, which boils down in using connect descripers which might be as easy as "starboss/db1". Ofcourse, some sort of names resolution needs to be present, before that is going to work. In general, a connect string can take the form of: CONNECT username/password@[//]host[:port][/service_name] Here, in fact, you have specified all there is to know, for your software to locate the Database (service_name). So, you can bypass any need of a tnsnames.ora file. Remark: An Oracle Database is often specified by it's "Service ID" or SID, like "DBTEST" or "SALES". Technically, the Database has a "name", and the running code, the Instance, has a "name". Usually, they are the same. Sometimes, some people refer to the "Service name" instead of "SID". Don't worry too much about it. And lastly, you can hopefully imagine that a Service might described in a fully qualified name, like "sales.antapex.org" instead of just "sales". --> Possible changes on your Web server and profile: Since the pHp "engine" runs within your Application Server context, depending on what Application Server you are using, it's quite likely that you need to set the Oracle environment in some configuration file. What we mean here, is that the Application Server/Web Server needs to find the Oracle libraries and admin files (like tnsnames.ora). So, on unix or linux, you might expect that, in the for your Webserver relevant configuration file, something like the following is needed, by setting the right environment variables: ORACLE_HOME pointing to the directory where the Oracle stuff is installed, like for example "/apps/oracle/product/10r2" It has to be that directory, so that the underlying "bin" and "lib" directories can be found with: $ORACLE_HOME/lib and $ORACLE_HOME/bin TNS_ADMIN pointing to the directory where tnsnames.ora and sqlnet.ora can be found. Also, it's possible that the profile of the account that your Server runs with, needs those environment varibles set as well, and possible the $PATH variable extended with "$ORACLE_HOME/bin", and LD_LIBRARY_PATH extended like this: export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib Section 3: A few sample code fragments of connecting to Oracle: =============================================================== If everything is setup, you can use a rather large number of "oci_" functions in your pHp code, like: oci_commit oci_connect oci_define_by_name oci_error oci_execute oci_fetch_all oci_fetch_array oci_fetch_assoc and many many more. Also, many people like to use aliases to those functions like "ocilogon" which is an alias of "oci_connect". Example 1: ---------- \n"; } OCILogoff($conn); ?> Example 2: ---------- \n"; } // Commit to save changes... oci_commit($c); // Logoff from Oracle... oci_free_statement($s); oci_close($c); ?>