SQL::Functional Cookbook: Simple Select

2017-03-24

SQL::Functional now has reasonably complete converge of common SQL statements. There’s always going to be something else to add, but the main thing it needs now is a cookbook of standard cases. Along those lines, I thought I’d start up a series of blog posts that can be compiled into a complete cookbook.

We’ll start with a basic SELECT statement.

One of SQL::Functional‘s strong points is in making easy jobs easy. Its other strong point, making hard jobs possible, will be covered later.

    my ($sql, @sql_params) = SELECT [qw{ bar baz }],
        FROM( 'foo' ),
        WHERE match( 'bar', '=', 1 );

Let’s break down what’s actually going on. The SELECT function takes a scalar for all the fields, so we have to pass it as an arrayref. FROM takes a list of tables, which we might do for joins, but we’ll just pass it one for now. WHERE takes a list of clauses.

Ideally, we could say something like:

WHERE bar = 1

But that would require deep Perl voodoo (e.g. source code filters), so we content ourselves with the match function to build the same thing. It also helps us here by having the matched data passed as a placeholder.

In the end, $sql will contain:

SELECT bar, baz FROM foo WHERE bar = ?

With @sql_params containing the data for all the placeholders. We can run this through DBI like any other statement:

    my $dbh = DBI->connect( ... );
    my $sth = $dbh->prepare_cached( $sql )
        or die "Error preparing statement: " . $dbh->errstr;
    $sth->execute( @sql_params )
        or die "Error executing statement: " . $sth->errstr;
    while( my @row = $sth->fetchrow_array ) {
        say join ', ', @row;
    }
    $sth->finish;

Easy.