Wednesday, May 2, 2018

Some OpenVMS PHP updates


Introduction

This post provides a brief overview of new PHP extensions provided by VMS Software Inc. for the Oracle RDB and Mimer relational databases and for the Redis in memory cache. The extensions are included in the PHP 5.6.10J kit for OpenVMS provided by VMS Software Inc., which may be installed on OpenVMS V8.4-1H1 or higher. The RDB and Mimer extensions have been developed specifically by VMS Software Inc. while Redis support is facilitated by the PhpRedis open source project (see https://github.com/phpredis/phpredis).

The following sections provide a brief overview of the three extensions, outlining the functions that are available and providing simple examples to illustrate their usage. It should be noted that the RDB and Mimer extensions have been kept deliberately simple. It is likely that additional functionality will be added over time; however the functionality that is currently provided should be more than sufficient for most cases.

RDB API

Oracle RDB is arguably the most commonly used relational database platform for OpenVMS. Over the years, various custom solutions have been developed to serve data stored in RDB databases via some form of web-based interface; however it is relatively straightforward to implement a generic RDB interface by creating a simple PHP extension that will allow developers to include queries and other database operations directly into PHP web pages.

With this in mind, VMS Software Inc. has developed a simple PHP extension for Oracle RDB that implements the set of functions listed in the table below.

Function
Description
rdb_fetch_row
Fetches a row of data using the specified cursor. The fetched data is returned as an array of strings.
rdb_ncol
Returns the number of columns (values) that would be returned by a fetch for the specified cursor.
rdb_attach
Attaches to the specified database.
rdb_close_cursor
Closes the specified cursor.
rdb_commit
Commits the current database transaction.
rdb_data
Returns the data value for the specified column for the last fetch operation.
rdb_declare_cursor
Declares a cursor.
rdb_detach
Disconnects from the database.
rdb_exec
Executes a previously prepared SQL statement.
rdb_execi
Executes the supplied SQL statement immediately.
rdb_fetch
Fetches a row of data for the specified cursor but does not explicitly return the fetched data.
rdb_free
Frees resources associated with the supplied cursor handle.
rdb_open_cursor
Opens a cursor using the supplied (previously declared) cursor handle.
rdb_prepare
Prepares an SQL statement and returns a handle for the prepared statement.
rdb_rollback
Rolls back the current database transaction.
rdb_set_readonly
Starts a read-only transaction.
rdb_error
Returns a description of the last error.
rdb_sqlcode
Returns the SQLCODE for the last database operation.

The interface has been kept deliberately simple, providing a small set of easy-to-use functions that provide sufficient functionality to address most requirements. Usage of the interface is for the most part self-explanatory and a detailed description of each individual function is therefore not required. Instead, usage is perhaps best illustrated by way of example.

The following example script illustrates the use of the extension to declare and use a database cursor to query the employees table in the sample database provided with Oracle RDB installations. The code beings by attaching to the database using the rdb_attach() function, which takes as input a single argument specifying the name of the database to attach to (in this case via the logical name sql$database). Note that the rdb_attach() function returns a completion status; it does not return any form of connection handle. For any real-world web application, the database attach would generally be performed only once as part of some initialization routine, as repeatedly connecting and disconnecting to and from the database when processing each PHP page request would incur significant overhead.

After connecting to the database, the script declares a cursor using the rdb_declare_cursor() function. The inputs to this function are the name of the cursor and the SQL statement for the cursor. Upon successful completion, the function returns a cursor handle that can then be used in subsequent calls to open and close the cursor and to fetch records. Note that it is up to the caller to specify a unique name for each cursor. Also note that cursor handles can be reused until they are explicitly freed via a call to the rdb_free() function, which frees allocated memory and cleans up any database resources associated with the handle that are no longer required.

Before opening the cursor, the script starts a new transaction using the rdb_set_readonly() function, which explicitly starts a read-only transaction on the database. It should be noted that it is not necessary to explicitly start a transaction; however it is generally good practice to do so, and if it is known that the transaction will be only reading data then it is generally beneficial to declare the transaction as read-only. This reduces resource usage helps to avoid contention issues. After starting the transaction, the script opens the cursor (using the previously allocated handle) and fetches rows until the end of the record stream is reached. Data values are displayed for each fetched record, and the cursor is closed once all records have been processed. Finally, the read-only transaction is rolled back, resources associated with the cursor handle are freed, and the script detaches from the database by calling rdb_detach(). Note that the transaction could equally be committed via a call to rdb_commit(), however strictly speaking there is no actual work to commit.

<?php
  if (! extension_loaded('rdb')) {
     if (! dl('rdb.exe')) {
        exit;
     }
  }

  if (rdb_attach('sql$database') == -1) {
     printf("%s\n", rdb_error());
     exit;
  }

  $cursor = 'C0001';
  $ch = rdb_declare_cursor($cursor, 'select employee_id,last_name,first_name from employees');

  if ($ch == NULL) {
     printf("%s\n", rdb_error());
     exit;
  }

  rdb_set_readonly();

  if (rdb_open_cursor($ch) == -1) {
     printf("%s\n", rdb_error());
     exit;
  }

  while (($row = rdb_fetch_row($ch)) != NULL) {
     print_r(array_values($row));
  }

  if (rdb_sqlcode() != 100) {
     printf("%s\n", rdb_error());
     exit;
  }

  if (rdb_close_cursor($ch) == -1) {
     printf("%s\n", rdb_error());
     exit;
  }

  rdb_rollback();

  rdb_free($ch);
  rdb_detach();
?>

Note that all functions return either an integer completion code, a cursor or statement handle, or a result set. In the event of an error, functions that return an integer completion code will return a value of -1 to indicate that an error has occurred, and other functions will return a NULL value in the event of an error. Whenever an error is returned, the function rdb_error() can be used to get a textual description of the last error and the function rdb_sqlcode() can be used to get the integer SQL error code. It should also be noted that the error handling in the above example could be improved by ensuring that any outstanding transaction is committed or rolled back and resources are properly cleaned up before existing following an error. Similar comments apply to other example code included in this document.

The above example uses the rdb_fetch_row() function to retrieve a row of data at a time, loading values for each column into an array. This is arguably the most convenient method of fetching data; however it is also possible to retrieve individual column values by using the rdb_fetch() and rdb_data() functions as illustrated below, where the call to rdb_fetch_row() in the above example has been replaced by a call to rdb_fetch() and a loop that uses rdb_data() to retrieve the values for each column for each row that is fetched. Using rdb_fetch_row() is simpler and slightly more efficient; however there may be situations where this alternative approach may be more appropriate.

while (rdb_fetch($ch) == 1) {
   for ($i = 0; $i < 3; $i++) {
      $val = rdb_data($ch, $i);
      echo "\t$val";
   }
   echo "\n";
}

The next example illustrates use of the rdb_execi() (execute immediate) function to execute a dynamic SQL update statement against the database. Note here that it would generally be good practice to explicitly set the transaction scope before performing the update, reserving only the employees table for write operations. By not explicitly setting the scope of the transaction, RDB will start a default read/write transaction reserving all tables in the database for read/write operations, which may cause contention problems if there are multiple database users, and will reserve more resources than necessary for the transaction in question. An explicit scope for the transaction may be established by calling the rdb_execi() function with an appropriate SQL “set transaction” statement.

<?php
   if (! extension_loaded('rdb')) {
      if (! dl('rdb.exe')) {
         exit;
      }
   }

   rdb_attach('sql$database');

   if (rdb_execi('update employees set city=\'Bolton\' where employee_id=\'00249\'') == -1) {
      printf("%s\n", rdb_error());
      exit;
   }

   rdb_commit();
   rdb_detach();
?>

Note that it is also possible to prepare SQL statements (using the rdb_prepare() function and execute them using rdb_exec()); however the RDB PHP API currently provides no mechanism to specify parameter markers and parameter values. It is intended that this functionality will be included in a subsequent release of the RDB API. From a PHP coding perspective, the lack of such functionality is perhaps of little consequence; however from a performance perspective it can be advantageous to prepare frequently used SQL statements and it is therefore desirable to have this functionality in the future.

Mimer API

The Mimer database (see http://www.mimer.com/) has existed on OpenVMS for almost longer than any other relational database and represents a viable alternative to some of the more commonly known databases for the OpenVMS operating system platform, providing excellent performance and stability with a small footprint and low administrative overhead.

The following table summarizes the functions provided by the Mimer PHP API. As for the RDB API, the set of functions provided is deliberately minimal, providing a basic set of functionality that should be sufficient for most application needs.

Function
Description
mimerdb_connect
Connects to the specified database.
mimerdb_disconnect
Disconnects from the database.
mimerdb_exec
Executes the supplied SQL statement.
mimerdb_fetch
Fetches a row of data using the specified cursor.
mimerdb_close
Closes the specified cursor.
mimerdb_error
Returns a description of the last error.
mimerdb_ncol
Returns the number of columns (values) that would be returned by a fetch for the specified cursor.
mimerdb_commit
Commits the current database transaction.
mimerdb_rollback
Rolls back the current database transaction.
mimerdb_set_readonly
Starts a read-only transaction.
mimerdb_sqlcode
Returns the SQLCODE for the last database operation.

The functions provided by the Mimer API are also very similar in terms of operation to those that are provided by the RDB API; however there are a few differences that are hopefully illustrated by the following example.

In particular, it should be noted that there are no specific functions to declare, open, and close database cursors. The mimerdb_exec() function determines from the provided SQL statement whether the operation will return any rows or not and acts accordingly, returning a value of 0 for success when the statement returns no result set, a positive integer sequence number when there is a result set, and a value of -1 to indicate that an error has occurred. If a positive integer is returned by mimerdb_exec() this implies that a cursor has been created and opened, and the result set may be retrieved using mimerdb_fetch(), which retrieves data into an array a row at a time. Note that the function mimerdb_close() should only be called when mimerdb_exec() returns a positive integer value (when there is a result set).

<?php
   if (! extension_loaded('mimerdb')) {
      if (! dl('mimerdb.exe')) {
         exit;
      }
   }

   $rv = mimerdb_connect('mydb', 'SYSADM', 'password');
   if ($rv != 1) {
      printf("%s\n", mimerdb_error());
      exit;
   }

   mimerdb_set_readonly();

   $ch = mimerdb_exec('select * from metric_groups');
   if ($ch == -1) {
      printf("%s\n", mimerdb_error());
      exit;
   }

   while (($row = mimerdb_fetch($ch)) != NULL) {
      print_r(array_values($row));
   }

   if (mimerdb_sqlcode() != 100) {
      printf("%s\n", mimerdb_error());
      exit;
   }

   mimerdb_close($ch);
   mimerdb_rollback();
   mimerdb_disconnect();
?>

With the exception of the mimerdb_fetch() and mimerdb_error() functions, all functions return an integer value that can be used to determine whether the operation in question was successful or not. A return value of -1 indicates that an error occurred, whereupon the function mimerdb_error() may be used to retrieve a textual description of the error and the function mimerdb_sqlcode() can be used to determine the integer SQL status code associated with the error in question. The function mimerdb_fetch() returns an array upon successful completion or NULL if an error occurred.

As with the Oracle RDB PHP interface, the Mimer PHP interface can only be connected to one database at a time.

Redis API

Redis (https://redis.io/) is a powerful, efficient, and functionally rich open source in-memory data structure store that can be used as used as an in-memory database, cache, and message broker. It supports data structures such as strings, hashes, lists, sets, sorted sets with range queries, bitmaps, and geospatial indexes with radius queries. Redis is commonly used to improve the performance of websites by caching in memory frequently accessed static data; however it is also applicable to a wide range of other use-cases. Redis has recently been ported to OpenVMS, and the inclusion of Redis client functionality in the PHP distribution for OpenVMS provided by VMS Software Inc. makes it readily possible to leverage Redis functionality from OpenVMS-based PHP applications.

As noted above, the PHP Redis extension is provided by the PhpRedis open source project (https://github.com/phpredis/phpredis) and the reader should refer to the PhpRedis documentation  for full details regarding the use of the API; however it is perhaps useful to consider a few simple examples here.

The first example below illustrates the basics of connecting to the Redis cache and verifying that the cache is functioning by sending it a “PING” command, to which the cache should respond with the text “+PONG”. The example also calls the info() method to retrieve data about the cache including software version details and various operational metrics (for a details description of these data the reader should refer to the Redis documentation).

<?php
   if (! extension_loaded('redis')) {
      if (! dl('redis.exe')) {
         exit;
      }
   }

   $redis = new Redis();

   $redis->connect('127.0.0.1', 6379);
   echo print_r($redis->info(), true);
   echo $redis->ping();
   $redis->close();
?>


The next example illustrates adding a simple key/value pair to the cache and the retrieval and deletion of the key from the cache. The code also illustrates the use of exception handling that is provided by the API to catch and manage unexpected error conditions.


<?php
   if (! extension_loaded('redis')) {
      if (! dl('redis.exe')) {
        exit;
      }
    }

    $redis = new Redis();
    try {
        $redis->connect('127.0.0.1', 6379);
        $redis->set('greeting', 'Hello!');
        $reply = $redis->get('greeting');

        if ($reply){
            echo "Reply: '{$reply}'\n";
            if ($redis->delete('greeting')){
                echo "Key deleted\n";
            }
        }else{
            echo "Key not found\n";
        }
    } catch (RedisException $e){
        $exceptionMsg = $e->getMessage();
        echo "Houston we have a problem: {$exceptionMsg}\n";
    }
?>

The following final example illustrates some basic Redis list operations, such as populating a list and retrieving list elements. Once again, refer to https://github.com/phpredis/phpredis for additional information.

<?php
   if (! extension_loaded('redis')) {
      if (! dl('redis.exe')) {
         exit;
      }
   }

   // Connect to Redis on localhost
   $redis = new Redis();
   $redis->connect('127.0.0.1', 6379);
   echo "Connection to server successful";

   // Store list data
   $redis->lpush("language-list", "COBOL");
   $redis->lpush("language-list", "FORTRAN");
   $redis->lpush("language-list", "Pascal");

   // Get the stored data and print it
   $arList = $redis->lrange("language-list", 0 ,5);
   print_r($arList);
   $redis->delete("language-list");
?>


No comments:

Post a Comment