oci_bind_array_by_name
For IN string binds (i.e. INSERT or passing a value into a PL/SQL
procedure) where the length is not known when binding, use a length
that is longer than the longest possible string. If you don't know
what this is in advance, then re-call oci_bind_by_name() with the
actual size prior to each oci_execute() call.
For OUT binds, always specify a length so that PHP allocates enough
memory to hold the returned value.
The cardinal rule for binding is that the memory address used in the
bind call must continue to exist and be usable when the oci_execute
occurs. This is the common problem with 'foreach' loops.
oci_bind_by_name
(PHP 5, PECL oci8:1.1-1.2.4)
oci_bind_by_name — Associe une variable PHP à une variable SQL Oracle
Description
bool oci_bind_by_name
( resource $statement
, string $ph_name
, mixed &$variable
[, int $maxlength
[, int $type
]] )
Lie la variable PHP variable à l'emplacement Oracle ph_name , dans la commande préparée stmt . Son utilisation (comme entrée ou comme sortie) sera définie à l'exécution, et l'espace nécessaire sera alloué.
Liste de paramètres
- statement
-
Une requête OCI.
- ph_name
-
Le marqueur.
- variable
-
La variable PHP.
- maxlength
-
Fixe la taille maximum pour la liaison. Si vous affectez une longueur length de -1, oci_bind_by_name() utilisera la longueur de variable comme maximum maxlength .
- type
-
Si vous devez lier des types abstraits de données (LOB/ROWID/BFILE), vous devrez l'allouer dans un premier temps, avec ocinewdescriptor(). La longueur length ne sert pas pour ces types et devrait être fixée à -1. Le paramètre type indique au serveur Oracle, quel type de pointeur va être utilisé. Par défaut, vaut SQLT_CHR. Les valeurs possibles sont :
-
SQLT_FILE - pour les BFILEs;
-
SQLT_CFILE - pour les CFILEs;
-
SQLT_CLOB - pour les CLOBs;
-
SQLT_BLOB - pour les BLOBs;
-
SQLT_RDD - pour les ROWIDs;
-
SQLT_NTY - Pour les types de données nommés;
-
SQLT_INT - pour les entiers;
-
SQLT_CHR - pour les VARCHAR;
-
SQLT_BIN - pour les colonnes RAW;
-
SQLT_LNG - pour les colonnes LONG;
-
SQLT_LBI - pour les colonnes LONG RAW;
-
SQLT_RSET - pour les curseurs, qui ont été créés auparavant avec la fonction oci_new_cursor().
-
Valeurs de retour
Cette fonction retourne TRUE en cas de succès, FALSE en cas d'échec.
Exemples
Exemple #1 Exemple avec oci_bind_by_name()
<?php
/* Exemple ociBindByPos par thies@thieso.net (980221)
Insère 3 lignes dans emp, et utilise ROWID pour mettre à jour
les lignes, juste après l'insertion.
*/
$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);
?>
N'oubliez pas que cette fonction supprime les caractères d'espace terminaux. Par exemple :
Exemple #2 Exemple avec oci_bind_by_name()
<?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);
/*
Ce code va insérer la chaîne 'trailing spaces follow',
sans les espaces terminaux.
*/
?>
Exemple #3 Exemple avec oci_bind_by_name()
<?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);
/*
Ce code va insérer la chaîne 'trailing spaces follow ',
avec les espaces terminaux.
*/
?>
Valeurs de retour
Cette fonction retourne TRUE en cas de succès, FALSE en cas d'échec.
Notes
Avertissement
C'est une mauvaise idée que d'utiliser les magic_quotes_gpc et la fonction oci_bind_by_name() simultanément, car aucun ajout de guillemets n'est nécessaire sur les variables citées, et tout guillemet magique sera inscrit dans votre base de données car oci_bind_by_name() n'est pas capable de faire la différence entre les guillemets et ceux qui ont été ajoutés dynamiquement.
Note: Dans les versions de PHP antérieures à la version 5.0.0, vous devez utiliser la fonction ocibindbyname(). Cet ancien nom est toujours utilisable : un alias a été fait vers la fonction oci_bind_by_name(), pour assurer la compatibilité ascendante. Toutefois, il est recommandé de ne plus l'utiliser.
add a note
User Contributed Notesoci_bind_by_name
sixd at php dot net
09-May-2008 04:11
09-May-2008 04:11
ajitsingh4u at gmail dot com
09-May-2008 09:39
09-May-2008 09:39
//Calling Oracle Stored Procedure
//I assume that you have a users table and three columns in users table i.e. id, user, email in oracle
// For example I made connection in constructor, you can modify as per your requirement.
//http://www.devshed.com/c/a/PHP/Understanding-Destructors-in-PHP-5/1/
<?php
class Users{
private $connection;
public function __construct()
{
$this->connection = oci_connect("scott", "tiger", $db); // Establishes a connection to the Oracle server;
}
public function selectUsers($start_index=1, $numbers_of_rows=20)
{
$sql ="BEGIN sp_users_select(:p_start_index, :p_numbers_of_rows, :p_cursor, :p_result); END;";
$stmt = oci_parse($this->connection, $sql);
//Bind in parameter
oci_bind_by_name($stmt, ':p_start_index', $start_index, 20);
oci_bind_by_name($stmt, ':p_numbers_of_rows', $numbers_of_rows, 20);
//Bind out parameter
oci_bind_by_name($stmt, ':p_result', $result, 20); // returns 0 if stored procedure succeessfully executed.
//Bind Cursor
$p_cursor = oci_new_cursor($this->connection);
oci_bind_by_name($stmt, ':p_cursor', $p_cursor, -1, OCI_B_CURSOR);
// Execute Statement
oci_execute($stmt);
oci_execute($p_cursor, OCI_DEFAULT);
oci_fetch_all($p_cursor, $cursor, null, null, OCI_FETCHSTATEMENT_BY_ROW);
echo $result;
echo '<br>';
var_dump($cursor); // $cursor is an associative array so we can use print_r() to print this data.
// you can return data from this function to use it at your user interface.
}
public function deleteUser($id)
{
$sql ="BEGIN sp_user_delete(:p_id, :p_result); END;";
$stmt = oci_parse($this->connection, $sql);
// bind in and out variables
oci_bind_by_name($stmt, ':p_id', $id, 20);
oci_bind_by_name($stmt, ':p_result', $result, 20);
//Execute the statement
$check = oci_execute($stmt);
if($check == true)
$commit = oci_commit($this->connection);
else
$commit = oci_rollback($this->connection);
return $result;
}
// You can make function for insert ,update using above two functions
}
?>
08-May-2007 01:59
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
tom at bitworks dot de
27-Jan-2007 04:27
27-Jan-2007 04:27
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.
Chris Delcamp
11-Jan-2007 08:48
11-Jan-2007 08:48
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;
js at mcs dot be
20-Feb-2006 04:48
20-Feb-2006 04:48
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]);
}
ehsmeng
16-Feb-2006 12:43
16-Feb-2006 12:43
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?!
}
...
hfuecks at nospam dot org
16-Aug-2005 01:12
16-Aug-2005 01:12
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