I am trying to hook up an external database to Views in Drupal 6. But am having some difficulties. Here is what I have done:
Added a DB DSN for the the external db to settings.php. (The external MySQL is on a separate machine than the MySQL that runs Drupal.)
Created and enabled a custom module called digir .
Created the info file digir.info.
Created the digir.module (empty right now) .
Created the digir.views.inc files with the following code:
NOTE: that database is digircahe and that table is fish_tissue_digir_cache.
<?php
function digir_views_data()
{
$data = array();
$data['fish_tissue_digir_cache']['table']['group'] = t('DiGIR');
$data['fish_tissue_digir_cache']['table']['base'] = array(
'field' => 'tmpcoredataid',
'title' => t('Taxon'),
'help' => 'This is the species name',
'database' => 'digircache',
);
$data['fish_tissue_digir_cache']['abbreviation'] = array(
'title' => t('Abbreviation'),
'help' => t('Institution code'),
);
return $data;
}
?>The function does not define all of the fields in the database. I wanted to see if I could get it to work first.
This is not joined to a Drupal Node as there are millions of records in the database.
I don't know if Views is reading the digir.views.inc file. If I put code in the file that I know will throw an error, nothing happens.
I am not sure where my new database will show up so I have been checking both the "View Type" and "Fields" to no avail.
As you can tell, I am pretty new to Views and Drupal. Any advice would be greatly appreciated. Thanks.
Comments
It should show up as a view
It should show up as a view type, 'Taxon'. ONce you have it, you won't have any fields or filters because you have none defined. You should probably add a 'field' declaration for fish_tissue_digir_cache.abbreviation and then you'll at least see that.
Also, something about 'fish tissue' in the database creeps me out =)
You need a call to hook_views_api() in your .module
Not sure if you did this, but I noticed you said your .module file was empty.
I think you need to implement the hook_views_api() in order to get views to call your views.inc file.
Basically, you need to add:
function digir_views_api() {return array('api' => 2.0);
}
Basically, this is registering your module as "views aware".
Hmm. Still no go.
@Spinningbull thanks. That helped. Views is definitely seeing my module now.
I still don't see the external table listed under "View Type." Does Views check the database connection before it lists the table? I ask because I changed my DSN to something that is invalid, and there was no change to Views (i.e. no error message). Is there a way that I can check that Drupal is connecting to my external DB?
Pinging
Sorry, I am just pinging this to see if anyone has any more insight.
Me too
This is the best thread I've found for getting external data into views2, so I'd be interested in more info as well. There seems to be a real lack of info on the subject.
You may want to go to
You may want to go to admin/build/views/tools and check the box next to "Disable Views data caching" while you're developing your module. Also, I wrote up a brief tutorial on writing a module that exposes non-Drupal data to Views.
http://www.stevekarsch.com/2008/11/30/scratching-itch-myweather-views-2
Scroll down a little bit to get to the "Displaying the Forecast" section, which explains it a bit.
Another great way to learn about how Views "sees" tables and fields is to look at other modules that expose their data to views, like the twitter module. Or you could just look in the modules folder within Views itself to see how it exposes nodes, comments, users, etc. Check out comment.views.inc or user.views.inc.
Hope that helps!
Steve
Perfect!
karschsp, that is exactly what I've been looking for. I really appreciated the description of the handlers.
John
Mutiple db's in one query?
I was digging around in the view.inc file and noticed that it makes a call to drupal_set_active on line 688. Does this imply that views2 can only deal with one db at a time in a single view? We have two dbs on the same machine that use identical authentication. We've always coded our queries by hand with no problem (not even having a db line for the second db in settings.php), but now it has been decided to use views instead. Any Ideas?
John
re: Mutiple db's in one query?Mutiple db's in one query?
Bump. If anyone could answer this great question, it would help others.
I'm currently working towards finding the answer out for myself, but have a ways to go still...
I worked up an example
I worked up an example of a view that pulls in data from both Drupal and external tables at http://drupal.org/node/610128#comment-2174272. It works, but help solving that bug would be appreciated. :)
Interested in this topic, as well.
I am looking to tap an externally facing website we have (Drupal 4.7) to drive an internally facing site. I have successfully connected to the external database with my settings.php file and writing code into a node. I could probably write a module to do this, but thought it would be more efficient to merge the data in a view. Maybe not?
Can't connect to external drupal database "users table"
I am trying to connect to an external drupal site and get a list of its users to display as a staff directory. I can get every thing into the view but the query allways uses the wrong table. How can I tell the view to connect to the users table in the external database. Here is my code.
<?php
$data = array();
$data['staff_directory'] = array(
'table' => array(
'name' => 'staff_directory.users',
'group' => t('Staff Directory'),
'base' => array(
'field' => 'uid',
'title' => t('Database table staffweb.users'),
'help' => 'Staff Users - from Staff Web',
'weight' => 10,
'database' => 'users',
),
)
);
// For other base tables, explain how we join - variable does not join with any other tables
$data['staff_directory']['table']['join'] = array();
// StaffWeb Users Table -- Fields
$data['staff_directory']['name'] = array(
'title' => t('Name'),
'help' => t('Login Name'),
'field' => array(
'handler' => 'views_handler_field',
'click sortable' => TRUE,
),
'filter' => array(
'handler' => 'views_handler_filter_string',
'allow empty' => TRUE,
),
'argument' => array(
'handler' => 'views_handler_argument_string',
),
'sort' => array(
'handler' => 'views_handler_sort',
),
);
?>
Thanks for any Help.