Installation guide

mxODBC - Python ODBC Database Interface

See http://msdn.microsoft.com/en-us/library/ms188635.aspx for details on the

various formats and how to configure them.

Multiple active result sets (MARS) on a single connection

The SQL Server Native Client per default does not support having more than one

active result set per connection. This means that you cannot have two cursors on

the same connection, which both have active result sets.

Starting with SQL Server 2005, there is a connection option which can be used to

enable the MARS feature of SQL Server Native Client

, which enables working with

multiple active result sets on the same connection:

from mx.ODBC.Manager import DriverConnect, SQL

# Enable MARS on this connection:

options = [(SQL.COPT_SS_MARS_ENABLED, SQL.MARS_ENABLED_YES)]

# Pass the options to the connection constructor:

db = DriverConnect('DSN=mssqlserver2008;UID=sa;PWD=sa-passwd',

connection_options=options)

This option is available on SQL Server Native Client for Windows and Linux. The

FreeTDS ODBC driver 0.91 does not support this option.

Parameter Binding with SQL Server 2012 and later

SQL Server 2012 and later have changed the SQL parser or optimizer to use a

more stringent method of determining the unkown parameter input types.

This results in SQL statements such as "

WHERE col1 >= ? + ?" to fail with an

error such as

mx.ODBC.Error.ProgrammingError: ('42000', 11503,

"[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The

parameter type cannot be deduced because a single expression

contains two untyped parameters, '@P1' and '@P2'.", 10191).

There are two solutions to this incompatibility between SQL Server 2008R2 and

SQL Server 2012:

1. use explicit casts to tell the SQL parser which type to expect from the

right hand side operation, e.g. "

WHERE col1 >= ? + CAST(? as int)".

This gives the second argument an explicit type and allows the parser to

deduce the type of the result,

2. run the query using

.executedirect() instead of .execute(). This

causes the parameter values to be embedded into the SQL command and

allows the SQL parser on the server side to determine the types at

preparation time by looking at the values, rather than just their

placeholders.

Stored procedures with output parameters and result sets

MS SQL Server stored procedures are fully supported by mxODBC, but there is

one important detail to know when using stored procedures which use both

output or input/output parameters and result sets.