Without seeing the exact error message, and without seeing a minimal, self-contained (say, 20 lines) program that exhibits the error, it's hard to advise you.

Maybe you want to check whether your DBD can access more than one database at a time. Also, you might want to check whether the problem is related to an SQL statement you're running on the second database connection. There are many, many possibilities.

I've never had any luck in accessing two databases such that I could transfer any data between them without getting the data locally and then retransmitting it. However, some databases (e.g. MS SQL Server) have the ability to link to a different server such that you can transfer data between the databases using a simple select. You might investigate that possibility.

In fact, I'm working on a project right now that connects to over 20 different databases to generate some summary reports via statements like:

INSERT Summary ( <fieldlist> )
SELECT <expressions>
FROM   <Server>.<Database>.<schema>.<table> ALIAS1
JOIN   <Server2>.<Database2>.<schema2>.<table2> ALIAS2
       ON ALIAS1.CommonKey = ALIAS2.CommonKey
WHERE  <expression>
[download]

If you can swing it, this is the way you'd want to go as it will save network traffic (the data goes straight from one server to the other without having to visit the computer running your program), simplify your coding (half as many SQL statements to run, other databases are easier to access by just adding an additional qualifier to their name).

If you're in a large shop, this can help you document data flows between systems, as the linked server definitions can be accessed by the DBAs.

i think i wasnt able to define the problem clearly... i am getting the data locally in "@row" and then using this data as parameter for the subsequent query which accesses a different database on a different host .. the trouble is usage of two database handles... they dont work together ... if the 1st connection is open using $dbh1 the second connection cannot be opened using $dbh2 , i have close the connection and open a new connection on same database handle to prepare the the next query i would like to use different databse handles for each new connections i open

mayankshah:

Oh, I see what you want. Yeah, you can't expect two databases to be able to share a single database connection. However, you can get the results you want using something like the (untested) bit below:

$dbh1->connect("to db2 database"); 
$dbh2->connect("to sybase database"); 
$sth1=$dbh1->prepare("select apples, bananas from fruit_salad");
$sth2=$dbh2->prepare("insert snack (apples, bananas) values (?,?)");
$sth1->execute(); 
while (@row=$sth1->fetchrow_array()) {
   $sth2->execute(@row);
}
[download]

This way, you get the data out of your statement handle in a nice, simple array which you feed to your *other* statement handle.

...roboticus