SQL Server ODBC Driver Conversion – 2022

Web Services’ plan to migrate from EasySoft’s ODBC driver for SQL Server to Microsoft’s driver.

What Is Changing?

Web Services is migrating all our Linux-based hosting environments from the third party EasySoft ODBC (Open Database Connectivity) driver for sites using Microsoft SQL Server databases to the first party driver from Microsoft. This change will only impact sites using SQL Server databases, and it will require at least some work on the part of site developers. We’ve already migrated our internal servers and services to the new driver, and as a result have an idea what developers using our services will go through. Below we’ll discuss the problems we ran into as well as how to solve those problems.

We expect this migration to be completed before the end of fall 2022.

Why Is It Changing?

The EasySoft driver has been a reliable, stable, performant driver for years, but it is quite costly. Microsoft began offering a completely free SQL Server driver for Linux some time ago, but it was very limited in what features it supported. As of their version 17 driver, it is now fully comparable to their official Windows driver, and can take the place of the EasySoft driver on our Linux platforms. Making this change will save Purdue on licensing costs, since the Microsoft driver is free, and will offer the features and benefits of a first-party driver, including new SQL Server database features being made available as they’re released.

How Will It Be Changed?

We have already identified which sites are using MS SQL databases on our Linux platforms and will be reaching out to site owners individually. Site-by-site, and starting in Development, we will switch the driver behind each site’s ODBC configuration in coordination with site developers. Once all sites have been switched over and are stable, we will disable the EasySoft driver after announcing the change through Change Management. We will then remove the EasySoft driver entirely at some later time.

What Do I Need to Do?

If your PHP website is not using a Microsoft SQL Server database, you don’t need to do anything. Otherwise, most sites will require no changes to convert to the new driver, but at a minimum will need to be tested to ensure everything using the database still works. We don’t know how to test your site, so we need you to do that and report issues to us as you find them. We will be reaching out to the owners of sites we know are using ODBC connections to a Microsoft SQL Server database to walk each through this migration.

Some sites will find they have compatibility issues with the Microsoft driver and will need to make changes to code. You’ll find the most common issues and solutions to them below. For the most part, these issues stem from EasySoft supporting features that Microsoft does not officially support or recommend using, so making these corrections will ensure your code continues working with your SQL Server database regardless of the driver used.

What Are Common Solutions to Problems?

Date Formats

Dates in queries should have a leading zero for months and days less than 10. The EasySoft driver would silently fix this, while the Microsoft driver will throw an exception and parse the date incorrectly. Make sure to use standard date formats like YYYY-MM-DD within queries.

Old PHP Code (one line):

$expdate = join('-', array($date['year'], $date['mon'], $date['mday']));

This will generate an error message like the following under the Microsoft driver:

PHP Warning:  odbc_execute(): SQL error: [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification, SQL state 22005 in SQLExecute in /var/www/html/root/www.purdue.edu/examplesite/example.php on line 27, referer: https://dev.www.purdue.edu/examplesite/example.php

Fixed PHP Code (one line):

$expdate = join('-', array($date['year'], ($date['mon'] < 10 ? '0' . $date['mon'] : $date['mon']), ($date['mday'] < 10 ? '0' . $date['mday'] : $date['mday'])));

Prepare and Execute Statement Ordering

The Microsoft driver does not support nested “prepare” and “execute” sets used against the same database object. Update code to execute each prepared statement in sequence instead.

Old PHP Code (four lines):

$insertResult = odbc_prepare($dbConnection, $insertStatement);
$deleteResult = odbc_prepare($dbConnection, $deleteStatement);
odbc_execute($insertResult, array($newId, $newValue));
odbc_execute($deleteResult, array($oldId, $oldValue));

This will generate an error message like the following under the Microsoft driver:

PHP Warning:  odbc_execute(): SQL error: [Microsoft][ODBC Driver 17 for SQL Server]Function sequence error, SQL state S1010 in SQLDescribeParameter in /var/www/html/root/www.purdue.edu/examplesite/example.php on line 42, referer: https://dev.www.purdue.edu/examplesite/example.php

Fixed PHP Code (four lines):

$insertResult = odbc_prepare($dbConnection, $insertStatement);
odbc_execute($insertResult, array($newId, $newValue));
$deleteResult = odbc_prepare($dbConnection, $deleteStatement);
odbc_execute($deleteResult, array($oldId, $oldValue));