oci_bind_array_by_name
"$type [...] The type parameter tells Oracle which descriptor is used. Default to SQLT_CHR"
This is misleading. You don't need to specify a $type to insert numbers. Actually, there isn't any allowed $type constant to insert floating point numbers.
oci_bind_by_name
(PHP 5, PECL oci8:1.1-1.2.4)
oci_bind_by_name — Привязывает переменную PHP к соответствующей метке в SQL-выражении.
Описание
bool oci_bind_by_name
( resource $stmt
, string $ph_name
, mixed $&variable
[, int $maxlength
[, int $type
]] )
oci_bind_by_name() привязывает переменную variable к метке ph_name . Будет ли она использоваться для вывода или ввода - выяснится в процессе выполнения и необходимые ресурсы будут выделены по необходимости. Параметр length устанавливает максимальный объем в байтах получаемой переменной. Если параметр length равен -1, то oci_bind_by_name() будет использовать текущую длину variable как максимальную.
Если вы хотите привязать абстрактный тип данных (LOB/ROWID/BFILE), то вам необходимо сначала создать дескриптор с помощью oci_new_descriptor(). Параметр length не используется с абстрактными типами данных и должен быть равен -1. Параметр type говорит Oracle, какой тип дескриптора мы хотим использовать. Возможные значения этого параметра:
-
OCI_B_FILE - для BFILE;
-
OCI_B_CFILE - для CFILE;
-
OCI_B_CLOB - для CLOB;
-
OCI_B_BLOB - для BLOB;
-
OCI_B_ROWID - для ROWID;
-
OCI_B_NTY - для именованных типов данных;
-
OCI_B_CURSOR - для курсоров, созданных ранее с помощью oci_new_cursor().
Пример #1 Пример использования oci_bind_by_name()
<?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;
while (list($empno, $ename) = each($data)) {
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);
?>
Помните о том, что при использовании этой функции, конечные пробелы у строки будут обрезаны. Смотрите следующий пример:
Пример #2 Пример 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);
/*
Этот код добавит в базу только строку 'trailing spaces follow', без
пробелов в конце строки
*/
?>
Пример #3 Пример 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);
/*
А этот код добавит в базу строку 'trailing spaces follow ' вместе с
пробелами в конце строки
*/
?>
Внимание
Использовать magic_quotes_gpc, magic_quotes_runtime или addslashes() вместе с oci_bind_by_name() - это определенно плохая идея, т.к. в этих случаях кавычки будут записаны в базу вместе с данными. oci_bind_by_name() не может отличить "магические кавычки" от тех, что были добавлены намеренно.
Замечание: В версиях PHP ниже 5.0.0 эта функция называлась ocinewcollection(). В PHP 5.0.0 и выше ocinewcollection() является алиасом oci_new_collection(), поэтому вы можете продолжать использовать это имя, однако это не рекомендуется.
Возвращает TRUE в случае успешного завершения или FALSE в случае возникновения ошибки.
add a note
User Contributed Notesoci_bind_by_name
alvaro at demogracia dot com
24-Sep-2008 02:14
24-Sep-2008 02:14
sixd at php dot net
10-May-2008 01:11
10-May-2008 01:11
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.
ajitsingh4u at gmail dot com
09-May-2008 06:39
09-May-2008 06: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 10: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 01:27
27-Jan-2007 01: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 05:48
11-Jan-2007 05: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 01:48
20-Feb-2006 01: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 09:43
16-Feb-2006 09: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 10:12
16-Aug-2005 10: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