First, that's not the final interface, just a prove of concept.

> maybe a (&) prototype might help

That's the plan, but for testing and demonstration I needed a lambda.

> "because that's how it works", ie, enforced cargo-culting.

I don't understand your point here.

There are different use cases at my job already

• we have a lot of legacy code without placeholders.
• colleagues are reluctant to adjust it
• some colleagues insist they need "named" placeholders
• I need a lot of boiler plate to prepare (?,?,...) lists

local $"=",";
my $sth = $dbh->prepare(
       "SELECT * FROM $table 
        WHERE lastname  = $lastname 
        AND   firstname IN (@firstnames)
       "
);
$sth->execute( $lastname, @firstnames );
[download]

I want to change it to something like:

my $sth = $dbh->xprepare( ph {
       "SELECT * FROM $_table 
        WHERE lastname  = $lastname 
        AND   firstname IN (@firstnames)
       "
});
$sth->xexecute();
[download]

(not sure yet, but methods can't have prototypes and DBI allows to hook in with callbacks)

> (access the variables outside their scopes, delay the interpolation), so that's magic where the implementation leaks into the calling context, the wrong kind of magic IMHO.

I don't get your point, nothing is leaking here. I'm overwriting the pad with references of new vars. This means all effects are restricted to the code block, which is only executed once.

My goals are:

• DRY-ness : avoiding repetition of code and boilerplates
• more flexibility and speed when SQL changes
• less errors
• better control over code quality

update

While writing this post I had to re-edit the "legacy" part twice, because I mistyped repeated variables.

And it's still wrong. ;-)

nothing is leaking here

Your code is implemented with closures because they let you replace the closed over variables. That's fine. But those closures must be created by the user, so either the user understands that your code is creating a closure, and that's what I call a leak (calling the tool correctly is done by knowing how it's implemented), or the user doesn't know that and just knows that this is how the call must be written, because reasons (that's where you get into cargo cult territory).

I think your proposed version with ph is nearly fine though, becauses it kind of hides that exposed implementation detail, and it's easier to abstract away as a magic block where interpolation becomes placeholders, unlike the version with sub where advanced features of a common keyword are used.

You didn't answer my question about package vars though, which is one of the potential issues with using closures: if you don't know that this is what happens, you don't understand that you only have to use lexicals (assuming your code doesn't work with package vars).

Also, what would the following do, search for Eily twice or work as intended?

my $username = 'LanX';
my $req1 = $db->xprepare(ph { "SELECT from users WHERE nick = '$userna
+me'" });
$username = 'Eily';
my $req2 = $db->xprepare(ph { "SELECT from users WHERE nick = '$userna
+me'" });
$req1->xexecute();
$req2->xexecute();
[download]

If they work as intended then the main issue might be package vars (unless your code works with them), and the fact that it looks like you have a more complicated syntax to do basically the same thing. That's again a good reason for ph { } rather than sub { }, several call to ph may work fine, but you have to be careful with sub. If it searches twice for Eily, this means that you have to understand how it works.

If you can ask your colleagues to change how the prepare is made, maybe you could do something like that instead:

my %vars = (rank => 'Pope', xp => 4);
my $req1 = $mytool->xprepare(ph { "SELECT from users WHERE rank = '$_{
+rank}' AND xp > '$_{xp}'" } %vars );
$req1->execute(); #There's no reason your new object can't call its me
+thod execute BTW
[download]

That way it looks like a more classical command BLOCK PARAMS (except instead of setting $_ before each call, it sets %_). And then the values can be stored as members of the returned object, or closed over by a closure that will set %_ before calling &BLOCK(), ie the implementations details stay hidden.

> You didn't answer my question about package vars though, which is one of the potential issues with using closures:

I read your post 3 times and I couldn't find a question about package vars. neither "our" nor "package"

Package vars are actually easier to handle than lexicals, that's why it's not included in the first demo.

PadWalker::peek_our and local are all you need.

Though I haven't thought it thru yet.

> if you don't know that this is what happens, you don't understand that you only have to use lexicals (assuming your code doesn't work with package vars).

with "if you" you mean "if one" or "me" ? (English is confusing sometimes)

> Also, what would the following do, search for Eily twice or work as intended?

Search "Eily" twice is intended, prepare statements are meant to speed up repeated execution, i.e. $username is normally set inside a loop.

for (qw/Lanx Eily/) {
   $username = $_;
   $req1->xexecute();
   $req2->xexecute();
}
[download]

You could also use a DBI::do otherwise which prepares and executes in one run.

Or one could also provide a prepare which stores the variables at preparation time, if really needed.

Again, I didn't have time to think about the best API, because DBI is vast and allows plenty of approaches.

> If they work as intended

Hmm I think DBI doesn't work like you intend it to

> the main issue might be package vars

I don't see a difference.

> my %vars = (rank => 'Pope', xp => 4);

well I could also allow that bound variables are overriden if xexecute gets arguments. (not sure if that's what you mean)

$req1->xexecute(username=>'value');

main issue

Look my main problem is that I want to inspect into interpolation in a sane way, that's why I inspect the closed over variables inside an anonymous sub and replace them.

Like this I don't need to write a SQL parser to find variables in strings. (the same technique can be used for other kind of embedded string DSL languages)

This placeholder{"string"} function is analysing the interpolation and

• returns an object with methods
• know the references of the interpolated variables
• know the names of the interpolated variables
• know the values of the variables at interpolation time
I have full flexibility to implement any wanted (your "intention") behaviour then.

The real problem is to identify the use cases and to find the right names for the methods.

update

but you are raising good points, thanks! :)

Cheers Rolf
_{(addicted to the Perl Programming Language and ☆☆☆☆ :)
Wikisyntax for the Monastery}