This blog is also available on Gemini (What's Gemini?)

SQL::Functional Cookbook: Simple Select

2017-03-24

SQL::Functional[1] 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.