Re: SQL parser from fragments

I puzzled with this same issue a few days ago, and received similar advice from a co-worker: put it together as you go. Below is my routine, although I went through and tried to make it more generic (query was for an experimental interface into an internal database used at work). Comments contain more details about what was expected, etc. Still a large chunk of code, if you have lots of fields, but perhaps it might give you an idea. (Comments and suggestions appreciated.)

Update: samgold, my thanks for your catching that bit of code in your response. As I stated, this was code I had attempted to make more generic before posting. In the original code, there were several such sections. That code, as it appears, should rightly have occurred just above the

$lstatement .= "AND " if ($count);

, as it does in the other if blocks. I commented it out below (rather than removing it, so others could understand the thread), and made the appropriate change. My thanks for catching it.

#
# This code snippet assumes a MySQL database table containing two fiel
+ds 
# (field1, perhaps a string, and field2, a string representing an IP 
# address) and a timestamp (field3).
#
# The CGI object $q (made available perhaps by "use vars qw($q);") may
+ 
# contain values for param1 and param2 ("like" or "exact" SQL search),
+ 
# param1value and param2value (search values corresponding to field1 a
+nd 
# field2), and/or the sets (beforeMonth, beforeDay, and beforeYear) an
+d 
# (afterMonth, afterDay, and afterYear), which correspond to a value f
+or 
# field3 to before or after, respectively. Values must occur in the 
# particular field for its inclusion in the query. Searches default to
+ 
# "exact" unless "like" is specified. If no values are given, a query 
+to 
# return all values. Results of the query would be ordered first on th
+e 
# timestamp, then on the IP address.
#
# Code would be called as "$statement = &assemble_statement('logtable'
+);", 
# for example.
#
sub assemble_statement {
    my ($table) = @_;

    my %tableinfo = { field1 => param1, field2 => param2 };

    my $lstatement = "SELECT ";
    $lstatement .= "field1, field2, date_format(field3, '%Y-%m-%d %H:%
+i:%S') ";
    $lstatement .= "FROM $table ";
    my $order_by = "ORDER BY field3, inet_aton(field2) ;";

    my $count = 0;
    my ($parameter);

    if (
        (
            defined( $q->param( $tableinfo{"field1"} . "value" ) )
            and ( length( $q->param( $tableinfo{"field1"} . "value" ) 
+) )
        )
        or ( defined( $q->param( $tableinfo{"field2"} . "value" ) )
            and ( length( $q->param( $tableinfo{"field2"} . "value" ) 
+) ) )
      )
    {
        #
        # Commented out and corrected thanks to comment
        #   from samgold
        #
        # $lstatement .= "WHERE " unless ($count);
        #

        foreach my $k ( keys(%tableinfo) ) {
            if ( ( defined( $q->param( $tableinfo{$k} ) ) )
                and ( length( $q->param( $tableinfo{$k} ) ) ) )
            {
                #
                # "WHERE" line added to fix issue pointed
                #   out by samgold
                #
                $lstatement .= "WHERE " unless ($count);
                $lstatement .= "AND " if ($count);
                $lstatement .= "($k ";
                if ( $q->param( $tableinfo{$k} ) eq "like" ) {
                    $lstatement .= "LIKE ";
                }
                else {
                    $lstatement .= "= ";
                }
                $parameter = $q->param( $tableinfo{"field1"} );
                foreach ( 0 .. 1 ) {
                    $parameter = reverse($parameter);
                    $parameter =~ s/\s+$//g;
                }
                $lstatement .= "\"" . $parameter . "\") ";
                $count++;
            }
        }
    }

    if (
        (
            (
                defined( $q->param("beforeMonth") )
                and ( length( $q->param("beforeMonth") ) )
            )
        )
        and (
            (
                defined( $q->param("beforeDay") )
                and ( length( $q->param("beforeDay") ) )
            )
        )
        and (
            (
                defined( $q->param("beforeYear") )
                and ( length( $q->param("beforeYear") ) )
            )
        )
      )
    {

        $lstatement .= "WHERE " unless ($count);
        $lstatement .= "AND " if ($count);

        $lstatement .= sprintf(
            "(TO_DAYS(field3) <= TO_DAYS('%04d-%02d-%02d')) ",
            $q->param("beforeYear"),
            $q->param("beforeMonth"),
            $q->param("beforeDay")
        );
    }
    if (
        (
            (
                defined( $q->param("afterMonth") )
                and ( length( $q->param("afterMonth") ) )
            )
        )
        and (
            (
                defined( $q->param("afterDay") )
                and ( length( $q->param("afterDay") ) )
            )
        )
        and (
            (
                defined( $q->param("afterYear") )
                and ( length( $q->param("afterYear") ) )
            )
        )
      )
    {

        $lstatement .= "WHERE " unless ($count);
        $lstatement .= "AND " if ($count);

        $lstatement .= sprintf(
            "(TO_DAYS('%04d-%02d-%02d') <= TO_DAYS(field3)) ",
            $q->param("afterYear"),
            $q->param("afterMonth"),
            $q->param("afterDay")
        );
    }

    $lstatement .= $order_by;
    return ($lstatement);
}
[download]

Comment on Re: SQL parser from fragments Download Code

Replies are listed 'Best First'.
Re: Re: SQL parser from fragments by samgold (Scribe) on Apr 19, 2002 at 03:38 UTC
why do you have: $lstatement .= "WHERE " unless ($count); on line 8 starting from sub... $count = 0 and nothing changes it between where you declare it and line 8. This is reference to the code posted by atcroft.	[reply]