db2_execute - Executes a prepared SQL statement
Вернуться к: IBM DB2 Функции
db2_execute
(PECL ibm_db2 >= 1.0.0)
db2_execute — Executes a prepared SQL statement
Описание
$stmt
[, array $parameters
] )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 resource 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 advantages of using db2_prepare() and db2_execute() rather than db2_exec().
Список параметров
-
stmt -
A prepared statement returned from db2_prepare().
-
parameters -
An array of input parameters matching any parameter markers contained in the prepared statement.
Возвращаемые значения
Возвращает TRUE в случае успешного завершения или FALSE в случае возникновения ошибки.
Примеры
Пример #1 Preparing and executing an SQL statement with parameter markers
The following example prepares an INSERT statement that accepts four parameter markers, then iterates over an array of arrays containing the input values to be passed to db2_execute().
<?php
$pet = array(0, 'cat', 'Pook', 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.";
}
}
?>
Результат выполнения данного примера:
Successfully added new pet.
Пример #2 Calling a stored procedure with an OUT parameter
The following example prepares a CALL statement that accepts one parameter marker 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 changes 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!";
?>
Результат выполнения данного примера:
I have 7 pets!
Пример #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 = ?
';
$stmt = db2_prepare($conn, $query);
$name = 'Kathy 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);
?>
Результат выполнения данного примера:
1000 Kathy Smith 416-555-1358 1001 Kathy Smith 905-555-7258
Пример #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 = ?
';
$stmt = db2_prepare($conn, $query);
$name = 'Kathy 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);
?>
Результат выполнения данного примера:
1001 Kathy Smith 905-555-7258 5002 Shipped
Пример #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";
<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);
?>
Результат выполнения данного примера:
<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 kg</weight>
</description>
</product>
<startdate>2004-11-19</startdate>
<enddate>2004-12-19</enddate>
<promoprice>7.25</promoprice>
</promoitem>
</promoList>
Смотрите также
- db2_exec() - Executes an SQL statement directly
- db2_fetch_array() - Returns an array, indexed by column position, representing a row in a result set
- db2_fetch_assoc() - Returns an array, indexed by column name, representing a row in a result set
- db2_fetch_both() - Returns an array, indexed by both column name and position, representing a row in a result set
- db2_fetch_row() - Sets the result set pointer to the next row or requested row
- db2_prepare() - Prepares an SQL statement to be executed
- db2_result() - Returns a single column from a row in the result set
Вернуться к: IBM DB2 Функции