Updating a Paradox DB using PHP and the Paradox Extension

Written by James McDonald

October 20, 2016

A few years ago I blogged about reading a paradox DB. Recently I had need of updating some records and thought I would blog about it. See below.

Reading a Paradox DB Under Windows using XAMPP

Firstly there doesn’t seem to be a modern Linux distro that comes with the PHP Paradox extension baked in. So first I fired up a copy of Ubuntu 15.10 and installed lamp-server etc:

apt-get install lamp-server^ php-pear pxlib1 pxlib-dev
apt-get install build-essential php5-dev re2c

And following the instructions here ==> http://php.net/manual/en/paradox.installation.php#117151 which says you can’t install the product with pecl install paradox but need to download the https://pecl.php.net/package/Paradox/1.4.3 install package untar it and patch it ( although I found I couldn’t get the patch to apply cleanly using to patch < patchname so I just edited paradox.c to match what the patch was trying to say )

Once you have it installed you need to create a /etc/php5/mods-available/paradox.ini file as follows

; configuration for php Paradox module
; priority=30
extension=paradox.so

and then enable it using:

php5enmod paradox

Once I restarted apache2 (not sure that was needed seeing as I am using the php-cli)

And wrote some code I found that I had a seg fault when trying to use the px_* style of paradox px_update_record() function. So I switched over to OOP hoping it would work. (Which it did!) To find out how to properly configure the OOP calls there was some really nice test files in the paradox package (https://pecl.php.net/get/paradox-1.4.3.tgz) specifically paradox-1.4.3/tests/017.phpt actually looking in tests/*.phpt you will find better examples that you will probably find anywhere of how to use all the different functions.

Anyway what follows is some really ordinary code (IANAP) that works using the php-cli on Ubuntu 15.10 with PHP 5.6.11-1ubuntu3.4 (cli) and the paradox 1.4.3 extension built against pxlib 0.6.5 to update a record. This script echo’s each record to the console. It assigns one new value and then updates the record.

<?php

class ParadoxWriter extends paradox_db {

    public function getSchema() {

        $stock_schema = $this->get_schema();

        return $stock_schema;
    }

    public function getType($fieldname) {
        $schema = $this->getSchema();
        $type = $schema[$fieldname]['type'];

        if ($type == PX_FIELD_DATE) {

            return [ 'function' => 'date2string',
                'format' => $this->dateFormat
            ];
        } elseif ($type == PX_FIELD_TIMESTAMP) {
            return [ 'function' => 'timestamp2string',
                'format' => $this->dateTimeFormat];
        } elseif ($type == PX_FIELD_TIME) {
            return [ 'function' => 'timestamp2string',
                'format' => $this->timeFormat];
        }

        return null;
    }

    public function __construct($options = []) {
        //parent::__construct();
        $defaults = [
            'dateFormat' => "d/m/Y",
            'timeFormat' => "h:i:s A",
            'dateTimeFormat' => "d/m/Y h:i:s A"
        ];

        $a = $options + $defaults;

        foreach ($a as $k => $v) {

            $this->$k = $v;
        }
    }

}

$options = [
    'dateFormat' => "d/m/y",
    'timeFormat' => "h:i:s A",
    'dateTimeFormat' => "d/m/Y h:i:s A",
    'randomvalue' => 3
];

$pxdoc = new ParadoxWriter($options);
echo $pxdoc->dateFormat . "\n";
echo $pxdoc->timeFormat . "\n";
echo $pxdoc->dateTimeFormat . "\n";


$total = 0;
$trandb = dirname(__FILE__) . "/paradox/data/Transaction.DB";


$fp = fopen($trandb, "r+") or exit("Cannot open $trandb");

if (!$pxdoc->open_fp($fp)) {

    exit("px_open_fp error");

    /* Error handling */
}

$myfromdate = '2016-07-25';
$mytodate = '2016-10-14';
$carrier = 'UBER';
//convert to unix time so we can compare to db
$fromdate = strtotime($myfromdate);
$todate = strtotime($mytodate);

$int = $pxdoc->numrecords();

//reset my record to nothing
$myRecord = null;


/*  just use the PX_FIELD constants
 *  datetime = 21
 *  date = 2
 *  time = 20
 */



//I'm looping back through the db from newest to oldest hence the $i--
for ($i = $int - 1; $i > 0; $i--) {

    // get each record in turn
    $myRecord = $pxdoc->retrieve_record($i);

    //reset the array back to it's first element
    reset($myRecord);

    //grab the transaction date from the record so we can compare it to our constraints
    $record_date = strtotime($pxdoc->date2string($myRecord["TranDate"], "Y-m-d"));
    if (( $record_date >= $fromdate ) and ( $record_date <= $todate) and
            $myRecord["Carrier"] == $carrier
    ) {

        echo "#BEGIN $i\n";

        $myRecord['Item'] = '20250';
        // px_update_record seg faults so converted it all to OOP
        if ($pxdoc->update_record($myRecord, $i)) {
            echo "Sucess update \n";
        } else {
            echo "Fail update \n";
        };

        $count = 0;
        $len = count($myRecord);

        foreach ($myRecord as $key => $record_val) {
            // this bit checks for the type of record
            // and if it needs special formatting because
            // of being a date, a time or a datetime
            // it will return an array 
            // $func = [ 'function' => 'date2string', 'format' => 'd/m/Y' ]
            // which will provide the info to call the right pxlib function
            $func = $pxdoc->getType($key);

            if ($func !== null) {
                $record_val = $pxdoc->$func['function']($record_val, $func['format']);
            }
            echo $key . ' ' . $record_val;

            if ($count !== $len - 1) {
                echo ',';
            }

            $count++;
        }

        echo "\n";

        $total++;
        echo "#END\n";
    }
}

echo "Total Records edited " . $total . "\n";

//Close the pxlib objects
$pxdoc->close();

$ret = fclose($fp);
var_dump($ret); // true for success
?>

 

 

 

 

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

The reCAPTCHA verification period has expired. Please reload the page.

You May Also Like…

Squarespace Image Export

To gain continued access to your Squarespace website images after cancelling your subscription you have several...

MySQL 8.x GRANT ALL STATEMENT

-- CREATE CREATE USER 'tgnrestoreuser'@'localhost' IDENTIFIED BY 'AppleSauceLoveBird2024'; GRANT ALL PRIVILEGES ON...

Exetel Opt-Out of CGNAT

If your port forwards and inbound and/or outbound site-to-site VPN's have failed when switching to Exetel due to their...