DB2 Version 9.7 for Linux, UNIX, and Windows

Executing a single SQL statement in PHP (ibm_db2)

To prepare and execute a single SQL statement that accepts no input parameters, use the db2_exec function. A typical use of the db2_exec function is to set the default schema for your application in a common include file or base class.

Before you begin

To avoid the security threat of SQL injection attacks, use the db2_exec function only to execute SQL statements composed of static strings. Interpolation of PHP variables representing user input into the SQL statement can expose your application to SQL injection attacks.

Obtain a connection resource by calling one of the connection functions in the ibm_db2 API.

Procedure

To prepare and execute a single SQL statement, call the db2_exec function, passing the following arguments:

connection

A valid database connection resource returned from the db2_connect or db2_pconnect function.

statement

A string that contains the SQL statement. This string can include an XQuery expression that is called by the XMLQUERY function.

options

Optional: An associative array that specifies statement options:

DB2_ATTR_CASE: For compatibility with database systems that do not follow the SQL standard, this option sets the case in which column names will be returned to the application. By default, the case is set to DB2_CASE_NATURAL, which returns column names as they are returned by the database. You can set this parameter to DB2_CASE_LOWER to force column names to lower case, or to DB2_CASE_UPPER to force column names to upper case.
DB2_ATTR_CURSOR: This option sets the type of cursor that ibm_db2 returns for result sets. By default, ibm_db2 returns a forward-only cursor (DB2_FORWARD_ONLY) which returns the next row in a result set for every call to db2_fetch_array, db2_fetch_assoc, db2_fetch_both, db2_fetch_object, or db2_fetch_row. You can set this parameter to DB2_SCROLLABLE to request a scrollable cursor so that the ibm_db2 fetch functions accept a second argument specifying the absolute position of the row that you want to access within the result set.

If the function call succeeds, it returns a statement resource that you can use in subsequent function calls related to this query.

If the function call fails (returns False), you can use the db2_stmt_error or db2_stmt_errormsg function to retrieve diagnostic information about the error.

For more information about the ibm_db2 API, see http://www.php.net/docs.php.

Example

Example 1: Executing a single SQL statement.

<?php
$conn = db2_connect("sample", "db2inst1", "");
$sql = "SELECT * FROM DEPT";
$stmt = db2_exec($conn, $sql); 
db2_close($conn);
?>

Example 2: Executing an XQuery expression

<?php
$xquery = '$doc/customerinfo/phone';
$stmt = db2_exec($conn, "select xmlquery('$xquery' 
PASSING INFO AS \"doc\") from customer");?>

What to do next

If the SQL statement selected rows using a scrollable cursor, or inserted, updated, or deleted rows, you can call the db2_num_rows function to return the number of rows that the statement returned or affected. If the SQL statement returned a result set, you can begin fetching rows.