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

I need to figure out whether a table is empty so I can send it to the appropriate subroutine. Using DBI with sqlite. The solution would appear to be: 

my $empty = $dbh->do("SELECT count(*) FROM audio");

if ( $empty >= 1 ) {
    &processItem;
    }
else { &noItems; }

[download]
When I print $empty, I get: 
automation@Sherman:~/Desktop/SQL_Scripts/output$ sudo perl parse_audio.pl 
0E0
I've looked here, the sqlite module documentation and at sqlite3 sites in general.

When I run the query via CLI I get the correct answer of 23.

I am overlooking something simple, I'm sure ...but what?
Thanks y'all!

Bubnoff

Replies are listed 'Best First'.
Re: Find empty tables - sqlite module
by Your Mother (Archbishop) on Dec 31, 2008 at 23:58 UTC

    0E0 is a DBI shorthand meaning no rows returned/affected on a successful query. See the DBI docs for more-

    For a non-SELECT statement, execute returns the number of rows affected, if known. If no rows were affected, then execute returns "0E0", which Perl will treat as 0 but will regard as true. Note that it is not an error for no rows to be affected by a statement. If the number of rows affected is not known, then execute returns -1.
[reply]
[d/l]

      Thanks for clarifying that! The following code works: 

      my $empty5 = 
$dbh->selectall_arrayref("SELECT count(item) FROM audio");

foreach my $row (@$empty5)
{
    my ($count) = @$row;
    if ( $count > 1 ) {
        print "Processing $count Items\n";
        &processItem;
    }
    else { 
        print "No Items\n";
        &noItems; 
}

    print "$count\n";
}

      [download]

      I figured that selectall_arrayref might be the key, but it took a little experimentation to hone it to what I needed.

      Thanks again for your answer!

      Bubnoff

[reply]
[d/l]

        That seems like overkill for what you are looking for,

        my ($counted) = $dbh->selectrow_array("SELECT COUNT(*) FROM audio");

print "No. Rows: $counted\n";

        [download]
[reply]
[d/l]
      To clarify more (or not), 0E0 is 0 x 10**0 =0 x 1 =0
      Ooops...my goof before.... This is the Perl way to return a "true", "zero value".
      I like most, seldom use exponential notation in Perl, but that is what this is.
[reply]

        Oh, nice. I didn't realize that but it's obvious once you hear it.

        moo@cow[74]~/bin>perl -le 'print eval "0E0"'
0
moo@cow[75]~/bin>perl -le 'print eval "10E0"'
10
moo@cow[76]~/bin>perl -le 'print eval "10E1"'
100
moo@cow[77]~/bin>perl -le 'print eval "10E10"'
100000000000

        [download]
[reply]
[d/l]
Re: Find empty tables - sqlite module
by Jenda (Abbot) on Jan 01, 2009 at 06:24 UTC

    From perldoc DBI:
    do 

      $rows = $dbh->do($statement)           or die $dbh->errstr;
  $rows = $dbh->do($statement, \%attr)   or die $dbh->errstr;
  $rows = $dbh->do($statement, \%attr, @bind_values) or die ...

    [download]
    Prepare and execute a single statement. Returns the number of rows affected or undef on error. A return value of -1 means the number of rows is not known, not applicable, or not available.

    This method is typically most useful for non-SELECT statements that either cannot be prepared in advance (due to a limitation of the driver) or do not need to be executed repeatedly. It should not be used for SELECT statements because it does not return a statement handle (so you can't fetch any data).

    Jenda
    Support Denmark!
    Defend the free world!

[reply]
[d/l]

Back to Seekers of Perl Wisdom