2

I have to read an existing SQL-SERVER database with Django FWK and after connecting succesfully to the DB, when I refer to MyModel.objects.all() Django launch a query with the table name quoted, but it fails because SQL-Server needs the prefix schema, getting the error:

('42S02', "[42S02] [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'table_name'. (208) (SQLExecDirectW)")

EDIT: This only happens with a non 'default' django database settings. Misteriously, my default database doesn't need any special treatment, and is a sql-server database with a db user without default schema.

How can I configure the model or the settings.DATABASE or the dbrouter to make django connect to the specific schema and don't need to use the schema_name?

Option 1: model Meta class property: db_table = 'schema_name.table_name' doesn't work, because the query that builds has the FROM clause as [schema_name.table_name] and what sql needs is [schema_name].[table_name]

Option 2: settings.DATABASE 'NAME' already refers to the schema_name, but it's not enough

Option 3: no idea how to tell django to connect to the specific schema or to add the prefix schema name to all the queries it builds.

Option 4: make django do not surround db_table with brackets. When I define db_table as 'schema.table' the resulting query surround this with [schema.table]. Without "[]" brackets query would work.

toscanelli
  • 1,202
  • 2
  • 17
  • 40
  • If you need only one schema to prefix your objects, you can set is as the default schema of your user. I mean, if you refer the thable as MyTable without any schema, it does not cause error, SQL Server first looks for it in your user default schema, then it tries dbo, and only than fails if the object was not found either in default schema or in dbo – sepupic Aug 10 '17 at 11:10
  • Related question: [set default schema for a sql query](https://stackoverflow.com/q/4942023/2144390) – Gord Thompson Aug 10 '17 at 11:28
  • I have no privilegies to alter the users default schema, but if you think is the only way, maybe I could apply for it to the upper echelons... – toscanelli Aug 10 '17 at 12:08
  • I have tried your suggestion in a local db, and I get the same error. Django can't connect with a specific schema even if the db user has a default one. I updated my question with a 4th option, cause I see that the eternal problem are the brackets... – toscanelli Aug 10 '17 at 12:40
  • Option 2 should be enough, if db router is properly configured... – toscanelli Aug 14 '17 at 08:57

2 Answers2

1

Django doesn't have support for multiple schemata; it uses the connect user's default schema.

However, this has worked for me in a pinch with both PostgreSQL and SQL Server:

db_table = 'your_schema\".\"your_table'

If or when Django offers multiple schemata support (many years of discussion here: https://code.djangoproject.com/ticket/6148), this will likely break. In my project, I'm okay with that, but it is something that should be assessed on a project-by-project basis as it isn't officially supported.

There was a PR from last year that started work on this as well: https://github.com/django/django/pull/6162

Good luck!

FlipperPA
  • 13,607
  • 4
  • 39
  • 71
0

This is an absolute hack, but it works. In your model file, you can include the following (you'll want to include the DB_SCHEMA in your Django settings file):

from django.conf import settings

DB_SCHEMA_PREFIX = ''
if settings.DB_SCHEMA and len(settings.DB_SCHEMA) > 0:
    DB_SCHEMA_PREFIX = settings.DB_SCHEMA + '].['enter code here
...
db_table = DB_SCHEMA_PREFIX + 'MyTableName'

To be clear, this is not a great solution - proper schema support in the framework would be vastly preferred. It's the thing I could come up with to get schemas to function properly in Django.

T3am5hark
  • 856
  • 6
  • 9