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

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 doesn’t seem to want to run, here’s 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 (ie: 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 stuff, ok?!

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. Hopefully you did too! 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 😉

Leave a Reply

Rohjaynator::1660382131::22836748