Re: How do Monks programatically construct SQL selects

[graff]

I followed the CPAN links mentioned in the first two replies, and those look like they are certainly worth trying out. (I'll try them myself next chance I get -- but when I need to do "production code", I tend to be a little skittish about modules whose current version number starts with "0." -- maybe that's just shyness or modesty on the part of the module authors, but I do have to wonder...)

As for the code you proposed, it seems more or less headed in the right direction, but maybe you're not going far enough in the amount of flexibility you're trying to achieve.

First off, you probably shouldn't be trying to handle the quotation marks around condition values in the "where" clause -- you'll have a problem when someone wants to look for a name like "O'Hara". Use "?" placeholders for the condition values, and pass the actual values as extra parameters when you execute the statement. (This will also make the process more secure.)

Also, it's risky to do a "select * from table" sort of query; it can happen that a column will be added to the table someday, or some other "event" will cause the database to return the columns in a different order from what the perl script was expecting. Ask for the specific columns you want, by name -- and if you don't know the column names, there are ways to find out what they are, and you should do that. (If you're using DBI to return each matching row into a hash, keyed by field name, then this is less of a problem.)

Finally, I'm not sure how much good it does you to generate the sql statement in a sub and then execute it somewhere else -- especially if you're using the "?" place-holders in the statement (as you should). It may be better for the sub to prepare and execute the statement as well.

Assuming you know how the table or RDB schema is defined, and can scope out the appropriate range of "options" for selection criteria, it's not that hard to set up a GUI or web interface to support on-the-fly creation of SQL for a "select" statement; your example, using a single table and conditions that are all conjoined by "and" is the easiest case -- consider that for each field, you need to present the name of the field (so the user knows what is being controlled), a pull-down menu to choose a suitable comparison operator, and a type-in box to provide a suitable value to test for.

• for the "name" and "department" fields, the "operator" choices could be '=', '!=', 'like', 'not like'
• for the "salary" field, the choices could be "=", "!=", "<", ">", "<=", ">=".

(Presumably, you could also provide "is null" and "is not null" for each field, either as separate choices on the "operator" menus, or by accepting the string "NULL" in the type-in box, with the "=" or "!=" operator.) It gets trickier if you're hoping to support joins on two or more tables, or you want to combine "and" and "or" conjunctions in a single query (which would require putting parens in the right places as you construct the statement).

Supposing that the user's specs are stored in a hash, keyed by field name, the code to create the sql statement could be something like this (which has not been tested, and would normally include some error checks at strategic points):

sub runquery {
    my ($dbh, $query, $conds) = @_;
    my @fields = keys %$conds;
    my @values = ();
    if ( @fields ) {
        $query .= " where ";
        $query .= join " and ", map { "$_ $$conds{$_}{op} ?" } @fields
+;
        @values = map { $$conds{$_}{val} } @fields;
    }
    my $sth = $dbh->prepare( $query );
    $sth->execute( @values );

    my $arref = $sth->fetchrow_arrayref; # (use your own favorite here
+)
    $sth->finish;
    return $arref;
}

# that sub would be used as follows:

my %qryconditions;  # to be set via GUI or CGI (or not)

# e.g. 
# = ( name =>   { op => '=', val => 'smith' },
#     salary => { op => '>', val => 50000 },
#     department => { op => 'not like', val => 'eng%' } )

# ... connect to DB (with $dbh as object handle), then:

# maybe there's a UI component to choose or create this as well:
my $qstr = "select name, salary, deparment from employees";

my $result_arref = runquery( $dbh, $qstr, \%qryconditions );
for my $row ( @$result_arref ) {
    print join( " ", @$row ), "\n";
}

Naturally, there are other ways to do this, and maybe the modules cited in earlier replies would make it all even easier. (Maybe you've noticed that I left out the handling of "is (not) null" conditions "as an exercise for the reader"; you could also consider offering options like an "order by" clause, etc.)

update: fixed a missing "=" in the sub