Installation guide

4. Accessing Popular Databases

[mssql]

Driver = /usr/local/freetds/lib/libtdsodbc.so

Description = MS SQL Server 2008 running on Monet

Trace = 0

Servername = MONET

Database = tempdb

Note that the libtdsodbc.so file may be located in a different directory on

your machine.

• Using these settings, you can then connect to SQL Server using a simple

connection string such as:

"DSN=mssql;UID=username;PWD=password"

4.1.2 General Notes

ODBC API Extensions and the SQL Server Native Client

The ODBC API is the native MS SQL Server call level interface and provides the

best performance when interfacing to SQL Server.

Microsoft has also extended ODBC with various custom extensions they make

available in their SQL Server Native Client API. If you need support for those

extensions, please contact support@egenix.com

Static vs. forward-only Cursors

MS SQL Server supports static ODBC cursors, but mxODBC defaults to forward

only cursors.

While static cursors allow scrolling through the result set and also provides ways

of accessing the correct

.rowcount value, it does come with a significant

performance penalty. We have seen slow-downs in fetching rows of 2-3x times for

average queries, up to 300x for simple ones, so we recommend not using static

cursors on connections that do not need scroll support. To enable static cursors,

you can adjust the

connection.cursortype:

connection = mx.ODBC.Windows.DriverConnect(…)

connection.cursortype = mx.ODBC.Windows.SQL.CURSOR_STATIC

# All cursors created on connection will then default to the static

# cursor type.

Please refer to section 5.8 ODBC Cursor Types for more details on cursor types.

Timestamp Resolution

Unlike many other databases which support nanosecond precision on

timestamps, MS SQL Server has a limitation when it comes to the seconds part of

timestamp values. Version of SQL Server earlier and including SQL Server 2005

only accept timestamp precisions of 1 millisecond, while version SQL Server 2008

and later work with precision of 100 nanoseconds.

Please note that in mxODBC 3.2, mxODBC used to default to static cursors.