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]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.