Hack #24. Query Databases Dynamically Without SQL
Write Perl, not SQL.
SQL is a mini-language with its own tricks and traps. Embedded SQL is the bane of many programs, where readability and findability is a concern. Generated SQL isn't always the answer either, with all of the quoting rules and weird options.
In cases where you don't have a series of fully baked SQL statements you always runâwhere query parameters and even result field names come from user requests, for exampleâlet SQL::Abstract
do it for you.
The Hack
Create a new SQL::Abstract
object, pass
in some data, and go.
Suppose you have a reporting application with a nice interface that allows people to view any list of columns from a set of tables in any order with almost any constraint. Assuming a well-factored application, the model might have a method resembling:
use SQL::Abstract; sub get_select_sth { my ($self, $table, $columns, $where) = @_; my $sql = SQL::Abstract->new(â); my ($stmt, @bins) = $sql->select( $table, $columns, $where ); my $sth = $self->get_dbh(â)->prepare( $stmt ); $sth->execute(â); return $sth; }
$table
is a string containing the name of the table (or view, preferably) to query, $columns
is an array reference of names of columns to view, and $where
is a hash reference associating columns to values or ranges.
If a user wants to query the users
table for login_name
, last_accessed_on
, and email_address
columns for all users whose signup_date
is newer than 20050101
, the calling code might ...
Get Perl Hacks now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.