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");
?>
