6

I am currently working a academic medical department that uses a lot of MS SQL databases because faculty set them up - making us the maintainers :(

We also recently launched Drupal 6 site on LAMP, with the goal to move from what was supposed to be a .NET shop to PHP and MySQL ... go open-source!

My problem: Fetch data from external MS SQL database tables into Drupal which runs on MySQL, more specifically integrating Views 2 with this external data and dynamically show it on the site.

What I have found out so far:

  • People have tackled this problem since Drupal 4, but no one is owning it and so ... no stable solution.
  • Drupal can only run one type of db driver per site. Either MySQL/PostgreSQL/etc ... given you have the driver.
  • The MS SQL drivers that are there are not stable and still only solve the problem if my site would already run on MS SQL.
  • The are interesting modules that rely on the schema module to build table definitions and enable access to external MySQL data. (Table Wizard, Data API)

Right now: Looking into how the Data API module can be used for my purpose by creating a plugin for it for instance.

Any thoughts that would help me in how to access external MS SQL tables would be very appreciated.

Improve this question
2
  • Can you write a custom module that makes an external connection, imports the data into mysql, then you can use views with it?
    – Kevin
    Nov 24, 2010 at 16:09
  • That is one of the solutions I would be looking at, but would you have any pointers of how I can do this in a way that is generic and can be applied across multiple SQL databases?
    – JeroenEijkhof
    Dec 3, 2010 at 14:56

4 Answers 4

Reset to default
5

As pointed in the question, Views is currently unable to do it. It relies on Drupal's DB Layer which is currently unable to query different DB engines and doesn't support MS-SQL. Views 3 (currently in alpha) provides pluggable query backend so it must be possible to write a backend on top of PDO ODBC or an other mean to reach the MS-SQL DB. Queryin the MS-SQL DB from PHP will probably be a performance issue. Views' caching will certainly be needed to avoid unnecessary repeated queries to the server.

In order to work around this and avoid diving into Views 3, you may use Views (2) to query the Drupal's database, or another one on the same engine. And simply import the needed data into this database. This is what I recently did on a project with a small amount of daily updated data. I used PDO ODBC (with unixODBC and FreeTDS) to periodically import data from a MS SQL Server database into MySQL tables. The tables were then exposed to Views using Views' API. The Data module could also have been used for this purpose.

Another option may be to import the data from MS-SQL as Drupal's nodes. If the data are imported as nodes using a custom content-type with the needed fields (using CCK), you get Views integration as well as the tons of existing modules to leverage Drupal's nodes.

One easy solution to import external data into Drupal is the Feeds module. While initialy build as a rewrite of the Feed API to import RSS and Atom feeds, Feeds is actually a powerful framework to import data into Drupal. It can easily be extended through plugins and hooks to support different data sources, formats and processors.

Improve this answer
1
  • Thanks, I know about FreeTDS but found out that PHP has to rebuilt for it to work and currently the site I am dev on is on a UNIX cluster operated by an IT department that just said no to putting FreeTDS into their PHP build. I believe that the suggestions I will bring forward are the ones that use some external SQL to MySQL migration that can run on a cron and then pull external MySQL data into Drupal with Table Wizard or Data modules. Thanks
    – JeroenEijkhof
    Dec 3, 2010 at 16:55
2

It will most likely be easier for you to custom code pages and blocks than it will be to use views to access an external MS SQL database. It's hard enough to get views to access an external database, making it a MS SQL database only makes a hard thing harder.

Now I haven't gone through the Views query builder, bit I'm pretty sure that it will create queries with LIMIT in them. IIRC MS SQL can't handle LIMIT in queries, so not only will you need to switch database while views execute it's queries and switch back after wards, you will need to extend it's query builder to handle a MS SQL database and patch Drupal to add support for making queries to a MS SQL database.

All of this isn't impossible, but it will be a lot easier to import the data into the MySQL database instead. Drupal 6 performance on MS SQL wont be too good either.

Improve this answer
1
  • The LIMIT issues is a big one that the creators of the MSSQL driver for Drupal had trouble with. I am probably not going to create a separate module or a Views integration, that would be if they pay me for more work :)
    – JeroenEijkhof
    Dec 3, 2010 at 17:02
1

Drupal for windows does exsist. But I can't imagine that many modules will work on it. You could however share a theme accross two sites one lamp and one windows. Saving trying to connect to MS SQL from a lamp drupal instance.

Improve this answer
1
  • I know Drupal for Windows exists, but my current department site is on a cluster of UNIX machines, so no alternatives right now. But thanks.
    – JeroenEijkhof
    Dec 3, 2010 at 16:51
1

For anybody still asking the same question these days: there is a module for it, it's called Forena. It can be used to query (not update) any MySQL database.

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 mysql database part in the questions:

  1. Using a SQLite instead of MySQL 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 My SQL, MS SQL, 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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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