PHP: db2_exec - Manual (original) (raw)

(PECL ibm_db2 >= 1.0.0)

db2_exec — Executes an SQL statement directly

Description

If you plan to interpolate PHP variables into the SQL statement, understand that this is one of the more common security exposures. Consider callingdb2_prepare() to prepare an SQL statement with parameter markers for input values. Then you can call db2_execute() to pass in the input values and avoid SQL injection attacks.

If you plan to repeatedly issue the same SQL statement with different parameters, consider calling db2_prepare() anddb2_execute() to enable the database server to reuse its access plan and increase the efficiency of your database access.

Parameters

connection

A valid database connection resource variable as returned fromdb2_connect() or db2_pconnect().

statement

An SQL statement. The statement cannot contain any parameter markers.

options

An associative array containing statement options. You can use this parameter to request a scrollable cursor on database servers that support this functionality.

For a description of valid statement options, see db2_set_option().

Return Values

Returns a statement resource if the SQL statement was issued successfully, or [false](reserved.constants.php#constant.false) if the database failed to execute the SQL statement.

Examples

Example #1 Creating a table with db2_exec()

The following example uses db2_exec() to issue a set of DDL statements in the process of creating a table.

`<?php conn=db2connect(conn = db2_connect(conn=db2connect(database, user,user, user,password);// Create the test table
$create = 'CREATE TABLE animals (id INTEGER, breed VARCHAR(32),
name CHAR(16), weight DECIMAL(7,2))'; result=db2exec(result = db2_exec(result=db2exec(conn, $create);
if ($result) {
print "Successfully created the table.\n";
}// Populate the test table
$animals = array(
array(0, 'cat', 'Pook', 3.2),
array(1, 'dog', 'Peaches', 12.3),
array(2, 'horse', 'Smarty', 350.0),
array(3, 'gold fish', 'Bubbles', 0.1),
array(4, 'budgerigar', 'Gizmo', 0.2),
array(5, 'goat', 'Rickety Ride', 9.7),
array(6, 'llama', 'Sweater', 150)
);

foreach ( animalsasanimals as animalsasanimal) { rc=db2exec(rc = db2_exec(rc=db2exec(conn, "INSERT INTO animals (id, breed, name, weight)
VALUES ({$animal[0]}, '{$animal[1]}', '{$animal[2]}', {$animal[3]})");
if ($rc) {
print "Insert... ";
}
}
?>`

The above example will output:

Successfully created the table. Insert... Insert... Insert... Insert... Insert... Insert... Insert...

Example #2 Executing a SELECT statement with a scrollable cursor

The following example demonstrates how to request a scrollable cursor for an SQL statement issued by db2_exec().

<?php <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>c</mi><mi>o</mi><mi>n</mi><mi>n</mi><mo>=</mo><mi>d</mi><mi>b</mi><msub><mn>2</mn><mi>c</mi></msub><mi>o</mi><mi>n</mi><mi>n</mi><mi>e</mi><mi>c</mi><mi>t</mi><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">conn = db2_connect(</annotation></semantics></math>conn=db2connect(database, <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>u</mi><mi>s</mi><mi>e</mi><mi>r</mi><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">user, </annotation></semantics></math>user,password); $sql = "SELECT name FROM animals WHERE weight < 10.0 ORDER BY name"; if ($conn) { require_once 'prepare.inc'; <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>s</mi><mi>t</mi><mi>m</mi><mi>t</mi><mo>=</mo><mi>d</mi><mi>b</mi><msub><mn>2</mn><mi>e</mi></msub><mi>x</mi><mi>e</mi><mi>c</mi><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">stmt = db2_exec(</annotation></semantics></math>stmt=db2exec(conn, $sql, array('cursor' => DB2_SCROLLABLE)); while ($row = db2_fetch_array($stmt)) { print "$row[0]\n"; } } ?>

The above example will output:

Bubbles Gizmo Pook Rickety Ride

Example #3 Returning XML data as an SQL ResultSet

The following example demonstrates how to work with documents stored in a XML column using the SAMPLE database. Using some pretty simple SQL/XML, this example returns some of the nodes in a XML document in an SQL ResultSet format that most users are familiar with.

`<?php

$conn

= db2_connect("SAMPLE", "db2inst1", "ibmdb2");$query = 'SELECT * FROM XMLTABLE(
XMLNAMESPACES (DEFAULT 'http://posample.org\'),
'db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo'
COLUMNS
"CID" VARCHAR (50) PATH '@Cid',
"NAME" VARCHAR (50) PATH 'name',
"PHONE" VARCHAR (50) PATH 'phone [ @type = "work"]'
) AS T
WHERE NAME = 'Kathy Smith'
'; stmt=db2exec(stmt = db2_exec(stmt=db2exec(conn, $query);

while( row=db2fetchobject(row = db2_fetch_object(row=db2fetchobject(stmt)){
printf("$row->CID row−>NAMErow->NAME row−>NAMErow->PHONE\n");
}
db2_close($conn);?>`

The above example will output:

1000 Kathy Smith 416-555-1358 1001 Kathy Smith 905-555-7258

Example #4 Performing a "JOIN" with XML data

The following example works with documents stored in 2 different XML columns in the SAMPLE database. It creates 2 temporary tables from the XML documents from 2 different columns and returns an SQL ResultSet with information regarding shipping status for the customer.

`<?php

$conn

= db2_connect("SAMPLE", "db2inst1", "ibmdb2");$query = '
SELECT A.CID, A.NAME, A.PHONE, C.PONUM, C.STATUS
FROM
XMLTABLE(
XMLNAMESPACES (DEFAULT 'http://posample.org\'),
'db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo'
COLUMNS
"CID" BIGINT PATH '@Cid',
"NAME" VARCHAR (50) PATH 'name',
"PHONE" VARCHAR (50) PATH 'phone [ @type = "work"]'
) as A,
PURCHASEORDER AS B,
XMLTABLE (
XMLNAMESPACES (DEFAULT 'http://posample.org\'),
'db2-fn:xmlcolumn("PURCHASEORDER.PORDER")/PurchaseOrder'
COLUMNS
"PONUM" BIGINT PATH '@PoNum',
"STATUS" VARCHAR (50) PATH '@Status'
) as C
WHERE A.CID = B.CUSTID AND
B.POID = C.PONUM AND
A.NAME = 'Kathy Smith'
';$stmt = db2_exec($conn, $query);

while( row=db2fetchobject(row = db2_fetch_object(row=db2fetchobject(stmt)){
printf("$row->CID row−>NAMErow->NAME row−>NAMErow->PHONE row−>PONUMrow->PONUM row−>PONUMrow->STATUS\n");
}db2_close($conn);?>`

The above example will output:

1001 Kathy Smith 905-555-7258 5002 Shipped

Example #5 Returning SQL data as part of a larger XML document

The following example works with a portion of the PRODUCT.DESCRIPTION documents in the SAMPLE database. It creates a XML document containing product description (XML data) and pricing info (SQL data).

`<?php

$conn

= db2_connect("SAMPLE", "db2inst1", "ibmdb2");$query = '
SELECT
XMLSERIALIZE(
XMLQUERY('
declare boundary-space strip;
declare default element namespace "http://posample.org";
{
for prodinprod in prodindoc/product
where $prod/description/price < 10.00
order by $prod/description/price ascending
return(
{
$prod,
{$start} ,
{$end} ,
{$promo}
}
)
}
' passing by ref DESCRIPTION AS "doc",
PROMOSTART as "start",
PROMOEND as "end",
PROMOPRICE as "promo"
RETURNING SEQUENCE)
AS CLOB (32000))
AS NEW_PRODUCT_INFO
FROM PRODUCT
WHERE PID = '100-100-01'
';$stmt = db2_exec($conn, $query);

while( row=db2fetcharray(row = db2_fetch_array(row=db2fetcharray(stmt)){
printf("$row[0]\n");
}
db2_close($conn);?>`

The above example will output:

Snow Shovel, Basic 22 inch

Basic Snow Shovel, 22 inches wide, straight handle with D-Grip

9.99 1 kg 2004-11-19 2004-12-19 7.25

Found A Problem?

shawn at frozen-o dot com ¶

19 years ago

If you need to "emulate" offset/limit (as PEAR::DB puts it) for db2 queries, you will definitely need to add array('cursor' => DB2_SCROLLABLE) to your db2_exec() call. Otherwise, you will get nothing useful from db2_fetch_{whatever}() when you try to (see following hack for example):

<?php
$limit = 10;
$offset = 20;

for ($i = 0; <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>i</mi><mo>&lt;</mo></mrow><annotation encoding="application/x-tex">i &lt; </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6986em;vertical-align:-0.0391em;"></span><span class="mord mathnormal">i</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">&lt;</span></span></span></span>limit && <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>r</mi><mi>o</mi><mi>w</mi><mo>=</mo><mi>d</mi><mi>b</mi><msub><mn>2</mn><mi>f</mi></msub><mi>e</mi><mi>t</mi><mi>c</mi><msub><mi>h</mi><mi>a</mi></msub><mi>r</mi><mi>r</mi><mi>a</mi><mi>y</mi><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">row = db2_fetch_array(</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.4306em;"></span><span class="mord mathnormal">ro</span><span class="mord mathnormal" style="margin-right:0.02691em;">w</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1.0361em;vertical-align:-0.2861em;"></span><span class="mord mathnormal">d</span><span class="mord mathnormal">b</span><span class="mord"><span class="mord">2</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3361em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.10764em;">f</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.2861em;"><span></span></span></span></span></span></span><span class="mord mathnormal">e</span><span class="mord mathnormal">t</span><span class="mord mathnormal">c</span><span class="mord"><span class="mord mathnormal">h</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">a</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal" style="margin-right:0.02778em;">rr</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.03588em;">y</span><span class="mopen">(</span></span></span></span>result, <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>o</mi><mi>f</mi><mi>f</mi><mi>s</mi><mi>e</mi><mi>t</mi><mo>+</mo></mrow><annotation encoding="application/x-tex">offset + </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">o</span><span class="mord mathnormal" style="margin-right:0.10764em;">ff</span><span class="mord mathnormal">se</span><span class="mord mathnormal">t</span><span class="mord">+</span></span></span></span>i); $i++) {
    // stuff goes here
}
?>

You can accomplish the same time of thing using sub-selects, "with" statements and other things new to me in the world of DB2, but the more dynamically generated the queries, the more difficult it gets to implement limit/offset behavior on the fly.

PHP: db2_exec - Manual (original) (raw)

Description

Parameters

Return Values

Examples

See Also

Found A Problem?