Access DB2 through “ibm_db2” extension

Sun, 15 May 2016 13:39:33 +0000

* Access to DB2 through “ibm_db2” extension is only supported in the commercial license.

phpGrid now supports IBM DB2 Universal Database through “ibm_db2” extension that uses native IBM DB2 functions. It’s faster and more efficient than using PDO_ODBC driver. Simply define “PHPGRID_DB_TYPE” value as “db2” in the conf.php file. You should use “*LOCAL” or database IP address as the “PHPGRID_DB_HOSTNAME”.

1
2
3
4
5
6

define('PHPGRID_DB_HOSTNAME','*LOCAL'); // database host name
define('PHPGRID_DB_USERNAME', ''); // database user name
define('PHPGRID_DB_PASSWORD', ''); // database password
define('PHPGRID_DB_NAME', 'SAMPLE'); // database name
define('PHPGRID_DB_TYPE', 'db2'); // database type
define('PHPGRID_DB_OPTIONS', serialize(array('i5_lib'=>'CLASSICMODELS', 'i5_naming' => DB2_I5_NAMING_ON)));

Notice the new “PHPGRID_DB_OPTIONS” constant, it defines the value for the last paramter “$options” in db2_connect function. In this constant, you can set values for the i5_* options.

Below is the phpGrid code sample:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

use phpCtrl\C_DataGrid;
require_once("/file/path/to/conf.php");

$dg = new C_DataGrid("SELECT * FROM STAFF", "ID", "STAFF");
$dg->set_col_title('NAME', 'Last Name')->set_col_readonly('NAME');
$dg->enable_search(true);
$dg->enable_export('PDF');
$dg->enable_edit('FORM');
$dg->set_col_required('DEPT');
//$dg->set_col_edittype('COUNTRY', 'select', ':;');
$dg->set_conditional_format("COMM","CELL",array(
"condition"=>"gt","value"=>1000,"css"=> array("color"=>"black","background-color"=>"lightgreen")));

$dg -> set_databar("SALARY","blue", 120000)->set_col_width('SALARY', '190px');
$dg -> display();

That’s all there is to it. Happy Gridding!

The post Access DB2 through “ibm_db2” extension appeared first on phpGrid - PHP Datagrid.

phpGrid Now Has Improved IBM DB2 Support!

Tue, 27 Oct 2015 09:26:54 +0000

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
19
20
21

use phpCtrl\C_DataGrid;
require_once("/file/path/to/conf.php");

$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:

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

The post phpGrid Now Has Improved IBM DB2 Support! appeared first on phpGrid - PHP Datagrid.

DB2 Archives | phpGrid - PHP Datagrid

Access DB2 through “ibm_db2” extension

phpGrid Now Has Improved IBM DB2 Support!

IBM i Developers, Rejoice.