Connecting to a Pervasive SQL (PSQL) v12 Database using PHP ODBC+PDO on Modern Linux

Hi friend. If this article title interests you, you – like me – have probably scoured the internet for hours trying to figure out how to fire up a db connection to an old, aging database… maybe so you can begin the process of evolving out of the 90’s.

To get all this set up, here’s what we’ll be covering:

1. Finding the client for PSQL
2. Extracting the contents and getting everything in place
3. Installing a few dependencies before we run the installer
4. Running the installation scripts
5. Defining the LD_LIBRARY_PATH in your bash profile
6. Writing a PHP Test script
7. [Bonus] Ensure www-data can use it

I may be your new best friend – let’s trauma bond!

Step 1: Finding the Client

To get started, we’re gunna need the client. This will require you to sign up for Actian’s Electronic Software Distribution website. It may be confusing, but they now refer to it as “Actian Zen” but still “PSQL”. In their downloads, you can do the rpm or whatever, but I went with the tar.gz – I like pain I guess… Also, I’m running ubuntu, sooooo RPM’s don’t exactly play nice.

I’m going to pretend now that you’ve successfully found and downloaded the tarball named something like PSQL-Client-linux-12.11-033.000.x86_64.tar.gz – should be around 100mb or so. Regardless: excellent work! Well done =]

Download, extract, yadda yadda…

Step 2: Extract and place the client in position

Since you already knew how to tar -zxvf PSQL-Client-linux-12.11-033.000.x86_64.tar.gz , I’m not going to spend any time there. Suffice to say, you should be left with a folder named psql. We’re going to need to scoot that into the /usr/local folder.

It probably required a good ol’ fashion sudo to get it in place, but once there, let’s cd /usr/local/psql/etc

Step 3: Install a few dependencies (forgot about these)

Since I’m using Ubuntu, I’ll be using apt, but I’m sure yum and whatnot have similar proxies for the following (I initially did this in a docker container, so… I needed all of these things):

sudo apt install file php7.4 php7.4-cli php7.4-odbc

Of course, choose your flavor of php as necessary, but these should be pretty minimum… may even need to include vim in that list if you’re running this in a docker container 👍️

Step 4: Running the Client install scripts

Back to the /usr/local/psql/etc/ folder, there are two scripts that we need to run: the first one seems to just make sure you’re ready to install, and the second one does all the installing. They will require sudo, so buckle in.

The first script to run is (again, this is from /usr/local/psql/etc/):

sudo ./clientpreinstall.sh

If that reports back that all is well, it’s time for the second one. However, if it blows up, here’s probably why:

1. You didn’t run as sudo
2. You’ve already installed the client (or something else that’s sitting in the same path)
3. You’re not running something in the proper architecture (64bit / x86)

The second script we’ll need to run is (still in /usr/local/psql/etc/):

sudo ./clientpostinstall.sh

THAT one does all the installing. At a glance, it appears to create the user/group for PSQL, set permissions on the psql bin stuff, symlinks some version naming pieces together, registers the so’s, aaaaand my eyes went crossed. It does things and stuff…

Step 5: Ensure your bash environment variables

We need to place the following line in our .bash_aliases file (or .profile, or .bash_profile, or .bashrc – whatever!)

export LD_LIBRARY_PATH=/usr/local/psql/lib64

^^ If you don’t place that in one of your sourced files and slurp it back in, it’s going to say something like: Can't open lib '/usr/local/psql/lib64/libodbcci.so.12.11.033.000' : file not found — even though that file is VERY F!@#ING MUCH THERE, BRO! (sorry) and you’ve even tried temporarily chmod 0777 libodbcci.blahblah to see if it’s a simple permissions issue – it’s not.

It’s because you haven’t exported the LD_LIBRARY_PATH variable. There. I just saved you a little insanity from an error message that isn’t lying, it’s just not clear =]

Now I do see where psql adds its bin folder to your $PATH – however, I’m not sure that’s entirely necessary for our purposes alone. If you’re considering using tools like isql or odbcinst (both under unixODBC), then I would consider it. For that, I would also expect to be setting configuration files in /etc/odbc.ini, /etc/odbcinst.ini and maybe even ~/.odbc.ini — none of this is required for ***our specific use-case***, but it might be handy if you need a cli option to explore the data. Here are the docs for unixODBC if you’d like to get started with that. For now, I’m going to assume we don’t need them.

Step 6: Test Script – make it count!

This is the big one! I wish you all the luck; let’s put down a quick test connection script and see how we do:

<?php

// Of course, you'll need to fill in your server and db name.
$dsn = 'odbc:Driver={Pervasive ODBC Interface};ServerName=localhost;dbq=DBNAME';

// So secure... Of course, if this is running on a Windows server (from 1998),
// there's a good chance these are your windows credentials
$username = 'myusername';
$userpass = 'securepass';

try {
    $db = new PDO( $dsn, $username, $userpass );
    // I promise I'm not shouting any of this - at you or the db...
    $results = $db->query('SELECT TOP 10 * FROM TABLENAME', PDO::FETCH_OBJ);

    foreach ( $results as $k => $row ) {
        echo "$k => ";
        var_dump( $row );
    }
} catch (PDOException $e) {
    echo "\n\nHELP! THERE'S FIRE EVERYWHERE AND GARY'S STILL IN THE SERVER ROOM!!!\n" . $e->getMessage() . "\n\n";
    // You would absolutely hate working with me, amiright? o.O
}

Copy that code and edit in your details quick… I’ll give you a keyboard replay of what happens next:

> vim test.php
> :set paste
> i, [ctrl] + [shift] + v
> :x
> php test.php && echo 'TADA!!! =D'

Did it work?!! If not, hopefully Gary made it out alright.

Step 7: [Bonus] Ensure www-data can use it

Ok – so our script works perfectly when we run it against the CLI, but when we hit it via the web (nginx or apache2 -> php) we maaaaay see that we’re back to not being able to find the file. [envision a slow-mo eyeroll and a sigh that blows all the paper off the table].

If you’re using the default www-data user for your web services, you may notice that adding something to its environment is……. problematic. Shoving the value in the nginx conf doesn’t work, adding it into a php.ini doesn’t work – there are no confs in the web services than can help you – believe me, I tried.

Then I found this solution https://stackoverflow.com/a/13428971/1238804 – basically, there should be a folder at /etc/ld.so.conf.d/ . In there, we’re going to create a new file (it appears everything in the folder with a .conf suffix is getting slurped in) and add our path to it.

cd /etc/ld.so.conf.d
sudo vim pervasive-sql.conf

# In our pervasive-sql.conf file, we'll just add the following:
/usr/local/psql/lib64

Save, close, and finish the job with a hearty sudo ldconfig

Seriously, don’t forget the sudo ldconfig. I suppose restarting the machine would do the trick too, so… YA GOT OPTIONS! Huzzah!

Now as you might imagine, if you had to go this far and add the path to the pervasive-sql.conf file, you can get rid of it from your bash profile… but of course, that’s up to you =]

Well that was graphic.

I won’t go as far as to say I had fun, but hopefully all that got you moving again. So let me know in the comments below, my fellow wayward devs… How’d you fare? Did I miss anything? Are you running into issues? Let me know in the comments below, or – and I mean this – email me at ryan@ this domain 😉