PHP Database Programming with DB2

Add Functionality to the Class

Now, let's add the following functionality to our DB class:

1. Parse a result set (fetch)


2. Number of rows in a result set (numRows)


3. Transactions (transIsOpen, transStart, transCommit, transRollback)


4. Last Insert ID (lastInsertID)

Within the DB class, add the following lines of code:

// Member variables used to cache array template to reduce odbc calls  
var $lastrs;  
var $aTemplate;  
 
 // Generate a template for the column names (cached to reduce odbc calls)  
// void genTemplate(resourceid $rs)  
function genTemplate($rs)  
{  
 $numCols = odbc_num_fields($rs);  
 $this->aTemplate = array();  
 for($i = 0; $i < $numCols; $i++)  
   $this->aTemplate[$i] = strtolower(odbc_field_name($rs, $i));  
}  
 
 // Creates an associative array for the next row in the result set provided  
 // If a column name is provided, return the value for that column name only.  
 // Note: must use lower case column names!  
// array fetch(resourceid $rs[, string $colname])  
function fetch($rs)  
{  
 if ($rs != $this->lastrs)  
 $this->genTemplate($rs);  
 
 // Copy the Values into an array  
 $aTmp = array();  
 odbc_fetch_into($rs, $aTmp);  
 
 $numCols = sizeof($this->aTemplate);  
 // Copy the array into the associative array and return that array  
 $aRV = array();  
 for($i = 0; $i < $numCols; $i++)  
 {  
   $aRV[$this->aTemplate[$i]] = $aTmp[$i];  
   $aRV[$i] = $aTmp[$i];  
 }  
 
 if (func_num_args() > 1)  
   return $aRV[func_get_arg(1)];  
 
 return $aRV;  
}

When we execute an SQL query, the result of that query is provided to us in a result set. The fetch function will provide us with either an array that represents the current row in the given result set, or, if a column name is provided, a single value from the current row.

It's important to note that when fetch is called, the cursor is advanced to the next row automatically. So, if you want more than one value from a given row, fetch the whole row into an array, and get the values out using the column names as indices. Calling the fetch function multiple times with different column names will retrieve the value of each column from a different row.

Like any associative array in PHP, the values can also be obtained using numerical indices rather than row names.

// get the number of rows in the current result set  
// int numRows(resourceid $rs)  
function numRows($rs)  
{  
 return odbc_num_rows($rs);  
}

The above function simply returns the number of rows in a given result set.

// is there a transaction open  
// bool transIsOpen()  
function transIsOpen()  
{  
 return !odbc_autocommit($this->handle);  
}  
 
 // start an SQL transaction  
// void transStart()  
function transStart()  
{  
 odbc_autocommit($this->handle, false);  
}  
 
 // commit the current SQL transaction  
// void transCommit();  
function transCommit()  
{  
 odbc_commit($this->handle);  
 odbc_autocommit($this->handle, true);  
}  
 
 // rollback the current SQL transaction  
// void transRollback()  
function transRollback()  
{  
 odbc_rollback($this->handle);  
 odbc_autocommit($this->handle, true);  
}

Now we get into some of the great features of DB2, including transactions. A transaction is a collection of SQL commands grouped as an atomic unit. A given transaction either executes entirely or not at all. This is beneficial in situations in which data integrity is essential.

When we're not using transactions (i.e. when no transaction is open), queries are automatically committed to the database. This allows us to make queries to the database without having to start and end transactions.

The other situation in which transactions are important is when we wish to find the last ID inserted into a particular table. The following function will return the last id:

// get the last inserted ID into the specified table  
// int lastInsertID(string $tblName)  
function lastInsertID($tblName)  
{  
 if ($this->transIsOpen())  
 {  
   $sql = "SELECT SYSIBM.IDENTITY_VAL_LOCAL() AS id FROM " . $tblName;  
   $rs = $this->query($sql);  
   return $this->fetch($rs, "id");  
 }  
 return -1;  
}

This function is similar to the mysql_insert_id() function. Determining the last inserted ID is essential for generated columns, yet it's not well-documented (I actually had some correspondence with IBM to figure this out) so -- enjoy! It's now exposed in all its beauty.

Escape a Single Quotation Mark

Although it is common to escape a quote with a backslash(\) in programming languages such as C, PHP and Java, DB2 does not permit the use of backslashes in strings (unlike MySQL). You can use two quotes in a row to escape a single quotation mark, as shown here:

INSERT INTO testtbl (name, created)  
   VALUES ('Mark''s favorite popsicle flavour', '2004-01-01')

This will insert with the name, Mark's favorite popsicle flavour.

It is also relevant to note that DB2 does not allow you to enclose string within double quotation marks as MySQL does. You must use single quotation marks inside a query to enclose a string.

Table Modification

Although it is permitted by the SQL99 Standard, DB2 does not allow you to alter tables in certain ways:

1. Alter Column – DB2 does not allow you to alter the data type, default value or any other attribute of a column once it has been created.


2. Delete Column – You can not remove a column from a table once the table has been created.

You can still create, remove and modify indexes on columns, as well as add new columns. You can always remove, modify and create rows via DELETE, UPDATE and INSERT statements. If you need to complete one of the above operations on a table, the best method is to rename the old table, and create a new table.

You can copy data from one table to another using in the INSERT statement. Let's say you want to copy data from a table named oldTbl into newTbl. You can achieve this as follows:

INSERT INTO newTbl (col1, col2, col3)  
SELECT (col1, col2, col3) FROM oldTbl

This will copy the contents from oldTbl into newTbl.

Statement

I'm surprised that no database other than MySQL supports the LIMIT clause. It's not part of the SQL99 standard, but it's so convenient. Some of the functionality provided by the LIMIT clause can be duplicated in MS SQL using the TOP clause, and DB2 has a similar clause (although it is rather long winded). Unfortunately, like the TOP clause, DB2's LIMIT attempt can only be used on SELECT statements. Here's an example:

SELECT * FROM testtbl WHERE id > 10 FETCH ONLY FIRST 4 ROWS

I'm not exactly sure why this clause is so long. Possibly in the future it will have more functionality, but for now it is more interesting that useful.

Conclusion

I hope you've enjoyed your first encounter with DB2. Although best known for its size and obscure commercials, IBM's Standard Query Language (SQL) is an excellent database product of which you can take advantage in your PHP applications!

There are many areas in which the DB2 SQL Dialect differs from that of MySQL, but your initial frustrations should pay off in speed and performance benefits (and the joy of learning another industry standard application)! For more information, check out the official DB2 Documentation.