MDL-32365 mssql and sqlsrv: introspect metainfo to look for identities
authorEloy Lafuente (stronk7) <stronk7@moodle.org>
Tue, 10 Apr 2012 18:24:05 +0000 (20:24 +0200)
committerEloy Lafuente (stronk7) <stronk7@moodle.org>
Tue, 10 Apr 2012 18:24:05 +0000 (20:24 +0200)
lib/dml/mssql_native_moodle_database.php
lib/dml/sqlsrv_native_moodle_database.php

index 6e8b357..47c4177 100644 (file)
@@ -805,6 +805,7 @@ class mssql_native_moodle_database extends moodle_database {
         }
 
         $returning = "";
+        $isidentity = false;
 
         if ($customsequence) {
             if (!isset($params['id'])) {
@@ -812,13 +813,21 @@ class mssql_native_moodle_database extends moodle_database {
             }
             $returnid = false;
 
-            // Disable IDENTITY column before inserting record with id
-            $sql = 'SET IDENTITY_INSERT {' . $table . '} ON'; // Yes, it' ON!!
-            list($sql, $xparams, $xtype) = $this->fix_sql_params($sql, null);
-            $this->query_start($sql, null, SQL_QUERY_AUX);
-            $result = mssql_query($sql, $this->mssql);
-            $this->query_end($result);
-            $this->free_result($result);
+            $columns = $this->get_columns($table);
+            if (isset($columns['id']) and $columns['id']->auto_increment) {
+                $isidentity = true;
+            }
+
+            // Disable IDENTITY column before inserting record with id, only if the
+            // column is identity, from meta information.
+            if ($isidentity) {
+                $sql = 'SET IDENTITY_INSERT {' . $table . '} ON'; // Yes, it' ON!!
+                list($sql, $xparams, $xtype) = $this->fix_sql_params($sql, null);
+                $this->query_start($sql, null, SQL_QUERY_AUX);
+                $result = mssql_query($sql, $this->mssql);
+                $this->query_end($result);
+                $this->free_result($result);
+            }
 
         } else {
             unset($params['id']);
@@ -851,13 +860,16 @@ class mssql_native_moodle_database extends moodle_database {
         $this->free_result($result);
 
         if ($customsequence) {
-            // Enable IDENTITY column after inserting record with id
-            $sql = 'SET IDENTITY_INSERT {' . $table . '} OFF'; // Yes, it' OFF!!
-            list($sql, $xparams, $xtype) = $this->fix_sql_params($sql, null);
-            $this->query_start($sql, null, SQL_QUERY_AUX);
-            $result = mssql_query($sql, $this->mssql);
-            $this->query_end($result);
-            $this->free_result($result);
+            // Enable IDENTITY column after inserting record with id, only if the
+            // column is identity, from meta information.
+            if ($isidentity) {
+                $sql = 'SET IDENTITY_INSERT {' . $table . '} OFF'; // Yes, it' OFF!!
+                list($sql, $xparams, $xtype) = $this->fix_sql_params($sql, null);
+                $this->query_start($sql, null, SQL_QUERY_AUX);
+                $result = mssql_query($sql, $this->mssql);
+                $this->query_end($result);
+                $this->free_result($result);
+            }
         }
 
         if (!$returnid) {
index 5fe2ab2..01f9241 100644 (file)
@@ -871,14 +871,26 @@ class sqlsrv_native_moodle_database extends moodle_database {
         if (!is_array($params)) {
             $params = (array)$params;
         }
+
+        $isidentity = false;
+
         if ($customsequence) {
             if (!isset($params['id'])) {
                 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
             }
+
             $returnid = false;
-            // Disable IDENTITY column before inserting record with id
-            $sql = 'SET IDENTITY_INSERT {'.$table.'} ON'; // Yes, it' ON!!
-            $this->do_query($sql, null, SQL_QUERY_AUX);
+            $columns = $this->get_columns($table);
+            if (isset($columns['id']) and $columns['id']->auto_increment) {
+                $isidentity = true;
+            }
+
+            // Disable IDENTITY column before inserting record with id, only if the
+            // column is identity, from meta information.
+            if ($isidentity) {
+                $sql = 'SET IDENTITY_INSERT {'.$table.'} ON'; // Yes, it' ON!!
+                $this->do_query($sql, null, SQL_QUERY_AUX);
+            }
 
         } else {
             unset($params['id']);
@@ -894,9 +906,12 @@ class sqlsrv_native_moodle_database extends moodle_database {
         $query_id = $this->do_query($sql, $params, SQL_QUERY_INSERT);
 
         if ($customsequence) {
-            // Enable IDENTITY column after inserting record with id
-            $sql = 'SET IDENTITY_INSERT {'.$table.'} OFF'; // Yes, it' OFF!!
-            $this->do_query($sql, null, SQL_QUERY_AUX);
+            // Enable IDENTITY column after inserting record with id, only if the
+            // column is identity, from meta information.
+            if ($isidentity) {
+                $sql = 'SET IDENTITY_INSERT {'.$table.'} OFF'; // Yes, it' OFF!!
+                $this->do_query($sql, null, SQL_QUERY_AUX);
+            }
         }
 
         if ($returnid) {