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.

> That post is long enough,

yes, (tomorrow maybe =)

> but I think that should be fairly easy to implement.

yes, with Tie::Hash I suppose.

goodnight! :)

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

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

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

It depends on the DB manager that DBI calls.

I did try my own version (yes I have a funny notion of what "tomorrow" means :). It relies on a tied hash that keeps track of the accessed keys rather than parsing the optree. And while there are two closures in my examples, if the output of ph is fed directly to xprepare or xdo, it is invisible (and if the user tries to access the output of ph, you get a subref, so it's easier to expect the closure behaviour).

use strict;
use warnings;
use feature "say";

# Hash where every value is "?", but that remembers the order in which
+ the keys where accessed
package PlaceHolderHash
{
  use Tie::Hash;
  our @ISA = qw(Tie::StdHash);

  my %use_order;

  sub FETCH
  {
    my $self = shift;
    push @{ $use_order{$self} }, shift;
    "?";
  }

  sub get_required
  {
    $use_order{$_[0]};
  }
}

# A dummy DBI that prints "PREPARE $string" instead of actually callin
+g prepare
package MyDBTemplate
{
  use Data::Dump qw( pp );
  sub new
  {
    bless {};
  }

  sub prepare 
  { 
    say qq<PREPARE "$_[1]">;
  }

  sub xprepare 
  {
    my ($self, $params) = @_;
    $self->prepare($params->{Prepare});
    
    my %query = %{$params}{qw(Default Required)}; # Yeah, hash-slice!
    bless \%query, 'MyQuery';
  }

  sub xdo 
  {
    my $self = shift;
    $self->xprepare(@_)->xexecute();
  }
}

# A dummy query object that prints "EXECUTE $string" instead of actual
+ly calling execute
package MyQuery
{
  sub execute
  {
    my $self = shift;
    local $" = ", ";
    say "EXECUTE (@_)";
  }

  sub xexecute
  {
    my $self = shift;
    my %values = (%{$self->{Default}}, @_);
    my @required = @{ $self->{Required} };
    exists $values{$_} or die "Param $_ required but not defined" for 
+@required;
    $self->execute(@values{@required});
  }
}

# called as ph BLOCK HASH, 
# returns a hash with 
# - Prepare: the string to send with prepare()
# - Default: the default value for named placeholders
# - Required: the list of required values, and the order they were use
+d in
sub ph(&%)
{
  tie my %prepare, 'PlaceHolderHash';
  local *_ = \%prepare;
  my $sub = shift;

  { 
    Prepare   => $sub->(),
    Default   => { @_ }, 
    Required  => (tied %prepare)->get_required()
  };
}

my $dbh = new MyDBTemplate;
my %default = (a => "A", b => "X");
my $table = "table";
say "First query";
# Table isn't part of %_, so do not use a place older
my $query = $dbh->xprepare(ph { "SELECT * FROM $table WHERE _a = $_{a}
+ OR _b1 = $_{b} AND _c = $_{c} AND _b2 = $_{b}" } %default);
$table = "I can change it now, it doesn't matter";

eval { $query->xexecute() } or print "$@";
$query->xexecute(b => "B", c => "C");
$query->xexecute(b => "B2", c => "C2");

say "\nSecond query";
my %values = (rank  => 'Prior',
              xp    => 42);
$table = "users";
$dbh->xdo(ph { "SELECT * FROM $table WHERE rank = $_{rank} AND xp > $_
+{xp}" } %values);
[download]

First query
PREPARE "SELECT * FROM table WHERE _a = ? OR _b1 = ? AND _c = ? AND _b
+2 = ?"
Param c required but not defined at pm_1210536.pl line 71.
EXECUTE (A, B, C, B)
EXECUTE (A, B2, C2, B2)

Second query
PREPARE "SELECT * FROM users WHERE rank = ? AND xp > ?"
EXECUTE (Prior, 42)
[download]