CODEWITHSUNDEEP
perldbi
user1973591
user1973591

Reputation:

How to make sure a query executed successfully

I need to execute a select statement query and capture the returned result and store it in a variable.

The execute isn't returning any data. I have checked my connection is correct since it's not returning any error.

$dbh = DBI->connect($data_source, $username, $password) or die $DBI::errstr;

The following is my query 

my $sth = $dbh->prepare('select abc from pqr');
$sth->execute();

Now I am checking whether it has some data

if($sth->rows) {
    print "We have data!\n";
} else {
    print"No Data";
}

It always printing No Data.

For fetching the data I am using following

while(my @data = $sth->fetchrow_array()) {
    print "$data[1]\n";
}
$dbh->disconnect;

I am not getting any data. Can anyone please suggest me what thing can be wrong here? Same Query is giving me desired result with Java.

Upvotes: 1

Views: 2505

Answers (2)

Analog
Analog

Reputation: 261

If you are not dealing with large result sets then fetching everything is a little faster

my $rows = $db->selectall_arrayref(qq|Select abc from def|) or die "Can't select " . $db->errstr();
if (@$rows) {
    print "I have rows";
    foreach my $row (@$rows) {
        # do something with $row->[0]
    }
} else {
    print "I did not get any rows/data";
}

Just another way of doing it

Upvotes: 1

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38775

According to the DBI docs

$rv = $sth->rows;

Returns the number of rows affected by the last row-affecting command, or -1 if the number of rows is not known or not available.

Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement.

WRT your:

while(my @data = $sth->fetchrow_array()) {
    print "$data[1]\n";
}

Asking for the second ([1]) element of the row doesn't make sense for your query.

Upvotes: 1

Related Questions