MSSql or SQL Server Wrapper for custom module?

Question

I'm writing a custom module and I need to access data that is currently stored in an MSSql database. It would be great if I could use the built-in database abstraction layer, but obviously that isn't possible.

Are there any modules or resources that provide a similar abstraction layer for MSSql?

If not, how would I go about writing my own? I feel very badly about just putting the connection and query strings directly in the module, it seems so.. antiquated.

Answer 1

Since you didn't specify a Drupal version, I'll try to answer for both Drupal 6 and 7.

Drupal 7

See http://drupal.org/project/sqlsrv

Drupal 6

Even if there would be SQL Server integration in the official db-api, it wouldn't help you much because with Drupal 6, you can only connect to multiple databases of the same type. This is why https://github.com/dereine/views_pgsql was created for example.

However, it might be an option to use the DBTNG project, which is a backport of the new Drupal 7 database abstraction layer and use that in combination with the sqlsrv project. I haven't tested that but it could work. And if not, maybe with a few changes (which you could contribute back)

Answer 2

Module Forena is another alternative to consider. It can be used to query (not update) any MS SQL 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 MS SQL 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 MySQL, 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.