As part of an e-commerce site I'm working on, I had a need to update my product data with data for available inventory from a supplier. In this case, the supplier didn't provide a feed, but simply provided access to its MS SQL Server database, which meant that I had to connect to the database, query the table for new and changed records, and determine which records had been deleted. The trick in all this is connecting from a *nix system to a Microsoft database. On a Windows PC, I would just define an ODBC connection, but since that capability does not exist natively in *nix, it needs to be added. After doing some research, I decided to go with unixODBC and FreeTDS. During development I'm working on my MacBook Pro, and since I'm already using MacPorts for my AMP stack, I was just able to install the unixODBC and FreeTDS ports. Once those are installed, there are three different config files that need to be configured:
- freetds.conf
- odbc.ini
- odbcinst.ini
The first one that needs to be configured is freetds.conf, located in /opt/local/etc/freetds, since data in the other two files refers to it.
[bti_db] host = 123.45.67.89 (IP address changed to protect the innocent) port = 14333 tds version = 8.0
This contains the info that tells where the database is located. Also, the name of the connection ([bti_db] in this case) is important, because it will be referenced in the odbc ini files.
Next is odbcinst.ini, located in /opt/local/etc:
[FreeTDS] Description = TDS Driver (Sybase/MSSQL) Driver = /opt/local/lib/libtdsodbc.so Setup = /opt/local/lib/libtdsS.so FileUsage = 1 [ODBC Drivers] FreeTDS = Installed
As you can see, the two primary files referenced here are libtdsodbc.so and libtdsS.so. Finally, we need to configure odbc.ini, located in /opt/local/etc:
[bti_dsn] Driver = /opt/local/lib/libtdsodbc.so Description = BTI Data Database Trace = no Servername = bti_db Database = btidata [ODBC Data Sources] bti_dsn = FreeTDS
Notice how the Servername settings refers to the section of freetds.conf detailed above.
Now that the server has been configured, we need to write the script. As pointed out in a response to my pleas for help at Stackoverflow, I had to set two variables (ODBCINSTINI and ODBCINI) to identify the locations for my odbc.ini and odbcinst.ini files.
putenv("ODBCINSTINI=/opt/local/etc/odbcinst.ini");
putenv("ODBCINI=/opt/local/etc/odbc.ini");
Once that is done, it's just a matter of using the odbc_* functions to connect to the database and pull the data to my local MySQL database.
$user = 'my_user_id';
$pw = 'my_password';
$conn = odbc_connect("bti_dsn", $user, $pw);
if (!$conn){
if (phpversion() < '4.0'){
exit("Connection Failed: . $php_errormsg" );
}
else{
exit("Connection Failed:" . odbc_errormsg() );
}
}
if ($conn){
// This query generates a result set with one record in it.
$sql="SELECT top 100 * FROM inventory";
# Execute the statement.
$rs=odbc_exec($conn,$sql);
// Fetch and display the result set value.
if (!$rs){
exit("Error in SQL");
}
echo "Importing data from BTI...";
while ($row = odbc_fetch_object($rs)) {
$sql = "INSERT INTO bti_inventory_temp (item_id, vendor_item_id, upc, ean, item_description, item_text, attribute_keys, attribute_values, image_path, available, msrp,";
$sql .= " price, current_price, is_on_sale, sale_starts_on, sale_ends_on, is_on_closeout, is_new, manufacturer_id, manufacturer_name, category_id, category_name, sub_category_id,";
$sql .= " sub_category_name, group_id, group_description, group_text, associated_group_ids, updated_at, is_ormd) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %d, %d, %d, %d, %d, '%s', '%s', %d, %d, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %d)";
db_query($sql, trim($row->item_id), $row->vendor_item_id, $row->upc, $row->ean, $row->item_description, $row->item_text, $row->attribute_keys, $row->attribute_values, $row->image_path, $row->available, $row->msrp, $row->price, $row->current_price, $row->is_on_sale, $row->sale_starts_on, $row->sale_ends_on, $row->is_on_closeout, $row->is_new, $row->manufacturer_id, $row->manufacturer_name, $row->category_id, $row->category_name, $row->sub_category_id, $row->sub_category_name, $row->group_id, $row->group_description, $row->group_text, $row->associated_group_ids, $row->updated_at, $row->is_ormd);
odbc_close($conn);
}
From there, I can do comparisons with previous runs to see what's been added, changed, and deleted, and even use the Table Wizard module to expose my table to Views.
I had initially done this in a separate script and just bootstrapped Drupal to make all the Drupal functions available to it, but since then I've written a custom drush command that I call from a cron job, and it's working well.
If anyone has suggestions for ways to improve this, I'm open to them. I've shared this data with Damien Tournoud, since he has been the driving force behind the SQL Server driver for Drupal for D7, so hopefully it can be backported to D6 at some point.