By biosv on

Assume you already have tons of data in mysql or postgre db.
I am looking for a simple demo or tutorial that shows how to access those DB tables outside Drupal. All examples I've seen deal with DB tables in Drupal database, not outside DB.
Can anybody provide me links?
thx

⋅ Categories: Drupal 6.x

Comments

marcvangend’s picture

marcvangend
he/him
Primary language Dutch
Location Amsterdam
commented

Have a look at the table wizard module, http://drupal.org/project/tw.

stephenrobinson’s picture

stephenrobinson commented 

<?php
global $db_url; // the internal variable that contains database link
$default_db = $db_url;
$db_url = array();

//set up the new database value
$db_url['mydb']='mysql://user:pwd@localhost/anotherdb';

db_set_active('mydb');    // activation & execution same as explained above
$results = db_query($sql); //sql represents the query to be executed
$db_url = $default_db;  // set back to original
db_set_active('default');
?>
nevets’s picture

nevets commented

For the recommended approach see "How to connect to multiple databases within Drupal".

biosv’s picture

biosv commented

Many thanks for your help.

Would there be any limitation on working with outside DBs?
For example, indexing, querying, ...?
One of my friends told me that he had to bring all outside DB tables into Drupal DB to effectively and efficiently work on those tables. Is that true?

ludo1960’s picture

ludo1960 commented

First line of the link Nevet's sent you :

"Drupal can connect to different databases with elegance and ease!"

One of my friends told m.................

biosv’s picture

biosv commented

Since I don't have much experience of working on Drupal DB processing, I have no way of knowing whether there is significant differences between working directly on Drupal DB and working outside DB.
At least I can buy the "elegance" and "easiness", but how much?
Would there be virtually no difference in all (or most) operations (querying, indexing, caching, ...)? Or, would there be certain operations (something that are often used in critical matter) that suffer a lot (in performance or in effectiveness) by working with outside DB?

thx

marcvangend’s picture

marcvangend
he/him
Primary language Dutch
Location Amsterdam
commented

There is not really a need to move your tables to the Drupal database if that's all you're going to do. In certain situations, you may want to import the existing content into your Drupal db while converting it to Drupal nodes. The advantage of that would be that you can use the Drupal API and existing modules to do stuff with your data. If you keep your data in a separate db, you'll have to write your own queries and functions to get the data out of the database.

biosv’s picture

biosv commented

That makes sense.
Thanks a bunch.

One problem I see is that if I need to process some sensitive data which is located in a central database. The DB admin of the data may still allow me to access to the db and query the db, but may not willing to allow me to import whole data into my Drupal DB. If the main DB data changes, the whole tables need to be imported to Drupal again to maintain the consitency (or the modified portion has to be selectively updated in Drupal DB). Even, if the DB size is big (or huge, hundreds of MB of size, or even GB), then importing such large volume into Drupal only to exploit Drupal API or module may not be seem to be appealing.

Any thoughts?

marcvangend’s picture

marcvangend
he/him
Primary language Dutch
Location Amsterdam
commented

Converting to Drupal nodes is most convenient for one-time imports. If the external database will keep changing, you will indeed need to keep syncing to keep the Drupal db up-to-date. That can be a dirty job, especially if there are large amounts of data involved, or if the data needs to be synced both ways. Always try to avoid a situation where two databases need to be synced both ways because records can be created and updates on both sides.

If you don't need the main db data to be available as nodes, I think you will be fine accessing the main db directly from a custom Drupal module.

biosv’s picture

biosv commented

Do we have to import external DB tables into Drupal to be able to treat them as Drupal nodes?
Or, can Drupal be installed under an existing DB? Then, we do not need to import all those external tables into Drupal, but still those tables can be treated as Drupal nodes?

nevets’s picture

nevets commented

If you want to treat the data as nodes, you need to import the data as nodes (ie convert the data).

marcvangend’s picture

marcvangend
he/him
Primary language Dutch
Location Amsterdam
commented

Once you have created your Drupal installation and configured it (especially the content types) so it can hold all data you want to store, you can use the migrate module (http://drupal.org/project/migrate) to convert the existing data to nodes in your Drupal database.

You can two separate databases, provided that they can both be accessed from the server where Drupal is installed (this would be my preferred setup). If you decide to install Drupal in the existing database, use a table prefix to avoid table name collisions.