The programming features of the Microsoft ODBC Driver for SQL Server on macOS and Linux are based on ODBC in SQL Server Native Client (SQL Server Native Client (ODBC)). SQL Server Native Client is based on ODBC in Windows Data Access Components (ODBC Programmer's Reference).
An ODBC application can use Multiple Active Result Sets (MARS) and other SQL Server specific features by including
/usr/local/include/msodbcsql.h after including the unixODBC headers (
sqlucode.h). Then use the same symbolic names for SQL Server-specific items that you would use in your Windows ODBC applications.
The following sections from the SQL Server Native Client documentation for ODBC (SQL Server Native Client (ODBC)) are valid when using the ODBC driver on macOS and Linux:
- Communicating with SQL Server (ODBC)
- Connection and query timeout support
- Date/Time Improvements (ODBC)
- Executing Queries (ODBC)
- Handling Errors and Messages
- Kerberos authentication
- Large CLR User-Defined Types (ODBC)
- Performing Transactions (ODBC) (except distributed transactions)
- Processing Results (ODBC)
- Running Stored Procedures
- Sparse Columns Support (ODBC)
- SSL encryption
- Table Valued Parameters
- UTF-8 and UTF-16 for command and data API
- Using Catalog Functions
The following features have not been verified to work correctly in this release of the ODBC driver on macOS and Linux:
- Failover Cluster Connection
- Transparent Network IP Resolution (before ODBC Driver 17)
- Advanced Driver Tracing
The following features are not available in this release of the ODBC driver on macOS and Linux:
- Distributed Transactions (SQL_ATTR_ENLIST_IN_DTC attribute is not supported)
- Database Mirroring
- Profiling ODBC driver performance, discussed in SQLSetConnectAttr, and the following performance-related connection attributes:
- C interval types such as SQL_C_INTERVAL_YEAR_TO_MONTH (documented in Data Type Identifiers and Descriptors)
- The SQL_CUR_USE_ODBC value of the SQL_ATTR_ODBC_CURSORS attribute of the SQLSetConnectAttr function.
Character Set Support
For ODBC Driver 13 and 13.1, SQLCHAR data must be UTF-8. No other encodings are supported.
For ODBC Driver 17, SQLCHAR data in one of the following character sets/encodings is supported:
|CP437||MS-DOS Latin US|
|CP850||MS-DOS Latin 1|
|CP936||Simplified Chinese, GBK|
|CP950||Traditional Chinese, Big5|
|ISO-8859-1 / CP1252||Latin-1|
|ISO-8859-2 / CP1250||Latin-2|
|ISO-8859-8 / CP1255||Hebrew|
|ISO-8859-9 / CP1254||Turkish|
Upon connection, the driver detects the current locale of the process it is loaded in. If it uses one of the encodings above, the driver uses that encoding for SQLCHAR (narrow-character) data; otherwise, it defaults to UTF-8. Since all processes start in the "C" locale by default (and thus cause the driver to default to UTF-8), if an application needs to use one of the encodings above, it should use the setlocale function to set the locale appropriately before connecting; either by specifying the desired locale explicitly, or using an empty string for example
setlocale(LC_ALL, "") to use the locale settings of the environment.
Thus, in a typical Linux or Mac environment where the encoding is UTF-8, users of ODBC Driver 17 upgrading from 13 or 13.1 will not observe any differences. However, applications that use a non-UTF-8 encoding in the above list via
setlocale() need to use that encoding for data to/from the driver instead of UTF-8.
SQLWCHAR data must be UTF-16LE (Little Endian).
When binding input parameters with SQLBindParameter, if a narrow character SQL type such as SQL_VARCHAR is specified, the driver converts the supplied data from the client encoding to the default (typically codepage 1252) SQL Server encoding. For output parameters, the driver converts from the encoding specified in the collation information associated with the data to the client encoding. However, data loss is possible --- characters in the source encoding not representable in the target encoding will convert to a question mark ('?').
To avoid this data loss when binding input parameters, specify a Unicode SQL character type such as SQL_NVARCHAR. In this case, the driver converts from the client encoding to UTF-16, which can represent all Unicode characters. Furthermore, the target column or parameter on the server must also be either a Unicode type (nchar, nvarchar, ntext) or one with a collation/encoding, which can represent all the characters of the original source data. For avoiding data loss with output parameters, specify a Unicode SQL type, and either a Unicode C type (SQL_C_WCHAR), causing the driver to return data as UTF-16; or a narrow C type, and ensure that the client encoding can represent all the characters of the source data (this is always possible with UTF-8.)
For more information about collations and encodings, see Collation and Unicode Support.
There are some encoding conversion differences between Windows and several versions of the iconv library on Linux and macOS. Text data in codepage 1255 (Hebrew) has one code point (0xCA) that behaves differently upon conversion to Unicode. On Windows, this character converts to the UTF-16 code point of 0x05BA. On macOS and Linux with libiconv versions earlier than 1.15, it converts to 0x00CA. On Linux with iconv libraries that do not support the 2003 revision of Big5/CP950 (named
BIG5-2003), characters added with that revision will not convert correctly. In codepage 932 (Japanese, Shift-JIS), the result of decoding of characters not originally defined in the encoding standard also differs. For example, the byte 0x80 converts to U+0080 on Windows but may become U+30FB on Linux and macOS, depending on iconv version.
In ODBC Driver 13 and 13.1, when UTF-8 multibyte characters or UTF-16 surrogates are split across SQLPutData buffers, it results in data corruption. Use buffers for streaming SQLPutData that do not end in partial character encodings. This limitation has been removed with ODBC Driver 17.
You can make a dedicated administrator connection (DAC) using SQL Server authentication and host,port. A member of the Sysadmin role first needs to discover the DAC port. See Diagnostic Connection for Database Administrators to discover how. For example, if the DAC port were 33000, you could connect to it with
sqlcmd -U <user> -P <pwd> -S <host>,33000
DAC connections must use SQL Server Authentication.
The UnixODBC driver manager returns "invalid attribute/option identifier" for all statement attributes when they are passed through SQLSetConnectAttr. On Windows, when SQLSetConnectAttr receives a statement attribute value, it causes the driver to set that value on all active statements which are children of the connection handle.