mysql - perl script to connect two databases at same time -

i trying connect 2 databases on same instance of mysql 1 perl script.

i using in migration script grabbing data original database , inserting new one.

connecting 1 database , trying initiate second connection same user changes current database new one.

#!/usr/bin/perl  use dbi; use strict;  $driver = "mysql"; $database1 = "db1"; $dsn1 = "dbi:$driver:database=$database1"; $userid = "userhead"; $password = "pwdhead"; $database2 = "db2"; $dsn2 = "dbi:$driver:database=$database2";  $dbh1 = dbi->connect($dsn1, $userid, $password ) or die $dbi::errstr; $dbh2 = dbi->connect($dsn2, $userid, $password ) or die $dbi::errstr;  $sth = $dbh2->prepare("insert persons") $dbh1->prepare("select *from persons"); $sth->execute() or die $dbi::errstr; print "number of rows found :" + $sth->rows; 

in above example trying copy 1 database table datbase table. getting error while running script. please me out

at guess, you're trying use same database handle connect both databases. if need operate 2 separate connections need 2 separate handles

this program uses data_sources class method discover of available mysql databases , creates connection each of them, putting handles in array @dbh. can use each element of array normal, instance

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

it may prefer set @databases array manually, or username , password may different 2 data sources, variation on may necessary

use strict; use warnings 'all';  use dbi;  $user = 'username'; $pass = 'password';  @databases = dbi->data_sources('mysql');  @dbh = map { dbi->connect($_, $user, $pass) } @databases; 

---

update

you need select data source table, fetch 1 row @ time, , insert each row destination table

here's idea how might work, need adjust number of question marks in values of insert statement match number of columns

note that, if you're intending copy whole dataset, there aree better ways go this. in particular, if have foreign key constraints won't able add data until table it dependent on populated

#!/usr/bin/perl  use strict; use warnings 'all';  use dbi;  $userid   = "userhead"; $password = "pwdhead";  ($dbase1, $dbase2) = qw/ db1 db2 /;  $dsn1 = "dbi:mysql:database=$dbase1"; $dsn2 = "dbi:mysql:database=$dbase2";  $dbh1 = dbi->connect($dsn1, $userid, $password ) or die $dbi::errstr; $dbh2 = dbi->connect($dsn2, $userid, $password ) or die $dbi::errstr;  $select = $dbh1->prepare("select * persons"); $insert = $dbh2->prepare("insert persons values (?, ?, ?, ?, ?)");  $select->execute; while ( @row = $select->fetchrow_array ) {     $insert->execute(@row); } 

if need handle columns source data separately can use named scalars instead of array @row. this

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