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

4 Responses to “oci_bind_by_name maxlength is not so optional”

  1. cj Says:

    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).

  2. Harun Yayli Says:

    @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.

  3. matt Says:

    there is a solution to this which will enable you to use -1, still

    if(is_array($bindvars) && count(array_keys($bindvars)) > 0)
    {
    while(list($key, $val) = each($bindvars))
    {
    if(! oci_bind_by_name($stmtid, ‘:’ . $key, $bindvars[$key], -1))
    {
    //Error
    }
    }
    }

  4. matt Says:

    i hate to spam your site, but i forgot to point out the very important piece. it has nothing to do with the column length, however it has everything to do with the way in which you reference the bindvar from within the loop. notice how i reference it $bindvars[$statement_bind] and not by $val.

    this is true for foreach loops as well. in fact foreach loops are faster than the while list.

    right

    foreach($bindvars as $statement_bind => $variable_bind)
    {
    if(! oci_bind_by_name($stmtid, ‘:’ . $statement_bind, $bindvars[$statement_bind], -1))
    {
    //Fail
    }
    }

    wrong

    foreach($bindvars as $statement_bind => $variable_bind)
    {
    if(! oci_bind_by_name($stmtid, ‘:’ . $statement_bind, $variable_bind, -1))
    {
    //Fail
    }
    }

Leave a Reply

authimage