Azure Data Warehouse support

[kdazzle]

I'm getting a couple of errors when connecting to a SQL Data Warehouse database in Azure:

Dateformat error

File "/Users/kvalade/Repos/inventory-django/.env/lib/python2.7/site-packages/django/db/backends/base/base.py", line 199, in ensure_connection
    self.connect()
  File "/Users/kvalade/Repos/inventory-django/.env/lib/python2.7/site-packages/django/db/backends/base/base.py", line 173, in connect
    self.init_connection_state()
  File "/Users/kvalade/Repos/inventory-django/.env/lib/python2.7/site-packages/sql_server/pyodbc/base.py", line 360, in init_connection_state
    cursor.execute('SET DATEFORMAT ymd; SET DATEFIRST %s' % datefirst)
  File "/Users/kvalade/Repos/inventory-django/.env/lib/python2.7/site-packages/sql_server/pyodbc/base.py", line 538, in execute
    return self.cursor.execute(sql, params)
ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]Setting DateFormat to ymd is not supported. (104409) (SQLExecDirectW)')

ESCAPE error

If I comment out the DATEFORMAT line mentioned above, I can get further, but error out on queries that use ESCAPE. For example, when I query Foo.objects.filter(bar__icontains='B'), the error is thus:

  File "/Users/kvalade/Repos/inventory-django/.env/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/kvalade/Repos/inventory-django/.env/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/kvalade/Repos/inventory-django/.env/lib/python2.7/site-packages/sql_server/pyodbc/base.py", line 538, in execute
    return self.cursor.execute(sql, params)
ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Parse error at line: 1, column: 153: Incorrect syntax near 'ESCAPE'. (103010) (SQLExecDirectW)")

So if I go to pyodbc.base and change the operators dict around line 103, and remove the ESCAPE operator at icontains, then the query works.

operators = {
    icontains: "LIKE UPPER(%s)",
}

And that's fine for simple things, though I'm guessing that will introduce more errors and maybe the possibility of injection.

This is only my first day with Azure Data Warehouse, so I'm not sure yet if ESCAPE isn't allowed here or what the problem could be. It seems pretty arbitrary.

Env

I'm using OSX and my Django settings are:

'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'foo',
        'HOST': 'foo.database.windows.net',
        'PORT': '1433',
        'USER': config.get('databases', 'SQL_USERNAME'),
        'PASSWORD': config.get('databases', 'SQL_PASSWORD'),
        'AUTOCOMMIT': False,
        'OPTIONS': {
            'driver': 'FreeTDS',
            'host_is_server': True,
            'extra_params': 'TDS_VERSION=7.3',
        }
}

django-pyodbc-azure==1.10.0.1 pyodbc==3.0.10

[kdazzle]

Ah, I think I figured out why it's crashing on the DATEFORMAT line.

The line in question is in pyodbc.base:

cursor.execute('SET DATEFORMAT ymd; SET DATEFIRST %s' % datefirst)

The database that I'm connecting to is part of the Azure data warehouse and, according to these docs, DATEFORMAT cannot be overridden.

[michiya]

Azure SQL Data Warehouse doesn't support many features that are fundamental for Django models (primary key, foreign keys, constraints etc.) as described in the document below. It would be nice if django-pyodbc-azure supports Azure SQL Data Warehouse, but it lacks feasibility unless Azure SQL Data Warehouse supports those features. https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-overview#unsupported-table-features