blazar has asked for the wisdom of the Perl Monks concerning the following question:

<premise>
I have never received any formal education about relational databases. I think I'm a decent Perl programmer, though. Now my current work requires me to work a lot with relational db's. Moreover I don't know the structure of our own db('s) which is also an obstacle. However I'm slowly learning and thus beginnig to cope with both problems. Generally I try to stay "on the Perl" side as much as possible, rather than to delve into SQL.
</premise>

Now, I'm often using SQL::Abstract to build queries. Needless to say, it greatly helps me. But I have a problem: when all of the values are determined, SA gives me a $where clause with placeholders and a @bind array with the corresponding values. So far, so fine. OTOH sometimes I'd need, so to say, to leave some values unspecified at clause-buildup-time.

The reason why I want to do what I've described at the end of the last paragraph is that I'm writing a closure around a lexical $sth (that I'm using as a callback), and in the arguments passed to $sth->execute() I want to include some arguments passed to the closure itself.

Let me get straight: I do have a solution to the problem. But I find it inelegant, not terribly reliable and on the whole unpleasant. Currently I give to some of the values passed to the where() method a special formatting and I later reprocess @bind with an ad hoc sub to substitute the "unspecified" values with those I want.

All in all I'd like something that would hardcode in $where all the values that are, say, defined and put a placeholder if a value is undef.

Any idea?

Replies are listed 'Best First'.
Re: Question re SQL::Abstract (or something similar, whose existance I ignore!)
by xdg (Monsignor) on May 18, 2006 at 16:46 UTC

    It's possible that you're building up your query clauses too early. Why not just build up the "where" hash that SQL::Abstract wants and turn that into your statement and bind variable only just before running the query?

    Also, you might look at SQL::Interpolate. You can similarly just keep building up a list of clauses and then fire them all off to SQL::Interpolate at once right before you run your query.

    -xdg

    Code written by xdg and posted on PerlMonks is public domain. It is provided as is with no warranties, express or implied, of any kind. Posted code may not have been tested. Use of posted code is at your own risk.

[reply]
[d/l]
[select]

      I'll check SQL::Interpolate. Thank you!

      It's possible that you're building up your query clauses too early. Why not just build up the "where" hash that SQL::Abstract wants and turn that into your statement and bind variable only just before running the query?

      Because my code takes an anonymous closure as a callback, roughly along these lines:

      # of course this is just an excerpt of code.
            sub {
                my $caller=shift;
                $rf->execute(substvars(@bind, @_)) or
                  $caller->complain("Error executing this and that.");
                $rf->fetchall_arrayref;
            }

      [download]

      This is a closure around $rf and @bind. $rf is an object of the class DBIx::ContextualFetch::st.

      All in all this is a clean approach that I like. But I don't like the substvars() bit, which IMHO makes it dirty...

[reply]
[d/l]
[select]

        You want to predefine a bunch of default values for various slots in @bind, but want to fill the rest in later, right? I.e. you want to curry the bind values, but not necessarily in order? And is your issue with substvars its opacity?

        That general goal of defaults plus replacements tends to make me think "hash".

         
my %default_bind = ( age => 23, height => 75, birthday => undef );
my @key_order = qw( age height birthday );

sub {
    my $caller=shift;

    my @bind = @{ { %default_bind, @_ } }{ @key_order };

    $rf->execute(@bind) or
        $caller->complain("Error executing this and that.");

    $rf->fetchall_arrayref;
}

        [download]

        Or, assuming the order of arguments to the callback is well-defined, what about just doing the undef element replacement in-line? (Shown verbose for clarity):

        sub {
    my $caller=shift;
    $rf->execute( map { defined $_ ? $_ : shift @_ } @bind ) or
        $caller->complain("Error executing this and that.");
    $rf->fetchall_arrayref;
}

        [download]

        -xdg

        Code written by xdg and posted on PerlMonks is public domain. It is provided as is with no warranties, express or implied, of any kind. Posted code may not have been tested. Use of posted code is at your own risk.

[reply]
[d/l]
[select]
Re: Question re SQL::Abstract (or something similar, whose existance I ignore!)
by jZed (Prior) on May 18, 2006 at 15:47 UTC
    Why not just leave everything in @binds and then at execution time, loop through @binds replacing undefined values with your new ones? What about NULLs though? Neither your solution nor mine leaves the ability to insert things you *want* to be undefined. Maybe you should let SQL::Abstract build your $where and then ignore what it gives you for @binds and just use your own @binds at execution time. If these won't work for you and you use hard-coded values in the $where, don't forget to use $dbh->quote on them.
[reply]

      This is almost exactly what I'm doing. Except that to keep "consistent" I set the values I want to be "undefined" to '?'. As I wrote, I feel uneasy with this setup. Building the whole of @binds at execution time sounds like being more error prone, since if I change the clause, I will most probably also have to change the code that builds it, and if I forget to, then... I will screw it all up. I'd like to minimize the chances to do so instead!

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom