This is for those of us who are just too stubborn to use SQL front-ends like Class::DBI or DBIx::Recordset.

Over the last few months, I've taken to putting the fields I want to insert or select into a list placed inside a bareblock with the subroutine:

{
    my @FIELDS = qw( field1 field2 field3 );

    sub run_insert 
    {
        my $dbh = shift || return;
        my ($field1, $field2, $field3) = @_;

        my $sql = q/
            INSERT 
            ( / . 
                join(',' @FIELDS) . 
            q/)
            INTO some_table 
            VALUES 
            ( *** PLACEHOLDERS HERE ***)
        /;
        my $sth = $dbh->prepare($sql);
        $sth->execute($field1, $field2, $field3);
        $sth->finish;
    }
}
[download]

I've gone through a few different tricks for getting the placeholders into the SQL. Initialy, it was an ugly while loop which made each field into a '?', adding a comma unless it was the last element of the list.

It wasn't long before I hit myself over the head and used a join/map construct instead:

$BEGINING_SQL . 
join(',', map('?', @FIELDS)) . 
$ENDING_SQL;
[download]

Much nicer than the while loop. However, map still scales linearly, so this will be slow on large tables or when calling the subroutine sequentially.

Not knowing about how the x operator works in list context, I recently saw golf entry by BrowserUK which enlightened me to a new way of building the placeholders:

$BEGINING_SQL . 
join(',', '?' x @FIELDS) . 
$ENDING_SQL;
[download]

This method is fast, as the following benchmark shows:

#!/usr/bin/perl
use strict;
use warnings;
use Benchmark qw( cmpthese );

my $count = shift || 100000;
my @test_array = (0 .. 99);

sub map_bench 
{
    my @array = map { '?' } @test_array;
}

sub x_bench 
{
    my @array = '?' x @test_array;
}

cmpthese($count, {
    map => \&map_bench, 
    x   => \&x_bench, 
});


__OUTPUT__
Benchmark: timing 100000 iterations of map, x...
       map: 76 wallclock secs (76.75 usr +  0.00 sys = 76.75 CPU) @ 13
+02.93/s (n=100000)
         x:  1 wallclock secs ( 1.91 usr +  0.00 sys =  1.91 CPU) @ 52
+356.02/s (n=100000)
       Rate   map     x
map  1303/s    --  -98%
x   52356/s 3918%    --
[download]

This method saves a few characters, and I don't think its any less clear than the map style (a quick glance at perlop will enlighten people unfamiler with the specifics of x). Increasing @test_array's size to 1000 elments shows a small but significant slowdown in x's speed (about 30k runs per second on my dev system), while map is reduced to around 100 runs per second (about a ten-fold decrease, which should be expected since we're increasing the array's size by 10).

----
I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
-- Schemer

Note: All code is untested, unless otherwise stated

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.