byterock

Complex Placeholders

In part 2 we has a look as the '?' or simple placeholder now we will have a look more complex placeholders.

There is nothing wrong with working with just the execute() statement and '?' placeholders, but sooner or later you will be forced to mix in-line SQL and parameter SQL, or have an SQL with so many parameters that the SQL statement will be unreadable and confusing.

bind_param Method

A statement like this comes to mind.

$sql = 'INSERT INTO table_1 values(field_1, field_2, field_3, field_4, month) select field_1, field_2, field_3, field_4, add_months(sysdate, ?) FROM locations WHERE (city = $city_1 or city = ?) AND country_id <> ? AND state_province <> ?';

$sth = $dbh->prepare($sql);
$sth->execute($var_1, $var_2, $var_3, $var_4);

Happily, the statement handle has a the bind_param() method so we can write code that strongly binds one parameter to a placeholder. So, given this code:

$sql = 'SELECT city FROM locations WHERE (city = ? or city = ?) AND country_id = ? AND state_province <> ?';

$sth = $dbh->prepare($sql);

We can use the bind_param() like this:

$sth->bind_param(1, "Rome");
$sth->bind_param(2, "New York");
$sth->bind_param(3, "US");
$sth->bind_param(4, "New York");

This will make your code much easier to read.

Named Placeholders

Most DBDs also allow you to use the colon ':' placeholder or 'Named Placeholder'. Using this placeholder makes your code very readable. The above code could be re-written something like this:

$sql = 'SELECT city FROM locations
WHERE
(city = :p_city_1 or city = :p_city_2)
AND country_id = :p_country_id
AND state_province <> p_state_province';

$sth = $dbh->prepare($sql);

$sth->bind_param(":p_city_1", "Rome");
$sth->bind_param(":p_city_2", "New York");
$sth->bind_param(":p_county_id", "US");
$sth->bind_param(":p_state_province", "New York");

In the case above, I could have used any string or numeric value after the ':' as long as it matches the value used in the bind_param() method. The use of the 'p_' prefix makes the code even more easier to read and maintain.

Another feature of the ':' placeholder is that it can be used to substitute the same value a number of times within your SQL. Given this SQL,

SELECT to_char(add_months(sysdate, 1), 'Month yyyy'), add_months(sysdate, 1), sysdate FROM dual

we could use the bind_param() method like

$sql = "SELECT to_char(add_months(sysdate, :p_add_months), 'Month yyyy'),
add_months(sysdate, :p_add_months),
sysdate
FROM dual";

$sth->bind_param(":p_add_months", $months_to_add);

As such, we need only make one call to the bind_param() method to make two substitutions.

The only problem with named params is that some DBDs do not support them so if you are trying to write DBI code that is transportable between DBs the '?' placeholder would be the way to go.

Placeholders are Single Scalars

You should remember that all placeholders are considered as a single 'scalar' value. So trying something like this.

my $sql='SELECT * FROM locations WHERE country_id in (?)';
my $sth = $dbh->prepare($sql);
$sth->execute('Canada','USA');

May not work as expected, while this will

my $sql='SELECT * FROM locations WHERE country_id in (?,?)';
my $sth = $dbh->prepare($sql);
$sth->execute('Canada','USA');

Some smarty pants out there like to use placeholders in places they do not really belong. So one can see a poor SQL like this is being abused

my $sql='SELECT state_province,?,? FROM locations WHERE country_id in (?,?)';
my $sth = $dbh->prepare($sql);
$sth->execute('city','zip','Canada','USA');

This is of course DBD dependent so no transportability here but to me it is just poor style of not knowing what you want before you go for it. Just as bad IMHO as using '*' in a select but that is more a question of 'SQL' rather than perl and DBI.

Next well have a look at non select statements.