How to write to a file only if SQL query returns more than 0 rows - Perl

Question

I have this code, which I want should open the file(Output.csv) only if there are more than 0 rows returned by the SQL query. I tried using mysqli_num_rows() but wasn't helpful. here is my current code:

#!/usr/bin/perl -w


BEGIN {
  $ENV{ORACLE_HOME}='/u01/app/oracle/product/11.1.0/';
}
use strict;

use DBI;
use utf8;
#use Text::CSV;

my $DB='database';
my $db_user='user';
my $password=`/usr/bin/pmo view password -u $db_user -t $DB`; chomp($password); my $db = DBI->connect( "dbi:Oracle:database", $db_user, $password )

    || die( $DBI::errstr . "\n" );

$db->{AutoCommit}    = 0;

$db->{RaiseError}    = 1;

$db->{ora_check_sql} = 0;

$db->{RowCacheSize}  = 16;


my $sth = $db->prepare("SELECT * from my table T where T.last_updates=(SYSTDATE -2) ");


open my $fh, '>>', 'Output.csv' or die "Could not open file Output.csv: $!"; 
$sth->execute;


while (my @res = $sth->fetchrow_array) {
    print $fh qq{$res[0]\t$res[1]\n};
}

close $fh;

print "If you see this, execute phase succeeded without a problem.\n";

END {

    $db->disconnect if defined($db);
}

mysqli_num_rows()? It'd be very surprising if that was helpful, since that's a PHP function. — Marc B, May 05 '14 at 05:53
I meant I was searching for a function which does the same task in perl — fiddle, May 05 '14 at 05:57
possible duplicate of [How do I know how many rows a Perl DBI query returns?](http://stackoverflow.com/questions/440799/how-do-i-know-how-many-rows-a-perl-dbi-query-returns) — Marc B, May 05 '14 at 05:58
Please `use warnings` instead of the `-w` command-line option — Borodin, May 05 '14 at 07:32

score 1 · Accepted Answer · edited May 05 '14 at 06:19

Well, just open the file when you encounter the first line.

my $fh; # = undef

while (my @res = $sth->fetchrow_array) {
  unless ($fh) {
    open $fh, '>>', 'Output.csv' or die "Can't open output file: $!";
  }
  print $fh qq{$res[0]\t$res[1]\n};
}

close $fh if $fh;

Using a function to determine how many rows are present in the result set is usually a bad idea. It can be very expensive. Since you're going to read all the returned rows anyway, no point really. (And if you only need the count, select count(*) ... so that the database does only that and you don't carry a large resultset from the database to your app only to discard its contents.)

Please, do never try to write CSV-files yourself — there are too many pitfalls. Either use Text::CSV or, since working with databases, use DBI, DBD::csv. — vanHoesel, May 05 '14 at 09:13

How to write to a file only if SQL query returns more than 0 rows - Perl

1 Answers1