Which ODBC Driver to Use

Due to the many ODBC drivers/versions Microsoft has come up with to date, let alone the many database access technologies, it is quite understandable that it can be unclear what driver and/or version to choose when creating an DSN in the ODBC Data Source Administrator or defining in the connection profile of the framework.

In this article an attempt is made to clear up some of that confusion in relation to which ODBC driver to use when trying to access a database via the Ometa framework.

What NOT to Use!

What definitely should NOT be used are the ODBC drivers starting with the name "SQL Server Native Client", e.g. "SQL Server Native Client 11.0" or "SQL Server Native Client RDA 11.0".

These are drivers are deprecated by Microsoft for several years and thus, should not be used at all.

SQL Server ODBC or Microsoft ODBC Driver?

Remaining to choose from, are the following ODBC drivers: "SQL Server ODBC Driver" and "Microsoft ODBC Driver for SQL Server".

Which driver to take from the point of view from the Ometa framework, depends on requirements of available features. The paragraphs below describe the differences between each driver and the pro and cons of using them.

SQL Server ODBC

This driver is the oldest driver. Was added to Windows Vista, and later, as part of WDAC (= Windows Data Access Components). This means that this driver is still supported and will always be available after installation. Also, it seems that this driver deviates a bit from the ODBC standard. By default the ODBC standard does not support the SQL Server datetimeoffset column type but this driver does support the type.

However, the supported feature set is limited to SQL Server 2016 and any new features added to SQL Server 2017 and later are not supported. Most likely only bugs and security issues will be fixed. The driver cannot be downloaded separately and updates are only available via Windows Update.

Note

The limitation of feature set support does not prevent of connecting to SQL Server 2017 and later with this driver. This only means that specific features of those SQL Server versions are not supported.

Driver Selection - SQL Server ODBC

Microsoft ODBC Driver

The latest driver from Microsoft. Gets new features of later SQL Server versions and other updates, and is released quite often (at time of writing version 18 is available). This driver is also recommended by Microsoft going forward for new application development. Every release can be downloaded separately from the Microsoft site.

A "drawback" here, from the point of view of the framework, is that the driver adheres closely to the ODBC standard. This means that types like the datetimeoffset column type are not supported, triggering an error when an attempt is made to read data from such columns. Requiring the need to add conversion functions in SQL statements to cast data from one type to another with potential precision loss.

Selected Driver - Microsoft ODBC

Note

The additional feature set support may not be accessible directly via the framework ODBC interface. Mostly those features can only be used when the driver is directly called from applications like those written in C/C++ or similar. In the case of the Ometa framework, the framework interface does some translation to execute the SQL statements on the ODBC driver, and also the Microsoft .NET Framework runtime sits in between which may in turn limit the use advanced features.

Something to keep in mind when selecting the proper ODBC driver.

Which One to Take?

In conclusion, the following table lists the most important differences:

Name Feature Set Support Updates
SQL Server ODBC Limited to SQL Server 2016 features but supports datetimeoffset. Only via Windows Update.
Microsoft ODBC Adheres closely to ODBC standard, no datetimeoffset but regularly features added. Separately downloadable from Microsoft site.

The easiest option to choose and also the first choice by Ometa is the SQL Server ODBC driver.

The main reason for this choice: that driver is always available and supports a data type, datetimeoffset, that is used in several tables of the Ometa framework databases, e.g. logging, case management. Unless there is a need for specific SQL statements and other new features of SQL Server 2017 and later, this is the driver to go for.

When one of these items are applicable, the Microsoft ODBC driver can be selected:

  • tables do not have columns of datetimeoffset data type, for example: in separate solution database;
  • data type conversions are coded into the SQL statements;
  • there is need for a specific feature.

References

Below are several webpages that were consulted in writing this article and can provide some more information in depth if needed.