1

I'm trying to connect to an external data source within out intranet site. The database is Intersystems Cache. Our web-servers are running Cent OS 6.4, so I have setup the necessary unixODBC drivers to connect to it and this is successfully working. What I'm struggling to work out is how I access this data from within Drupal.

I know I could just call odbc_connect within my code, but this isn't the Drupal way. I also loose the benefits of Memcached etc.

Would it be possible to use PDO's ODBC driver, which I think Drupal uses anyway?

Thank you.

1

As @Bala said you want to try to install the PHP ODBC driver. Drupal does not use this driver by default (a drupal install may use MySQL, Postgres, or I believe MsSQL on install if those drivers are found by PHP).

Once the ODBC driver is installed for PDO, you can inform Drupal of your 2nd database by following the Multiple Databases Documentation. Bala linked you to a previous example I gave using ODBC to an MsAccess database file -- you're request is simply a different DBMS vendor. The same approach should work InterSystems Cache DB by following the concepts in the documentation page linked about.

Note that ODBC is a slow interface as its essentially a wrapper to your client Database. Its not as fast as a full native DBMS driver. Keep that in mind when querying and using your data.

  • 1
    Thank you for the pointers. I now successfully have PDO working with ODBC using native PHP, but I'm struggling to find the correct way of adding it to settings.php. I have a DSN setup in odbc.ini so I can simply go $dbh = new PDO("odbc:CCMACDF"); and I'm connected. If I do the same in settings.php, only adding the driver = 'odbc:CCMACDF' key/value, it doesn't work. – BeardedGeek Mar 18 '14 at 16:18
  • What do you mean "it doesn't work" in drupal you would use db_set_active('YourDatabaseKey for ODBC'); $result = db_query('select FOO;'); not $dbh = new PDO(...);. How to select the database connection to use and issue a query is in the documentation link I provided. – tenken Mar 18 '14 at 18:24
  • 1
    The code posted above is showing the code used to test in native PHP to make sure PDO iss working outside Drupal. I use this within drupal: db_set_active('CCMACDF'); db_query("SELECT * FROM dbo.iActivityCodeStat"); db_set_active();. settings.php is $databases['CCMACDF']['default'] = array( 'driver' => 'odbc', 'hostname' => '####', 'namespace' => 'CCMA_STAT', 'uid' => '####', 'password' => '####', ); but the error is Failed opening required '/includes/database/odbc/database.inc' – BeardedGeek Mar 19 '14 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.