CODEWITHSUNDEEP
phpsql-serverdatabasedrupal
Toby Allen
Toby Allen

Reputation: 11213

How to access data in a separate MS SQL database from within a Drupal site?

I would like to set up a Drupal site for some of our customers to access their info externally to us. I have decided to implement this in Drupal to take full effect of all it has to offer. However our current system is in mssql which is not really supported by Drupal and not even really supported by PHP anymore.

So my question is how should I link the two databases (eg get info into Drupal for modules etc).

  1. Move existing system to MySQL for easy integration (not likely at the moment)
  2. Simply provide IFrames to pull in the info I require as pages
  3. Get PDO MSSQL working on machine I've installed Drupal and make my own connection in Drupal module when I need it. (is this frowned upon? I may want to move machines)
  4. Create an API for our main system to get the information out it requires. Set of URLs that return JSON

The easiest is probably 3 but I think I may live to regret that since MSSQL support is being rolled back and it seems like a bit of a hack.

I think I will go with 4 but was wondering whether people feel this is a good plan. Is it realistic from a performance point of view? Can anyone suggest a good API framework / route to go down?

Upvotes: 1

Views: 2284

Answers (2)

Pierre.Vriens
Pierre.Vriens

Reputation: 2117

The Forena module can be used to query (not update) all sorts of databases, such as a MS SQL database, and to establish database connections to the external databases. And it comes with various hooks to use Forena from within another module.

For more details about Forena, 2 types of documentation are available:

  • Community documentation.

  • Documentation that comes with Forena, which you can access right after install and enable of the module. Checkout the demo site for an online example of the current:

    • Forena documentation - use the link 'Reporting documentation' or visit relative link /reports/help.
    • Forena samples - use the link 'Reporting samples' or visit relative link /reports/samples (these samples are fully functional, so make sure to experiment a bit with it, such as the drill downs available on the SVG Graph sample).

The newest 7.x-4.x version also includes an amazing (I think) UI for either creating your reports (the WYSIWYG report editor) and/or for creating your SQL queries (the Query Builder).

Here are some variations of the MS SQL database part:

  1. Using a SQLite instead of MS SQL database: the Forena samples actually get shipped including a (Tiny) SQLite database. Go check it out in the demo site: the data shown there are data contained in the sampledb, which is in SQLite format.
  2. Forena comes with a full suite of Supported database connections ... such as MySQL, Oracle, Postgress or any PDO compliant variation.

Enough reasons for considering giving Forena a try? While doing so, use it's issue queue for any type of support/docu requests you may have.

Be aware: I'm a co-maintainer of Forena.

Upvotes: 2

Berdir
Berdir

Reputation: 6891

This doesn't really answer your actual question, but you might want to check out https://drupal.stackexchange.com/questions/1631/mssql-or-sql-server-wrapper-for-custom-module.

Not sure what you mean with " MSSQL support is being rolled back", but Microsoft created a PDO driver recently and a main reason for that was Drupal 7.

Upvotes: 0

Related Questions