Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Devel::Declare type syntactic sugar for DBI select bind_col param

by Anonymous Monk
on Sep 30, 2012 at 11:44 UTC ( #996497=perlmeditation: print w/ replies, xml ) Need Help??

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"; }
SELECT $dbh, " :name, :email from ... where DIAPER LIKE ? ", 'soft and absorbant' { print "$name < $email >\n"; }
SELECT $dbh, ":name, :email from ... where DIAPER=?", 'soft and absorbant' { print "$name < $email >\n"; }
SELECT $dbh, " :name, :email from ... where DIAPER LIKE ? ", 'soft and absorbant' { print "$name < $email >\n"; }
$dbh->SELECT ":name, :email from ... where DIAPER=?", 'soft and absorbant' { print "$name < $email >\n"; }
$dbh->SELECT " :name, :email from ... where DIAPER LIKE ? ", 'soft and absorbant' { print "$name < $email >\n"; }
$dbh->SELECT( ":name, :email from ... where DIAPER=?", 'soft and absorbant', sub { print "$name < $email >\n"; });
$dbh->SELECT( " :name, :email from ... where DIAPER LIKE ? ", 'soft and absorbant', sub { print "$name < $email >\n"; } );
for my $quality ( 'soft', 'absorbant' ){ SELECT ":rec{name}, :rec{email} from ... where DIAPER=?", $quality + { print "$rec{name} < $rec{email} >\n"; } }
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"; } }
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

But my thinking isn't clear, any ideas?

Comment on Devel::Declare type syntactic sugar for DBI select bind_col param
Select or Download Code
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"; }

    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"; }

    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.

Re: Devel::Declare type syntactic sugar for DBI select bind_col param
by tobyink (Abbot) 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); };
    perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://996497]
Approved by moritz
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (12)
As of 2014-12-29 16:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (193 votes), past polls