MaxDB & PHP - Ready for the Web!

by Ulf Wendel

MaxDB is ready for the Web! Or should I say ready for beginners? PHP is known as the "basic of the web". Rasmus Lerdorf, the inventor of the popular web scripting language PHP used the comparison with BASIC to describe some of the strengths of PHP, the ease of use and the ease of learning. On MySQL ComCon Europe in Frankfurt (8-10.11.2004) a native PHP extension for MaxDB was announced that brings the power of MaxDB to all PHP users. So far MaxDB could only be accessed from PHP via ODBC, but now PHP has gained access to the full power of MaxDB through the new MaxDB extension.

Some words about MaxDB

MaxDB by MySQL is one of the two enterprise level databases of MySQL AB. MaxDB has its origins in Adabas D which in 1997 was bought from Software AG by SAP, Europe's largest software development company. SAP continued the development of the database and made it a stable, powerful and reliable product that can be used as a standalone industrial strength relational database management system or in conjunction with SAP's main software product R/3. MaxDB is an excellent choice for R/3. It performs very well both in aspects of performance and total cost of ownership. R/3 hosting companies have found out that the deployment costs of MaxDB, from human resources and hardware requirements, are at least 30% below the costs of well known competitors. . In 2003 MySQL and SAP formed a partnership, where MySQL AB acquired the full commercial rights to develop, market and sell the database. The aim of the partnership is to make MaxDB an even more popular product in the database market. MySQL influences and contributes to the MaxDB development by the signals received by customers in the market and by users in the community. In order to facilitate a broader adoption of MaxDB in the web-market, MySQL started the project in May 2004 of making the product fully PHP enabled. Finally, now in November 2004, PHP stands ready to utilize the full power of the MaxDB database.

Installation notes

The first contact to MaxDB might be a cultural shock for most PHP and MySQL users, so please be patient if some things do not go easy or are uncommon for you. MaxDB is different from MySQL. I will try to teach how to start using MaxDB in this short article and I hope that we can warn you of some pitfalls when you port applications between MySQL to MaxDB.

If you do not have MaxDB installed already, go to http://dev.mysql.com/downloads/ and download the latest production version of MaxDB. At the time of writing this is 7.5.00 build 19. On Unix choose the Server download. Do not try to build it on your own from the source and prefer an SDB*-tools based installation over a RPM distribution as long as you are new to MaxDB. Don't worry: SDB*-tools are the "natural" tools to install it on Unix, they work quite well.

If you are interested in a GUI based installation, then try the beta version of the MaxDB Installation Manager. You can download it from http://dev.mysql.com/doc/maxdb/instupgrade.html. It worked fine for me both on XP and SuSE 9.1.

Please also download the latest version of the documentation. MaxDB is continually improved and some improvements require changes that are not backwards compatible. So it is possible that future versions of the manual do not mention all the changes that has happened, and then you might need to consult the older version of the manual that belongs to your version of the software.Therefore it is wise, to download and store locally the current documentation at the same time, as you download the current MaxDB version.

After you have installed MaxDB on your computer, create an instance. An instance is comparable to a (logical) database on a MySQL database server of a certain version. Instances can be created very easily with the GUI tool "Database Manager" or the web interface "WEBDBM" using wizards. The Database Manager runs only on Windows but you can use it to administrate every MaxDB server in your network. The web interface is available both on Unix and Windows. Please consult the manual for details.

Once you have created your first MaxDB instance, you can continue with the installation of the PHP extension. The extension requires PHP version 4.3.6+. Download the extension from http://dev.mysql.com/doc/maxdb/interfaces.html. We are currently working on making future versions a part of PECL. PECL - Pear Extension Code Library - is the official repository for PHP extensions that are not part of core PHP downloads but are officially distributed and maintained.

On Windows you do not need to do more but add the pre-compiled php_maxdb.dll to your PHP extension directory and enable it in your php.ini. If you are in doubt what your extension directory is or where the php.ini resides, check the output of phpinfo() to discover the path information.

On Unix you need to compile a PHP with MaxDB support on your own. The PHP extension is based on the SQLDBC C/C++ call interface for MaxDB. If you have not downloaded it already, download it now and unpack it to the root directory of your MaxDB installation. For me, on SuSE Linux 9.1, the path was /opt/sdb/programs. The sources are required to link PHP against them. Follow the installation instructions in the "MaxDB PHP Manual", run buildconf --force, configure -with-maxdb=/opt/sdb/programs/, make, make install and wait.

Hands on!

It is time for a first hands-on. The example connects to a MaxDB instance, runs a simple select statement, prints out the results, frees the resources allocated for the result set and closes the connection.

It is good practice to free all resources as soon as possible. Always remember that PHP works in a web environment with many parallel requests. Every byte that you waste has to be multiplied with the number of concurrent PHP processes to measure the effect on the entire server.

<php
// turn on error reporting
error_reporting(E_ALL);

// create a connection
$link = maxdb_connect(
    'localhost', // host
    'DBA',       // user
    'DBA',       // password
    'DEMO'       // instance
  );

if (!$link) {
  // handle connection problems
  $msg = sprintf('Cannot connect to database: %s [%d]',
     maxdb_connect_error(),
     maxdb_connect_errno()
  );
  error_log($msg);
  print $msg;
  exit(1);
}

// run the select statement
if ($result = maxdb_query($link, 'SELECT people_id, firstname, 
lastname FROM people WHERE rowno <= 10')) {

  // fetch all records into an associative array
  while ($row = maxdb_fetch_assoc($result)) {
    printf("%010d %40s %80s\n",
      $row['PEOPLE_ID'],
      $row['FIRSTNAME'],
      $row['LASTNAME']);
  }
}

// important: free all resources as soon as possible!
maxdb_free_result($result);

// close the maxdb connection
maxdb_close($link);
?>

You may have already noted that the API looks like a 1 by 1 copy of the procedural style of the PHP mysqli extension. We tried to stay as close to the syntax of the mysqli_* functions as possible. To migrate applications that use the procedural API of the mysqli extension to MaxDB you simply have to search and replace the "mysqli_" function name prefix with "maxdb_". Easy isn't it? Well, not quite... as every experienced programmer knows, database systems tend to have very useful proprietary features, that make porting applications difficult once you use them.

Pitfall 1: Simple Identifiers. If you work with the MaxDB GUI tools or the CLI tools you will soon realize, that they do not work case-sensitive in many areas. This applies to database connection parameters and SQL identifiers. So called "simple identifiers" like the column names in the above select statement are always converted into upper case characters in the database. This feature is very handy if you work with the GUI tools, but in our case it makes porting harder. The keys of the associative array $row returned by maxdb_fetch_assoc($result) in our example are: PEOPLE_ID, FIRSTNAME, LASTNAME. With mysqli_fetch_assoc($result) they would have been: people_id, firstname, lastname. To get lower case identifiers you have to rewrite the select query to use "Special Identifiers". The rewritten statement is: SELECT people_id AS "people_id", firstname AS "firstname", lastname AS "lastname" FROM people WHERE rowno <= 10.

Pitfall 2: no LIMIT clause. The tale goes that Rasmus Lerdorf introduced LIMIT, a non-standard SQL extension to the MySQL SELECT statement. LIMIT is extremely handy and common in web programming. For example, it is used to switch between page n and n+1 of a result list. LIMIT does not exist in MaxDB. What comes closest to LIMIT is the "magic" rowno column. The rowno WHERE condition in the example simulates LIMIT 10. The problem with rowno is, that it is not as powerful as LIMIT. You cannot convert a query that uses LIMIT 10, 10 or has an ORDER BY clause. That means you have to rewrite all queries in your mysqli based application that use LIMIT and utilize for example maxdb_data_seek().

So what does that mean? Although we tried to make the APIs of the MaxDB and the MySQL PHP extension (mysqli) as similar as possible, we cannot provide you with a solution for the glory details. Nevertheless, we will show you that if you are familiar with the mysqli extension, then you will be able to use the MaxDB extension without much effort.

More differences: sample table definition

For all remaining examples we will continue to use the "people" table. The data definition statement to create the table is:

CREATE TABLE people (
  people_id INTEGER DEFAULT SERIAL PRIMARY KEY,
  firstname CHAR(40),
  lastname CHAR(80),
  gender CHAR(6) CHECK gender IN ('male', 'female')
)

MySQL users, we did warn you. MaxDB is different from MySQL. In MySQL the classical schema definition would have been:

CREATE TABLE people (
  people_id INT(10) AUTO_INCREMENT PRIMARY KEY,
  firstname CHAR(40),
  lastname CHAR(80),
  gender ENUM('male', 'female')
)TYPE = InnoDB

Once again we see incompatibilities: MySQL AUTO_INCREMENT translates into DEFAULT SERIAL on MaxDB. The constraint on the "gender" column in MaxDB cannot be translated toMySQL. The datatype ENUM does not reject INSERT statements that contain other values for the column gender but 'male' and 'female'. MySQL silently converts those values to empty strings.

Pitfall 3: SQL dialects differ. Whoever tells you that porting an application from database A to database B is very easy if you use some simple abstraction layers that give you the same API on database A and database B does not tell you all of the story. SQL dialects differ from each other in subtle ways and can cause quite a bit of confusion. We could have easily shown similar differences between the SQL dialects of products from other vendors.

Inserting data

The insertion of data into the sample table is a straightforward modification of the select example.

<?php
// turn on error reporting
error_reporting(E_ALL);

// create a connection
$link = maxdb_connect(
    'localhost', // host
    'DBA',       // user
    'DBA',       // password
    'DEMO'       // instance
  );

if (!$link) {
  // handle connection problems
  $msg = sprintf('Cannot connect to database: %s [%d]',
     maxdb_connect_error(),
     maxdb_connect_errno()
  );
  error_log($msg);
  print $msg;
  exit(1);
}

// insert 1.000 random rows
for ($i = 0; $i < 1000; $i++) {

  $query = sprintf("
             INSERT INTO people
                (firstname, lastname, gender)
             VALUES
                ('firstname%d', 'lastname%d', '%s')",
             $i,
             $i,
             (mt_rand(0, 1) > 0) ? 'male' : 'female'
            );

  if (!maxdb_query($link, $query)) {
    $msg = sprintf('INSERT failed: %s (%d)',
             maxdb_error($link),
             maxdb_errno($link)
           );
    error_log($msg);
    print $msg;
    exit(2);
  }

}

// delete one of the entries
if (!maxdb_query($link, "DELETE  FROM people WHERE firstname = 'firstname11'"))

{
  $msg = sprintf('DELETE failed: %s [%d]',
           maxdb_error($link),
           maxdb_errno($link)
         );
  error_log($msg);
  print $msg;
  exit(3);
}

// close the maxdb connection
maxdb_close($link);
?>

The code creates 1.000 entries in the people table and then deletes one of the entries. Question: how many entries exists in the table people, 1.000 or 999? With MySQL it is 999, with MaxDB it is 0 if you use the default settings. The reason for this is the different assumption for autocommit, our next pitfall.

Pitfall 4: The MaxDB default setting for autocommit is off, MySQL uses on. To ensure that the default settings do not play tricks on you, we recommend that you explicitly enable the behaviour you want at the beginning of your database session using maxdb_autocommit($link, true|false). Please note also that the default for the MaxDB sqlcli command line tool and the GUI tools is autocommit = on.

Years ago one could speed up PHP scripts a little bit by using single quotes to enclose strings instead of double quotes, because PHP does not do variable substitutionwithin strings that are enclosed in single quotes. Although this speed difference no longer exists, some programmers started to use single quotes over double quotes whenever possible and continued to do so until nowadays. I have adapted my coding style. Let's see what happens if we replace the double quotes with single quotes and the single quotes with double quotes in the sprintf() function.

$query = sprintf('
             INSERT INTO people
                (firstname, lastname, gender)
             VALUES
                ("firstname%d", "lastname%d", "%s")',
             $i,
             $i,
             (mt_rand(0, 1) > 0) ? 'male' : 'female'
            );

Warning: maxdb_query() [function.maxdb-query]: -5004 Missing constant [42000] in C:\www\maxdbext\index.php on line 35

Ouch - MaxDB indicates and error and gives you anl error message. The insert statement you have send to MaxDB is: INSERT INTO people (firstname, lastname, gender) VALUES ("firstname1", ... Everything that is enclosed in double quotes looks like a special identifier for MaxDB.

Pitfall 5: be careful with double quotes, they look like special identifiers. Even if it does not match you personal coding style, use double quotes to enclose query strings and use single quotes to encapsulate values within UPDATE and INSERT statements. You can work around this problem by using parameter bindings.

Prepared statements

Prepared statements are one of the major benefits that the mysqli extension has added over the old mysql extension. Prepared statements give you a performance benefit and make your applications more secure against SQL injection.

If you send the same query to a database server multiple times, then the query has to parsed and evaluated each time it gets executed. With prepared statements, queries are parsed only once. Once a query has been parsed, the parse information is stored in an internal format and the query can be executed very fast.

Prepared statements are not bound to static statements. The DML statements can contain placeholders, parameters. Input and output parameters are supported. Whenever the database uses input parameters the contents of the input parameters are escaped according to their datatypes. This frees the programmer from the task to evaluate all input parameters very thoroughly before they get used in SQL statements with maxdb_real_escape_string() so that no SQL injection attacks can happen.

<?php
// turn on error reporting
error_reporting(E_ALL);

// create a connection
$link = maxdb_connect(
    'localhost', // host
    'DBA',       // user
    'DBA',       // password
    'DEMO'       // instance
  );

if (!$link) {
  // handle connection problems
  $msg = sprintf('Cannot connect to database: %s [%d]',
     maxdb_connect_error(),
     maxdb_connect_errno()
  );
  error_log($msg);
  print $msg;
  exit(1);
}
// set the autocommit behaviour for this session
maxdb_autocommit($link, true);


// create a prepated statement
if (!($stmt = maxdb_prepare($link, 'INSERT INTO people (firstname, lastname, 
gender) VALUES (?, ?, ?)'))) {
  $msg = sprintf('Cannot create prepared statement: %s [%d]',
    maxdb_error($link),
    maxdb_errno($link)
  );
  error_log($msg);
  print $msg;
  exit(2);
}

// bind three string (sss) parameters to the prepared statement
$firstname  = NULL;
$lastname   = NULL;
$gender     = NULL;
maxdb_bind_param($stmt, 'sss', $firstname, $lastname, $gender);

// insert 1.000 random rows
for ($i = 0; $i > 1000; $i++) {

  $firstname = 'firstname' . $i;
  $lastname = 'lastname' . $i;
  $gender = (mt_rand(0, 1) > 0) ? 'male' : 'female';

  if (!maxdb_execute($stmt)) {
    // NOTE: extra function to retrieve the error message for prepared statements!
    $msg = sprintf('Prepared INSERT failed: %s', maxdb_stmt_error($stmt));
    error_log($msg);
    print $msg;
    exit(3);
  }

}

// close the prepared statement, free resources
maxdb_stmt_close($stmt);

// close the maxdb connection
maxdb_close($link);
?>

maxdb_prepare() is used to create a prepared statement with three input parameters. The position of each parameter within the INSERT statement is indicated with a question mark. maxdb_bind_params() tells the database server, that the three question marks shall be replaced with the string values ('sss') contained in the variables $firstname, $lastname, $gender whenever the prepared statement gets executed with maxdb_execute(). maxdb_stmt_close() frees all resources associated with a prepared statement.

The following values are available to describe how the extension shall interpret the weakly typed PHP variables.

This example is so simple and nice that this does not add any additional pitfalls. The syntax is 100% compatible with mysqli and the code runs on MySQL if you replace maxdb_* with mysqli_*.

Reading results from prepared statements

Prepared statements are not only handy for INSERT and UPDATE but also for SELECT statements. The code below shows how it is done.

<?php
// turn on error reporting
error_reporting(E_ALL);

// create a connection
$link = maxdb_connect(
    'localhost', // host
    'DBA',       // user
    'DBA',       // password
    'DEMO'       // instance
  );

if (!$link) {
  // handle connection problems
  $msg = sprintf('Cannot connect to database: %s [%d]',
     maxdb_connect_error(),
     maxdb_connect_errno()
  );
  error_log($msg);
  print $msg;
  exit(1);
}
// set the autocommit behaviour for this session
maxdb_autocommit($link, true);


// create a prepated statement
if (!($stmt = maxdb_prepare($link, 'SELECT people_id, firstname, 

lastname FROM people WHERE gender = ?'))) {
  $msg = sprintf('Cannot create prepared statement: %s [%d]',
    maxdb_error($link),
    maxdb_errno($link)
  );
  error_log($msg);
  print $msg;
  exit(2);
}

// bind input parameter to the prepared statement
$gender     = "male";
maxdb_bind_param($stmt, 's', $gender);

// bind output/result values to variables
$people_id = null;
$firstname = null;
$lastname = null;
maxdb_bind_result($stmt, $people_id, $firstname, $lastname);

// create result set
if (!maxdb_execute($stmt)) {
  // NOTE: extra function to retrieve the error message for prepared statements!
  $msg = sprintf('Prepared INSERT failed: %s', maxdb_stmt_error($stmt));
  error_log($msg);
  print $msg;
  exit(3);
}

// read all results
while (maxdb_stmt_fetch($stmt)) {
  printf("people_id [%10s]: %80s\n", gettype($people_id), $people_id);
  printf("firstname [%10s]: %80s\n", gettype($firstname), $firstname);
  printf("lastname  [%10s]: %80s\n", gettype($lastname), $lastname);
  printf("\n");
}

// close the prepared statement, free resources
maxdb_stmt_close($stmt);

// close the maxdb connection
maxdb_close($link);
?>

No pitfalls here either. The MaxDB does even choose appropriate datatypes for $people_id, $firstname and $lastname: $people_id is of type integer, $firstname and $lastname are strings.

API comparison chart

We conclude the article with an API comparison chart. The table shows all new MaxDB functions and their counterparts in the mysqli extension. Whenever there is a important difference between the implementations for MaxDB and MySQL we have added a note for you.