This patch allows multiple database connections to be used within Drupal.

The method of specifying :

$db_url = "mysql://user:pass@localhost/dbname";

will still work, it will create the 'default' connection.

However, if you need multiple connections .. you can specify : 

$db_url["default"]      = "mysql://user:pass@localhost/dbname";
$db_url["other_db"]     = "mysql://user:pass@localhost/other_dbname";
$db_url["other_server"] = "mysql://user:pass@my.server.com/dbname";

Within your module you can execute

set_active_db("other_db"); //or another named connection

to switch the db_api to use that connection. If you do not remember to switch back to default, you might cause interesting errors. so beware.

The set_active_db() function defaults it's first parameter to 'default', so you only need to run : 

set_active_db(); // defaults to 'default'

to get back on the right track

Caveats:

  • If you specify a named connection that doesnt exist, it creates a duplicate of the default. I am not sure if this is the wrong behaviour though, as it is easily fixed , but creating a new pipe is perhaps the desired functionality? I don't know. please comment.
  • Because of the nature of the DB Api , it does not allow you to have both postgres and mysql connections running simultaneously, infact only the first db api include loaded will function. It is beyond the scope of this feature request to rewrite the db api as extensively as is needed to fix this problem. More discussion is needed
CommentFileSizeAuthor
#1 multi_db_connections_0.diff6.54 KBadrian
multi_db_connections.diff5.23 KBadrian

Comments

adrian’s picture

adrian commented
StatusFileSize
new multi_db_connections_0.diff6.54 KB

added some documentation as per Dries' request

dries’s picture

dries commented

Committed to HEAD. Thanks Adrian.

Anonymous’s picture

(not verified) commented
sandeep-1’s picture

sandeep-1 commented
Category: feature » bug
Priority: Normal » Critical

If I was writing a module that needed to connect to another databases... for example my conf.php might look like this:


$db_url["default"]      = "mysql://user:pass@localhost/dbname";
$db_url["some_pgsql_db"]     = "pgsql://user:pass@localhost/some_pgsql_db";

Within my module I execute

set_active_db("some_pgsql_db");

Then I would get the Fatal error "Cannot redeclare..." because now there would be two sets of the db_* functions. One from database.mysql.inc which Drupal will include by default and another from database.pear.inc which my call to set_active_db() will include.

killes@www.drop.org’s picture

killes@www.drop.org commented
Category: bug » feature
Priority: Critical » Normal

I think using databases running two different systems is a feature rather than a bug. A feature that I don't think many people will require. There is also no easy way of providing this feature I am afraid.

sandeep-1’s picture

sandeep-1 commented

I know of a friend who uses Drupal to manage his entire business process. He has written a custom reporting module that collates data form the Drupal database (MySQL) and his clients database which is in MSSQL. I do agree that not many people will require this feature but its a cool feature to have in a web application platform.

I am currently working on a database abstration library based heavily on the Drupal database abstraction library to achieve the above feature. I think it should be fairly easy to implement. The idea is to have a wrapper file that has all the db_* functions. Depending on the current database connection type we can include the appropriate file for that particular database type. Now, the db_* function will check the current database type, for example if it is mysql, then db_connect() will call db_mysql_connect() which is defined in the mysql include file. Similarly, the other databases can be supported with the PEAR db library by defining the db_pear_* functions in the pear include file.

When I get enough time to complete my library I will contribute a patch for the Drupal database system.

adrian’s picture

adrian commented

Firstly, this patch made it into core early on in the 4.5 work.

Secondly, I made a note about not being able to use postgres and mysql in the same application. I think there's even a comment in the source.

Thirdly, we are moving away from PEAR in the next release, and mssql is no longer supported by drupal.

Fourthly, changing the db api to have db_ functions that redirect to the correct function is probably a good idea, but good luck trying to convince people it's needed as it is such a specific use case.

Anonymous’s picture

(not verified) commented

I think the idea of being able to flick databases is a good one, we are using it within our drupal code :)

It seems to have introduced a problem, raised on a seperate report http://drupal.org/node/13115

Thanks,

Sil

adrian’s picture

adrian commented

I am closing this.

Look here for a continuation : http://drupal.org/node/19522

jomodo’s picture

jomodo commented

It is mentioned here that this patch made it into the Drupal core - is that really the case? As i am getting :

"Fatal error: Call to undefined function: set_active_db()"

when trying to switch databases???

gerhard killesreiter’s picture

gerhard killesreiter commented

yes, the patch is in core. db_set_active is your friend.