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.