WilliamRen / django-pyodbc

Automatically exported from code.google.com/p/django-pyodbc
BSD 3-Clause "New" or "Revised" License
0 stars 0 forks source link

statement uses OUTPUT without INTO which fails with triggers #105

Open GoogleCodeExporter opened 8 years ago

GoogleCodeExporter commented 8 years ago
I have a table with a trigger on it, representing one of my Django objects.  
Trying to call the object's 'save()' function results in:

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL ServerDriver][SQL 
Server]The target table 'qa_baselineVersionCompat' of the DML statement cannot 
have any enabled triggers if the statement contains an OUTPUT clause without 
INTO clause. (334) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server 
Driver][SQL Server]Statement(s) could not be prepared. (8180)")

The latest comment in 
http://code.google.com/p/django-pyodbc/issues/detail?id=101 looks like a 
promising start to a bug fix for this.

Original issue reported on code.google.com by afranz...@gmail.com on 11 May 2011 at 9:18

GoogleCodeExporter commented 8 years ago

Original comment by vcc.ch...@gmail.com on 13 May 2011 at 4:56

GoogleCodeExporter commented 8 years ago
I fix the last patch from here: 
http://code.google.com/p/django-pyodbc/issues/detail?id=101
to apply for r190.
It's now works for me.

--- a/sql_server/pyodbc/compiler.py
+++ b/sql_server/pyodbc/compiler.py
@@ -279,29 +279,35 @@ class SQLInsertCompiler(compiler.SQLInsertCompiler, 
SQLCompiler):
         # going to be column names (so we can avoid the extra overhead).
         qn = self.connection.ops.quote_name
         opts = self.query.model._meta
-        result = ['INSERT INTO %s' % qn(opts.db_table)]
+        returns_id = bool(self.return_id and
+                self.connection.features.can_return_id_from_insert)
+        if returns_id:
+            result = ['DECLARE @result table (id int);\n']
+        else:
+            result = []
+        result.append('INSERT INTO %s' % qn(opts.db_table))
         result.append('(%s)' % ', '.join([qn(c) for c in self.query.columns]))
-        if self.return_id and 
self.connection.features.can_return_id_from_insert:
-            output = 'OUTPUT inserted.%s' % qn(opts.pk.column)
+        if returns_id:
+            output = 'OUTPUT inserted.%s INTO @result' % qn(opts.pk.column)
             result.append(output)
         values = [self.placeholder(*v) for v in self.query.values]
         result.append('VALUES (%s)' % ', '.join(values))
-        params = self.query.params
+        if returns_id:
+            result.append(';\nSELECT id FROM @result')
         sql = ' '.join(result)

-        meta = self.query.get_meta()
-        if meta.has_auto_field:
+        if opts.has_auto_field:
             # db_column is None if not explicitly specified by model field
-            auto_field_column = meta.auto_field.db_column or 
meta.auto_field.column
-
+            auto_field_column = opts.auto_field.db_column or 
opts.auto_field.column
+            
             if auto_field_column in self.query.columns:
-                quoted_table = self.connection.ops.quote_name(meta.db_table)
-                if len(self.query.columns) == 1 and not params:
+                quoted_table = self.connection.ops.quote_name(opts.db_table)
+                if len(self.query.columns) == 1 and not self.query.params:
                     sql = "INSERT INTO %s DEFAULT VALUES" % quoted_table
                 else:
                     sql = "SET IDENTITY_INSERT %s ON;\n%s;\nSET IDENTITY_INSERT %s OFF" % \
                         (quoted_table, sql, quoted_table)
-        return sql, params
+        return sql, self.query.params

Original comment by Iv.Kravc...@gmail.com on 16 Aug 2011 at 11:52