MS SQL Server backend using pyodbc.

MS SQL Server backend patch

Exclude regular expressions tests

Please note that you may also need to apply the #5242 patch for syncdb command.

this is pretty cool! i dropped my pymssql version for this. I would say let's join our forces to make this the official django mssql backend.

so filip, let me know if you need any help to move things forward.

MS SQL Server backend patch, version 2.

Exclude regular expressions and empty model tests

Changes in the second patch version

• Added introspection. Note that introspection will resolve all datetime columns to DateTimeField, even though some of them might be used to store TimeField and DateField (there are no separate data types for time and date in MSSQL).

• Allow for MSSQL wildcards (%_[]) in __regex and __iregex.

• Added quoting of identifiers and parameters in several places.

• Client module for manage.py dbshell.

• Some general cleanup & comments.

There are still some TODOs spread across the source code files, so your help and comments with these would be very appreciated (pk11?:-)).

Thanks for the great work. I just tested both patch1 and patch2. It works fine for doing the tutorial, except when I hit the History button in admin, it will give me the following error:

('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. (306)')

Do you have any idea?

Thanks for pointing this out. This error is a known issue when working with MSSQL 2000, although I didn't spot that it occurs in the admin interface.

The data type used to store TextFields in SQL Server 2000 is ntext. On the other hand Python Unicode strings are passed to SQL Server via pyodbc as nvarchars. The problem is when someone tries to compare this two different data types for equality with the "=" operator. The following will raise an error in MSSQL 2000 and 2005:

   SELECT 1 WHERE cast('a' as ntext) = N'a'

In SQL Server 2005 there is a new [n]varchar(max) data type which does not have such limitations:

   SELECT 1 WHERE cast('a' as nvarchar(max)) = N'a'

and we can use that for TextFields. Also, according to the MSDN, the [n]text type is going to be removed in future versions of MSSQL.

There are several possible ways to solve this for MSSQL 2000:

1. Use a specific __exact operator for TextField.
   The exact matching is translated into "= %s", where "%s" is parameter placeholder (see operators in DatabaseWrapper). For the TextField it would need to be translated into "LIKE %s" statement or use explicit casting of parameter to ntext type - "= cast(%s as ntext)". This approach would require patching the get_where_clause function in django\db\models\query.py, which I would prefer to avoid.
   
   
2. Change the django admin model for LogEntry.
   I'm not sure about that, but maybe we don't need object_ids longer than 255 characters and could change the model a bit from:

         class LogEntry(models.Model):
             ...
             object_id = models.TextField(_('object id'), blank=True, null=True)
             ...
   

   to something like:

         class LogEntry(models.Model):
             ...
             object_id = models.CharField(_('object id'), blank=True, null=True, max_length=255)
             ...
   

Personally, I would incline to the second option as there is already quite a lot of backend-specific conditional statements in the django.db, but would like to hear how this may influence the admin interface functionality and how would work with other backends first.

Note: Regarding to the versions of the patch listed on this site, the highest number is the most recent patch version (currently mssql_pyodbc.2.patch) and previous versions should not be used. I don't have rights to replace or remove the older attachments and they are simply listed here as a ticket history.

MSSQL 2000: Fix for "History button" in the Admin interface -- second option.

Thanks again. I switched to SQL Server 2005, which solved the problem. But then I just noticed that 'python manage.py syncdb' worked only on the first time. As I called it again, it tried to recreate the table 'auth_message' and errored, as the following:

C:\Programming\Python\django\mysite>python manage.py syncdb Creating table auth_message Traceback (most recent call last):

File "manage.py", line 11, in <module>

execute_manager(settings)

File "C:\Python25\lib\site-packages\django\core\management\init.py", line 180, in execute_manager

utility.execute(argv)

File "C:\Python25\lib\site-packages\django\core\management\init.py", line 124, in execute

command.execute(*args[1:], **options.dict)

File "C:\Python25\lib\site-packages\django\core\management\base.py", line 33, in execute

output = self.handle(*args, **options)

File "C:\Python25\lib\site-packages\django\core\management\base.py", line 117, in handle

return self.handle_noargs(**options)

File "C:\Python25\lib\site-packages\django\core\management\commands\syncdb.py", line 66, in handle_noargs

cursor.execute(statement)

File "C:\Python25\lib\site-packages\django\db\backends\util.py", line 19, in execute

return self.cursor.execute(sql, params)

File "C:\Python25\lib\site-packages\django\db\backends\mssql\base.py", line 170, in execute

return self.cursor.execute(sql, params)

pyodbc.ProgrammingError?: ('42S01', "[42S01] [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'auth_message' in the database. (2714)")

I saw also the same behaviour with SQL Server 2000. Looks like it doesn't recognized that the tables already exists. I tried to debug, but haven't got any further. Any hints? Thanks.

Yes, I have submitted a patch #5242 (see the third comment from the top) some time ago that now awaits to be checked in. Applying this should fix the syncdb.

Does it make any sense to split this into abstract ODBC classes and then a set of subclasses for MsSQL?

I am considering making 2 or 3 other odbc modules (c-tree, VFP and Access). I am guessing this mssql_pyodbc odbc module is a good place to start, just wondering if there will be any code that should stay synchronized.

Also, it would be nice if there was a 'generic' odbc module that would give some functionality. For instance, I am not planning on implementing any write access, which I am sure will make it easier.

I'm new to django and SVN and am interested in trying out mssql_pyodbc, as I would need to use django with a SQL server backend. When I tried to apply these patches to my working copy using patch.exe from GnuWin?32, I got the error, "patch: **** Only garbage was found in the patch input." I suspect this is because my version is out of sync as that in the patch. I checked out the trunk at revision 6474. Is there a branch I should checkout instead, or do I just need a specific version of the trunk?

Any help here would be appreciated.

Thanks, David Holt

Quoting what I said about #1261 (since this is a similar situation):

This is far too big a patch/change for our existing infrastructure to handle. Luckily, there's a better way: turn this backend into an external project (hosted on Google Code, perhaps). Once it's bulletproof, clearly stable, and has active maintainers you can propose addition into Django proper (do so on django-dev). See http://www.pointy-stick.com/blog/2007/11/11/django-tip-external-database-backends/ and http://www.djangoproject.com/documentation/settings/#database-engine for information about how to turn this backend into an external project.

http://code.google.com/p/django-pyodbc/

let me know if you want commit access.

I'd really like to get this working (I'm prepared to help with development). It seems there are some files missing from the subversion repository so I can't actually run it. I'm writing here as I can't seem to find any way of contacting the developers from the google code page (or anywhere else).

Oh.. when I said 'subversion repository' I mean the one at http://django-pyodbc.googlecode.com/svn/trunk/.