oci_bind_by_name maxlength is not so optional
by Harun Yayli on Tuesday, May 6th, 2008 at 10:22 am under Hacks, PHP
If you think that the maxlength parameter in the documentation of oci_bind_by_name is optional, see this example and think again.
Lets say you have your column names in an array already and you want to bind them in a loop smartly.
$cols = array('a','b','c');
$data = array(
array('a'=>1,'b'=>2,'c'=>3),
array('a'=>4,'b'=>5,'c'=>6),
);
$q = "INSERT INTO table_a (a,b,c) VALUES (:a,:b,:c)";
$stmt = oci_parse($db, $q);
foreach ($cols as $key) {
oci_bind_by_name($stmt, ':'.$key, $$key); // < << optional length
}
// then you want to loop through the data and execute the statment.
foreach($data as $row){
extract($row);
oci_execute($stmt, OCI_DEFAULT);
}
oci_free_statement($stmt);
oci_commit($db);
oci_close($db);
actually this would return you a nice obscure
Warning: oci_execute(): ORA-01461: can bind a LONG value only for insert into a LONG column in …
even if you don’t have any LONG value in the col types.
Just add a large enough number to oci_bind_by_name line:
oci_bind_by_name($stmt, ':'.$key, $$key,200)
If you know the data type length for each column it’s better to put that. In my case I wouldn’t know. So I entered the max of the max col length.
Happy inserting!
PS: I also tried using $row[$key] instead of a lame $$key. However it didn’t work. I guess it has something to do with the $key changing all at each iteration.
Recent Entries
- oci_bind_by_name maxlength is not so optional
- Is Sun going to buy PHP too?(PHP Quebec 2008)
- PHP APC apc_shm_create error on CLI
- Facebook’s Buggy Spam Detection
- Is it Firefox or Zend Debugger? Cookie Standards
- ezComponents ready for prod?
- You Used PHP to Write WHAT?!
- CPM and Ajax (a.k.a New Metrics)
- Where are you Rasmus?
- Look ma! Mysql Federation sucks!
It makes senses that a length would be required because when the oci_bind_by_name() call is made, there is no data in $$key (a.k.a. $a, $b or $c). Without a length passed, PHP tells the DB to expect a single byte string.
For string binds 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.
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.
The oci_commit() in your example is not needed because oci_execute() commits by default. For best performance and transaction control, use oci_execute($stmt, OCI_DEFAULT).
May 7th, 2008 at 1:16 am@cj
thanks for the explanation. It makes sense to me what you’re explaining. The example shown at the php.net’s documentation, has the similar approach that I have. It explicitly sets the size of the field. However the documentation says, on top, the length is optional.
This makes you think that dynamically oci will grab the size of the field by default and throw an error based on the table definition in oracle when oci_execute is called .
PS: I’ve corrected the example.
May 7th, 2008 at 12:36 pm