Using PHP Array and Oracle Databases error: oci_bind_by_name(): Invalid variable used for bind

This is the second time in the past week I have had an issue with using PHP arrays while talking to an Oracle database.

I found that the value stored within a PHP array was unable to bind to the Oracle variable.
An example of this not working in what seems to be perfectly valid code is

$count = 0;

foreach ($sr as $element) {

if ( $count == 0 ) {
$eprint_id = $element;
} else {
$scopus_id = $element;
}
unset($element);
$count++;
}

Even if we try and call the value by stating the index of the array explicitly it still does not work as shown below.

$eprint_id = $sr[0];
$scopus_id = $sr[1];

The fix in the end was to get the string representation of the value like this…

$eprint_id = strval($sr[0]);
$scopus_id =  strval($sr[1]);

Below is the finished product of a simple database connection

function do_insert($conn,$sr, $c) {
    //date format "30-May-89"
    $date_time = date("j-F-y");
    $eprint_id = strval($sr[0]);
    $scopus_id =  strval($sr[1]);
    $citation_count = $c;
    $stmt = "insert into LSS.citations ( date_day , eprint_id , scopus_id , citation_count ) values (:bind_date_day, :bind_eprint_id, :bind_scopus_id, :bind_citation_count)";
    $s = oci_parse($conn, $stmt);
    oci_bind_by_name($s, ":bind_date_day", $date_time);
    oci_bind_by_name($s, ":bind_eprint_id", $eprint_id);
    oci_bind_by_name($s, ":bind_scopus_id", $scopus_id);
    oci_bind_by_name($s, ":bind_citation_count", $citation_count);
    $oci_execute = oci_execute($s);  // automatically commit
}
Advertisements

2 thoughts on “Using PHP Array and Oracle Databases error: oci_bind_by_name(): Invalid variable used for bind

  1. Your example of what wasn’t working is has no OCI8 calls so it’s hard to guess the problem.
    It looks like the common issue of trying to bind within a foreach(). See Example 3 on
    http://www.php.net/manual/en/function.oci-bind-by-name.php for how to resolve this.
    There are other bind examples in the free book:
    http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html

    Also, instead of relying on a preset Oracle date format you can bind within a to_date() like:
    $si = oci_parse($c, “insert into t (id, dt) values (:id, to_date(:dt, ‘YYYY-MM-DD’))”);
    $id = 1;
    $dt = date(‘Y-m-d’);
    oci_bind_by_name($si, “:id”, $id);
    oci_bind_by_name($si, “:dt”, $dt);
    oci_execute($si);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s