asked on

Perl Mysql Multiple select queries issue

Hi, I am using perl and mysql to update list of bounced emails. Now issue is that I can insert data of unique emails in table. But I am now trying to do this that it should run select records from table A and then insert only the new ones in Table B and update the old ones number and latest date. Now when I try to use 2 select queries in $dbh and assign to $skh1 and $skh2 then when I run execute and try to get data from both queries, $skh2 gives error

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

now can you please tell me how can I compare 2 tables and update only the old ones date and insert new ones. I think I need to run 2 queries for it and then compare and update insert. But how can I run 2 select queries with 1 db handle and update the table B. or is there any other way for it. Kindly help me in this regards as I am new in perl/mysql.

Thankyou,

Regards.

ASKER CERTIFIED SOLUTION

Adam314

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

jazahn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

batman9

ASKER

Here is my code

#!/usr/bin/perl

use strict;
use DBI;

my $email;
my $reason;
my $times;
my $last_occurrance;
my $reciever;
my $kth1;
my $kth2;
my @data1;
my @data2;
my $dbuser = "bounces";
my $dbpass = "bounces123";
my $dsn = "DBI:mysql:database=bounced_emails;host=localhost";
my $dbh = DBI->connect($dsn, $dbuser, $dbpass) or die "Couldn't connect to database: " . DBI->errstr;

$kth1 = $dbh->prepare('select email, reason, count(email) times, MAX(time_stamp) last_occurance from mailbot_table_dot_com gr
oup by email limit 50;') or die "Couldn't prepare statement: " . $dbh->errstr;
$kth2 = $dbh->prepare("select email, reason, times, time_stamp from Summary where reciever = 'table.com'") or die "Couldn't
prepare statement: " . $dbh->errstr;
$kth1->execute() or die "Couldn't execute statement: " . $kth1->errstr;
$kth2->execute() or die "Couldn't execute statement: " . $kth2->errstr;
while (@data1 = $kth1->fetchrow_array()) {
while (@data2 = $kth2->fetchrow_array()) {
print @data2[0] . "\n";
}
}
$kth1->finish;
$kth2->finish;

$dbh->disconnect;

and it is giving the error

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

SOLUTION

clockwatcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Adam314

I think you can have two open statements on one handle. I think the problem is here:
while (@data1 = $kth1->fetchrow_array()) {
while (@data2 = $kth2->fetchrow_array()) {
print @data2[0] . "\n";
}
}

The first while loop executes, gets one record from $kth1.
The second while loop executes, gets one record from $kth2 at a time, until it gets all records
(there are now no more records in $kth2)
The first while loop executes, gets one more record from $kth2
The second while loop tries to execute, but there are no more records in $kth2.
If you want those records again, you need a new execute.

If you are going to be doing this, you might want to try it like this instead:
my $data2 = $kth2->fetchall_arrayref();
while (@data1 = $kth1->fetchrow_array()) {
while (@data2 = @{$data2}) {
print @data2[0] . "\n";
}
}

batman9

ASKER

I will try and then tell you ....