How to prevent unicode strings being passed as search parameters

[fzzylogic]

Not at all sure this is the right place to ask.. I'm running django 1.11.12 and dango-pyodbc-azure 1.11.12.1 with driver "ODBC Driver 13 for SQL Server" on Windows 10 and it works fine. However, when running queries via Django's ORM, i noticed about a 3x slowdown using certain queries that convert to LIKE in sql (e.g. field__startswith). Pinned it down in sql profiler to query parameters all being passed in as N'parameter', even when the underlying db column is not unicode. For examle, sql profiler output may show:

declare @p1 int
set @p1=6
exec sp_prepexec @p1 output,N'@P1 nvarchar(6)',N'SELECT TOP 1 [dbo].[MyView].[MyVarcharColumn] FROM [dbo].[MyView] WHERE [dbo].[MyView].[MyVarcharColumn] LIKE @P1 ESCAPE ''\''',N'Hello%'
select @p1

Column MyVarcharColumn really is a varchar column. I've tried many things, including encoding strings to ascii and passing various driver flags via 'extra_params' (such as "Auto Translate", "driver_charset", "collation" etc) and also tried "SQL Server Native Client 11.0" and setting conn.setencoding and conn.setdecoding to 'latin1' in base.py as an attempt to use what's stated here. Would really appreciate any pointers on this.

[FRReinert]

@fzzylogic, did you manage to workaround this issue? I'm strugling with the same problem :/

I have query which takes like 5ms using T-SQL but using sp_prepexec with NVARCHAR instead of VARCHAR it takes 5~9 seconds to run...

[fzzylogic]

@FRReinert Wish that were so, but i ended up by changing to calling a stored procedure instead of directly doing a select via sp_prepexec, and that overcame the speed problem (or at least seemed to). Even though calling an sp also happens using sp_prepexec unicode encoded parameters, i suspect it works because the sp allows one to define the data type of the input parameters and so probably converts to the correct datatype before doing the query. Pretty convoluted approach, but in my case it turned out to only be necessary in one place, an advanced search, and i found some good guidance on making an advanced search sp here: http://www.sommarskog.se/dyn-search-2008.html. Hope that helps.

[FRReinert]

Hey, i also managed to fix that by doing a monkey patch on the Django engine and using an encoding pyodbc feature, which you can find here https://github.com/mkleehammer/pyodbc/wiki/Unicode

I created a new base.py file into a folder and overrided get_new_connection() on the DatabaseWrapper class, it's also important to modularize the folder by creating an __init__.py file. Something like that:

# base.py

from sql_server.pyodbc.base import *

class DatabaseWrapper(DatabaseWrapper):
    '''Custom data types'''

    def get_new_connection(self, conn_params):
        conn = super().get_new_connection(conn_params)
        conn.setencoding('utf-8')
        return conn

Then i changed my engine on settings.py to point to this folder.

database': {
        'NAME': 'xxxxx',
        'ENGINE': 'Core.CustomMSSQL',
        'HOST': 'xxxxx',
        'USER': 'xxxxx',
        'PASSWORD': 'xxxxx',
        'OPTIONS': {
            'driver': 'xxxxx',
        },