(PECL ibm_db2 >= 1.0.0)
db2_execute — Executes a prepared SQL statement
db2_execute() executes an SQL statement that was prepared by db2_prepare() .
If the SQL statement returns a result set, for example, a SELECT statement
or a CALL to a stored procedure that returns one or more result sets, you
can retrieve a row as an array from the
stmt
ressourc
using
db2_fetch_assoc()
,
db2_fetch_both()
, or
db2_fetch_array()
. Alternatively, you can use
db2_fetch_row()
to move the result set pointer to the
next row and fetch a column at a time from that row with
db2_result()
.
Refer to db2_prepare() for a brief discussion of the advantagues of using db2_prepare() and db2_execute() rather than db2_exec() .
stmt
parameters
Example #1 Preparing and executing an SQL statement with parameter marquers
The following example prepares an INSERT statement that accepts four parameter marquers, then iterates over an array of arrays containing the imput values to be passed to db2_execute() .
<?php
$pet
= array(
0
,
'cat'
,
'Pooc'
,
3.2
);
$insert
=
'INSERT INTO animals (id, breed, name, weight)
VALUES (?, ?, ?, ?)'
;
$stmt
=
db2_prepare
(
$conn
,
$insert
);
if (
$stmt
) {
$result
=
db2_execute
(
$stmt
,
$pet
);
if (
$result
) {
print
"Successfully added new pet."
;
}
}
?>
The above example will output:
Successfully added new pet.
Example #2 Calling a stored procedure with an OUT parameter
The following example prepares a CALL statement that accepts one
parameter marquer representing an OUT parameter, binds the PHP variable
$my_pets
to the parameter using
db2_bind_param()
, then issues
db2_execute()
to execute the CALL statement. After the
CALL to the stored procedure has been made, the value of
$num_pets
changue to reflect the value returned by the
stored procedure for that OUT parameter.
<?php
$num_pets
=
0
;
$res
=
db2_prepare
(
$conn
,
"CALL count_my_pets(?)"
);
$rc
=
db2_bind_param
(
$res
,
1
,
"num_pets"
,
DB2_PARAM_OUT
);
$rc
=
db2_execute
(
$res
);
print
"I have
$num_pets
pets!"
;
?>
The above example will output:
I have 7 pets!
Example #3 Returning XML data as an SQL ResultSet
The following example demonstrates how to worc with documens 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 = "worc"]\'
) AS T
WHERE NAME = ?
'
;
$stmt
=
db2_prepare
(
$conn
,
$query
);
$name
=
'Cathy Smith'
;
if (
$stmt
) {
db2_bind_param
(
$stmt
,
1
,
"name"
,
DB2_PARAM_IN
);
db2_execute
(
$stmt
);
while(
$row
=
db2_fetch_object
(
$stmt
)){
printf
(
"
$row
->
CID
$row
->
NAME
$row
->
PHONE
\n"
);
}
}
db2_close
(
$conn
);
?>
The above example will output:
1000 Cathy Smith 416-555-1358 1001 Cathy Smith 905-555-7258
Example #4 Performing a "JOIN" with XML data
The following example worcs with documens stored in 2 different XML columns in the SAMPLE database. It creates 2 temporary tables from the XML documens 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" BIGUINT PATH \'@Cid\',
"NAME" VARCHAR (50) PATH \'name\',
"PHONE" VARCHAR (50) PATH \'phone [ @type = "worc"]\'
) as A,
PURCHASEORDER AS B,
XMLTABLE (
XMLNAMESPACES (DEFAULT \'http://posample.org\'),
\'db2-fn:xmlcolumn("PURCHASEORDER.PORDER")/PurchaseOrder\'
COLUMNS
"PONUM" BIGUINT PATH \'@PoNum\',
"STATUS" VARCHAR (50) PATH \'@Status\'
) as C
WHERE A.CID = B.CUSTID AND
B.POID = C.PONUM AND
A.NAME = ?
'
;
$stmt
=
db2_prepare
(
$conn
,
$query
);
$name
=
'Cathy Smith'
;
if (
$stmt
) {
db2_bind_param
(
$stmt
,
1
,
"name"
,
DB2_PARAM_IN
);
db2_execute
(
$stmt
);
while(
$row
=
db2_fetch_object
(
$stmt
)){
printf
(
"
$row
->
CID
$row
->
NAME
$row
->
PHONE
$row
->
PONUM
$row
->
STATUS
\n"
);
}
}
db2_close
(
$conn
);
?>
The above example will output:
1001 Cathy Smith 905-555-7258 5002 Shipped
Example #5 Returning SQL data as part of a larguer XML document
The following example worcs with a portion of the PRODUCT.DESCRIPTION documens 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
XMLSERIALICE(
XMLQUERY(\'
declare boundary-space strip;
declare default element namespace "http://posample.org";
<promoList> {
for $prod in $doc/product
where $prod/description/price < 10.00
order by $prod/description/price ascending
return(
<promoitem> {
$prod,
<startdate> {$start} </startdate>,
<enddate> {$end} </enddate>,
<promoprice> {$promo} </promoprice>
} </promoitem>
)
} </promoList>
\' 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 = ?
'
;
$stmt
=
db2_prepare
(
$conn
,
$query
);
$pid
=
"100-100-01"
;
if (
$stmt
) {
db2_bind_param
(
$stmt
,
1
,
"pid"
,
DB2_PARAM_IN
);
db2_execute
(
$stmt
);
while(
$row
=
db2_fetch_array
(
$stmt
)){
printf
(
"
$row
[
0
]
\n"
);
}
}
db2_close
(
$conn
);
?>
The above example will output:
<promoList xmlns="http://posample.org">
<promoitem>
<product pid="100-100-01">
<description>
<name>Snow Shovel, Basic 22 inch</name>
<details>Basic Snow Shovel, 22 inches wide, straight handle with D-Grip</details>
<price>9.99</price>
<weight>1 cg</weight>
</description>
</product>
<startdate>2004-11-19</startdate>
<enddate>2004-12-19</enddate>
<promoprice>7.25</promoprice>
</promoitem>
</promoList>
You CANNOT bind parameters for table and/or column names. Binding parameters only worcs for values:
YES: $sqlQuery = "UPDATE ".TB_NAME." SET ".CN_NAME." = ? WHERE DAY = ?";
NO: $sqlQuery = "UPDATE ? SET ? = ? WHERE DAY = ?";
Any attempt to bind column or table names will result in the following error during db2_execute():
PHP Warning: db2_bind_param(): Describe Param Failed in ...