in reply to Re: Building SQL Query on the fly
in thread Building SQL Query on the fly

I think I got SQL Abstract working like it should, selecting the correct columns (but I am not inserting them sorted based on lowest to highest, how would I do that?) But this is what I am doing now... 
my $sql = SQL::Abstract->new;

        ($group,$source,$orig,$term,$i_sig_start,$cdr_day,$call_status
+,$e_rel_cause,$pdd,$asr,$call_duration,$npa,$i_tg_name,$e_tg_name,$st
+ate,$gc_id,$dn,$noacpn,$i_rel_cause) = @_;

        if ($gc_id) {
                push @fields, "GC_ID";
        }

        if ($pdd) {
                push @fields, "PDD";
        } 
        
         %where = (DAY => '2007-10-06');
        $table = "TABLE_NAME";

        my($stmt, @bind) = $sql->select($table, \@fields, \%where);

       my $sth = $dbh->prepare($stmt);

        unless ($sth) {
                $dbh->disconnect;
        }
        $sth->execute(@bind);

         while (my $row = $sth->fetchrow_hashref ){
                 >>>>>>WHAT DO I DO HERE FOR XML????>>>>
         }

[download]
Ideas on how to push the column names into the XML tag elements, and the values inside the tags? As for RoR, I am digging it. I don't know very advanced functions with it, but it was pretty easy to pick up, and I like incorporating it with AJAX, making this a pretty rich application. This is what I am doing with the XML::Generator on other canned/static reports, but the stuff it returns is hard coded, and not dynamic variables. 
        $sth->execute();

        while (
                (
                        $gc_id, $cdr_day, $call_status, $direction, $p
+dd, $state, $noacpn, $i_cause
                )
                = $sth->fetchrow_array
          )
        {
                push @output,
                  $xml->cdr(
                        $xml->GC_ID($gc_id),
                        $xml->Date($cdr_day),
                        $xml->CallStatus($call_status),
                        $xml->Direction($direction),
                        $xml->State($state),
                        $xml->PDD($pdd),
                        $xml->NOACPN($noacpn),
                        $xml->I_REL_CAUSE($i_cause)
                  );
        }

        my $cgi = CGI->new;
        $hdr = "Custom Report";
        print $cgi->header('text/xml');
        print "<dataset name=\"$hdr\">\n";
        print @output;
        print "</dataset>";

[download]
Maybe do a...? 
my $row = $sth->fetchrow_hasharray;

[download]
Just need help pushing the hash into the XML format properly. But since the amount of rows will always be different, I am not sure how to do it. Thanks!

Replies are listed 'Best First'.
Re^3: Building SQL Query on the fly
by graff (Chancellor) on Oct 08, 2007 at 05:08 UTC
    ... I know this is totally incorrect, because this way will always leave a trailing comma.

    So just do chop $SQL; or $SQL =~ s/,$//; to get rid of the comma, before you add the next part of the statement ($SQL .= " FROM ...").

    But I think the sample code you just gave is a bit off track. Let's go back to your CGI param string (from the OP): 

    ../cgi-bin/script.pl?column3=1&column2=2&column1=3

    [download]
    If you get your params like this: 
    my $q = new CGI;
my $href = $q->Vars;

    [download]
    Then you can form the string of column names like this: 
    my $col_string = join( ',', sort {$$href{$a} <=> $$href{$b}} keys %$hr
+ef );

    [download]
    I don't think you'll ever need to worry about any extra commas with that approach, but if you want to be totally certain: 
    $col_string =~ s/^,+//;      # remove leading commas
$col_string =~ s/,+$//;      # remove trailing commas
$col_string =~ s/,{2,}/,/g;  # reduce adjacent commas to ","

    [download]
    Or, using something more like your approach, just append column names in order, and get rid of the last comma: 
    my $col_string = '';
for my $var ( sort {$$href{$a} <=> $$href{$b}} keys %$href ) {
    $col_string .= "$var,";
}
chop $col_string;  # remove final comma

    [download]
    Either way, now you just finish the SQL statement: 
    my $sql = "SELECT $col_string FROM my_table";

    [download]
    Also, in all cases, if/when you decide (or realize) that you do need to worry about security issues, make sure to test the "$href" keys (assumed table column names) against a known (untainted) list that is based directly on the how the table was defined. Delete any hash keys from the CGI parameter string that do not exactly match a known table column.

    This sort of dynamic query construction is an area where things can go horribly wrong if you are not meticulously careful about checking your tainted input. If you don't have "-T" on the shebang line of your script (to turn on taint-checking), add it now.

    This is what I am doing with the XML::Generator, but the stuff it returns is hard coded, and not dynamic variables...

    That's why I say you should use XML::Simple (or something else besides XML::Generator) -- you need to write XML data based on a data structure, where the highest (outermost) level of the structure is a hash.

[reply]
[d/l]
[select]
      my $col_string = '';
for my $var ( sort {$$href{$a} <=> $$href{$b}} keys %$href ) {
    $col_string .= "$var,";
}
chop $col_string;  # remove final comma

      [download]

      I always view the chopping of a trailing comma in building an SQL statement as something of a Red Flag. It usually means that a join (in the Perl sense) is called for. For instance, the above snippet is better written as

      my $col_string = join( ',',
    sort {$href->{$a} <=> $href->{$b}} keys %$href
);

      [download]

      • another intruder with the mooring in the heart of the Perl

[reply]
[d/l]
[select]
      I edited my posted you commented on using SQL::Abstract, but I like your way better with the sorting. It looks like it will work well, but since other parameters are being passed besides just column names (like order by, where clauses) I can't really push it all in which is why I was doing it one by one, but then again, that's the only way I thought of doing it. So in my edited posted, I mentioned doing 
       while (my $row = $sth->fetchrow_hashref ){
        <<<<SOME KIND OF XML::SIMPLE PUSH HERE???>>>>>
 }

      [download]
      I am confused on how to make the column names the element tags, and the returned values in the hash ($row), into XML tags. I will continue to read about the XML::Simple, but all pointers are extremely appreciated.
[reply]
[d/l]
        If you provide a snippet of real data from the table, and a snippet of what you want the XML to look like, I expect others will have no trouble pointing you in the right direction. But you might very well figure it out on your own first.

        In the meantime, I'll just say that your loop over the fetched database rows will be building (array elements within) a hash structure, and the structure will be passed to the XMLout method of your XML::Simple object. I'm not sure, but your structure loading loop might be something like this: 

        my %xml_hash = ( rootnode => [] ); # top XML node has an empty array_r
+ef as value

while ( my $row = $sth->fetchrow_hashref ) {
    push @{$xml_hash{rootnode}}, $row;
}

print $xmlobj->XMLout( \%xml_hash );

        [download]
        I'm probably missing some important details there, and I'm sorry that the XML::Simple docs are so huge. Good luck, and good night.
[reply]
[d/l]

In Section Seekers of Perl Wisdom