Re: where clause determined at runtime

[prev] [thread] [next] [Date index for 2005/01/26]

On Wed, 2005-01-26 at 13:56 -0500, Warren Pollans wrote:
> I have several queries where the nature of the where clause is not 
> known until runtime.  I have separate queries defined for each 
> possibility - and select the appropriate one based on conditions at 
> runtime - ie, user input.
[...]
> QUESTION: Is there a "better" way of doing this?  - perhaps by, 
> somehow, constructing/modifying the sql at runtime?

If you only have a couple of queries, what you're doing is fine.  If you
have lots of them, you should do something fancier.

When I have this situation, I grab the database handle and run my
generated SQL (using prepare_cached, if I expect it to get repeated) and
then pass it through sth_to_objects() just like the search_* methods
would.

sub runtime_query {
    my $self = shift;

    my $dbh = $self->db_Main();
    my $sql = $self->generate_some_sql();
    my $sth = $dbh->prepare_cached($sql);

    $sth->execute(); # maybe pass bind params here
    my @results = $self->sth_to_objects($sth);
    return @results;
}

- Perrin