PyDAL+MSSQL+FreeTDS: pyodbc.ProgrammingError

[Wei Wang]

I'm getting the error from a short pytest script:

E       pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query. (7405) (SQLExecDirectW)')

                                                                                                                                                                                             /usr/local/lib/python3.6/dist-packages/pydal/adapters/base.py:412: ProgrammingError

PyDAL shows the SQL command is:

command = "SELECT NacConnectionsView.eventdate, NacConnectionsView.nacmacaddress, NacConnectionsView.nacswitch FROM NacConnectionsView WHERE (NacConnectionsView.nacmacaddress LIKE '000000%' ESCAPE '\\');"

Where did that [ESCAPE '\\'] part came from?

Thank you for any help!

--Wei Wang

[villas]

This is the "LIKE predicate escape character". It's harmless, but google it if you wish.

https://github.com/web2py/pydal/blob/5a31738a28028392515b26d10a4a87df6a38d7a1/pydal/dialects/mssql.py#L163

FYI please also note:

Driver / Unicode Support

PyODBC works best with Microsoft ODBC drivers, particularly in the area of Unicode support on both Python 2 and Python 3.

Using the FreeTDS ODBC drivers on Linux or OSX with PyODBC is not recommended; there have been historically many Unicode-related issues in this area, including before Microsoft offered ODBC drivers for Linux and OSX. Now that Microsoft offers drivers for all platforms, for PyODBC support these are recommended. FreeTDS remains relevant for non-ODBC drivers such as pymssql where it works very well.