ADODB-change database

Carl on aug 9th 2011 09:41 am | Tags: ,

In ADODB you cannot use multiple database connections simultaneously. If you need to work cross database, you have two possibilities:

  • Close the existing connection en setup a new one so you can retrieve the required data from the database;
  • Change database.

1. New Connection

$dbhandle = new ADONewConnection( $DSN_1 );
... queries etc. ...
$dbhandle->close ();
$dbhandle = new ADONewConnection( $DSN_2 );
... queries etc. ...
etc, etc

2. Change Database

$dbhandle = new ADONewConnection( $DSN_1 );
... queries etc. ...
$dbhandle->SelectDB ( $databasename );
... queries etc. ...

At first sight the differences do not seem to be very big. However, if your database server has a heavy load, it will certainly make a difference. The first option initialises a new connection every time (even with pconnect) and though you closed the previous connection, the latter still needs to timeout on the server. As a database server is no different then e.g. a web server in handling connections, this means you will be running out of connections (max connections in the server configuration), thus your code will be waiting for a response as the requested connections are being queued. Result for the web application … page timeouts.
The second option re-uses the existing connection. It will only switch from one database to the next. This, however, is only possible if the databases you use can be read by the same user from the same host, as you will understand. The bottleneck of this method is your service provider: Does he, if you are not the one providing the service, allow you to select/update/delete in your multiple databases with one account.

Filed in MySQL @en,PHP @en

Comments are closed.