phpGrid Now Has Improved IBM DB2 Support!

phpgrid db2
THIS TUTORIAL IS OUTDATED.

“ibm_db2” is now the recommended extension to access the DB2 database. You should only use “PDO_ODBC” driver to access DB2 as a fallback when “ibm_db2” extension fails to work. To access DB2 through “ibm_db2” extension, please refer to phpGrid DB2 with “ibm_db2” extension tutorial.

DB2 supports database access for client applications written in the PHP programming language using either or both of the “”ibm_db2” extension and the “pdo_ibm” driver for the PHP Data Objects (PDO) extension. This tutorial uses the unixODBC as the PDO_ODBC driver to connect to DB2. To access DB2 through “ibm_db2” extension, please refer to phpGrid “ibm_db2” tutorial.

After months of hard work, phpGrid now finally has the PDO_ODBC DB2 database support! phpGrid’s DB2 support has been spotty in the past, owing to the fact that the ADOBdb data access library uses non-supported legacy IBM DB2 driver. A new PDO data access class has been implemented specifically for DB2.*

We worked uber-hard to ensure that the existing phpGrid API stayed the same. The only changes we made were essential, and they are transparent to our users. To use the new database, simply type “pdo_odbc_db2” as the “PHPGRID_DB_TYPE“” value in “conf.php”, and everything else stays the same.

 

IBM i Developers, Rejoice.

 

A large number of IBM i developers have been working with DB2, the IBM’s Relational Database Management System (RDBMS). phpGrid can be used as a data management tool in an IBM i environment because the PHP runtime module is already preloaded with IBM i. This allows for a super-charged datagrid with built-in CRUD capability to be up and running very quickly with minimal knowledge of the ins and outs of PHP.

The DB2 was fully tested in IBM DB2 Express-C. It requires the “PDO_ODBC(unixODBC) extension“. As of PHP 5.1, PDO_ODBC is included in the PHP source. You should verify it in phpinfo.

Both the web server and DB2 must be on the same server. If you have trouble installing unixODBC, I suggest you install the Zend Server (free) and install PDO_ODBC (unixODBC) extension extension quickly and easily through Zend Server’s wonderful admin dashboard. Finally, set up the DSN in the odbc.ini and odbcinst.ini configuration files.

odbc.ini

1
2
3
[sample]
Description = Test to DB2
Driver      = DB2

odbcinst.ini

1
2
3
4
5
6
[db2]
Description = DB2 Driver
Driver      = /opt/ibm/db2/V10.5/lib32/libdb2.so
Driver64    = /opt/ibm/db2/V10.5/lib64/libdb2.so
FileUsage   = 1
DontDLClose = 1

*Note that DB2 support is only available with the Enterprise license. Here’s a sample of conf.php in phpGrid. Notice the “putenv” environment variable settings.

conf.php

1
2
3
4
5
6
7
8
9
10
define('PHPGRID_DB_HOSTNAME', 'localhost'); // database host name
define('PHPGRID_DB_PORT', '50000'); // database host name
define('PHPGRID_DB_USERNAME', 'db2inst1');     // database user name
define('PHPGRID_DB_PASSWORD', 'xxxxxxxx'); // database password
define('PHPGRID_DB_NAME', 'SAMPLE'); // database name or DSN name (cataloged)
define('PHPGRID_DB_TYPE', 'pdo_odbc_db2');  // database type
define('PHPGRID_DB_CHARSET','utf8'); // ex: utf8(for mysql),AL32UTF8 (for oracle), leave blank to use the default charset

putenv('ODBCSYSINI=/etc');
putenv('ODBCINI=/etc/odbc.ini');

Finally, the sample code using the DB2 db2sampl database:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$dg = new C_DataGrid("SELECT * FROM EMPLOYEE", "EMPNO", "EMPLOYEE");
$dg->set_col_title('EMPNO', 'Employee #');
$dg->set_col_title('PHONENO', 'Phone Number');
$dg->set_col_width('SEX', 50)->set_col_align('SEX', 'center');
$dg->set_col_width('MIDINIT', 30)->set_col_align('MIDINIT', 'center');
$dg->enable_search(true);
$dg->enable_edit('FORM');
$dg->enable_export('CSV');
$dg->enable_autowidth(true);
$dg->set_col_edittype('WORKDEPT', 'select', 'select DEPTNO, DEPTNAME from DEPARTMENT');
$dg->set_col_edittype('SEX', 'select', 'M:M;F:F');
$dg->set_conditional_format("SEX","CELL",array(
    "condition"=>"eq","value"=>"F","css"=> array("color"=>"black","background-color"=>"#FDA6B2")));
$dg->set_conditional_format("SEX","CELL",array(
    "condition"=>"eq","value"=>"M","css"=> array("color"=>"black","background-color"=>"#A6D7FC")));
$dg->set_conditional_format("SALARY","CELL",array(
    "condition"=>"gt","value"=>75000,"css"=> array("color"=>"black","background-color"=>"lightgreen")));
$dg -> display();

Sample datagrid output:
 

phpgrid-db2sampl-employee

 

To use PHP “ibm_db2” extension, please see the phpGrid “ibm_db2” tutorial.