PHP and Oracle: Database Connection Strings

(Page 1 of 2 )

In this second part of a seven-part article series on PHP's Oracle functionality, you'll learn how to connect to and disconnect from an Oracle database. This article is excerpted from chapter 32 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Database Connection Strings

There are three different types of connection strings: easy connect, database alias, and full database connection. A connection string is a set of one or more parameters that define the database name, server, network protocol, and port number of the database you wish to connect to. The type of connection string you use depends on whether you have Oracle Net configuration files already in place on your server and whether you want to specify some of the more advanced connection parameters.

Using Easy Connect Strings

As the name implies, the easy connect string is the easiest to use; in most cases, all you need to specify is the username, password, and a connection string that may look familiar if you use JDBC to access a database from a Java application. The syntax of an easy connect string is as follows:

[//]hostname[:port][/service_name]

If you are accessing a database on the same host computer as your Web server, you can use localhost for the hostname parameter; otherwise, you use the hostname you see in your /etc/hosts file on Linux. You can also get the hostname of your computer by using the uname -n command. The port parameter defaults to 1521, which is the default port for any Oracle installation. This parameter may not be 1521 when you have more than one database on your host computer. Similarly, the service_name parameter defaults to the name of the only database installed on the host computer, which in the case of Oracle Database XE is XE. As a result, the oci_connect() call in Listing 32-1 can be further abbreviated as

oci_connect('system', 'yourpassword', 'localhost')

since the leading // is optional as well.

Using a Database Alias for Connection Strings

If you have a tnsnames.ora file on your client, or use Oracle Internet Directory (OID) in your environment (OID is beyond the scope of this book), you can use an alias for your service_name parameter that you store in tnsnames.ora or OID. In Listing 32-2, you see a sample tnsnames.ora file with a total of seven connections: three from the default Oracle Database XE installation, and four others added to connect to a remote Oracle Database 10g Real Applications Cluster (RAC) database. For a Linux installation of Oracle Database XE, you can find the file tnsnames.ora in $ORACLE_HOME/network/admin. 

Listing 32-2. Sample tnsnames.ora File

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ath4800)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

RACI2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = voc2i.sample.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = raci.world) 
      (INSTANCE_NAME = raci2)
    )
  )

RACI1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = voc1i.sample.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = raci.world)
      (INSTANCE_NAME = raci1)
    )
  )
RACI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = voc1i.sample.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = voc2i.sample.com)(PORT = 1521))
      (LOAD_BALANCE = yes) 
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = raci.world)
    )
  )

RACSVC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = voc1i.sample.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = voc2i.sample.com)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = raci.world)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5) 
      )
    )
  )

If the host machine running PHP is using the tnsnames.ora file in Listing 32-2, this PHP code will connect to the database as well:

oci_connect('system', 'yourpassword', 'xe');

If your PHP application will connect to the RAC database on the server sample.com, you could connect using any one of these four connect requests using tnsnames.ora entries:

oci_connect('hr', 'hr', 'racsvc'); oci_connect('hr', 'hr', 'raci');
oci_connect('hr', 'hr', 'raci1'); oci_connect('hr', 'hr', 'raci2');

Which one you use depends on your failover and application requirements.

These are the same alias names you can use in SQL*Plus or SQL command line; as with the oci_connect call, SQL*Plus defaults to XE (Oracle Database XE) if you are running SQL*Plus on the same host machine as the database.

Using Full Database Connection Strings

If you do not have a tnsnames.ora file on your host machine, and you want to connect to Oracle Database XE on a remote machine, you can use the same connection information you would use in a tnsnames.ora file. In this PHP code snippet, you connect to an XE database on the server sample2.com:

$dbci =
'
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = sample2.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )
';

$conn = oci_connect('hr', 'hr', $dbci);

This syntax gives you the flexibility to specify many other network settings not available with the easy connect string syntax.

Next: Disconnecting from the Database >>
 

More Database Articles Articles
More By Apress Publishing

blog comments powered by Disqus