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.