-------- Original Message --------
Subject: fetchrow_arrayref()
Date: Wed, 13 Jul 2005 11:08:00 +0100
From: David Foley <[Email Removed]>
To: [Email Removed]



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??

Thanks,
David

On Jul 13, David Foley said:

Subject: fetchrow_arrayref()
Date: Wed, 13 Jul 2005 11:08:00 +0100
From: David Foley <[Email Removed]>
To: [Email Removed]



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