db2_execute

Executes a prepared SQL statement (PECL ibm_db2:1.0-1.6.2)
bool db2_execute ( resource stmt [, array parameters] )

Example 930. 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().

copy to clipboard

<?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.";

    }

}
?>

The above example will output:

copy to clipboard

Successfully added new pet.

Example 931. 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.

copy to clipboard

<?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:

copy to clipboard

I have 7 pets!

Example 932. Returning XML data as a 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 a SQL ResultSet format that most users are familiar with.

copy to clipboard

<?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);

?>

The above example will output:

copy to clipboard

1000     Kathy Smith     416-555-1358

1001     Kathy Smith     905-555-7258

Example 933. 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 a SQL ResultSet with information regarding shipping status for the customer.

copy to clipboard

<?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);

?>

The above example will output:

copy to clipboard

1001 Kathy Smith 905-555-7258 5002 Shipped

Example 934. 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).

copy to clipboard

<?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);

?>

The above example will output:

copy to clipboard

<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>

Change Language

db2_autocommit
db2_bind_param
db2_client_info
db2_close
db2_column_privileges
db2_columns
db2_commit
db2_conn_error
db2_conn_errormsg
db2_connect
db2_cursor_type
db2_escape_string
db2_exec
db2_execute
db2_fetch_array
db2_fetch_assoc
db2_fetch_both
db2_fetch_object
db2_fetch_row
db2_field_display_size
db2_field_name
db2_field_num
db2_field_precision
db2_field_scale
db2_field_type
db2_field_width
db2_foreign_keys
db2_free_result
db2_free_stmt
db2_get_option
db2_lob_read
db2_next_result
db2_num_fields
db2_num_rows
db2_pconnect
db2_prepare
db2_primary_keys
db2_procedure_columns
db2_procedures
db2_result
db2_rollback
db2_server_info
db2_set_option
db2_special_columns
db2_statistics
db2_stmt_error
db2_stmt_errormsg
db2_table_privileges
db2_tables

db2_execute

Executes a prepared SQL statement (PECL ibm_db2:1.0-1.6.2) bool db2_execute ( resource stmt [, array parameters] )

Example 930. Preparing and executing an SQL statement with parameter markers

Example 931. Calling a stored procedure with an OUT parameter

Example 932. Returning XML data as a SQL ResultSet

Example 933. Performing a "JOIN" with XML data

Example 934. Returning SQL data as part of a larger XML document

Change Language

Executes a prepared SQL statement (PECL ibm_db2:1.0-1.6.2)
bool db2_execute ( resource stmt [, array parameters] )