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

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! :)

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

Using the fact that I forgot to ask a question to not answer it, that's low :P.

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

OK, package vars not a problem then. Honestly my main concern was with the explicit use of closures, I didn't think this through so I didn't imagine what this could look like with the (&) prototype. I think it isn't as bad with the prototype version. At least I agree that there's no issue with package vars now that you've answered the question I didn't ask.

I wrote this:

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]

because in your example the variables where interpolated directly in the string fed to prepare. So unless that's with some version of perl with an extra layer of magic, the prepare is completly useless, which means probably not understood. So someone who wrote code with all the values in the prepare call might have written code like this:

my $username = 'LanX';
my $req1 = $db->prepare("SELECT from users WHERE nick = '$username'");
$username = 'Eily';
my $req2 = $db->prepare("SELECT from users WHERE nick = '$username'");
$req1->execute();
$req2->execute();
[download]

In which case you will get two different results. Someone who thinks your version is pretty much the same but is "optimized somehow" might fall into that trap. And that's counter intuitive anyway, people expect that when you use a variable in a statement, changing the variable later doesn't change the result of the previous statement. So by "as intended" I mostly meant by "as intended by a newbie". Notice that I called xprepare twice though, not once before changing the values like in your proposal. While it may be understood that calling xprepare once and changing the values will execute a different request each time, you could expect that calling xprepare again overwrites the biding, so the previously prepared queries are left unchanged. Maybe calling xprepare twice without calling xexecute should be forbidden (die), because according to your interpretation (twice the same result), the second call to xprepare is useless. If someone writes useless code on purpose, they probably don't understand what it does, in this case, they probably interpreted it like me (two different results).

prepare statements are meant to speed up repeated execution

They do speed up execution most of the time, but they can also do that across sessions, if the optimizer sees the exact same request prepared again, it can reuse the execution plan from the previous call and skip the evaluation phase. But the main advantage of bind variables is security, because they are protected against injection. So even if you had to call xprepare again each time before a xexecute, replacing the interpolated variables by bound variables would still be a huge benefit, that's why I didn't realize you were concentrating on the "one prepare, several executes" case.

BTW, since xprepare is supposed to be called instead of prepare, calling it several times in a row could change the values sent to execute without calling prepare again if the query string is the same. That way your colleagues could even write (with xdo to mean xprepare + xexecute):

$first_node = 1210590;
$first  = $dbh->xdo(ph { "SELECT * WHERE node_id = $first_node" }); # 
+Calls prepare and execute
$second_node = 1210586;
$second = $dbh->xdo(ph { "SELECT * WHERE node_id = $second_node" }); #
+ Same query, skips prepare, calls execute
$third_node = 1210578;
$third  = $dbh->xdo(ph { "SELECT * WHERE node_id = $third_node " }); #
+ Prepare still not called
[download]

Although the DB manager might already do that for you (ignore a prepare that it already processed). And since I'm putting myself in the place of someone who doesn't understand everything or even doesn't want to understand (because the starting point is people who are reluctant to rewrite the code with placeholders), I didn't do the smart thing and select all three at once.

> 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)

No I didn't think about that, but that's a good idea, I guess you could imagine something like that:

my %default = (rank => "Pope", xp => 4);
my $req = $dbh->xprepare(ph { "SELECT * FROM users WHERE rank = $_{ran
+k} AND xp > $_{xp} AND username != $_{username}" } %default);
$req->execute(rank => "Prior", username => "Eily");
[download]

Where xp uses the value bound during xprepare, rank is overwritten, and username is required because it doesn't have a default. At least that way if you want to replace some, or all values you don't have something that looks like you change a value after it was used. And when you reuse several times the same query in a row, some of the values might be left unchanged each time. And for this version to work you don't need to look into variables a closure closed over (ie accessing lexicals outside of their scope), prototypes are enough, and maybe tie if you want to die when a required parameter is missing (ie, when trying to read a value for a key that doesn't exist in %_)

FYI, probably more than half of that conversation happened in my head, because I realized things as I typed, and corrected myself and rewrote a lot. That's why my posts may lack questions I intended to ask, and be confusing. And I meant "you" as in "someone" BTW :D

That post is long enough, so I won't edit it to add new things :).

I just realized, among the advantages of the ph BLOCK [HASH] version (syntax that looks like the other command BLOCK PARAMS, not having to parse the opcode, access pads or lexicals out of their scope), it also means you don't need that rule with the leading _, just use any variable that's not %_:

my %default = (rank => "Bishop");
$dbh->xprepare(ph { "SELECT * FROM $table WHERE rank = $_{rank} AND le
+ngth(username) = 4" } %default)->execute(rank => "Prior");
[download]

I'll give it a try tomorrow, but I think that should be fairly easy to implement.

Please let me answer in chunks, without answering all questions at once, even the imaginated once. :P

> In which case you will get two different results.

I get your point, code can't compensate bad programing.

I'd say I'll offer two alternative versions of prepare (dynamic references vs static values) and emphasize on a do which does the execution after a prepare_cached right away.

>  Maybe calling xprepare twice without calling xexecute should be forbidden (die),

Nah, but I can throw a warning if two dynamic prepares bind the same refs.

>  but they can also do that across sessions, if the optimizer sees the exact same request prepared again,

Does it mean that prepare_cached from DBI is not necessary anymore?

In any case I think that "deinterpolated" templates with placeholders are easier to cache than queries with hardcoded $names.

Again, my emphasis ATM is on the "deinterpolater" and not the SQL api.

I could think of many use cases, like generating / refactoring to printf or various HTML templates.

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