Perl Hacks

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 be equivalent ...