Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : Oracle Functions : ocibindbyname

ocibindbyname

Alias of oci_bind_by_name (PHP 4, PHP 5, PECL oci8:1.0-1.2.4)


Code Examples / Notes » ocibindbyname

maxwell_smart

You cannot use a reserved word as a bind variable.Otherwise you'll get "OCIStmtExecute: ORA-01745: invalid host/bind variable name". So, while "RETURNING ROWID INTO :RowId" may be nice, it'll cause an error, as will "RETURNING ROWID INTO :SELECT", etc.

jriley

To select a value from DUAL, use a  length of -1.  DUAL is defined as a table of VARCHAR2(1).
ex:
$stmt = OCIParse($conn,  "select  :VAR from DUAL");
ocibindbyname($stm, ":VAR", &$Var, -1);
For a query using LIKE,  put the percent signs in the variable definition and skip the single quotes you would normally put around the regex in SQL.
ex:
$ename = "%{$ename}%";
$stmt = OCIParse($conn,  "select empno from emp where ename like :ename");
OCIBindByName($stmt, ":ename", $ename, 32);
Adding single quotes around :ename in $query will cause an
"ORA-01036: illegal variable name/number" error.  Adding them in $ename will add  single quotes to the search string.


gmarcos

to load an image from oracle:
<?
Header("Content-Type: image/png");
$conn = OCILogon("user","passwd","bd");
$stmt = OCIParse($conn,"select picture
from table_with_picture ");
$picture = OCINewDescriptor($stmt,OCI_D_LOB);
OCIDefineByName($stmt,"PICTURE",&$picture);
OCIExecute($stmt);
while(OCIFetch($stmt)){
}
OCIFreeStatement($stmt);
$fp = fopen ("/directory/picture.tif","wb");
fwrite($fp,$picture);
fclose($fp);
passthru("/usr/bin/X11/convert /directory/picture.tif png:-");
?>
This work for blobs.


andrea

This script print the real name of a BFILE saved. It use OCIBindByName ...
$c1=OCILogon ("username","password","database");
$store_file = "NULL";
$query = "DECLARE Lob_loc  BFILE; DIRS   VARCHAR2(30); FILS  VARCHAR2(40); BEGIN SELECT B_FILE INTO Lob_loc FROM TABLE_WHIT_B_FILE WHERE KEY=2 ; DBMS_LOB.FILEGETNAME(Lob_loc,DIRS,FILS); :NOME:=FILS; END;"
$stmt = ociparse($c1,$query);
OCIBindByName($stmt,":NOME",&$store_file,50);

if(!ociexecute($stmt)) echo "Error";

echo  $store_file ;

OCIFreeStatement($stmt);
OCILogOff($c1);


cthrall

So, if you're calling OCIBindByName in a loop and binding to an associative array like this:
while (list($key, $val) = each($array))
{
   OCIBindByName($state, $key, $val['VALUE'], $val['LENGTH']);
}
it won't work.  My WAG is that OCI gets confused when you bind a bunch of values to what appears to be the same variable, then call OCIExecute.
Workaround is create a new array, and copy values into it:
$value = array();
$i = 0;
while (list($key, $val) = each($array))
{
   $value[$i] = $val['VALUE'];
   OCIBindByName($state, $key, $value[$i], $val['LENGTH']);
   $i++;
}


javier_8

Probably a common error using oci_bind_by_name:
<?php
for ($i=0; $i <= 20; $i++)
{
 $ParamName = ':FIELD'.$i;
 $Value = $_POST['FIELD'.$i];
 oci_bind_by_name($stmt, $ParamName, $Value);
}
?>
This is wrong and you'll get the error:
ORA-01461: can bind a LONG value only for insert into a LONG column
It's because oci_bind_by_name apparently works with placeholders, if you assign all the binds to the address of "$value", then all of the values will be the same.
You must do this instead:
<?php
for ($i=0; $i <= 20; $i++)
{
 $ParamName = ':FIELD'.$i;
 oci_bind_by_name($stmt, $ParamName, $_POST['FIELD'.$i]);
}
?>
So oci_bind_by_name links the parameter with the memory space of POST['FIELD1'], POST['FIELD2'], POST['FIELD3']...
Hope it helps (It took some time to figure this out, and I found no help on the net for this issue).


k_a_h_l_i_l

ocibindbyname with VARCHAR fields doesn't handle length dynamically. I had to define my variable with the correct length (and garbage data) before calling bindbyname.
   $myvar = "000000";
   OCIBindByName($stmt,":myvar",&$myvar, -1);
without the first initialization line ($myvar = "000000"), the statement will give a NULL length error when executed. This error happens even if the variable's length is provided as a parameter. So
   OCIBindByName($stmt,":myvar",&$myvar, 6);
won't work either unless the initialization statement is executed first.


philippe

Note: If the table field is VARCHAR2(1),
--> ocibindbyname($stm, ":VAR", &$Var, 1);
won't work. You have to put instead:
--> ocibindbyname($stm, ":VAR", &$Var, -1);


benjy

Note that when binding a variable to a
VARCHAR2 column, the length parameter should count an extra character (the terminating null.)
For example, when binding to a column of type VARCHAR2(500), the length parameter should be 501. Otherwise when a string of length 500 is supplied as a bind value an error ("ORA-01480: trailing null missing from STR bind value") will be thrown.
According to the OCI documentation this happens because the length parameter is used by OCI as a search limit for the terminating null in the underlying C/C++ character string.


adamb-php

If you use dba_tab_columns or user_tab_columns to get the sizes of the columns into which you are inserting data with OCIBindByName, be aware that a DATE type column is returned as 7 which is the size of it when it is stored in oracle's internal format.  The problem arises when you are in fact inserting dates using the TO_DATE function as your bound data could be something like "28/04/1972 12:22.13" which is larger than the width specified in the bind statement and will get you the ever so helpful OCI error message of:
ORA-01461: can bind a LONG value only for insert into a LONG column


yepster

If you get ora-01460 you might want to check whether the OCIBindByName was done with -1 on a date field, which on bind time (not execute time) was bound to a php variable with size 0. (e.g.: $var=""; bind var to date with length -1; loop; $var=realdate; execute will give ora-01460 -> unreasonable or unimplemented conversion). Changing the initial $var to a good length, or do the bind with the right size of your date in string format representation instead of -1 will solve it.

aidan.peiser

I have made two function to add and update a table with a CLOB field in it.all you have to do is pass the correct data through the function and it should work...
<?php
/*the table sql looks like this:
create table MY_PAGE (pageid varchar2(20),
  pagename varchar2(50),
  pageurl varchar2(100),  
  edited date,
  bodymessage clob);
create sequence mypage_sequence minvalue 1 nocache;
*/
function updatePageData($select,$pagename,$pageurl,$document) {
global $conn;

$current_time=date("YmdHis");
$sql = "update MY_PAGE set PAGENAME='$pagename',
PAGEURL='$pageurl',
EDITED=TO_DATE('$current_time','YYYYMMDDHH24MISS'), BODYMESSAGE = EMPTY_CLOB()
WHERE PAGEID = '$select' returning BODYMESSAGE into :bodymessage";
echo $sql;

$stmt = OCIParse($conn,$sql);
$lob = OCINewDescriptor($conn,OCI_D_LOB);
OCIBindByName($stmt,":bodymessage",&$lob,-1,OCI_B_CLOB);
       OCIExecute($stmt, OCI_DEFAULT);
       $lob->save($document);
       $lob->free();
       OCIFreeStatement($stmt);
if(OCICommit($conn)){
return true;
}else{
return false;
}
}
function insertPageData($pagename,$pageurl,$document) {

global $conn;

$current_time=date("YmdHis");
$query = "insert into MY_PAGE (PAGEID, PAGENAME, PAGEURL, EDITED, BODYMESSAGE)
values (mypage_sequence.nextval,
'$pagename',
'$pageurl',
TO_DATE('$current_time','YYYYMMDDHH24MISS'),
empty_clob()) returning BODYMESSAGE into :bodymessage";
$stmt = OCIParse($conn, $query);

$clob1 = OCINewDescriptor($conn, OCI_D_LOB);
OCIBindByName ($stmt, ":bodymessage", &$clob1, -1, OCI_B_CLOB);
OCIExecute($stmt, OCI_DEFAULT);
$clob1->save ($document);

if(OCICommit($conn)){
echo "data inserted";
}else{
echo "unable to insert data";
}
}
?>


md

I changed the Method below - all without comments:
function getBindVars($statement){
$regExp = "/(:[_a-z0-9]+)/i";
$statement = preg_replace('/\'[^\']*?\'/i', "", $statement);
//  --- Kommentare raus:
$statement = preg_replace('/--[^(\n)]*?\n/i', "", $statement."\n");
// /* */ Kommentare raus:
$statement = preg_replace('/(\/\*)([^\*]|[^\*](\*)[^\/])+?(\*\/)/i', "", $statement);
$statement = preg_replace('/[^s]el([^t](t)[^s])+?(ts)/i', "", $statement);
$test = preg_match_all($regExp, $statement, $return);
if (isset($return[1]) && count($return[1]) > 0)
return($return[1]);
else return array();
}
for this statement:
$testStatement = " select * from /* Komm \n* :v_comment /asdf */ elements := [asdf]||test where\n elements_id = :v_test;".
"(:value-1) ':text[not]text'||:test123 --test 'diesen:auch_nicht' :v_not\n asdf";
we get this Array:
Array
(
   [0] => :v_test
   [1] => :value
   [2] => :test123
)


k_a_h_l_i_l

How to bind an nvarchar field:
$sql = "insert into my_table values (translate(:varname using nchar_cs))";
$stmt = OCIParse($conn, $sql);
OCIBindByName($stmt,":varname", &$value, strlen($value) + 1, 1);
$err = OCIError($stmt);
if(!$err) {
 OCIExecute($stmt);
 $err = OCIError($stmt);
}
OCIFreeStatement($stmt);


16-sep-2004 11:18

Hi all,
I wrote a little function to get all the bind variables of a statement. I use this for a tool that creates a formular to execute different statements the user can select:
function getBindVars($statement){
$regExp = "/[^'](:[_a-z0-9]+)/i";
$test = preg_match_all($regExp, $statement, $return);
if (isset($return[1]) && count($return[1]) > 0)
return($return[1]);
else return array();
}


david dot gaia dot kano

Here is a new twist I just discovered (at least with PHP 4.3.5). While it is true that doing something like this works:
----------
$dataArray = array("name" => "david", "sport" => "rock climbing");
$lengths = array("name" => 25, "sport" => 100);
// imagine the ociparse here
foreach($dataArray as $col => $val) {
   ocibindbyname($statement, $col, $dataArray[$val], $lengths[$col])
}
ociexecute($statement);
$dataArray["name"] = "jane";
$dataArray["sport"] = "kayaking";
ociexecute($statement);
---------
The following ADDITIONAL lines of code would not:
--------
function getDataArray() {
   $ret["name"] = "susan";
   $ret["sport"] = "walking";
   return($ret);
}
$dataArray = getDataArray();
ociexecute($statement);
--------
As far as I can tell, the last execute would attempt to insert using the same data as the second execute. In other words when reassigning the whole associative array rather than just each member in the array, the binds do not work as expected. I think this is also a problem when the array has never been assigned to anything yet, when you do the binds.


max

For those, who may wish to determine column size before execution of query, I suggest to use DBA_TAB_COLUMNS view. I did it with this function:
function db_column_size($table,$column) {
 $connection = db_connect();
 $query = "SELECT DATA_LENGTH FROM DBA_TAB_COLUMNS ".
          "WHERE TABLE_NAME='".strtoupper($table)."' ".
          "AND COLUMN_NAME='".strtoupper($column)."'";
 $statement = OCIParse($connection, $query);
 OCIExecute($statement);
 ocifetchinto($statement,$row,OCI_ASSOC+OCI_RETURN_NULLS);
 return $row["DATA_LENGTH"];
}


chris_se

Be warned if you use this function with a fixed-width character field:
If you have a table with the following fields:
create table personal_data (
name      char(20) not null,
street      char(50),
city         char(50),
primary key (name) using index
);
Now, if you want to update a column without OCIBindByName, you may write
$stmt = OCIParse ($conn, "update personal_data set street = '$street' where name = '$name'");
$res = OCIExecute ($stmt);
OCIFreeStatement ($stmt);
If you want to use OCIBindByName, the following will _only_ work, if the contents of $name has always the same length as field:
$stmt = OCIParse ($conn, "update personal_data set street = :STREET where name = :NAME");
OCIBindByName ($stmt, ":STREET", &$street, 50);
OCIBindByName ($stmt, ":NAME", &$name, 20);
$res = OCIExecute ($stmt);
OCIFreeStatement ($stmt);
(this will not produce any error, it's simply that the where-clause will never get true if the contents of $name is not as long as the field itself)
To make this work, you have to trim the field:
$stmt = OCIParse ($conn, "update personal_data set street = :STREET where trim(name) = :NAME");
OCIBindByName ($stmt, ":STREET", &$street, 50);
OCIBindByName ($stmt, ":NAME", &$name, 20);
$res = OCIExecute ($stmt);
OCIFreeStatement ($stmt);


alexander dot zimmer

Addition to the posting of cthrall@rocketmail.com (from 06-Sep-2000):
It's true, you can't call OCIBindByName in a loop and bind to an associative array like this:
foreach ($array as $key => $val)
{
OCIBindByName($state, $key, $val, -1);
}
BUT: You can do it this way:
foreach ($array as $key => $val)
{
OCIBindByName($state, $key, $array[$key], -1);
}
Background: the command OCIBindByName BINDS a PHP variable to a parameter, not just its current value. Thus, it is up to you to make sure that the variable has the correct value at the very time when OCIExecute is called, not just when OCIBindByName was called! The PHP variable gets evaluated at the point of OCIExecute and not earlier.
This is somewhat more legible than cthrall's solution (but it surely works, too).


Change Language


Follow Navioo On Twitter
oci_bind_array_by_name
oci_bind_by_name
oci_cancel
oci_close
OCI-Collection->append
OCI-Collection->assign
OCI-Collection->assignElem
OCI-Collection->free
OCI-Collection->getElem
OCI-Collection->max
OCI-Collection->size
OCI-Collection->trim
oci_commit
oci_connect
oci_define_by_name
oci_error
oci_execute
oci_fetch_all
oci_fetch_array
oci_fetch_assoc
oci_fetch_object
oci_fetch_row
oci_fetch
oci_field_is_null
oci_field_name
oci_field_precision
oci_field_scale
oci_field_size
oci_field_type_raw
oci_field_type
oci_free_statement
oci_internal_debug
OCI-Lob->append
OCI-Lob->close
oci_lob_copy
OCI-Lob->eof
OCI-Lob->erase
OCI-Lob->export
OCI-Lob->flush
OCI-Lob->free
OCI-Lob->getBuffering
OCI-Lob->import
oci_lob_is_equal
OCI-Lob->load
OCI-Lob->read
OCI-Lob->rewind
OCI-Lob->save
OCI-Lob->saveFile
OCI-Lob->seek
OCI-Lob->setBuffering
OCI-Lob->size
OCI-Lob->tell
OCI-Lob->truncate
OCI-Lob->write
OCI-Lob->writeTemporary
OCI-Lob->writeToFile
oci_new_collection
oci_new_connect
oci_new_cursor
oci_new_descriptor
oci_num_fields
oci_num_rows
oci_parse
oci_password_change
oci_pconnect
oci_result
oci_rollback
oci_server_version
oci_set_prefetch
oci_statement_type
ocibindbyname
ocicancel
ocicloselob
ocicollappend
ocicollassign
ocicollassignelem
ocicollgetelem
ocicollmax
ocicollsize
ocicolltrim
ocicolumnisnull
ocicolumnname
ocicolumnprecision
ocicolumnscale
ocicolumnsize
ocicolumntype
ocicolumntyperaw
ocicommit
ocidefinebyname
ocierror
ociexecute
ocifetch
ocifetchinto
ocifetchstatement
ocifreecollection
ocifreecursor
ocifreedesc
ocifreestatement
ociinternaldebug
ociloadlob
ocilogoff
ocilogon
ocinewcollection
ocinewcursor
ocinewdescriptor
ocinlogon
ocinumcols
ociparse
ociplogon
ociresult
ocirollback
ocirowcount
ocisavelob
ocisavelobfile
ociserverversion
ocisetprefetch
ocistatementtype
ociwritelobtofile
ociwritetemporarylob
eXTReMe Tracker