Prepared statement / bind SQL - :1 or ?
Moderator: General Moderators
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
Prepared statement / bind SQL - :1 or ?
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
Which do you prefer? Are there any definite advantages to using one over the other?
Jcart | I've added the requested option
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
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...
(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)
What you'd have to do, in that case, is use something like...
Code: Select all
$statement->setString(1, $string);
$statement->setFloat(2, $float);
//...Edit - So that's the difference! Does that mean this poll is null (if I remember correctly, MySQL doesn't have prepared statements)
May I suggest both?
I say this because I recently came on a situation where I had a variable number of statements to prepare...
I realize now that allowing question marks would be useful for such situations, although possibly confusing to someone else using your code...
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);
?>- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
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);
}I prefer named parameters instead of numbered ones... (ADO.net style)
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.
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);- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US