October 25, 2006

Bind Variables

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.

Until next time...