Perl Script to query MySQL

[ErrolDC2]

DBD::mysql::st fetchrow_array failed: fetch() without execute()

This is the error I'm getting on a simple script. I don't understand what I'm doing wrong. Can someone please help me understand what I'm doing wrong?

#!/usr/bin/perl

use strict;
use DBI;


# Connect to the database.
my $host = "host.domain.com";
my $database = "mail";
my $user = "readonly";
my $password = "***";

my $dsn = "DBI:mysql:database=$database;host=$host";
my $dbh = DBI->connect($dsn, $user, $password);
if (! $dbh) {
die(sprintf('error'));
}
my $sql = qq`SELECT username from accountuser`;
my $sth = $dbh->prepare($sql);

$sth->execute();
while (my @record = $sth->fetchrow_array()) {
my $numrows = $sth->rows();
my $command = "/usr/lib/cyradm-imapd/squatter -v -s -r ";
for (my $i=0; $i<$numrows; $i++) {
exec $command $record[$i];
}
$sth->finish();
}
$dbh->disconnect();


Thanks in advance.

 

[MillerH]

A couple of things. You aren't error checking your execute statement, which is just smart. But your problem is that you are calling finish on the statement handle within the while loop.

Simply move the finish to outside, along with the rows call, as that information is static.

Here's how I would reformat your logic:

[gray]#!/usr/bin/perl[/gray]

[url=http://perldoc.perl.org/functions/use.html][black][b]use[/b][/black][/url] [green]strict[/green][red];[/red]
[black][b]use[/b][/black] [green]DBI[/green][red];[/red]

[url=http://perldoc.perl.org/functions/our.html][black][b]our[/b][/black][/url] [blue]$command[/blue] = [red]'[/red][purple]/usr/lib/cyradm-imapd/squatter -v -s -r [/purple][red]'[/red][red];[/red]

[gray][i]# Connect to the database.[/i][/gray]
[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$host[/blue] = [red]'[/red][purple]host.domain.com[/purple][red]'[/red][red];[/red]
[black][b]my[/b][/black] [blue]$name[/blue] = [red]'[/red][purple]mail[/purple][red]'[/red][red];[/red]
[black][b]my[/b][/black] [blue]$user[/blue] = [red]'[/red][purple]readonly[/purple][red]'[/red][red];[/red]
[black][b]my[/b][/black] [blue]$pass[/blue] = [red]'[/red][purple]***[/purple][red]'[/red][red];[/red]
[black][b]my[/b][/black] [blue]$url[/blue]  = [red]"[/red][purple]DBI:mysql:database=[blue]$name[/blue];host=[blue]$host[/blue][/purple][red]"[/red][red];[/red]

[black][b]my[/b][/black] [blue]$dbh[/blue] = DBI->[maroon]connect[/maroon][red]([/red][blue]$url[/blue], [blue]$user[/blue], [blue]$pass[/blue][red])[/red]
	or [url=http://perldoc.perl.org/functions/die.html][black][b]die[/b][/black][/url] [red]"[/red][purple]DB connect failed: [blue]$DBI::errstr[/blue][/purple][red]"[/red][red];[/red]

[black][b]my[/b][/black] [blue]$username[/blue][red];[/red]
[black][b]my[/b][/black] [blue]$sth[/blue] = [blue]$dbh[/blue]->[maroon]prepare[/maroon][red]([/red][red]q{[/red][purple]SELECT username from accountuser[/purple][red]}[/red][red])[/red][red];[/red]
[blue]$sth[/blue]->[maroon]execute[/maroon][red]([/red][red])[/red] or [black][b]die[/b][/black] [blue]$dbh[/blue]->[maroon]errstr[/maroon][red];[/red]
[blue]$sth[/blue]->[maroon]bind_columns[/maroon][red]([/red]\[blue]$username[/blue][red])[/red][red];[/red]

[olive][b]while[/b][/olive] [red]([/red][blue]$sth[/blue]->[maroon]fetch[/maroon][red])[/red] [red]{[/red]
	[url=http://perldoc.perl.org/functions/exec.html][black][b]exec[/b][/black][/url] [blue]$command[/blue] [blue]$username[/blue][red];[/red]
[red]}[/red]

[blue]$sth[/blue]->[maroon]finish[/maroon][red]([/red][red])[/red][red];[/red]
[blue]$dbh[/blue]->[maroon]disconnect[/maroon][red]([/red][red])[/red][red];[/red]

[tt]------------------------------------------------------------
Pragmas (perl 5.8.8) used :
[ul]
[li]strict - Perl pragma to restrict unsafe constructs[/li]
[/ul]
Other Modules used :
[ul]
[li]DBI[/li]
[/ul]
[/tt]

- Miller