Drupal 7 webform submission to remote MSSQL server

Question

When users fill up a form on my Drupal 7 site, I need that information to go into a remote MS SQL server db. It's essentially a reservation system, so they fill up details like date/time and name, and when they click submit, these fields need to be pushed into a remote MS SQL db.

I'd like to know a) if this is possible and b) how do I go about implementing this?

Answer 1

A. Yes it is possible.

B. I recommend the below way, which is also the "Drupal" way of doing it.

1. Establish a connection to the remote database. Have a look here if you want to do it in your code, or have a look here if you want Drupal to take care of it by configuring settings.php. The key to switching between databases are db_set_active($database).
2. Run the queries you'd like to run against the MSSQL database. Just remember to put switch between the databases using db_set_active().

Answer 2

You can create a custom form in Drupal easily using its Form API in a custom module. In the submit handler for your form, you can insert the submitted data to your MS SQL database using any library avaiable in your PHP environment. Connection to the database is the tricky part, especially if your Drupal host is not running a Microsoft OS. In Drupal 7 running on IIS, you can use Drupal's Database API with the MS SQL driver. If your are not on IIS or Drupal 7, this is also doable without Drupal's Database API using PDO ODBC with unixODBC and FreeTDS. I did it, but I had a major issue when using typed bound parameters in my query, see Using typed bound parameters with PHP PDO-ODBC, unixODBC and FreeTDS.

That said, integration of applications at the database level is a bad idea. Such integration is tightly coupled. Any change to the schema of your MS SQL database or they way actual data are stored in columns will break your form submission. The best would be to have your reservation system provides a remote API to insert new data.