Introducing Bind Variables and Optimizations
As we indicated in Section 9.1.1, creating a statement in DBI is a two-step process: first you create a query, then you create a statement handle from the query. One reason for this division is a complication in SQL called bind variables , which are a kind of placeholder. Instead of specifying every field and value literally in a query, such as:
SELECT author FROM Titles WHERE ISBN = '156592567X'
you can create a generalized query with a question mark as a bind variable:
SELECT author FROM Titles WHERE ISBN = ?
and plug in 156592567X
or any other ISBN you want
before executing the statement.
Databases operate much more efficiently if you create a general
statement containing bind variables and issue repeated queries or
updates with different values for the bind variables. This is because
the database compiles the SQL statement into an internal format when
you create the statement and reuses the internal version on each
query or update. This compilation is shown as the second and third of
the three phases, parsing and optimization, in Figure 5-1. Perl DBI performs compilation in the
prepare(
)
statement. If you use bind
variables, you must plug in the values between the prepare(
)
and the execute(
)
statements.
We’ll show a trivial example using a bind variable in this section. More sophisticated examples appear later in the chapter.
The new example alters the previous example to use a
WHERE
clause in the SELECT
statement so that we can ...
Get Managing & Using MySQL, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.