Working external Django backend for MS SQL Server

[Ulf Kronman]

Hi all,
I'm back again with some good news - and some new problems and
questions.

The good news is that some clever guys have cleaned up and updated the
code of the external project django-pyodbc: http://code.google.com/p/django-pyodbc/
so now it is actually working code!

I have got it working in my Mac OS X environment by downloading code
from their SVN repository at
http://django-pyodbc.googlecode.com/svn/trunk/ and placing the
supplied mssql directory in django.db.backends
(I didn't get it working externally as stated in the documentation).

The not-so-good news is that django-pyodbc is dependent on pyodbc,
which in turn is dependent on an ODBC device manager (unixODBC or
iODBC), which in turn needs FreeTDS as a device to access the MS SQL
database. That's three pieces of software to download, install and
configure for working together. It takes some fiddling to get it done,
but I managed to get it working after a while.

To make things more complicated for me, I work in Mac OS X, and most
instructions for the combination of unixODBC and FreeTDS are written
for a UNIX/Linux environment. I downloaded the software via MacPorts,
and after a while I realized that the FreeTDS that came from MacPorts
was pre-configured to work with the Mac OS X pre-installed iODBC,
which have its configuration files in /Library/ODBC (I had good help
from these instructions: http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServerFromRailsOnOSX
)

The last remaining issue for me with django-pyodbc is regarding
something called "connection pooling". I couldn't get that to work and
found out that you can turn it off in pyodbc by setting pyodbc.pooling
= False (or in Django; Database.pooling = False).

After a while, I realized that it seems as if Django is re-using
cursor connections in some cases (I'm working with Django CVS revision
8204), where my installation thus will fail with a [FreeTDS][SQL
Server]Invalid cursor state (0) as I have reported in
http://code.google.com/p/django-pyodbc/issues/detail?id=6

The solution to my problem would presumably be to try to get
connection pooling working in my ODBC driver. iODBC is said to support
connection pooling, but I can't find any documentation on how to get
it working (especially on Mac OS X).

unixODBC has a better documentation on connection pooling (http://
www.unixodbc.org/doc/conn_pool.html), but then I have to recompile my
FreeTDS to work with unixODBC.

So my question is: Is someone here using ODBC in Mac OS X and did you
have any success with connection pooling in either iODBC or unixODBC?

A second question is: Am I right in my presumption that Django CVS
version is using connection pooling, and is there by chance any
setting to turn it of, if so?

Regards,
Ulf

[Ulf Kronman]

Hi again,

> After a while, I realized that it seems as if Django is re-using
> cursor connections in some cases (I'm working with Django CVS revision
> 8204),  where my installation thus will fail with a [FreeTDS][SQL

> Server]Invalid cursor state (0) as I have reported inhttp://code.google.com/p/django-pyodbc/issues/detail?id=6

>
> The solution to my problem would presumably be to try to get
> connection pooling working in my ODBC driver. iODBC is said to support
> connection pooling, but I can't find any documentation on how to get
> it working (especially on Mac OS X).

OK, so now I have installed unixODBC, so that my pyodbc is running the
unixODBC-FreeTDS path to access the MS SQL Server, and I have got rid
of the connection pooling problem, but I still find the same problem
as above when I set Django to retrieve related objects while
processing a list of objects (as for instance listing a related object
in Django admin listing).

Some research on the net gave me the following information:

This page: http://bugs.digium.com/view.php?id=5756 states the
following:

<cite>

I posted this problem to the FreeTDS mailing list and got this answer
regarding the
"invalid cursor state" error message:
"This can be caused by application trying do use 2 active statement on
same
connection (like a select and an update together) or if application do
not close
correctly statement (not getting all data)."

If I look in the log it looks like this happens. There are 2 equal sql
statements
prepared/executed and then it fails. Looking at other operations
including sql
statements it is never 2 equal statments following each other.

Also the FreeTDS documentations states:
"Is FreeTDS thread safe?
Different threads may all use separate connections without interfering
with each
other. Threads may not share a DBPROCESS or CS_CONNECTION without
controlling access
via a mutex."

</cite>

Question: Is there a way to force Django to close the connection and
not try to re-use the cursor/connection while processing lists?

Regards,
Ulf

[Malcolm Tredinnick]

On Sat, 2008-08-09 at 06:14 -0700, Ulf Kronman wrote:
[...]

> Question: Is there a way to force Django to close the connection and
> not try to re-use the cursor/connection while processing lists?

I presume you are not meaning that a single connection cannot be used
for more than one interaction when they are sequential, right? That
would be disastrous (read "the database wrapper is simply broken"). You
could probably just assign None to the connection attribute in the
django.db.backend class, since creating a cursor will create a new
connection if necessary, but that's not going to be something we ever
exposed.

However, SQLite also has the issue that you cannot have partially read
data from a select statement in a cursor and then call an update on the
same connection. Ticket #7411 was one way this appeared and it's not
been fixed.

The way to avoid the problem is to use the can_use_chunked_reads
attribute in the DatabaseFeatures class. Have a look at the use of this
in django/db/backends/sqlite/base.py. Most databases can happily
multiplex cursor interactions (often because they are already pulling
all the data over to the client side on their own), so we read data from
the cursor in chunks, but for SQLite we ensure all the data is read at
once.

Regards,
Malcolm

[Karen Tracey]

On Sat, Aug 9, 2008 at 7:39 PM, Malcolm Tredinnick <mal...@pointy-stick.com> wrote:

Ticket #7411 was one way this appeared and it's not
been fixed.

I think that was supposed to be "now been fixed" since in fact #7411 is closed as fixed.

Karen

[Malcolm Tredinnick]

Yes, indeed. That's the second time I've made that typo this
afternoon. :-(

#7411 has indeed been fixed. I fixed it.

Malcolm

>

[Ramiro Morales]

On Sat, Aug 9, 2008 at 8:47 PM, Malcolm Tredinnick
<mal...@pointy-stick.com> wrote:

>
> Yes, indeed. That's the second time I've made that typo this
> afternoon. :-(
>
> #7411 has indeed been fixed. I fixed it.
>

I posted a comment on issue #7 of the django-pyodbc project
pointing in the same direction:

http://code.google.com/p/django-pyodbc/issues/detail?id=7#c2

Because I suspect both issue #6 Ulf opened and issue #7 are
caused by the same limitation of the MS ODBC driver for SQL Server.

Regards,

--
Ramiro Morales

[Ulf Kronman]

Hi all,

> I posted a comment on issue #7 of the django-pyodbc project
> pointing in the same direction:
>
> http://code.google.com/p/django-pyodbc/issues/detail?id=7#c2
>
> Because I suspect both issue #6 Ulf opened and issue #7 are
> caused by the same limitation of the MS ODBC driver for SQL Server.

Yes, that limitation of the MS SQL Server seems to be the cause of my
problem.

I inserted
can_use_chunked_reads = False
on line 66 in mssql/base.py
and it started to work right away.

I'll report that back to the django-pyobc folks.

Thanks for your help,
Ulf