| Bind Variables | KEVIN RESCHENBERG
10-25-2006 |
Have you used the SQLExec function in PeopleCode? If so, you know that
it is simple, flexible and powerful (and correspondingly dangerous—use
with caution). SQLExec takes any arbitrary string and executes it as
SQL. This string can be built up piece by piece from other variables,
or it can contain bind variables that mark the locations of values
to be inserted into the SQL statement. Which method is better?
First, here are two versions
of a simple example to show the difference:
SQLExec("SELECT OPRCLASS FROM PSOPRDEFN WHERE OPRID = '" | &oprid | "'",
&oprclass);
SQLExec("SELECT OPRCLASS FROM PSOPRDEFN WHERE OPRID = :1", &oprid,
&oprclass);
The first version builds the SQL statement by concatenating
the variable value (&oprid) with other text. The result would be a
statement like this:
SELECT OPRCLASS FROM PSOPRDEFN WHERE OPRID = 'JOHN_DOE'
There are many reasons to prefer bind variables. First, they
make the code more readable in many (but not all) cases. They can also make
it easier to write the code, as you can see when you consider how the single
and double quotes are intermixed in the first example. But there are other
important reasons to use them.
Note that the operator ID in this example was JOHN_DOE. What if it had been
MARY_O'CONNOR instead? That embedded quote would have broken the SQL statement
if it had been built up as one string, as in the first example. But the bind
variable method handles this situation without any problems. This also
avoids the issue of SQL injection attacks, a possible security risk whenever
we are building SQL from concatenated strings.
There is also a performance benefit to be realized from using bind variables.
The database can cache the execution plan for a statement that doesn't change.
With bind variables, the statement itself is the same each time it is
submitted for execution. But if we construct a SQL statement by concatenating
values together, the database will see it as a new statement and will need
to determine an execution plan again each time it is submitted.
Bind variables are available for other uses as well—in Message Catalog
entries, for example. You also use them all of the time in SQR, maybe without
even realizing it:
begin-select
OPRCLASS
...
FROM PSOPRDEFN
WHERE OPRID = $oprid
end-select
The SQR syntax is different, but it's the same concept. Run your SQR with the
"-S" flag and you will see the bind variables in the SQL that was actually
submitted to the database.
|
