Understanding oci_bind_by_name() and how array elements can be trusted

[AFTNHombre]

I'm trying to use oci_bind_by_name() to bind elements of an array, but I'm confused by a few things. For a start: http://ca2.php.net/manual/en/function.oci-bind-by-name.php (see example #3) says that

foreach ($ba as $key => $val) {
       oci_bind_by_name($stid, $key, $val);
}

won't work. Fair enough, because $val is always the same variable so nothing will be bound when the loop is done, so

foreach ($ba as $key => $val) {
    oci_bind_by_name($stid, $key, $ba[$key]);
}

is necessary. But what's wrong with

foreach ($ba as $key => &$val) {
    oci_bind_by_name($stid, $key, $val);
}

? I would expect it to work just as well w/a slight performance increase.

 

Another thing I'd like to know is how effective the binding is when array elements are reassigned. If I have something like

$ba[':column_name'] = 'something';
oci_bind_by_name($stid, ':column_name', $ba[':column_name']);
...
$ba[':column_name'] = 'something_else';

will this give me what I expect? Is $ba[':column_name'] guaranteed to still point to the same memory?

[Adam]

In answer to your first question, it's because $val will only reference one variable at the point you execute the statement. That kind of answers you second question too, it would use "something else" in the statement. You're not binding to memory, you're binding to the variable.

[AFTNHombre]

I'm not sure what you're saying. My best guess is that what's available from &$val will only be good as long as the current usage of &$val is in scope. Even though it's handing off references to array elements that persist? Would that mean something like

function foo(&$ret) {
    $it = &$this->member;
    $ret = $it;
}

wouldn't work either?

I'm lost in your second answer, too. I want the array element to be 'something_else' at that point. But more importantly, I want 'something_else' to be used in my next OCI call. That "..." was an abstraction representing an execution and the assignment with 'something_else' was about giving the bind variables something new for the next call.

[xyph]

Unless you're specifying a type, using prepared statements is kind of overkill, IMO. Since I don't have an Oracle DB to mess with, I'll show you my findings using PDO w/ MySQL, which I'd imagine handles prepared statements in a very similar way

 

<?php

$qdata = array(
array('data'=>'bar','type'=>PDO::PARAM_STR),
array('data'=>4,'type'=>PDO::PARAM_INT)
);

try {
$db = new PDO('mysql:dbname=db;host=localhost','root','');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$q = 'SELECT * FROM some_table WHERE some_column <> ? AND another_column < ?';
$stmt = $db->prepare($q);
foreach( $qdata as $key => &$data ) {
	# With PDO and MySQL, we have to call both bindValue/bindParam and bindColumn
	$stmt->bindValue($key+1, $data['data'], $data['type']);
	$stmt->bindColumn($key+1, $data['data'], $data['type']);
}
unset($data); # Destroy unneeded reference.
$stmt->execute();
while( $stmt->fetch(PDO::FETCH_BOUND) )
	# And it appears references work
	echo $qdata[0]['data'].' '.$qdata[1]['data'].'<br>';
$stmt->closeCursor();
} catch( PDOException $e ) {
echo $e->getMessage().'<br>';
}

?>

[Adam]

Unless you're specifying a type, using prepared statements is kind of overkill, IMO. Since I don't have an Oracle DB to mess with, I'll show you my findings using PDO w/ MySQL, which I'd imagine handles prepared statements in a very similar way

 

<?php

$qdata = array(
array('data'=>'bar','type'=>PDO::PARAM_STR),
array('data'=>4,'type'=>PDO::PARAM_INT)
);

try {
$db = new PDO('mysql:dbname=db;host=localhost','root','');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$q = 'SELECT * FROM some_table WHERE some_column <> ? AND another_column < ?';
$stmt = $db->prepare($q);
foreach( $qdata as $key => &$data ) {
	# With PDO and MySQL, we have to call both bindValue/bindParam and bindColumn
	$stmt->bindValue($key+1, $data['data'], $data['type']);
	$stmt->bindColumn($key+1, $data['data'], $data['type']);
}
unset($data); # Destroy unneeded reference.
$stmt->execute();
while( $stmt->fetch(PDO::FETCH_BOUND) )
	# And it appears references work
	echo $qdata[0]['data'].' '.$qdata[1]['data'].'<br>';
$stmt->closeCursor();
} catch( PDOException $e ) {
echo $e->getMessage().'<br>';
}

?>

 

Oracle doesn't support "?" parameters

[Adam]

I'm not sure what you're saying. My best guess is that what's available from &$val will only be good as long as the current usage of &$val is in scope. Even though it's handing off references to array elements that persist? Would that mean something like

function foo(&$ret) {
    $it = &$this->member;
    $ret = $it;
}

wouldn't work either?

I'm lost in your second answer, too. I want the array element to be 'something_else' at that point. But more importantly, I want 'something_else' to be used in my next OCI call. That "..." was an abstraction representing an execution and the assignment with 'something_else' was about giving the bind variables something new for the next call.

 

What I was saying is that when you bind a variable into a statement, you're not binding any particular value or memory address, you're binding the variable. When you use it in a foreach with a reference, the bound parameter is a variable which is a reference to some other variable. When the statement is executed every parameter is bound to the the $val variable, which is a reference back to the last item in the array.

 

I think I misunderstood your second question, but if you bind any variable or array index, whatever the value of it is at the time is what will be used in the execution.

[xyph]

That's so odd that my above snippet works, but this one fails.

 

<?php

$qdata = array(
':some' => array('data'=>'bar','type'=>PDO::PARAM_STR),
':another' => array('data'=>4,'type'=>PDO::PARAM_INT)
);

try {
$db = new PDO('mysql:dbname=db;host=localhost','root','');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$q = 'SELECT * FROM some_table WHERE some_column <> :some AND another_column < :another';
$stmt = $db->prepare($q);
foreach( $qdata as $key => &$data ) {
	# With PDO and MySQL, we have to call both bindValue/bindParam and bindColumn
	$stmt->bindValue($key, $data['data'], $data['type']);
	$stmt->bindColumn($key, $data['data'], $data['type']);
}
unset($data); # Destroy unneeded reference.
$stmt->execute();
while( $stmt->fetch(PDO::FETCH_BOUND) )
	# And references now fail for some reason
	echo $qdata[':some']['data'].' '.$qdata[':another']['data'].'<br>';
$stmt->closeCursor();
} catch( PDOException $e ) {
echo $e->getMessage().'<br>';
}

?>

[Adam]

That's so odd that my above snippet works, but this one fails.

 

Only difference aside from the "?" parameters is: $key+1? Can't help but feel things are being over done here, you can pass parameters to PDO ant it will use them in the context of their data type.

[xyph]

It was a quick example.

 

Yeah, those are the only differences. $key+1 was used because prepared statements are 1-indexed to the arrays 0-index. It can be removed if you 1-index the array, and it still works fine... with ? placeholders

$qdata = array(
1=> array('data'=>'bar','type'=>PDO::PARAM_STR),
2=> array('data'=>4,'type'=>PDO::PARAM_INT)
);

[Adam]

and it still works fine... with ? placeholders

 

PDO vodoo

[AFTNHombre]

What I was saying is that when you bind a variable into a statement, you're not binding any particular value or memory address, you're binding the variable. When you use it in a foreach with a reference, the bound parameter is a variable which is a reference to some other variable. When the statement is executed every parameter is bound to the the $val variable, which is a reference back to the last item in the array.

I see. Well... I guess a guru would know what he's talking about. Thanks.

I'm pretty sure I used the &$val method somewhere in my code--somewhere that gets heavy usage. It seems awfully lucky that it didn't blow up in my face.

[Adam]

What I was saying is that when you bind a variable into a statement, you're not binding any particular value or memory address, you're binding the variable. When you use it in a foreach with a reference, the bound parameter is a variable which is a reference to some other variable. When the statement is executed every parameter is bound to the the $val variable, which is a reference back to the last item in the array.

I see. Well... I guess a guru would know what he's talking about. Thanks.

I'm pretty sure I used the &$val method somewhere in my code--somewhere that gets heavy usage. It seems awfully lucky that it didn't blow up in my face.

 

Yeah, references can be risky if not used right. The thing you really need to consider is that after a foreach loop has ended, if you have a reference pointing to an index within the array or another variable, it will still exist after the foreach. So after a foreach loop ends that uses a reference, you should unset the reference. That will ensure any accidental use of the variable wouldn't have any adverse affects.