HELP! DB errors from my Drupal/CiviCRm instalation & Views Module

[nvisioncurtis]

Hi,  

I have been setting up a website with CiviCRM and Drupal.  I am slowly added modules and configuring the site.

Versions used
Drupal 6.13
CiviCRM 2.27
views-6.x-2.6.tar.gz

What I am doing...
I wanted to show CiviCRM event on a calendar and so I was following this link
  http://wiki.civicrm.org/confluence/display/CRMDOC/Using+Drupal+Calendar+with+CiviEvent

So after I enable the calendar view and actually looked at the calendar I am getting these database errors.


user warning: Table '414442_vetshealing.drupal_civicrm_event' doesn't exist query: SELECT civicrm_event.id AS id, civicrm_event.start_date AS civicrm_event_start_date, civicrm_event.title AS civicrm_event_title FROM drupal_civicrm_event civicrm_event WHERE (civicrm_event.is_active <> 0) AND (civicrm_event.is_public <> 0) ORDER BY civicrm_event_start_date ASC in /mnt/target02/349486/414442/www.vetshealing.org/web/content/sites/all/modules/views/includes/view.inc on line 759.
user warning: Table '414442_vetshealing.drupal_civicrm_event' doesn't exist query: SELECT civicrm_event.id AS id, civicrm_event.start_date AS civicrm_event_start_date, civicrm_event.title AS civicrm_event_title FROM drupal_civicrm_event civicrm_event WHERE (civicrm_event.is_active <> 0) AND (civicrm_event.is_public <> 0) ORDER BY civicrm_event_start_date ASC in /mnt/target02/349486/414442/www.vetshealing.org/web/content/sites/all/modules/views/includes/view.inc on line 759.


It appears that the Drupal table prefix I used is being applied to the Civi table in this view.

Do you think this is a Drupal views issue or a CiviCRM issue?

I loaded the default Civi data and that info appears in the CiviCRM Upcomming events block. Why would the table prefix drupal_ which added to the Drupal Install be added for the calendar view but not the block?
 
Looking at the database the table civicrm_event does exist, but drupal_civicrm_event of couse does not and shouldn't. 

What's going on here?

How can I fix this issue?

 

[nvisioncurtis]

CiviCRm shouldn't use the Drupal Prefix in the Cal view for a Civi table.

I renamed my tables and removed the prefix and that seems to fixe the issue.

[nvisioncurtis]

FYI - I could be wrong here but I don't believe is says anywhere in the installation not to use a Drupal table Prefix.

That was the specific issue I had here.

[Hershel]

FWIW I believe I have seen this issue reported elsewhere as well.

Dave, is this issue expected to be fixed soon? If not, let me know and I could make a note somewhere in the docs about this--that if a Drupal db prefix is used, then Views integration will fail.

[Donald Lobo]

the documentation on the wiki tells you how to set the drupal prefix array. You might want to improve that docs to indicate what to do if civicrm and drupal are sharing the same database AND the drupal tables are prefixed

lobo

[bob_bingham]

I am running into the exact same problem trying to build a Drupal view of the civicrm events.
Seems like the civicrm events view needs to read from the civicrm database, and drupal is looking for the event table in its own database.
So I am getting the same error.

Lobo mentions (above) documentation on setting up multiple databases in Drupal? Where is this? I have searched high and low....
And then I suppose there would need to be some code to switch between the two databases that I'd need to insert somewhere?

I feel as if I am running into the age-old problem:
the urge to solve the problem and the simultaneous realization that solving the problem is, in fact, perhaps the least elegant solution.

Is there an easy way to deal with this or is it time to scrap using the civicrm calendar in Drupal and just use Drupal's calendar?

[Hershel]

> I feel as if I am running into the age-old problem:
> the urge to solve the problem and the simultaneous realization that solving the problem is, in fact, perhaps the least elegant solution.

Not sure what you mean by that.

> Is there an easy way to deal with this or is it time to scrap using the civicrm calendar in Drupal and just use Drupal's calendar?

There is a simple solution, so long as you have two DBs, one for Drupal and one for CiviCRM:

http://wiki.civicrm.org/confluence/display/CRMDOC/Views2+Integration+Module

[nvisioncurtis]

This would have been helpful to me back when I was trying to figure out my original issues.  I opted at the time to put CiviCrm in Drupal's database with no prefixs.

Now that I have CiviCRM and Drupal in a single database with no table prefixes, is there a way to integrate the calendar with other Drupal Events? Currently I have two separate calendars cause I couldn't make it work.

[Donald Lobo]

check:

http://wiki.civicrm.org/confluence/display/CRMDOC/Using+Drupal+Calendar+with+CiviEvent

lobo

[nvisioncurtis]

Thanks Lobo,

I think I have seen this before but gave up on it.  I will check in to it again and give it another go.

[bob_bingham]

Hershel,
I imagine that "simple" doesn't include finding all the places I need to insert code like this:
<?php
db_set_active('mydb');

db_query('SELECT * FROM table_in_anotherdb');

//Switch back to the default connection when finished.
db_set_active('default');
?>

I am assuming that Lobo's link is REALLY all I have to do to get this working and that the fact that it isn't is because of some older version of Date or Calendar or something like that?

[bob_bingham]

OK. So Lobo's link was the most informative.
Hershel's suggestion involves adding snippits of code to tell Drupal which database to read from as far as I can tell everywhere where Drupal needs to read from civicrm's database - not exactly an out of the box type solution.

So following Lobo's link, and then updatiung the Calendar and Date modules past the versions requested, I am still getting:

user warning: Table '_drupal.civicrm_event' doesn't exist query: SELECT civicrm_event.id AS id, civicrm_event.start_date AS civicrm_event_start_date, civicrm_event.title AS civicrm_event_title FROM civicrm_event civicrm_event WHERE (civicrm_event.is_active <> 0) AND (civicrm_event.is_public <> 0) AND (civicrm_event.event_type_id in ('7')) ORDER BY civicrm_event_start_date ASC in /httpdocs/sites/all/modules/contrib/views/includes/view.inc on line 759.


Probably a way to get this working if I had another day to mess with this type of thing. So I guess time to stop wasting time on this! I'm officially throwing in the towel on trying to get this Drupal site working with Civievents and instead using Drupal's events and calendering using CCK and Views.

If anyone manages to wrestle a stable Drupal/Civicrm combination into working order, please take a snapshot of the entire thing, I imagine development on this is a bit tough trying to keep up with several moving targets (Drupal development, views development, Date development, Calendar development, etc etc)

My project would perhaps be better served with a standalone installation of Drupal, and another of Civicrm. Integrating them is proving too much hassle.

[Hershel]

Hershel's suggestion involves adding snippits of code to tell Drupal which database to read from as far as I can tell everywhere where Drupal needs to read from civicrm's database - not exactly an out of the box type solution.

I figured out, eventually, what you mean by this. The code mentioned on http://drupal.org/node/18429, which I believe is what you quoted, is not required for Views integration. At all.

All you have to do for Views integration is visit civicrm/admin/setting/uf?reset=1 on your site and find the View Integration instructions there--you add a bit of code to your settings.php to tell Views about your CiviCRM database and that's it.

If anyone manages to wrestle a stable Drupal/Civicrm combination into working order, please take a snapshot of the entire thing, I imagine development on this is a bit tough trying to keep up with several moving targets (Drupal development, views development, Date development, Calendar development, etc etc)

I have a site with this. As far as I recall, the latest stable versions of all relevant modules available on Drupal.org have all fixes necessary. I installed them, did the Views Integration step as mentioned above, and that was it. It should not be difficult. If you are having difficulties, please let us know specifically what the issue(s) are.

[bob_bingham]

Cool. Thanks Hershel. That was extremely helpful - and didn't take forever.

The documentation here:
http://wiki.civicrm.org/confluence/display/CRMDOC/Using+Drupal+Calendar+with+CiviEvent

needs to be updated to reflect the need to add the prefix array in the drupal settings (and where that can be found in the civicrm admin page) and then of course, the doc needs to mention that permission needs to be granted in mysql to the drupal user to access the civicrm tables.

Thanks for the help!

[Hershel]

Glad to hear it was easy.

Please review that wiki page again and let me know if it's now accurate.

Thank you.