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 😉

Seeking Happiness

Quick aside first: if this was shared with you, please know I wrote this for myself and nobody else. Ideally, this is just a thoughtful article to get folks thinking about things in their lives and is worth sharing. That’s what I’m hoping for at least!

Is it just me, or is happiness one of those elusive things… something that comes and goes, or maybe leaves you feeling like you’re coming up short? A roller coaster of highs and lows, or maybe even just something that you never can quite grasp?

I would like to make a slightly bold statement: we all have an imperative to greedily seek out the things that make us happy.

Not to the detriment of ourselves or others – in some cases, service to others is what makes us happy. Simply put, I want us to begin (or continue) the avid pursuit of achieving our own happiness. Let’s face it: nobody is going to find it for you and nobody is going to lay it in your hands.

I mean that with all kindness =]

Plotting a Course

What do we do about this? How do we approach this? The one bit of wisdom I can eke out of this concept (seeking happiness), is that if you’re not where you want to be, if you don’t have the things in life that you want, chances are high you’re going to have to do something you’ve never done before to get there. A fresh approach, taking with you all the learnings of before to achieve something closer than you’ve ever been. The great climb to a genuine smile!

I’m writing this right now because I currently find myself in a situation where I have an infinite of options in front of me, and I’m not sure what the next step is. I’m not quite terrified by this prospect though. I look at it like this: if you find yourself in the middle of the ocean and aren’t sure which way will get you to land, the best course of action is to pick a best guess direction and go.

Is that the best we can do though? Does it all boil down to a good guess, a bit of luck, and maybe even a little good ol’ fashioned gumption? A little bit every day or haymakers until we have to stop to catch our proverbial breath? This is starting to sound like the ultimate showdown of the Turtle Vs. the Hare. Perhaps there’s wisdom there (#UnawarePoet).

Necessity for Adapting

Life at times can feel like it’s nothing but curve balls and monkey wrenches… so expect that! Trials will be had, hurdles appear… the obstacles that we anticipate seem to never happen, and yet what we least expect generally occurs (speaking as a developer – hah! Damn that left field!). So let’s expect that in the course of our lives we have to roll with the punches and adapt.

So that’s it? We plot a course, make a plan, execute it, and adapt as the situation changes? I suppose so… then when we come up short (which is bound to happen – monkey wrench incoming!), we adapt. We rinse and repeat, starting back at step one – plot a new course with all of our learnings from our previous attempts, and press on.

I think the point here is: don’t let a little thing like failure slow you down. When beating your head against the wall doesn’t get you through it, try to find a way around it – or at least find a method that isn’t quite as head-trauma inducing!

What can I say? I’m a caring guy like that =]

Simple Concept, Not Simple

Ugh! You know what though?! That sounds like a lot of work. A lot of heartache. A lot of struggle and pain. A lot of bullshit. But then again, you tell me – are you where you want to be in life? Maybe there is something to be said for finding happiness without finding land — in the middle of that ocean, so to speak. Maybe…

Yet I find myself wanting… friends, family (especially one of my own), connection, a loving relationship, success, happiness, trust, and purpose! I do know where I’m wealthy – my friends and family. I’ve had success and found software development to be my craft of choice – something I’m pretty good at!

Real Talk

Hold the phone! Did I really just confess that what I’m seeking right now is the lady of my dreams? Whoops. Eh… I’m ok with being vulnerable to the entire internet. Pshhh! It’s just the internet! Joking aside – I think we could all be a little more honest with ourselves and each other.

So let’s do this! What is it that you want in life? What steps can you take to get yourself closer to realizing that dream? What’s in your way to getting there now? How can we get there?

I’d like to apply one last parting concept from software development to life. Be forewarned, this one is taken from agile (hey! don’t hate!): whenever you start any endeavor, you’re starting from nothing. Effectively, you’re at your dumbest point! Give yourself the space and a little kindness to muck things up a bit – because you will – and if it’s worth it, you might muck it up a few times before you get there.

Learn from those moments, adjust course, and press on. Rinse and repeat my friends, rinse and repeat!

Lastly…

At the end of the day, I mainly wrote this for myself. Don’t let that stop you if you’re feeling compelled – please throw some comments below! Tell me (and everyone else for that matter) if something here resonated with you! Proclaim the things you seek! Share your plan or your wisdom! Alternatively, you could just enumerate the ways I sound like a looney person or lack solid conclusions in all of this whimsy! All is welcome.

And yes, lastly, I hope you find what you’re looking for – even if it’s just the contentment of being right there in the middle of the sea =]

Day One

Ahhh the open road =]

Technically, it’s more like day three. I’ve now built this web stack from scratch (still have some work to do, but you get the idea), secured the server as best as I personally know how, configured a buncha stuff, wrote some rudimentary update scripts, backup scripts, and fired up a WP blog. Eureka! It works!

Now comes the tough part: content. Continue reading “Day One”

Enabling Hot Corners on a Mac

So you got gotten D=

Here’s how you ensure you can quickly lock your mac to prevent folks from giving your computer a cronjob. Just enable hot corners and ensure that it requires a password to unlock. Here’s how:

Step 1: Enable hotcorners

  1. Go into your system preferences (the apple icon in the top left corner of the screen)
  2. Search for “Hot corners” (or just hot will probably do) which should light up “Desktop & Screensaver” stuff like this:
    Screen Shot 2016-04-12 at 3.33.23 PM
  3. Bonk the “Hot Corners” button in the bottom right corner
    Screen Shot 2016-04-12 at 3.33.45 PM
  4. Select your desired corner (I’m using the bottom left corner) and set it to “Start screensaver”
    Screen Shot 2016-04-12 at 3.34.25 PM
  5. Press OK and we’ll return to the System Settings for the next part

Step 2: Ensure the Screensaver requires a Password

  1. Back in your System Settings, search for “Security & Privacy”
    Screen Shot 2016-04-12 at 3.34.55 PM
  2. Then under the “General” tab, make sure the box is checked and set up to either “Immediately” or within “5 seconds” lock your computer
    Screen Shot 2016-05-16 at 1.21.25 PM
  3. If you encounter trouble modifying any of the options, you may need to unlock your settings. Look in the bottom left corner of the settings window and click the lock. You should be prompted to enter your password. Authoritate yer computer and rock on!
    Screen Shot 2016-05-16 at 1.26.26 PM

How it works

Now that you have hot corners enabled, let’s kick the tires, shall we? Move your mouse to the corner that you selected and it should enable your screensaver. Ensure that when it turns on, that it requires a password to unlock. This is a great way to protect yourself against shenanigans, and a good step to help make sure you aren’t the victim of a USBDriveby D=

…fin!

Adding Crontabs to your Coworkers Unlocked Computers

What’s a cron?

Alright, what we’re doing here is scheduling a mac to do something for us using cron. Cron is a program that checks to see if there is anything it needs to do every minute. Once something qualifies, it will execute the code it’s given. But what if the code we give it is…… shenaniganalous in nature? Mwah ha ha ha!!! The possibilities are limited only by your imagination =P

In this example, we’re going to make a job that will repeatedly say “nacho nacho nacho” every ten minutes until the end of time. Seem fair? Alright, the schedule when we’re done will look something like this:

*/10 * * * * ( /usr/bin/say "nacho nacho nacho" )

Ready. Set. Cron.

Here’s how it works on a mac:

  1. Open up a terminal on the victim’s computer. You can press the F4 key, type in “terminal” and press enter.
  2. Type in “crontab -e” (without quotes) to edit the current cron schedule for the user.
  3. To add the schedule above in Vim, press “i” – the bottom left corner should say -- INSERT --
  4. Paste in the schedule from above. You can change the text to say something else, but whatever you do, do not use exclamation points. It’s syntax in bash and will not work!
  5. Once you have your schedule pasted in and it’s perfectly crafted, press escape, then type “:x” (which you’ll see in the bottom left) and press enter — this will save your new schedule and exit Vim

It will tell you if it fails, otherwise, consider your plan in progress! Quit the terminal and wait.

Cleanup

Alright, so the joke is done, and if anyone hears your victim’s computer say “pee pee” one more time, …no wait it’s still hilarious. Ok, let’s say you want to remove it anyway, right? If they don’t have any crons they need to save, there’s a simple way to do it.

Open up the terminal again and type the following (WARNING: it will clear ALL CRONS on the machine!):

crontab -r

Now, if you’ve come across the rare person who has something in their crontab, you can simply edit their crontab again and remove the magnificent job you added like this:

  1. Open up the terminal and type crontab -e
  2. Using ‘j’ and ‘k’, you can navigate to the line that needs to be removed
  3. Once on the offending line, you can type ‘dd’ (yep, two of em) to delete the entire line
  4. Once the schedule is gone, type “:x” to save and quit vim and you’re set!
  5. Hit cmd+q and close the terminal as if nothing ever happened.
  6. Flex.

What else, Ryan?

…yes! A quick reference for you!

Great question! I’m so glad you asked! There is SO MUCH MORE we can do.

View current cron tasks
crontab -l (that’s an “L” to “List”)
Remove all current scheduled tasks
crontab -r
Schedule to open a google search for nachos every 10 minutes
*/10 * * * * ( /usr/bin/open "https://www.google.com/webhp?#q=nachos" )
…hey I get it. Sometimes, you just need some nachos.

More fun with Say

Say comes with a lot of different options… have a look using man say in your terminal to see the manual. Now when you look at this, if you have any questions on how to use some of these options, you can ask!

For instance, let’s say I’d like to use a different voice… but I don’t know which ones I can use. Ask yer mac like this: say -v ?

This will give us a massive list of voices to choose from. Now you can switch up your command to do things like:

14 * * * * ( /usr/bin/say -v Whisper "I can see you" )

The above schedule, on exactly the 14th minute of the hour, will creepily whisper “I can see you”.

Conclusion

Here we’ve explored three tools (crontab, say, and open) that in coordination can help you mess with unlocked computers. I give you this knowledge knowing full well it could be used against me……… but then again, I lock my computer =P

Blog Spammer Shootin’ Match

The Stage

For some reason….. this blog — THIS blog! …..gets a crap ton of spam. So I went looking. I figured I’d share the bulk of what I found.

mysql> update wp_comments set comment_approved='spam' where comment_approved='0' AND comment_content LIKE "%China%";
Query OK, 969 rows affected (0.46 sec)
Rows matched: 969  Changed: 969  Warnings: 0
 
mysql> update wp_comments set comment_approved='spam' where comment_approved='0' AND comment_content LIKE "%obama%";
Query OK, 119 rows affected (0.09 sec)
Rows matched: 119  Changed: 119  Warnings: 0
 
mysql> update wp_comments set comment_approved='spam' where comment_approved='0' AND comment_content LIKE "%nofollow%";
Query OK, 412 rows affected (0.09 sec)
Rows matched: 412  Changed: 412  Warnings: 0


Yep. If you’re getting crazy spam, watch out for China, obama, and nofollow. They. Will. Destroy. Your. Sanity.

The Apology

If you legitimately are trying to comment and noted something about any of the three mentioned above, I owe you an apology. I’m sorry I shot your comment in the spam box with glee and a special kind of whimsy.

Looking at the Data, Again!

I kinda enjoy using this as a psuedo “comment honeypot” because it’s just so darn entertaining. Here are some other fun ways to look at your comment spam:

By number of comments posted per IP:

mysql> select comment_author_IP, count(comment_author_IP) as count from wp_comments where comment_approved='0' group by comment_author_IP order by count desc;
+-------------------+-------+
| comment_author_IP | count |
+-------------------+-------+
| 123.45.67.89      |    15 |
| 123.45.67.89      |    14 |
| 123.45.67.89      |    10 |
| 123.45.67.89      |     3 |
| 123.45.67.89      |     3 |
| 123.45.67.89      |     2 |
| 123.45.67.89      |     2 |
| 123.45.67.89      |     2 |
| 123.45.67.89      |     1 |
| 123.45.67.89      |     1 |
+-------------------+-------+
10 rows in set (0.00 sec)

Original IP’s censored b/c I don’t wanna be a jerk to anyone but the guy at 123.45.67.89!

By my patented, foolproof, never wrong Offender Rating System®:

mysql> select comment_author_IP, (count(comment_author_IP)+SUM(LENGTH(comment_content))) as offender_rating from wp_comments where comment_approved='0' group by comment_author_IP order by offender_rating desc;
+-------------------+-----------------+
| comment_author_IP | offender_rating |
+-------------------+-----------------+
| 123.45.67.89      |           10051 |
| 123.45.67.89      |            3907 |
| 123.45.67.89      |            2367 |
| 123.45.67.89      |            1159 |
| 123.45.67.89      |             836 |
| 123.45.67.89      |             811 |
| 123.45.67.89      |             677 |
| 123.45.67.89      |             653 |
| 123.45.67.89      |             328 |
| 123.45.67.89      |             297 |
+-------------------+-----------------+
10 rows in set (0.00 sec)

Again, original IP’s censored b/c I don’t wanna be a jerk to anyone but the guy at 123.45.67.89!

…fin!

Wasn’t that fun?! =D

Don’t spam me, bro!

Getting a quick summary on your MySQL Database

Summary of yer tables

Sometimes, you just want to see what’s going on from a high level. “HOW CAN I GET A SUMMARY OF MY TABLES?!” you may have asked yourself…

To do this, you’ll need to do a simple query into the TABLES table in the information_schema database. Like this:


mysql> use information_schema;
Database changed

mysql> 

It’s a habit for me, but I always like to describe the table so I know what I’m looking at:


mysql> desc TABLES;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

mysql> 

Now, we can see what we’re looking for. Here’s what I generally like to do:


mysql> select TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, AUTO_INCREMENT from TABLES where TABLE_SCHEMA='werd';
+-----------------------+------------+----------------+-------------+------------------+--------------+----------------+
| TABLE_NAME            | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH  | INDEX_LENGTH | AUTO_INCREMENT |
+-----------------------+------------+----------------+-------------+------------------+--------------+----------------+
| wp_commentmeta        |          0 |              0 |           0 |  281474976710655 |         4096 |              1 |
| wp_comments           |          1 |            228 |         228 |  281474976710655 |         7168 |              2 |
| wp_hook_list          |          0 |              0 |       16384 |                0 |            0 |           NULL |
| wp_links              |          0 |              0 |           0 |  281474976710655 |         1024 |              1 |
| wp_options            |        154 |           2548 |      430244 |  281474976710655 |        13312 |            835 |
| wp_postmeta           |          9 |             44 |         404 |  281474976710655 |        10240 |             20 |
| wp_posts              |          8 |            298 |        2388 |  281474976710655 |        11264 |             17 |
| wp_term_relationships |          3 |             21 |          63 | 5910974510923775 |         3072 |           NULL |
| wp_term_taxonomy      |          1 |             40 |          40 |  281474976710655 |         4096 |              2 |
| wp_terms              |          1 |             40 |          40 |  281474976710655 |        11264 |              2 |
| wp_usermeta           |         19 |             61 |        1160 |  281474976710655 |        10240 |             20 |
| wp_users              |          1 |             92 |          92 |  281474976710655 |         4096 |              2 |
+-----------------------+------------+----------------+-------------+------------------+--------------+----------------+
12 rows in set (0.00 sec)

mysql> 

Yeah, even my schema names are trendy and clever.</sarcasm>

Anyways, now you can sort and/or select however you want! Grok yer data, amigo. =]

I realize my code blocks are not blocking well enough. Will fix.

Rohjaynator::1728415721::83603615