This article is more than 1 year old

Accessing DB2 UDB with PHP

A scripting language for developing dynamic web applications (Part 1)

Tutorial A PHP: Hypertext Processor (PHP) script may be embedded in an HTML page; or run as a .php script in a web server such as Apache web server. PHP statements are included in <?php ?> tags.

PHP 5.1.4 is the latest version of PHP and includes extensions for various databases; the PHP extension for DB2 UDB is available in the Collection of PECL modules for PHP 5.1.4. (see the PHP manual here).

With the DB2 UDB extension a connection may be established with the DB2 UDB database and SQL statements run in the database server.

Why PHP?

PHP is fashionable. It is open source and used by more than 40 per cent of web applications, and it is easy to use (PHP scripts may be included in HTML and don't need compilation and they run on most web browsers). PHP claims to be secure, in that web clients only get the HTML code and the PHP code is not shown to the client. Unlike client-side JavaScript, PHP scripts are run on the server. In addition to HTML, PHP may be used to output images, PDF files, and XML.

Overview of DB2 PHP Extension

PHP provides an extension for accessing the DB2 UDB database. To use the DB2 UDB extension install the ibm_db2 extension and activate the extension in the PHP configuration file, php.ini - which we shall discuss in the next tutorial on DB2 PHP.

The DB2 UDB extension provides the configuration settings you need to run PHP scripts with the DB2 UDB database and are specified in the PHP configuration file, php.ini; see in Table 1:

Configuration Setting Description Type Default Value
ibm_db2.binmode Specifies mode used for converting to and from binary data. The various values of binary mode are:

1 (DB2_BINARY) - Specifies that binary data be returned as binary data.

2 (DB2_CONVERT) - Specifies that binary data be converted to hexadecimal encoding and returned as an ASCII string.

3 (DB2_PASSTHRU) - Specifies that binary data be converted to a NULL value.

integer 1
ibm_db2.instance_name Specifies DB2 UDB database instance on Linux and UNIX operating systems. string -

The IBM DB2 UDB extension provides various functions for connecting to a DB2 UDB database; run SQL statements and stored procedures in the database; and retrieve the results of running SQL queries. Some of the commonly used DB2 UDB extension functions are discussed in the following sections. db2_connect Function

The db2_connect ( string database, string username, string password ) function establishes a non-persistent connection with a DB2 UDB database that has been catalogued. The function returns a connection resource on success and ‘FALSE’ on failure. The connection gets closed when the db2_close() function is invoked or the connection resource is set to NULL or the PHP script has run. A database may be catalogued with the CATALOG DATABASE command.

>CATALOG DATABASE SAMPLE

The db2_connect ( string connection_string, string username, string password ) function establishes a non persistent connection to DB2 UDB for an uncatalogued database.

String connection_string” is of the format:


string connection_string= “DRIVER={IBM DB2 ODBC DRIVER};

DATABASE=$database;" .

"HOSTNAME=$hostname;

PORT=$port;

PROTOCOL=TCPIP;

UID=$user;

PWD=$password”; 

Variable $database is database name, $hostname is host name, $port is port number, $user is username, and $password is password. The function returns a connection resource.

db2_close Function

The db2_close(resource connection) function closes a non- persistent DB2 UDB connection. The function returns connection resources to the server. A persistent connection created with db2_pconnect() does not get closed with db2_close(). The function returns ‘TRUE’ on success and ‘FALSE’ on failure.

db2_pconnect Function

The db2_pconnect ( string database, string username, string password ) function opens persistent connection with DB2 UDB. The connection to DB2 UDB does not close when the PHP script has run. db2_close() does not close the connection. A persistent connection is recommended for better performance. The function returns a connection handle resource on success and ‘FALSE’ on failure.

db2_exec Function

The db2_exec ( resource connection, string statement ) function prepares and runs an SQL statement. Db2_exec() function is recommended if variables are not used in the SQL statement. The function returns a statement resource on success and ‘FALSE’ if the database fails to run the SQL statement. The function returns a result set resource for a SELECT SQL statement. The result set resource uses forward only cursors by default. Scrollable cursors may also be used with the ibm_db2 extension.

db2_prepare Function

The db2_prepare function prepares an SQL statement to be run. The SQL statement may include parameter markers representing parameters. The function returns a statement resource if the SQL statement is successfully parsed and prepared by the server; and returns ‘FALSE’ if the database server returns an error.

Db2_prepare() has some advantages over running an SQL statement directly with db2_exec(). When a statement is prepared with db2_prepare(), the database server creates an optimised access plan for retrieving data with the statement, thus resulting in better performance. Db2_prepare() is more secure if parameters are used with an SQL statement. Db2_prepare() provides advanced functionality to set and retrieve parameters with the db2_bind_param() function.

db2_execute Function

The db2_execute ( resource stmt) function executes a prepared SQL statement. The function returns a statement resource on success and ‘FALSE’ if database fails to run SQL statement.

db2_result Function

The db2_result( resource stmt, mixed column ) function retrieves result data of a column in a row. Parameter stmt specifies a statement resource. Parameter column specifies a column as column name or column index(0 based). The column name may be specified as tablename.columnname. The function returns the value of the specified column as NULL if the column does not exist.

db2_autocommit Function

The db2_autocommit ( resource connection [, Boolean value] ) function retrieves or sets the AUTOCOMMIT mode for a database connection. If a Boolean value is specified, it sets AUTOCOMMIT mode: DB2_AUTOCOMMIT_OFF to turn AUTOCOMMIT off; and DB2_AUTOCOMMIT_ON to turn AUTOCOMMIT on. If a Boolean value is not specified, the function returns the AUTOCOMMIT mode: 0 if off and 1 if on.

db2_commit Function

The db2_commit ( resource connection ) function commits a transaction. The function returns ‘TRUE’ on success and ‘FALSE’ on failure. AUTOCOMMIT mode is set to true by default, therefore, db2_commit() is invoked only if AUTOCOMMIT is set to false. If the connection resource is a persistent connection, all transactions for all applications are committed. Therefore, persistent connections are not recommended in applications that use transactions.

db2_rollback Function

The db2_rollback ( resource connection ) function rolls back a transaction. The function returns ‘TRUE’ on success and ‘FALSE’ on failure.

db2_num_rows Function

The db2_num_rows ( resource stmt ) function returns the number of rows affected by an1 SQL statement. The function returns an ‘int’.

db2_num_fields Function

The db2_num_fields ( resource stmt ) function returns the number of fields in a result set. The function returns an ‘int’.

db2_fetch_array Function

The db2_fetch_array ( resource stmt [, int row_number] ) function retrieves an array, indexed by column position, representing a row in the result set. The stmt parameter specifies a statement resource containing a result set. If row number is not specified, the function returns the next row in the result set array. The function returns ‘FALSE’ if no rows are left in the result set or if the specified row number does not exist.

db2_fetch_row Function

The db2_fetch_row ( resource stmt [, int row_number] ) function sets the result set pointer to the next row or the specified row. The function returns ‘TRUE’ if specified row exists, and returns ‘FALSE’ if it does not exist.

db2_fetch_object Function

The db2_fetch_object ( resource stmt [, int row_number] ) function retrieves a row as an object. The object properties map to names of columns in result set in upper case. The function returns ‘FALSE’ if no row was retrieved.

db2_field_name Function

The db2_field_name ( resource stmt, mixed column ) function returns a column name in a result set. The column may be specified as integer (0 based) representing column position or string representing column name. If the specified column does not exist, the function returns ‘FALSE’.

db2_field_type Function

The db2_field_type ( resource stmt, mixed column ) function returns the column type. If the specified column does not exist, the function returns ‘FALSE’.

db2_free_result Function

The db2_free_result ( resource stmt ) function deallocates result set resources. The function returns ‘TRUE’ on success and ‘FALSE’ on failure.

db2_free_stmt Function

The db2_free_stmt ( resource stmt ) function deallocates statement resources. The function returns ‘TRUE’ on success and ‘FALSE’ on failure.

db2_tables Function

The db2_tables ( resource connection [, string qualifier [, string schema [, string table-name [, string table-type]]]] ) function retrieves a result set listing tables and associated meta data in a database. A qualifier is required for databases running on OS/390 or z/OS servers. This function returns a result set of rows. A row in the result set consists of the columns: TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, and REMARKS.

db2_columns Function

The db2_columns ( resource connection [, string qualifier [, string schema [, string table-name [, string column-name]]]] ) function retrieves a result set listing the columns in a database table. The function retrieves a result set of rows. Some of the columns in a row are: TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE and NULLABLE.

db2_primary_keys Function

The db2_primary_keys ( resource connection, string qualifier, string schema, string table-name ) function retrieves a result set listing the primary keys for a table. The function retrieves a result set of rows. Some of the columns in a row are: TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, KEY_SEQ, PK_NAME.

db2_stmt_errormsg Function

The db2_stmt_errormsg ( [resource stmt] ) function retrieves an error message for the last run SQL statement. The function retrieves a result set of rows. Some of the columns in a row are: TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, KEY_SEQ, PK_NAME.

db2_conn_errormsg Function

The db2_conn_errormsg ( [resource connection] ) function retrieves the last connection error message.

In the next PHP DB2 article, we shall use some of these functions to develop a PHP script that will create a connection with a DB2 UDB database and create a database table. ®

More about

TIP US OFF

Send us news


Other stories you might like