byterock

Queries That Return No Rows

In part 3 we look as advanced place holders, now we are going to look at non select statements.

Many SQL statements do not return data, and DBI handles those as well. One could code such a statement like this:

$sth = $dbh->prepare("UPDATE locations SET city = 'Ottawa' WHERE location_id = 1800"); $sth->execute();

However, this is a waste of resources or perhaps some typing, since you are creating a statement handle for a query that returns no rows. So the database handle has a built-in shortcut for just such a case. This is the do() method. The code below shows how it is used.

$rows_affected = $dbh->do("UPDATE locations SET city = 'Ottawa'
WHERE location_id = 1800");
print "Rows Affected = $rows_affected\n";

As an added bonus many drivers will return the number of rows affected by the query or –1 if an error occurred.

This shortcut 'do' function is great for doing DLL tasks however it does not take params so its usefulness is somewhat limited.

Placeholders for non Select Queries

The great thing about DBI is the Placeholders for Non Select Queries work in exactly the same way as 'Select' statement except they do not return any records but may return the number of rows effected.

It can even be used with params if you want by simply adding the bind values at the end of the method after a hashref for attributes

$rows_affected = $dbh->do("UPDATE locations SET city = ?
WHERE location_id = ?",undef,'Ottawa',1800);
print "Rows Affected = $rows_affected\n";

However the Do is just a shortcut which would be the exact same as

$sql = "UPDATE locations SET city = ?
WHERE location_id = ?";

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

$sth->execute('Ottawa','1800');

print "Rows Affected = ".$sth->rows();