LanX:

Pretty nifty concept. It got me thinking a bit, and I came up with a little thing like this:

#!env perl
#
#   ex_sql_placeholders_via_tied_variables.pl
#
use strict;
use warnings;
use PH:  # my fake placeholders package

# Tell PH which variables are to be converted into placeholders.
tie my $c1, 'PH';
tie my $c2, 'PH';
tie my $c3, 'PH';
tie my $c4, 'PH';

my $table='a_table';

sub the_sql { "select foo from $table where x=$c2 or y in ($c3, $c4, $
+c1)" };

# Prepare the statement
my $X = PH::prep(\&the_sql);

# Set up some values and execute:
($c2, $c3, $c4, $c1) = ('b','3','e','f');
PH::exec($X);

# Again...
($c1, $c2, $c3, $c4) = ('m','n','o','p','q');
PH::exec($X);
[download]

When I run it, I get:

$  perl ex_sql_placeholders_via_tied_variables.pl
PREPARED: select foo from a_table where x=? or y in (?, ?, ?)
EXEC(b, 3, e, f)
EXEC(n, o, p, m)
[download]

The placeholder package is:

package PH;

# Set to an empty array to indicate that we're preparing a statement
my $inside_prepare = undef;

sub TIESCALAR {
    my ($class, $var) = @_;
    return bless \$var, $class;
}

# When we're preparing a statement, we'll return '?' for a tied value,
# and push a reference to the variable on the inside_prepare vector
# so the values and placeholders line up
sub FETCH {
    my $self = shift;
    return $$self unless defined $inside_prepare;
    push @$inside_prepare, $self;
    return "?";
}

sub STORE {
    my ($self,$new_val) = @_;
    $$self = $new_val;
}

# Fake DBI prep-like statement
sub prep {
    # Tell FETCH() that we're prepping a statement
    $inside_prepare = [];

    # call the sub to make perl do the string interpolation and
    # build the vector of variable references that align with
    # the placeholders
    my $t = shift;
    my $retval = [ $t->(), $inside_prepare ];

    # Turn off preparation mode
    $inside_prepare = undef;

    print "PREPARED: $retval->[0]\n";
    return $retval;
}

# Fake DBI execute-like statement
sub exec {
    my $x = shift;
    print "EXEC(", join(", ", map { $$_ } @{$x->[1]}), ")\n";
}

1;
[download]

Mine isn't as fully-developed as yours, as I don't handle array types and such. I was just trying to find a way to simplify the interface as much as I could.

I'm not as happy with it as I could be: I couldn't find an easy way to make it defer evaluation of the string other than the ugly "pass a coderef" bit I show in the example. On the bright side, I didn't have to slog through the optree, either.

I look forward to what you ultimately come up with, as a way to simplify moving people string whacking to placeholders would be a nice thing to have.

...roboticus

When your only tool is a hammer, all problems look like your thumb.