Oracle with PHP and PDO

A few weeks ago, I was holding a PHP training for a customer who’s using the Oracle database on a regular basis. My boss and I agreed on teaching the Oracle database connection with PDO (instead of the MySQL database connection we usually teach in trainings).

Being used to MySQL which is very well connected to PHP, I noticed some differences to PHP’s handling of Oracle. At the beginning, we just tried to read data from a basic database table, change some data and insert new data – basically all that a simple web application has to do. I started with some code I would use with the MySQL database:

$pdo_string = 'mysql:host=localhost;dbname=my_database';

try {
    $dbh = new PDO($pdo_string, 'username', 'password');
} catch (PDOException $e) {
    echo "Failed to obtain database handle: " . $e->getMessage();
    exit;
}

$query = "SELECT id, name FROM datatable;";

$stmt = $dbh->prepare($query);

if ($stmt->execute()) {
    echo "<h4>$query</h4>";
    echo "<pre>";
    while ($row = $stmt->fetch()) {
        print_r($row);
    }
    echo "</pre>";
}

First, I just changed the $pdo_string so it would fit to an Oracle database server:

$tns = "
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = servicename)
        )
    )
";
$pdo_string = 'oci:dbname='.$tns;

Note: One could use the tnsnames.ora file to create connection strings much easier and faster, but for now, this should work.

At first, one would notice that nothing was happening at all. Just a white screen, and no entries in the error log. Even if one turns on error messages explicitely (you never know the current php.ini settings) with error_reporting(E_ALL), nothing is changing.

So the next step would be to let PDO throw exceptions by setting the appropriate attribute (instead of doing nothing – which is a strange default behaviour of PDO, IMHO):

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Now, the PHP code throws an exception which is visible in the error log (and which I should catch) telling me something about an invalid character. Reviewing my SQL statement, the only invalid character there is is the semicolon at the end (which I still append at the end of an SQL statement due to "historical reasons", although it is not necessary, but doesn’t hurt when deploying MySQL). After removing the semicolon, the SELECT statement was working on the Oracle database.

The next step was to actually change and insert new data into the database. This worked very easy by just changing the SQL code:

$query = "UPDATE datatable SET name='asdf' WHERE id=1";
$query = "INSERT INTO datatable (name) VALUES ('xyz')";

At the training we had more complex data tables. If you work on real life data tables you will notice (again compared to MySQL) that you have to escape pretty much all kinds of variables with single quotes (e.g. date fields can stay unescaped in MySQL when inserting, but not in Oracle). It is also reasonable to write all column names in UPPERCASE because that fulfills Oracle’s naming scheme. Another difference to MySQL occurs when writing strings into the database that contain more characters than the corresponding database fields. This happens especially when one is holding a training and just showing some simple things without all necessary checks one would implement in a regular application. MySQL just truncates the string (and updates at least the first characters), but Oracle throws an exception and does not write anything at all.

At the end of the training, our customer got a good first insight into PHP and its PDO connection to the Oracle database server (besides some other subjects). I personally think it’s easier to start PHP with MySQL because for most web based applications it’s a tough and sturdy database handling even some slightly inappropriate statements in the way the developer would assume it to do.

On the other hand, if your company uses Oracle, you’ll be able to store your web application data in the same database you use for all other applications if you just know which details might be a little tricky.

If you are interested into more details about PHP and Oracle, you should read the Underground PHP and Oracle Manual. An interesting application if you use the Oracle database might be the phpOraAdmin, which is a similar project as phpMyAdmin (a web-based database tool).