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
statement. If you use bind
variables, you must plug in the values between the
) and the
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 ...