I really don't like that it uses closures to be honest, because closures are a totally unrelated concept that would be needed to understand why declaring the request suddenly needs to be done with that extra sub layer (maybe a (&) prototype might help). Or that would mean using the syntax "because that's how it works", ie, enforced cargo-culting. And even if you understand closures, it's not really their normal use (potentially shared, static-like variables) but hacking around their implementation and side effects (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.

If tying the variables allows you to remove the closures I think that's fine, but otherwise if using your tools requires tied variables and closures, that would be too many advanced concept to make using DBI "simpler".

Upvoted for constructive critic! :)

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
Legacy code looks like

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

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

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.

LanX:

Pretty nifty concept. It got me thinking a bit, and I came up with a little thing like this:

#!env perl
#
#   ex_sql_placeholders_via_tied_variables.pl
#
use strict;
use warnings;
use PH:  # my fake placeholders package

# Tell PH which variables are to be converted into placeholders.
tie my $c1, 'PH';
tie my $c2, 'PH';
tie my $c3, 'PH';
tie my $c4, 'PH';

my $table='a_table';

sub the_sql { "select foo from $table where x=$c2 or y in ($c3, $c4, $
+c1)" };

# Prepare the statement
my $X = PH::prep(\&the_sql);

# Set up some values and execute:
($c2, $c3, $c4, $c1) = ('b','3','e','f');
PH::exec($X);

# Again...
($c1, $c2, $c3, $c4) = ('m','n','o','p','q');
PH::exec($X);
[download]

When I run it, I get:

$  perl ex_sql_placeholders_via_tied_variables.pl
PREPARED: select foo from a_table where x=? or y in (?, ?, ?)
EXEC(b, 3, e, f)
EXEC(n, o, p, m)
[download]

The placeholder package is:

package PH;

# Set to an empty array to indicate that we're preparing a statement
my $inside_prepare = undef;

sub TIESCALAR {
    my ($class, $var) = @_;
    return bless \$var, $class;
}

# When we're preparing a statement, we'll return '?' for a tied value,
# and push a reference to the variable on the inside_prepare vector
# so the values and placeholders line up
sub FETCH {
    my $self = shift;
    return $$self unless defined $inside_prepare;
    push @$inside_prepare, $self;
    return "?";
}

sub STORE {
    my ($self,$new_val) = @_;
    $$self = $new_val;
}

# Fake DBI prep-like statement
sub prep {
    # Tell FETCH() that we're prepping a statement
    $inside_prepare = [];

    # call the sub to make perl do the string interpolation and
    # build the vector of variable references that align with
    # the placeholders
    my $t = shift;
    my $retval = [ $t->(), $inside_prepare ];

    # Turn off preparation mode
    $inside_prepare = undef;

    print "PREPARED: $retval->[0]\n";
    return $retval;
}

# Fake DBI execute-like statement
sub exec {
    my $x = shift;
    print "EXEC(", join(", ", map { $$_ } @{$x->[1]}), ")\n";
}

1;
[download]

Mine isn't as fully-developed as yours, as I don't handle array types and such. I was just trying to find a way to simplify the interface as much as I could.

I'm not as happy with it as I could be: I couldn't find an easy way to make it defer evaluation of the string other than the ugly "pass a coderef" bit I show in the example. On the bright side, I didn't have to slog through the optree, either.

I look forward to what you ultimately come up with, as a way to simplify moving people string whacking to placeholders would be a nice thing to have.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

Thanks, I had already similar Tie::Array code but was struggling with the antics of the perltie implementation.

But TIMTOWTDI ...

I had the idea that blessing the variables into a package which overload stringification '""' should be even easier and more precise, because only placeholders inside strings would be caught.

Unfortunately I couldn't make this work without having leaking effects.° :(

Good news is, that at least the tie approach works with pads.

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

update

°) found the bug right after posting this :)

probably it's better to only ignore scalar references.

"SELECT * FROM $$table"

is a better visual hint than

"SELECT * FROM $_table"

and migrating old code is probably easier then....

A custom function

no_placeholders($table, VARLIST)

might help too.

#prepare
my $sth = $dbh->prepare('SELECT * FROM people WHERE lastname = ? AND f
+irstname = ?');

#execute with list of bindvars
$sth->execute( $lastname, $firstname );
[download]

But it's a bit cumbersome to adjust the bind values if the order chang
+es.
[download]

Re placeholders: PostgreSQL's DBD::Pg has an attribute 'pg_placeholder_dollaronly' which when set to 1 makes placeholdering (ordering or repeating) a little easier (fsvo easy):

$dbh->{pg_placeholder_dollaronly} = 1; # default is 0
my $sth = $dbh->prepare('SELECT * FROM people WHERE lastname = $1 AND 
+firstname = $2');
[download]

Native Postgresql uses the dollar-integer syntax for placeholders in prepared queries and doesn't support question marks at all, that is emulated by DBD::Pg. That is why you have to take care when you use question marks in SQL comments or as an operator, you have to escape them.

for ?-containing operators, of course.

But I can't find a way via DBI to let a postgres server complain about a questionmark in comments. (tried server versions 14, 15, 16). This kinda makes sense because '--'-style comments used to be removed, although '/* */'-style comments were kept. (btw, this will be changed (to both included) in next Pg version, 15)

(It would be another good reason to use $dbh->{pg_placeholder_dollaronly} = 1;. To me the operator-with-questionmark case is reason enough to use it)

as far as I understand you will still need to do $sth->execute( $lastname, $firstname );

hence I don't know how this is easier, apart of (probably) being able to change the order?

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