Page 1 of 1
Prepared statement / bind SQL - :1 or ?
Posted: Sun Apr 02, 2006 6:35 pm
by Ambush Commander
I came upon some bind SQL on the forum the other day, and I thought to myself, "Hmm, I use question-marks, but these people use :1, :2, :3s. Hmmm..."
Which do you prefer? Are there any definite advantages to using one over the other?
Jcart | I've added the requested option
Posted: Sun Apr 02, 2006 7:29 pm
by Christopher
PDO uses ? and :label1, :label2, :label3, so you should add the latter to your list.
Posted: Sun Apr 02, 2006 7:31 pm
by Ambush Commander
You can't edit poll options. I'd say :label1 is basically the same as :1.
Posted: Sun Apr 02, 2006 7:33 pm
by John Cartwright
I would avoid question marks, simply because their only relationship is the order of which they are presented. This can be a pain for debugging on going through someone elses code aswell, meanwhile a label eliminates this confusion.
Posted: Sun Apr 02, 2006 7:35 pm
by Christopher
Well they have a little different functionality. The ? is the one-shot style. With the labels you can bind variables to labels and then reuse the prepared statement with changed values.
Posted: Sun Apr 02, 2006 7:38 pm
by Ambush Commander
Well, if you pass an array like this: array('value1', 'value2', 'value3') you're still constrained by order.
What you'd have to do, in that case, is use something like...
Code: Select all
$statement->setString(1, $string);
$statement->setFloat(2, $float);
//...
(from PoEAA, I suppose this is the Java style. But they still use ? marks)
Edit - So that's the difference! Does that mean this poll is null (if I remember correctly, MySQL doesn't have prepared statements)
Posted: Sun Apr 02, 2006 7:39 pm
by Nathaniel
May I suggest both?
I say this because I recently came on a situation where I had a variable number of statements to prepare...
Code: Select all
<?PHP
foreach ( $data as $key => $value )
{
$query->prepare($value);
$sql .= $key . ' = ":' . $query->countBinds() . '" '; //countBinds() counts the prepared value array
}
$query->execute($sql);
?>
I realize now that allowing question marks would be useful for such situations, although possibly confusing to someone else using your code...
Posted: Sun Apr 02, 2006 7:43 pm
by Ambush Commander
Hmm... Nathaniel, something like this? (part of a Domain Object supertype)
Code: Select all
function _insert() {
global $wgDB;
$this->id = $wgDB->KeyGenerator->nextKey($this->_table);
$query = " INSERT INTO `{$this->_table}` ( `";
$query .= implode('`, `', $this->_fields);
$query .= "` ) VALUES ( ";
$places = array();
$values = array();
foreach ($this->_fields as $field) {
$places[] = '?';
$values[] = is_object($this->$field) ? $this->$field->toString() :
$this->$field;
}
$query .= implode(', ', $places);
$query .= " ); ";
return $wgDB->execute($query, $values);
}
Posted: Sun Apr 02, 2006 9:45 pm
by Nathaniel
Yes, something like that. When I can, I prefer using the numbered binds, though. IMO, easier to see what's going on.
Posted: Mon Apr 03, 2006 2:26 am
by timvw
I prefer named parameters instead of numbered ones... (ADO.net style)
Code: Select all
$command->CommandText = "SELECT blah FROM foo WHERE ((count > @count) AND (amount BETWEEN @minamount AND @maxamount))";
$command->Parameters->Add("@minamount", $minamount);
$command->Parameters->Add("@maxamount", 1000);
// let's forget to bind the count parameters
//$command->Parameters->add("@count", SOME_CONSTANT);
If i forget to bind one.. I get an error message that the parameters "@count" doesn't exist. Says (imho )more than parameter "1" does not exist.
Posted: Mon Apr 03, 2006 7:47 pm
by Ambush Commander
That's an interesting idea... maybe I'll do it (or maybe not... it requires so many more function calls)
We're all sure that @, ? and : have no meaning in SQL, right?
Posted: Mon Apr 03, 2006 9:57 pm
by Nathaniel
I would think that as long as they aren't escaped by mysql_real_escape_string or be likely to occur in a non-bind fashion*, it wouldn't matter.
* As in, you wouldn't want to use just numbers with nothing else, for example, because you might want to select "WHERE `flag` = '1'". But, duh.
Posted: Mon Apr 03, 2006 11:36 pm
by feyd
@ is a variable reference in MySQL.