PHP : Function Reference : Oracle Functions : oci_bind_by_name PHP Examples Tutorials References

Example 1633. oci_bind_by_name() example

<?php
/* oci_bind_by_name example thies at thieso dot net (980221)

  inserts 3 records into emp, and uses the ROWID for updating the

  records just after the insert.

*/

$conn = oci_connect("scott", "tiger");

$stmt = oci_parse($conn, "

                          INSERT INTO

                                     emp (empno, ename)

                               VALUES

                                     (:empno,:ename)

                            RETURNING

                                     ROWID

                                 INTO

                                     :rid

                         ");

$data = array(

              1111 => "Larry",

              2222 => "Bill",

              3333 => "Jim"

             );

$rowid = oci_new_descriptor($conn, OCI_D_ROWID);

oci_bind_by_name($stmt, ":empno", $empno, 32);
oci_bind_by_name($stmt, ":ename", $ename, 32);
oci_bind_by_name($stmt, ":rid",   $rowid, -1, OCI_B_ROWID);

$update = oci_parse($conn, "

                            UPDATE

                                  emp

                               SET

                                  sal = :sal

                             WHERE

                                  ROWID = :rid

                           ");
oci_bind_by_name($update, ":rid", $rowid, -1, OCI_B_ROWID);
oci_bind_by_name($update, ":sal", $sal,   32);

$sal = 10000;


foreach ($data as $empno => $ename) {

    oci_execute($stmt);

    oci_execute($update);

}

$rowid->free();

oci_free_statement($update);
oci_free_statement($stmt);

$stmt = oci_parse($conn, "

                          SELECT

                                *

                            FROM

                                emp

                           WHERE

                                empno

                              IN

                                (1111,2222,3333)

                         ");
oci_execute($stmt);


while ($row = oci_fetch_assoc($stmt)) {

    var_dump($row);

}

oci_free_statement($stmt);

/* delete our "junk" from the emp table.... */
$stmt = oci_parse($conn, "

                          DELETE FROM

                                     emp

                                WHERE

                                     empno

                                   IN

                                     (1111,2222,3333)

                         ");
oci_execute($stmt);
oci_free_statement($stmt);

oci_close($conn);
?>

Example 1634. oci_bind_by_name() example

copy to clipboard

<?php

    $connection = oci_connect('apelsin','kanistra');

    $query = "INSERT INTO test_table VALUES(:id, :text)";


    $statement = oci_parse($query);

    oci_bind_by_name($statement, ":id", 1);

    oci_bind_by_name($statement, ":text", "trailing spaces follow     ");

    oci_execute($statement);

    /*

     This code will insert into DB string 'trailing spaces follow', without

     trailing spaces

    */
?>

Example 1635. oci_bind_by_name() example

copy to clipboard

<?php

    $connection = oci_connect('apelsin','kanistra');

    $query = "INSERT INTO test_table VALUES(:id, 'trailing spaces follow      ')";


    $statement = oci_parse($query);

    oci_bind_by_name($statement, ":id", 1);

    oci_execute($statement);

    /*

     And this code will add 'trailing spaces follow      ', preserving

     trailing whitespaces

    */
?>

Examples ( Source code ) » oci_bind_by_name


<?php

        $c = oci_connect('hr', 'hrpwd', '//localhost/XE');

        $myblobid = 123;

        $myv = 'a very large amount of binary data';

        $lob = oci_new_descriptor($c, OCI_B_LOB);

        $s = oci_parse($c,

                'INSERT INTO mybtab (blobid, blobdata) '

                . 'VALUES(:myblobid, EMPTY_BLOB()) '

                . 'RETURNING blobdata INTO :blobdata');

        oci_bind_by_name($s, ':MYBLOBID', $myblobid);

        oci_bind_by_name($s, ':BLOBDATA', $lob, -1, OCI_B_BLOB);

        oci_execute($s, OCI_DEFAULT);

        $lob->save($myv);

        oci_commit($c);

?>

Related Examples ( Source code ) » oci_bind_by_name

Code Examples / Notes » oci_bind_by_name

This is what the old OCI_B_* constants are now called: (PHP 5.1.6 win32) OCI_B_NTY - SQLT_NTY OCI_B_BFILE - SQLT_BFILEE OCI_B_CFILEE - SQLT_CFILEE OCI_B_CLOB - SQLT_CLOB OCI_B_BLOB - SQLT_BLOB OCI_B_ROWID - SQLT_RDD OCI_B_CURSOR - SQLT_RSET OCI_B_BIN - SQLT_BIN OCI_B_INT - SQLT_INT OCI_B_NUM - SQLT_NUM

This is an example of returning the primary key from an insert so that you can do inserts on other tables with foreign keys based on that value. The date is just used to provied semi-unique data to be inserted. $conn = oci_connect("username", "password") $stmt = oci_parse($conn, "INSERT INTO test (test_msg) values (:data) RETURN test_id INTO :RV"); $data = date("d-M-Y H:i:s"); oci_bind_by_name($stmt, ":RV", $rv, -1, SQLT_INT); oci_bind_by_name($stmt, ":data", $data, 24); oci_execute($stmt); print $rv;

Referes to: Be careful that the variable argument is a reference. So, the following code does not work: foreach($some_array as $key => $value) { OCIBindByName($stmt, $key, $value); } I assume this is because the contents of $value changes, even though the reference remains the same, so all bound variables end up pointing to the last loop iteration's value. Instead use the following: foreach($some_array as $key => $value) { OCIBindByName($stmt, $key, $some_array[$key]); } This dues to the foreach statement. $some_array in foreach() is a copy of the origine array. $key would be the "reference" in the copy, but $some_array[$key] points to the original one.

Note that there have been some changes on the constant identifiers and the documentation is currently not entirely accurate. Running the following script; <?php foreach (array_keys(get_defined_constants()) as $const) { if ( preg_match('/^OCI_B_/', $const) ) { print "$const\n"; } } ?> Under PHP 4.4.0 I get; OCI_B_SQLT_NTY < renamed to OCI_B_NTY with PHP5 OCI_B_BFILE OCI_B_CFILEE OCI_B_CLOB OCI_B_BLOB OCI_B_ROWID OCI_B_CURSOR OCI_B_BIN Under PHP 5.0.4 I get; OCI_B_NTY OCI_B_BFILE < docs are wrong right now OCI_B_CFILEE < docs are wrong right now OCI_B_CLOB OCI_B_BLOB OCI_B_ROWID OCI_B_CURSOR OCI_B_BIN < it's a mystery

If you do a wrapper for these functions there is a bug I found on php5.1.1 / oracle 9 / windows xp. see the section with oci_bind_by_name. function db_layer_insert_1_row ($dbquery, $bindvar = array()) { global $_db_layer_database; global $_db_layer_lasterror; if (false === ($stid = oci_parse($_db_layer_database, $dbquery))) { db_layer_lasterror (); return -1; } // Bind variables. NOTE substituting $bindvar[$bcol] with $bval causes // all variables to be set to the last value of $bval?!?! foreach ($bindvar as $bcol => $bval) { oci_bind_by_name($stid, $bcol, $bindvar[$bcol]); //$bval); <- bug?! } ...

Be careful that the variable argument is a reference. So, the following code does not work: foreach($some_array as $key => $value) { OCIBindByName($stmt, $key, $value); } I assume this is because the contents of $value changes, even though the reference remains the same, so all bound variables end up pointing to the last loop iteration's value. Instead use the following: foreach($some_array as $key => $value) { OCIBindByName($stmt, $key, $some_array[$key]); }

oci_bind_by_name

Binds the PHP variable to the Oracle placeholder (PHP 5, PECL oci8:1.1-1.2.4)
bool oci_bind_by_name ( resource statement, string ph_name, mixed &variable [, int maxlength [, int type]] )

Example 1633. oci_bind_by_name() example

Example 1634. oci_bind_by_name() example

Example 1635. oci_bind_by_name() example

Related Examples ( Source code ) » oci_bind_by_name

Code Examples / Notes » oci_bind_by_name

Change Language

oci_bind_by_name

Binds the PHP variable to the Oracle placeholder (PHP 5, PECL oci8:1.1-1.2.4) bool oci_bind_by_name ( resource statement, string ph_name, mixed &variable [, int maxlength [, int type]] )

Example 1633. oci_bind_by_name() example

Example 1634. oci_bind_by_name() example

Example 1635. oci_bind_by_name() example

Related Examples ( Source code ) » oci_bind_by_name

Code Examples / Notes » oci_bind_by_name

Change Language

Binds the PHP variable to the Oracle placeholder (PHP 5, PECL oci8:1.1-1.2.4)
bool oci_bind_by_name ( resource statement, string ph_name, mixed &variable [, int maxlength [, int type]] )