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

Dear Monks,

I am struggling a bit with the following code: 

  foreach (@SQL_queries) {

    my $Get_results = $_;
    
    
    
    my $sth_atl = $dbh->prepare($Get_results) or die "Couldn't prepare
+ query: ".$dbh->errstr;
    $sth_atl->execute() or die "Couldn't execute query: ".$sth_atl->er
+rstr;
       
    my $cols_for_row_B;
    
       
    while ($cols_for_row_B = $sth_atl->fetchrow_array) {
      local $" = "\t";
      print OUTFILE $cols_for_row_B;
      print OUTFILE "\n";
    }
  }
  undef @SQL_queries;

[download]
I want it to return the output from the SQL queries to the result file(s).

Please can anyone help me with this?

Update:
The following just prints out the query. It doesn't print out the result of the query. I am wondering if I shouldn't have the SQL query specified over multiple lines. 

  while ($cols_for_row_B = $sth_atl->fetchrow_array) {
      local $" = "\t";
      print OUTFILE $_;
      print OUTFILE "\n";
    }

[download]

Replies are listed 'Best First'.
Re: returning rows using SQL + Perl DBI
by fisher (Priest) on Nov 22, 2010 at 10:47 UTC
    You fetch an array and assign it to a scalar lvalue.
    I mean here: 
     while ($cols_for_row_B = $sth_atl->fetchrow_array) {

    [download]
[reply]
[d/l]
      You mean the mistake is in that bit of code?
[reply]
        I mean, quoting perldoc DBI,
        If called in a scalar context for a statement handle that has more than one column, it is undefined whether the driver will return the value of the first column or the last. So don't do that. Also, in a scalar context, an "undef" is returned if there are no more rows or if an error occurred. That "undef" can't be distinguished from an "undef" returned because the first field value was NULL. For these reasons you should exercise some caution if you use "selectrow_array" in a scalar context, or just don't do that.
[reply]

        Yes. fetchrow_array returns an array not a scalar. Use @row = $sth->fetchrow_array.

[reply]
Re: returning rows using SQL + Perl DBI
by roboticus (Chancellor) on Nov 22, 2010 at 11:41 UTC

    Win:

    As fisher shows, you were pretty close. Fisher showed you how to do it using an array. Another small modification would let you use an arrayref:

    while ($cols_for_row_B = $sth_atl->fetchrow_arrayref) {
   print OUTFILE join("\t",@$cols_for_row_B),"\n";
}

    [download]

    I went ahead and merged the three lines of the body of your inner loop, too.

    The reason I bothered to reply, since you already had an answer is to warn you of a possible security problem. I don't know where you're getting your list of queries (@SQL_queries), but if they're coming from the user (such as from a web form, command line, etc.), then you'll want to be very careful. You don't want the users to give you queries that could potentially damage your database. I'd give you some links here, but I gotta get my kiddo off to school, so I'll try to update the node once I get to work.

    ...roboticus

[reply]
[d/l]
[select]
Re: returning rows using SQL + Perl DBI
by Corion (Patriarch) on Nov 22, 2010 at 10:46 UTC

    For example, Querylet is a prepackaged application/module that allows you to write queries that produce Excel or HTML output easily.

[reply]

Back to Seekers of Perl Wisdom