On Tue, Feb 28, 2006 at 05:48:36PM -0500, Chris Drozdowski wrote:
> I have a three quick PDO questions.
>
> 1) If a PDOStatement is created within a function or method, is it's
> server result set connection is automatically freed up when the
> function returns (I assume so, but want to make sure)?
Correct. Assuming you dont return it or assign a function input
variable that is being passed by reference.
>
> 2) Does setting a PDOStatement to null (PDOStatement = null)
> terminate the server result set connection (I assume so, but want to
> make sure)?
I assume you mean a variable that contains a PDOStatement:
$stmt = $dbh->prepare($sql);
$stmt = null;
Yes this is correct. The one thing to keep in mind though is the
above will be only true if you havn't assigned $stmt to another
variable:
$another_stmt = $stmt;
$stmt = null;
The result set connection will still exist, until all variables
referencing to the object are unset/null'd
>
> 3) The documentation for PDO::quote encourages us to use prepared
> statements over the PDO::query() or PDO::exec() methods citing the
> economy and portability of prepared statements. For queries that will
> run only once, is there significantly more overhead using prepared
> statements rather than the PDO::query() or PDO::exec() methods? I
> know that that's kind of an unclear/subjective question, but I just
> don't know how to state it better.
The key points addressed in the docs with using prepare instead:
* portable
* immune to sql injection
* speed
As far as portable, as noted in the next paragraph, the ODBC driver
doesn't support the quote() method, so if you are crafting a sql
by hand (within your app) you will have to know what driver you are
dealing with.
Also, using just a prepare/execute method, it makes your code much
easier to allow for abstract database servers. This is why i like
pdo so much, it isn't an abstract layer but allows you to build a
common interface that can be abstracted.
The Immune to sql injection. This is rather straight forward, you
just set up your query with input variables and assign the
variables to the input variables:
$sql = "select * from some_table where userid = ? and name = ?";
$stmt = $dbh->prepare($sql);
$stmt->bindValue(1, $_GET['userid'], PDO::PARAM_INT)
$stmt->bindValue(2, $_GET['name'], PDO::PARAM_STR);
With the above code you dont have to worry about escaping data nor
how it is needed to quote the variable, it is handled by the
driver.
With speed, benchmarks will only show a difference if you have code
like:
$sql = "select * from some_table where id = ?";
$stmt = $dbh->prepare($sql);
foreach(array(1,2,3) as $id) {
$stmt->bindValue(1, $id, PDO::PARAM_INT);
$stmt->execute();
}
vs.
foreach(array(1,2,3) as $id) {
$sql = "select * from some_table where id = " . (int) $id;
$stmt = $dbh->query($sql);
}
The speed difference is with query() and the server has to parse
the query to make sure it is valid and then execute within each
iteration of the loop. With the prepare/execute method, the server
just parses the query once. I would assume stored procedures or
views benefit more with this method since they are a bit more
complicated to parse.
HTH,
Curt.
--
cat .signature: No such file or directory
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
