_get_sql_server_ver in operations.py (line 27) not working in multi-db-engine environment

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Use a PostgreSQL db as default Django (administrative) database 
2. Use SQL server (version 2005) as a secondary (production) db with Django
multi-db functionality
3. Try to update the SQL server database via a form view

What is the expected output? 
Return to the submitted updating form via the view.

What do you see instead?
An MS SQL query is sent to the default PostgreSQL backend:
------------------------------------------------------
DatabaseError at /vrcitering/rules/type/

function serverproperty(unknown) does not exist
LINE 1: SELECT CAST(SERVERPROPERTY('ProductVersion') as varchar)
                    ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.

Request Method:     POST
Request URL:    http://localhost/vrcitering/rules/type/
Django Version:     1.2 rc 1 SVN-13269
Exception Type:     DatabaseError
Exception Value:    

function serverproperty(unknown) does not exist
LINE 1: SELECT CAST(SERVERPROPERTY('ProductVersion') as varchar)
                    ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.

Exception Location: 
C:\Program\Python26\lib\site-packages\django\db\backends\postgresql_psycopg2\bas
e.py
in execute, line 44
Python Executable:  C:\Program\Apache Software
Foundation\Apache2.2\bin\httpd.exe
Python Version:     2.6.2
Python Path:    [removed to reduce clutter]
-----------------

What version of the product are you using? On what operating system?
Django-pyodbc rev 181
Django rev 13269
Windows XP, sp2
FreeTDS

Please provide any additional information below.
Traceback:
File "C:\Program\Python26\lib\site-packages\django\core\handlers\base.py"
in get_response
  100.                     response = callback(request, *callback_args,
**callback_kwargs)
File "C:\web\analyse\vrcitering\views.py" in rules_type
  733.                 obj_regeltyp =
OrganisationsRegelTyp.objects.get(regeltyp = 'org_stad')
File "C:\Program\Python26\lib\site-packages\django\db\models\manager.py" in get
  132.         return self.get_query_set().get(*args, **kwargs)
File "C:\Program\Python26\lib\site-packages\django\db\models\query.py" in get
  336.         num = len(clone)
File "C:\Program\Python26\lib\site-packages\django\db\models\query.py" in
__len__
  81.                 self._result_cache = list(self.iterator())
File "C:\Program\Python26\lib\site-packages\django\db\models\query.py" in
iterator
  269.         for row in compiler.results_iter():
File
"C:\Program\Python26\lib\site-packages\django\db\models\sql\compiler.py" in
results_iter
  672.         for rows in self.execute_sql(MULTI):
File
"C:\Program\Python26\lib\site-packages\django\db\models\sql\compiler.py" in
execute_sql
  717.             sql, params = self.as_sql()
File "C:\Program\Python26\lib\site-packages\sql_server\pyodbc\compiler.py"
in as_sql
  238.                                                      
with_col_aliases=with_col_aliases)
File
"C:\Program\Python26\lib\site-packages\django\db\models\sql\compiler.py" in
as_sql
  65.         where, w_params = self.query.where.as_sql(qn=qn,
connection=self.connection)
File "C:\Program\Python26\lib\site-packages\django\db\models\sql\where.py"
in as_sql
  91.                     sql, params = child.as_sql(qn=qn,
connection=connection)
File "C:\Program\Python26\lib\site-packages\django\db\models\sql\where.py"
in as_sql
  94.                     sql, params = self.make_atom(child, qn, connection)
File "C:\Program\Python26\lib\site-packages\django\db\models\sql\where.py"
in make_atom
  149.             field_sql = self.sql_for_columns(lvalue, qn, connection)
File "C:\Program\Python26\lib\site-packages\django\db\models\sql\where.py"
in sql_for_columns
  209.         return connection.ops.field_cast_sql(db_type) % lhs
File
"C:\Program\Python26\lib\site-packages\sql_server\pyodbc\operations.py" in
field_cast_sql
  68.         if self.sql_server_ver < 2005 and db_type and db_type.lower()
== 'ntext':
File
"C:\Program\Python26\lib\site-packages\sql_server\pyodbc\operations.py" in
_get_sql_server_ver
  27.             cur.execute("SELECT CAST(SERVERPROPERTY('ProductVersion')
as varchar)")
File "C:\Program\Python26\lib\site-packages\django\db\backends\util.py" in
execute
  15.             return self.cursor.execute(sql, params)
File
"C:\Program\Python26\lib\site-packages\django\db\backends\postgresql_psycopg2\ba
se.py"
in execute
  44.             return self.cursor.execute(query, args)

Exception Type: DatabaseError at /vrcitering/rules/type/
Exception Value: function serverproperty(unknown) does not exist
LINE 1: SELECT CAST(SERVERPROPERTY('ProductVersion') as varchar)
                    ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.

------
Sorry for my bad knowledge here, but I think the raw SQL query should be
sent to the database using another method, supporting mutiple databases and
database engines. Check documentation here:
http://docs.djangoproject.com/en/dev/topics/db/multi-db/ where it says (at
the bottom of the page):

Using raw cursors with multiple databases
-----------------------------------------
If you are using more than one database you can use django.db.connections
to obtain the connection (and cursor) for a specific database.
django.db.connections is a dictionary-like object that allows you to
retrieve a specific connection using it's alias:

from django.db import connections
cursor = connections['my_db_alias'].cursor()

Hope you can interpret this better than me.

Presently, I've hacked around the problem by hard-coding my SQL server
version in operations.py, line 12:

        super(DatabaseOperations, self).__init__()
        # self._ss_ver = None
        self._ss_ver = 2005

Thanks for keeping this Django backend up to date and regards,
Ulf

Original issue reported on code.google.com by ulf.kron...@gmail.com on 17 May 2010 at 1:45

[GoogleCodeExporter]

patch that passes the connection instance to the Operations class.
replace all calls to _get_sql_server_ver(con) with ops.sql_server_ver.
this is the same way django's default backends handle this.

Original comment by hgee...@gmail.com on 1 Nov 2010 at 12:45

Attachments:

• django-pyodbc_sql_version.patch

[GoogleCodeExporter]

Excellent solution to the issue.  Thanks.

Original comment by jordanre...@gmail.com on 1 Nov 2010 at 4:41

[GoogleCodeExporter]

Thanks for the patch.

// Ulf

Original comment by ulf.kron...@gmail.com on 2 Nov 2010 at 5:56

[GoogleCodeExporter]

The patch resolves this issue for me. I was running into the same problem.

Original comment by djfis...@gmail.com on 11 Jan 2011 at 6:46

[GoogleCodeExporter]

Original comment by vcc.ch...@gmail.com on 24 Mar 2011 at 4:51

• Changed state: Accepted

[GoogleCodeExporter]

Fixed in r186, thanks.

Original comment by vcc.ch...@gmail.com on 17 Apr 2011 at 4:00

• Changed state: Fixed