I am getting an incompatible comparison error detailed below, but it's dependent on the size of the string I'm passing to the filter. Anyone know what the cause of or solution to this error would be, or where I can dig in deeper to identify the root-issue?
When I query filtering with a string of length 255 I receive a False response as I expected (doesn't quite match my inserted column value):
>>> from core.models import TestTable
>>> test_str = '--publication_filter|920,921,922,923,925,926,927,928,929,930,932,933,934,935,936,937,938,939,940,941,1024,1237,1239,1255,1302,1386,1442,1724,1842,9926,9929,9979,12818,12822,12864,12867,21301,21417,21418,21419,21420,21570,22046,22080,22081,22087,22167,1234'
>>> len(test_str)
255
>>> test1 = TestTable.objects.filter(test_column=test_str)
>>> test1.exists()
False
However, with a string of length 256 which I expect return True (matches my inserted column value), it instead raises an error (this test is exactly the same as the one above except test_str is one character longer):
Note: I've redacted my path in the traceback below.
>>> from core.models import TestTable
>>> test_str = '--publication_filter|920,921,922,923,925,926,927,928,929,930,932,933,934,935,936,937,938,939,940,941,1024,1237,1239,1255,1302,1386,1442,1724,1842,9926,9929,9979,12818,12822,12864,12867,21301,21417,21418,21419,21420,21570,22046,22080,22081,22087,22167,12345'
>>> len(test_str)
256
>>> test2 = TestTable.objects.filter(test_column=test_str)
>>> test2.exists()
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/[REDACTED]/.venv/local/lib/python2.7/site-packages/django/db/models/query.py", line 565, in exists
return self.query.has_results(using=self.db)
File "/[REDACTED]/.venv/local/lib/python2.7/site-packages/django/db/models/sql/query.py", line 441, in has_results
return bool(compiler.execute_sql(SINGLE))
File "/[REDACTED]/.venv/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 818, in execute_sql
cursor.execute(sql, params)
File "/[REDACTED]/.venv/local/lib/python2.7/site-packages/sql_server/pyodbc/base.py", line 325, in execute
return self.cursor.execute(sql, params)
ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]The data types nvarchar and text are incompatible in the equal to operator. (402) (SQLParamData)')
Looking at and running the raw query works fine, which has me very suspicious of the django-pyodbc package I'm on.
>>> str(TestTable.objects.filter(test_column=test_str).query)
'SELECT [custom].[test_table].[test_id], [custom].[test_table].[test_column] FROM [custom].[test_table] WHERE [custom].[test_table].[test_column] = --publication_filter|920,921,922,923,925,926,927,928,929,930,932,933,934,935,936,937,938,939,940,941,1024,1237,1239,1255,1302,1386,1442,1724,1842,9926,9929,9979,12818,12822,12864,12867,21301,21417,21418,21419,21420,21570,22046,22080,22081,22087,22167,12345 '
Edited query (quotes fixed), returns without issues manually querying my DB:
SELECT [custom].[test_table].[test_id], [custom].[test_table].[test_column]
FROM [custom].[test_table]
WHERE [custom].[test_table].[test_column] = '--publication_filter|920,921,922,923,925,926,927,928,929,930,932,933,934,935,936,937,938,939,940,941,1024,1237,1239,1255,1302,1386,1442,1724,1842,9926,9929,9979,12818,12822,12864,12867,21301,21417,21418,21419,21420,21570,22046,22080,22081,22087,22167,12345'
djanbo-pyodbc package: https://github.com/avidal/django-pyodbc/tree/django-1.4
CREATE TABLE [custom].[test_table] (
test_id INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
test_column NVARCHAR(4000),
);
INSERT INTO custom.test_table (test_column)
VALUES ('--publication_filter|920,921,922,923,925,926,927,928,929,930,932,933,934,935,936,937,938,939,940,941,1024,1237,1239,1255,1302,1386,1442,1724,1842,9926,9929,9979,12818,12822,12864,12867,21301,21417,21418,21419,21420,21570,22046,22080,22081,22087,22167,12345');
class TestTable(models.Model):
test_id = models.AutoField(primary_key=True)
test_column = models.TextField(null=True)
class Meta:
db_table = u'custom].[test_table'
There are several django-pyodbc flavors out there, but out of the packages I've tried, django-pyodbc-azure has performed best. I would recommend giving it a shot: