I need a little help in redirecting the output of a SQL query to a file. My code looks like this:
my $sth = $dbh->prepare(
"select count(parameter2),
parameter2 as file_type
from KCRT_TABLE_ENTRIES where request_id = $mycrnum
group by parameter2"
) or die "Can't prepare SQL statement: ", $dbh->errstr(), "\n";
$sth->execute > $mydir\\file_detail.txt
or die "Can't execute SQL statement: ", $sth->errstr(), "\n";
I've had to invent a lot of code as you don't show much of your program, but the program below gives you the rough idea
Once you've called execute you have to call one of the fetch methods to retrieve the data in whatever form is most useful to you. Here I've just asked for a reference to an array containing each row's data
Then it's simply a matter of opening the required file for output and printing the rows of data to it
I've removed the status checks on each DBI call and replaced it with the RaiseError flag which does the same thing automatically. I've also replaced the parameter $mycrnum in the SQL statement with a placeholder and passed its value to execute. That way DBI looks after any necessary quoting etc.
use strict;
use warnings;
use DBI;
my ($dsn, $user, $pass);
my ($mycrnum, $mydir);
my $dbh = DBI->connect($dsn, $user, $pass);
@{$dbh}{qw/ PrintError RaiseError /} = (0, 1);
my $sth = $dbh->prepare(
"SELECT COUNT(parameter2),
parameter2 AS file_type
FROM kcrt_table_entries
WHERE request_id = ?
GROUP BY parameter2"
);
$sth->execute($mycrnum);
open my $fh, '>', "$mydir/file_detail.txt" or die $!;
select $fh;
while ( my $row = $sth->fetchrow_arrayref ) {
printf "%5d %s\n", @$row;
}
After the execute, open the output file:
open my $of, ">", "$mydir\\file_detail.txt";
Then read each line (or row) in the results:
while ( @row = $sth->fetchrow_array ) {
Printing the output to the opened file handle:
print $of "@row\n"; # NO COMMA AFTER $of!
Close the while() loop:
}
Finally, close your opened file handle:
close $of;
Now your done.
or die $! after the open, you'll definitely see an error if something went wrong.
Something like this, perhaps?
my $sth = $dbh->prepare(q{
select count(parameter2),
parameter2 as file_type
from KCRT_TABLE_ENTRIES where request_id = ?
group by parameter2
}) or die "Can't prepare SQL statement: ", $dbh->errstr(), "\n";
$sth->execute($mycrnum);
open my $OUT, '>', "$mydir/file_detail.txt" or die;
while (my @row = $sth->fetchrow_array) {
print $OUT @row, "\n"; # or whatever...
}
close $OUT;
$sth->finish;
This is a little bit of overkill, since you are only reading a single value, but it at least demonstrates a boilerplate for getting it done for future queries.
If you ever have a guaranteed single row, you can do something like this:
my ($val1, $val1) = $dbh->selectrow_array(q{
select foo, bar
});
q{} in the first line.