Tuesday, April 5, 2011

Installing PDO_OCI on Ubuntu 10.10 x64

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP.
The PDO provides a data-access abstraction layer that can be used with a variety of databases, giving the flexibility of changing the database backend, without having to alter access methods. Even when using a single RDBMS, the PDO can provide advantages, for example, if using the MySQL, the same data-access methods can be used regardless of the MySQL version.
I reviewed recently the chapter Lesson 1b: Creating Oracle Database Tables from Jeffrey Rubinoff's tutorial Creating a Database Driven Application With PHP, as part of the NetBeans NetCAT 7.0 program. To meat requirements I had to install Oracle XE, Instant Client and the PHP OCI8 extension.
I fancy PDO over all other options. Having Oracle XE running on my HP was a chance to try and test PDO_OCI driver.

If running 32bit Ubuntu read no more: All you need is The Underground PHP and Oracle Manual.

Getting PDO_OCI driver source code and patching conifg.m4

Grab the current PHP snapshot. A patch to PDO_OCI config.m4 for 64bit RPM support was merged on March 30th. See the Bug #44989. Over the time this change will be integrated and available in the downloads section
Extract from the downloaded archive the pdo_oci folder residing in the ext folder. 
Open config.m4 located in the extracted pdo_oci folder. 
If using the NetBeans IDE, ctrl+3 to open Favorites, browse to the extracted pdo_oci folder, right click on config.m4 and in the context menu click Open.


Verify that the date in the SVN $Id on the line one is equal or greater then 2011-03-29.
dnl $Id: config.m4 309818 2011-03-29 18:40:20Z felipe $
Download, save and apply patch config.m4.deb.patch attached to the Bug #54451
If using the NetBeans IDE, after downloading the patch, make sure that focus is on previously opened config.m4, click Apply Diff Patch in Tools menu, browse to the downloaded patch, select it and click Patch.
Say yes when asked to view applied changes and verify that config.m4 is patched correctly.
Move the folder pdo_oci containing patched config.m4 to /usr/local/src
If using the NetBeans IDE open Terminal from Window > Output > Terminal and type:
$ sudo mv ./wherever/is/pdo_oci /usr/local/src

Getting Oracle Instant Client and additional header files

Download from the Instant Client Downloads for Linux x86-64 page instantclient-basiclite-linux-x86-64-11.2.0.2.0.zip and instantclient-sdk-linux-x86-64-11.2.0.2.0.zip.
Extract content of those two archives to /opt/oracle.
If using the NetBeans IDE open Terminal from Window > Output > Terminal and type:
$ sudo unzip ./wherever/is/instantclient-basic-linux-x86-64-11.2.0.2.0.zip -d /opt/oracle
$ sudo unzip ./wherever/is/instantclient-sdk-linux-x86-64-11.2.0.2.0 -d /opt/oracle
# make symbolic links
$ sudo ln -s /opt/oracle/instantclient_11_2/libclntsh.so.11.1 /opt/oracle/instantclient_11_2/libclntsh.so
$ sudo ln -s /opt/oracle/instantclient_11_2/libocci.so.11.1 /opt/oracle/instantclient_11_2/libocci.so
Create new file oracleinstantclient.conf in /etc/ld.so.conf.d, add line /opt/oracle/instantclient_11_2, save, close and run ldconfig.
sudo nano /etc/ld.so.conf.d/oci8.conf
sudo ldconfig

make and make install

If using the NetBeans IDE open Terminal from Window > Output > Terminal and type:
$ cd /usr/local/src/pdo_oci/
$ phpize
$ ./configure
$ make
$ make install
Don't run make test. All test will fail, since PDO extension is not loaded in generated php.ini. 
Verify that pdo_oci.so is in your extension directory. Default path is /usr/lib/php5/20090626/pdo_oci.so.

Load PDO_OCI extension and restart Apache

Create new file pdo_oci.ini in /etc/php/apache2/conf.d, add line extension=pdo_oci.so, save close and restart Apache.
$ sudo nano /etc/php5/apache2/conf.d/pdo_oci8.ini
$ sudo /etc/init.d/apache2 restart

If the NetBeans IDE user grab database structure and insert records as described in the chapter Lesson 1b: Creating Oracle Database Tables from Jeffrey Rubinoff's tutorial Creating a Database Driven Application With PHP. Create new PdoOciTest.php file in the web root of the text application.

<html>
    <head>
        <title>PDO OCI Test Page</title>
    </head>

    <body>
        <?php
        $pdo = new PDO(
                        'oci:dbname=localhost/XE',
                        'jim',
                        'mypassword'
        );
        $sql = "
            SELECT DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID, STREET_ADDRESS,
            POSTAL_CODE, CITY, STATE_PROVINCE
            FROM departments NATURAL JOIN locations
            ORDER by DEPARTMENT_NAME
            ";
        $stmt = $pdo->query($sql);
        $result = $stmt->fetchAll();
        ?>
        <table>
            <?php
            foreach ($result as $row) {
                ?><tr><?php
                foreach ($row as $item) {
                    ?><td><?php
                    echo ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;");
                    ?></td><?php
                }
                ?></tr><?php
            }
            ?>
        </table>
    </body>
</html>
Having focus on the created file go to Run > Run File or press space+F6. The file will run on the defined local web server and open in the default browser,

1 comment:

  1. After installing the Oracle drivers, you can use this class here:

    https://github.com/taq/pdooci

    ReplyDelete