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

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]