RE: [Fwd: fetchrow_arrayref()]

[Moon, John]

Subject:        fetchrow_arrayref()
Date:   Wed, 13 Jul 2005 11:08:00 +0100
From:   David Foley <[EMAIL PROTECTED]>
To:     beginners@perl.org



Hi Guys,
             I need help with this. Please find below some code:

#!/usr/bin/perl -w

# Reduce scripting errors + call DBI module
use strict;
use DBI;

#SQL database handle 1
my $MySQL = DBI->connect (************************) or die "Could not 
connect to MySQL database on 82.195.128.88 as rw227_admin";

#SQL Query 1
my $SQLQ1a = "SELECT FirstName, SecondName FROM Jul05ord";

#SQL Query 1 HANDLE
my $SQLQ1 = $MySQL->prepare($SQLQ1a);

#Execute SQL Query 1
$SQLQ1->execute();

#Retreive ref. no. for results
my $resultsref1 = $SQLQ1->fetchall_arrayref();
$SQLQ1->finish();
$MySQL->disconnect();

#Add to local DB on TMA1 (192.168.42.12)

#SQL database handle 2
my $MySQL2 = DBI->connect (*************************) or die " Could not 
connect to MySQL database on localhost";

#Execute SQL Query 2
foreach my $WOT (@$resultsref1){
my ($FirstName, $SecondName) = @$WOT;

#SQL Query 2
my $SQLQ2a = "INSERT INTO dev (batman, robin)
VALUES ($FirstName, $SecondName)";

#SQL Query 2 HANDLE
my $SQLQ2 = $MySQL2->prepare($SQLQ2a);
$SQLQ2->execute();

$SQLQ2->finish();
}

$MySQL2->disconnect();

exit();

Above is the program. I want it to take info fromone database, and add 
it to another. Put I can't get the second query to put in the info from 
the previous query. I think the problem is with the red code can anyone 
help??

...
Maybe something like this would help... 

my $sth_get = prepare(select column_a, column_b from sometable) 
                or die "Error: " . DBI->errstr;
my $sth_put = prepare(q{insert into a_table (column_1, column_2)
        values (?,?)})
                or die "Error: " . DBI->errstr;
my ($column_a, $column_b);
$sth_get->bind_columns(\$column_a, \$column_b) 
                or die "Error: " . DBI->errstr;
$sth_get->execute
                or die "Error: " . DBI->errstr;;
while ($sth_get->fetch) {
        $sth_put->execute($column_a, $column_b)
                or die "Error: " . DBI->errstr;
        }

This way you do not have to do the prepare on the insert each time and if
SQL is in error then you can see what is wrong... I also like to use
placeholders as it takes care of "presenting" the value to the database...
Hope this gives you some ideas...
jwm


-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>