• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1334
  • Last Modified:

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.
0
batman9
Asked:
batman9
3 Solutions
 
Adam314Commented:
Not sure about the SQL commands to use, but the error is telling you that you didn't call execute before calling fetch.  You need something like this:

my $dbh=DBI->connect($data_source, $username, $auth, \%attr);
my $skh1=$dbh->prepare($SQL1);
my $skh2=$dbh->prepare($SQL2);

$skh1->execute(@parameters1);
$skh2->execute(@parameters2);

#Now you can fetch from both $skh1 and $skh2

0
 
jazahnCommented:
Adam has the first part covered.

The query sounds like you might best be served with REPLACE
http://dev.mysql.com/doc/refman/4.1/en/replace.html
Pretty much the same syntax as  an INSERT but if it finds a duplicate entry it does an UPDATE instead of an INSERT..  it uses the primary key/unique keys to determine if it's a duplicate entry.  So in the simplest form, you can have a primary key of some_id

REPLACE INTO table_name(some_id, col1, col2) VALUES('12', 'hello', 'goodbye');
will update and
REPLACE INTO table_name(some_id, col1, col2) VALUES(null, 'hello', 'goodbye');
will insert a new one

This is usually easier than doing an update/check/insert.
0
 
batman9Author Commented:
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
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
clockwatcherCommented:
Something in the back of my brain is telling me that you can't have two open statements on a single handle.  Pretty sure most databases would have issues with that.  Either use two connections:

my $dsn = "DBI:mysql:database=bounced_emails;host=localhost";
my $dbh1 = DBI->connect($dsn, $dbuser, $dbpass)  or die "Couldn't connect to database: " . DBI->errstr;
my $dbh2 =  DBI->connect($dsn, $dbuser, $dbpass)  or die "Couldn't connect to database: ";
$kth1 = $dbh1->prepare('select email, reason, count(email) times, MAX(time_stamp) last_occurance from mailbot_table_dot_com group by email limit 50;')  or die "Couldn't prepare statement: " . $dbh->errstr;
$kth2 = $dbh2->prepare("select email, reason, times, time_stamp from Summary where reciever = 'table.com'") or die "Couldn't ...


Or (the more typical solution) finish up with the statement handle before you use another on the same connection:

$kth2 = $dbh->prepare("select email, reason, times, time_stamp from Summary where reciever = 'table.com'") or die "Couldn't
prepare statement: " . $dbh->errstr;
$kth2->execute() or die "Couldn't execute statement: " . $kth2->errstr;
$kth2data = $kth2data->fetchall_arrayref;

$kth1 = $dbh->prepare('select email, reason, count(email) times, MAX(time_stamp) last_occurance from mailbot_table_dot_com group by email limit 50;')  or die "Couldn't prepare statement: " . $dbh->errstr;
$kth1->execute() or die "Couldn't execute statement: " . $kth1->errstr;

while (@data1 = $kth1->fetchrow_array()) {
        foreach $data2 (@{$kth2data}) { print $data2->[0]."\n"; ...
0
 
Adam314Commented:
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";
        }
}
0
 
batman9Author Commented:
I will try and then tell you ....
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now