Schema name needs to be checked and included in SQL statements

Issue #50 wontfix
Anonymous created an issue

If there are tables for which the schema is not the connecting db user's default schema, then inspectdb will fail.

I haven't tried manually creating django models yet to move forward with using django-mssql but I have a feeling this is may be a problem inherent in the core so it's not just inspectdb that will fail... probably regular selects will fail too.

>manage.py inspectdb --traceback
# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#     * Rearrange models' order
#     * Make sure each model has one field with primary_key=True
# Feel free to rename the models, but don't rename db_table values or field names.
#
# Also note: You'll have to insert the output of 'django-admin.py sqlcustom [appname]'
# into your database.
from __future__ import unicode_literals

from django.db import models

class Mytablename(models.Model):
Traceback (most recent call last):
  File "C:\Python27\lib\site-packages\django\core\management\base.py", line 222, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "C:\Python27\lib\site-packages\django\core\management\base.py", line 255, in execute
    output = self.handle(*args, **options)
  File "C:\Python27\lib\site-packages\django\core\management\base.py", line 385, in handle
    return self.handle_noargs(**options)
  File "C:\Python27\lib\site-packages\django\core\management\commands\inspectdb.py", line 26, in handle_noargs
    for line in self.handle_inspection(options):
  File "C:\Python27\lib\site-packages\django\core\management\commands\inspectdb.py", line 60, in handle_inspection
    relations = connection.introspection.get_relations(cursor, table_name)
  File "C:\Python27\lib\site-packages\django_mssql-1.4-py2.7.egg\sqlserver_ado\introspection.py", line 109, in get_relations
    source_field_dict = self._name_to_index(cursor, table_name)
  File "C:\Python27\lib\site-packages\django_mssql-1.4-py2.7.egg\sqlserver_ado\introspection.py", line 106, in _name_to_index
    return dict([(d[0], i) for i, d in enumerate(self.get_table_description(cursor, table_name, False))])
  File "C:\Python27\lib\site-packages\django_mssql-1.4-py2.7.egg\sqlserver_ado\introspection.py", line 80, in get_table_description
    cursor.execute("SELECT * FROM [%s] where 1=0" % (table_name))
  File "C:\Python27\lib\site-packages\django\db\backends\util.py", line 41, in execute
    return self.cursor.execute(sql, params)
  File "C:\Python27\lib\site-packages\django_mssql-1.4-py2.7.egg\sqlserver_ado\dbapi.py", line 532, in execute
    self._execute_command()
  File "C:\Python27\lib\site-packages\django_mssql-1.4-py2.7.egg\sqlserver_ado\dbapi.py", line 446, in _execute_command
    self._raiseCursorError(klass, _message)
  File "C:\Python27\lib\site-packages\django_mssql-1.4-py2.7.egg\sqlserver_ado\dbapi.py", line 384, in _raiseCursorError
    eh(self.connection, self, errorclass, errorvalue)
  File "C:\Python27\lib\site-packages\django_mssql-1.4-py2.7.egg\sqlserver_ado\dbapi.py", line 99, in standardErrorHandler
    raise errorclass(errorvalue)
DatabaseError: (-2147352567, 'Exception occurred.', (0, u'Microsoft SQL Server Native Client 10.0', u"Invalid object name 'MYTABLENAME'.", None, 0, -2147217865), None)
Command:
SELECT * FROM [MYTABLENAME] where 1=0
Parameters:
[]

Using django-mssql 1.4 (f8fb3d4577e6fb3581e687ddd1c90dd90941ff11)

Other packages:

>pip list
amqp (1.0.11)
anyjson (0.3.3)
billiard (2.7.3.28)
celery (3.0.19)
chardet (2.1.1)
Django (1.5)
django-csvimport (0.9)
django-debug-toolbar (0.9.4)
django-localflavor-us (1.1)
django-mail-queue (1.6.3)
django-mailer (0.2a1)
django-mssql (1.4)
django-session-security (2.0.3)
django-tastypie (0.11.0)
Genshi (0.7)
isodate (0.4.9)
kombu (2.5.10)
MySQL-python (1.2.4b4)
pep8 (1.4.5)
python-dateutil (2.1)
python-mimeparse (0.1.4)
pytz (2013b)
pywin32 (218)
selenium (2.31.0)
six (1.3.0)
South (0.8.2)
Trac (1.0.1)
tracaccountmanager (0.4.3)
unittest-data-provider (1.0.0)

Comments (1)

  1. Michael Manfre repo owner

    Change the connecting user's default schema or manually create the models and make sure you add db_tablespace = 'tbl_tbsp' to the model's definition.

    The inspectdb management command provides a nice starting point for generating models, but it's output is rarely 100% under ideal circumstances and you're usually better off writing all of the model definitions yourself.

  2. Log in to comment