 |
|
Connecting to Oracle with PHP
Oracle Tips by Burleson Consulting |
To connect to Oracle, PEAR DB uses DSN (Data
Source Name), inspired by ODBC and JDBC. DSN contains all the
information needed to connect to a database. In particular, it
contains the “driver type” such as OCI8, MySQL, PostgresSQL,
MS-Access or SQL Server. It also contains a username, password,
database, hostname, port and a “database identifier”, depending on
the database type. PEAR DB can be used not only to connect to
Oracle, but to all other database types that PHP supports. DSN
follows the URL-like syntax as seen in so many JDBC applications.
When connecting to an Oracle RDBMS,
DSN looks like the following:
$DSN="oci8://scott:tiger@local";
The driver type is OCI8. The username and
password are the standard Oracle DEMO username and password,
“scott/tiger” and the LOCAL username and password is the TNS
descriptor in the database, defined as follows:
LOCAL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = 10g))
)
(CONNECT_DATA =
(SERVICE_NAME = oracle)
(SERVER=DEDICATED)
)
)
TNS descriptors can be created by the standard
Oracle tools such as NETCA (NETwork Configuration Assistant) or NETMGR.
(Net Manager). Use of these tools and an Oracle*Net configuration are
beyond the scope of this book and is described in detail in the Oracle
documentation.
DSN can be an associative array with the following
elements:
$DSN=array('phptype'=> 'oci8',
'username'=>$_POST['user'],
'password'=>$_POST['passwd'],
'database'=>$_POST['database']);
Using the array form of DSN is useful when there
is a need to check each item separately; defining, for instance, the
default database such as the following:
if (empty $_POST['database'])
{
$_POST['database']=$_ENV['TWO_TASK'];
}
Once DSN is created, all that remains to do is to call the connect
method on this DSN and check for errors. The procedure to perform
this task with Oracle is shown in Example 20.
It is recommended to use the oci_new_connect()function for OCI8 in order to avoid handle initialization
errors. PEAR is written in PHP4, and is easily determined by observing
the ubiquitous “var” in the class definitions. If using PHP5, a small
part of PEAR DB needs to be hacked manually. On the basic PHP
directory that contains files such as PEAR.php and DB.php, there is a
subdirectory DB containing a file called oci8.php. On line 117, this
file contains the following line:
$connect_function
= $persistent ? 'OCIPlogon' : 'OCILogon';
This line MUST BE replaced by the line below:
$connect_function
= $persistent ? 'oci_pconnect' :
'oci_new_connect';
DB::connect does not work properly with PHP5
unless this line is replaced as shown above. If this is not
completed, OCI frequently issues a message that the database handle
is not being properly initialized.
Documentation for each PEAR module is available on
the PEAR site located at:
http://pear.php.net. Creating DSN for other databases is
documented in the PEAR DB documentation.
#!/usr/local/bin/php
<?php
require_once('DB.php');
$DSN="oci8://scott:tiger@local";
$db=DB::connect($DSN);
if (DB::iserror($db)) {
die($db->getUserInfo( );
SEE CODE DEPOT FOR FULL SCRIPTS
}
$sql="select * from emp";
$sth=$db->query($sql);if (DB::iserror($sth)) {
die($sth->getUserInfo());
}
$cols=$db->tableInfo($sth);
echo "This result has ".count($cols)." columns\n";
foreach ($cols as $col) {
echo $col['name']."\t";
}
echo "\n";
while ($sth->fetchinto($row)) {
foreach ($row as $val) {
echo "$val\t";
}
echo "\n";
}
?>
The output of this example, when executed, appears
as shown below:
It should be noted that the “require_once('DB.php');”
line in Example 20 includes the PEAR DB module. If this line is
omitted, it is not possible to work with the PEAR DB module.
PEAR is an open source module and users are
welcome to study the code. Database connection, SQL handles, error
objects and all other object types in PEAR DB are defined as classes.
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
