perl script to connect two databases at same time

Question

I am trying to connect to 2 databases on the same instance of MySQL from 1 Perl script.

I am using this in a migration script where I am grabbing data from the original database and inserting it into the new one.

Connecting to 1 database and then trying to initiate a second connection with the same user just changes the current database to the new one.

#!/usr/bin/perl

use DBI;
use strict;

my $driver = "mysql";
my $database1 = "db1";
my $dsn1 = "DBI:$driver:database=$database1";
my $userid = "userhead";
my $password = "pwdhead";
my $database2 = "db2";
my $dsn2 = "DBI:$driver:database=$database2";

my $dbh1 = DBI->connect($dsn1, $userid, $password ) or die $DBI::errstr;
my $dbh2 = DBI->connect($dsn2, $userid, $password ) or die $DBI::errstr;

my $sth = $dbh2->prepare("INSERT INTO Persons") $dbh1->prepare("SELECT *FROM Persons");
$sth->execute() or die $DBI::errstr;
print "Number of rows found :" + $sth->rows;

In the above example i am trying to copy from one database table to another datbase table. but i am getting error while running the script. Please help me out

Answer 1

At a guess, you're trying to use the same database handle to connect to both databases. If you need to operate two separate connections then you need two separate handles

This program uses the data_sources class method to discover all of the available MySQL databases and creates a connection to each of them, putting the handles in the array @dbh. You can use each element of that array as normal, for instance

my $stmt = $dbh[0]->prepare('SELECT * FROM table)

It may be that you prefer to set up the @databases array manually, or the username and password may be different for the two data sources, so some variation on this may be necessary

use strict;
use warnings 'all';

use DBI;

my $user = 'username';
my $pass = 'password';

my @databases = DBI->data_sources('mysql');

my @dbh = map { DBI->connect($_, $user, $pass) } @databases;

---

Update

You need to select data from the source table, fetch it one row at a time, and insert each row into the destination table

Here's an idea how that might work, but you need to adjust the number of question marks in the VALUES of the INSERT statement to match the number of columns

Note that, if you're just intending to copy the whole dataset, there aree better ways to go about this. In particular, if you have any foreign key constraints then you won't be able to add data until the table it it is dependent on is populated

#!/usr/bin/perl

use strict;
use warnings 'all';

use DBI;

my $userid   = "userhead";
my $password = "pwdhead";

my ($dbase1, $dbase2) = qw/ db1 db2 /;

my $dsn1 = "DBI:mysql:database=$dbase1";
my $dsn2 = "DBI:mysql:database=$dbase2";

my $dbh1 = DBI->connect($dsn1, $userid, $password ) or die $DBI::errstr;
my $dbh2 = DBI->connect($dsn2, $userid, $password ) or die $DBI::errstr;

my $select = $dbh1->prepare("SELECT * FROM Persons");
my $insert = $dbh2->prepare("INSERT INTO Persons VALUES (?, ?, ?, ?, ?)");

$select->execute;
while ( my @row = $select->fetchrow_array ) {
    $insert->execute(@row);
}

If you need to handle the columns from the source data separately then you can use named scalars instead of the array @row. Like this

while ( my ($id, $name) = $select->fetchrow_array ) {
    my $lastname = '';
    $insert->execute($id, $name, $lastname);
}