PHP查询中的DBCC CHECKIDENT错误

Question

This is the first time I've ever asked a question on here, but I've used stackoverflow many times in the past to find solutions for problems I'm having in my code.

I'm working on a database transfer page on a php site that uploads csv files and updates the database, and depending on the type of update the user selects this data can be updated/inserted by key. Because of this I want to run DBCC CHECKIDENT after the updates have been made to ensure that new entries will be incremented correctly after the largest key in the table.

This is the php code I'm running:

$getMaxID = new Query("SELECT MAX($tableKeys[$t]) as max from $t", __LINE__, __FILE__);
$maxID = $getMaxID->result(0,'max');
$result = new Query("DBCC CHECKIDENT ('$t', RESEED, $maxID)", __LINE__, __FILE__);

$t is the table name stored in an array of table names.

I get the following error from this code:

There has been a system error. We apologize for the inconvienience.
Error Details: [Microsoft][SQL Server Native Client 11.0][SQL Server]Checking identity information: current identity value '16', current column value '16'.
Line #: 615
File: C:\OCPOS\htdocs\OCPOS\menuTransfer\importMenu.php
Query: DBCC CHECKIDENT ('table', RESEED, 16)

What's confusing me is when I cut an paste DBCC CHECKIDENT ('table', RESEED, 16) into server management studio it works and i get:

Checking identity information: current identity value '16', current column value '16'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If anyone has any ideas what is causing this or if there's a post I missed that addresses this issue any help would be much appreciated.

Below is the query class. I didn't make it:

class Query{
    var $stmt; //hold link to result
    var $queryStr; //hold string
    var $queryLine;
    var $queryFile;

    function Query($str, $line, $file)
    {
      global $conn;
      $this->queryStr = $str;
      $this->queryLine = $line;
      $this->queryFile = $file;   
      $this->stmt = @sqlsrv_query($conn, $this->queryStr, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET)) or $this->error(sqlsrv_errors());
    }

    function error($var)
    {
        echo "
        <p style='border: 1px solid #c00; margin: 5px; padding: 5px;'>
        There has been a system error. We apologize for the inconvienience.<br/>
        Error Details: ". $var[0]['message'] ."<br/>
        Line #: $this->queryLine<br/>
        File: $this->queryFile<br/>
        Query: $this->queryStr<br/>
        </p>
        ";
    }

    function fetch_array()
    {
      $array = sqlsrv_fetch_array($this->stmt);
      if(is_array($array))
        return $array;
      else
      {
        return false;
      }
    }

    function fetch_assoc_array()
    {
      $array = sqlsrv_fetch_array($this->stmt, SQLSRV_FETCH_ASSOC);
      if(is_array($array))
        return $array;
      else
      {
        return false;
      }
    }


    function num_rows()
    {
      return sqlsrv_num_rows($this->stmt);
    }

    function numrows()
    {
      return $this->num_rows();
    }

    function result($row, $var)
    {
      $array = sqlsrv_fetch_array($this->stmt, SQLSRV_FETCH_ASSOC, SQLSRV_SCROLL_FIRST);
      return $array[$var];              
    }

      function all() {
          $return = array();
          while ($tmp = $this->fetch_array()) {
              $return[] = $tmp;
          }
          return $return;
      }


      function arr() {
          return $this->fetch_array();
      }

      function getAll() {
          $return = array();
          while ($tmp = $this->fetch_array()) {
              $return = array_merge($return, $tmp);
          }
          return $return;
      }

      function extract($var) {
          $rv = array();
          while ($tmp = $this->fetch_array()) {
              $rv[] = $tmp[$var];
          }
          return $rv;
      }

  }

Answer 1

I believe what is happening is that the database driver in PHP is assuming that any output from the database connection (as opposed to Result Sets, which are returned separately) is some form of error. Note that the output you get from SQL Server is actually identical in the two cases, except for some extra context information. I have seen similar problems when the ROW_COUNT setting is left on, with the textual "rows affected/returned" message interpreted as an error.

Since DBCC commands are, to put it nicely, not very standardised, I doubt there is any way to suppress this message at the SQL Server end. However, since you know that it is not really an error, you should be able to simply ignore it on the PHP side. You are already suppressing PHP's own error mechanism with the @ operator, so just need a version of your Query method without the or $this->error(sqlsrv_errors());.

Incidentally, this is a good example of the value of separating responsibilities carefully: since the database abstraction class has taken responsibility for formatting the error into HTML and displaying it to the user, you are more-or-less forced to alter or replace that logic. If, instead, it threw an Exception, your calling code could catch this particular case while leaving others to pass through to a global error-handler which formatted the message appropriately.