http://www.perlmonks.org?node_id=996497

I've been bouncing some ideas around after reading these: Tao::DBI::st, DBIx::bind_param_inline, Method::Signatures, Devel::Declare, DBI recipes#Binding columns and fetching 
SELECT $dbh,
     ":name, :email from ... where DIAPER=?",
     'soft and absorbant'
{
    print "$name < $email >\n";
}

[download]
 
SELECT $dbh,
     " :name, :email
      from ...
      where
        DIAPER LIKE ?
      ",
     'soft and absorbant'
{
    print "$name < $email >\n";
}

[download]
 
SELECT $dbh,
     ":name, :email from ... where DIAPER=?",
     'soft and absorbant'   {
    print "$name < $email >\n";
}

[download]
 
SELECT $dbh,
     " :name, :email
      from ...
      where
        DIAPER LIKE ?
      ",
     'soft and absorbant'   {
    print "$name < $email >\n";
}

[download]
 
$dbh->SELECT
     ":name, :email from ... where DIAPER=?",
     'soft and absorbant'
{
    print "$name < $email >\n";
}

[download]
 
$dbh->SELECT
     " :name, :email
      from ...
      where
        DIAPER LIKE ?
      ",
     'soft and absorbant'
{
    print "$name < $email >\n";
}

[download]
 
$dbh->SELECT(
     ":name, :email from ... where DIAPER=?",
     'soft and absorbant',
sub {
    print "$name < $email >\n";
});

[download]
 
$dbh->SELECT(
     " :name, :email
      from ...
      where
        DIAPER LIKE ?
      ",
     'soft and absorbant',
sub {
    print "$name < $email >\n";
} );

[download]
 

for my $quality ( 'soft', 'absorbant' ){
    SELECT ":rec{name}, :rec{email} from ... where DIAPER=?", $quality
+ {
        print "$rec{name} < $rec{email} >\n";
    }
}

[download]
Say these are lines 2 3 4 5 6 in file foo.pl 
for my $quality ( 'soft', 'absorbant' ){
    SELECT ":{name}, :{email} from ... where DIAPER=?", $quality {
        print "$rec{name} < $rec{email} >\n";
    }
}

[download]
So through some Devel::Declare type magic that becomes 
{
    my %rec = (
        name => undef,
        email => undef,
    );
# line 3 foo.pl
    my $sth = $dbh->prepare( "SELECT name, email from ... where DIAPER
+=?" );
# line 3 foo.pl
    $sth->execute( $quality );
    
    $sth->bind_col(1, \$rec{name});
    $sth->bind_col(2, \$rec{email});
    
    while( $sth->fetchrow_arrayref ){
# line 4 foo.pl
        print "$rec{name} < $rec{email} >\n";
    }
}
# line 7 foo.pl

[download]

But my thinking isn't clear, any ideas?

Replies are listed 'Best First'.
Re: Devel::Declare type syntactic sugar for DBI select bind_col param
by moritz (Cardinal) on Sep 30, 2012 at 14:45 UTC
    But my thinking isn't clear, any ideas?

    I guess you are looking for feedback on the design, not the implementation. So here are some thoughts:

    • If stuff is bound to variable $email, the variable should appear like that in the select statement
    • Interpolation of variables should probably still be possible, so you need some syntax beyond just the variable name to make it clear which variables are bind variables and which ones are interpolated
    • SQL column names and column expressions are not confined to Perl identifiers, so you need some (optional) syntax for aliasing non-perl-identifier column descriptions to Perl variables

    Given these constraints, I'd propose something along the lines of 

    $dbh->SELECT(':$name, :$email, :$duration(from - to) FROM table WHERE 
+duration > $min_duration' {
    say "$name <$email> has spent $duration days here";
}

    [download]

    Which would desugar to something like 

    $dbh->prepare('SELECT name, email, from - to AS duration FROM table WH
+ERE duration > ?');
$dbh->bind_columns(\my ($name, $email, $duration);
$dbh->execute($min_duration);
while ($sth->fetch) {
    say "$name <$email> has spent $duration days here";
}

    [download]

    Maybe you also want to add another syntax for interpolating variables that are piped through $dbh->quote_identifer instead of placeholders, for when you need to interpolate the table name.

    Perl 6 - the future is here, just unevenly distributed
[reply]
[d/l]
[select]
Re: Devel::Declare type syntactic sugar for DBI select bind_col param
by tobyink (Canon) on Oct 01, 2012 at 12:37 UTC

    Just by subclassing DBI::db and using Perl::MethodCallWithBlock I believe you could achieve:

    $dbh->SELECT(
  [qw( name email )],
  from  => 'employees',
  where => { id => 42 },
) {
    printf("%s <%s>\n", $_->name, $_->email);
};

    [download]
    perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'
[reply]
[d/l]

Back to Meditations