Doctrine 1
  1. Doctrine 1
  2. DC-1007

Cannot update a field to NULL with MSSQL connection

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Critical Critical
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Connection
    • Labels:
      None
    • Environment:
      Windows 7 32 bits with Apache 2.2.x, PHP 5.2.17, Sql Server 2008, Symfony 1.4.11 and Doctrine 1.2.4

      Description

      When trying to update a field to NULL in a MSSQL database, Doctrine generates the following request:

          UPDATE table SET fieldThatMustBeNull = , anotherField = 'blabla';
      

      therefore generating a syntax error.

      A fix would be to override the update method in the Doctrine_Connection_Mssql and add the following behavior:

      Doctrine_Connection_Mssql
          public function update(Doctrine_Table $table, array $fields, array $identifier)
          {
              if (empty($fields)) {
                  return false;
              }
      
              $set = array();
              foreach ($fields as $fieldName => $value) {
                  if ($value instanceof Doctrine_Expression) {
                      $set[] = $this->quoteIdentifier($table->getColumnName($fieldName)) . ' = ' . $value->getSql();
                      unset($fields[$fieldName]);
                  } else if (is_null($value)) {
                      $set[] = $this->quoteIdentifier($table->getColumnName($fieldName)) . ' = NULL';
                      unset($fields[$fieldName]);
                  } else {
                      $set[] = $this->quoteIdentifier($table->getColumnName($fieldName)) . ' = ?';
                  }
              }
      
              $params = array_merge(array_values($fields), array_values($identifier));
      
              $sql  = 'UPDATE ' . $this->quoteIdentifier($table->getTableName())
                    . ' SET ' . implode(', ', $set)
                    . ' WHERE ' . implode(' = ? AND ', $this->quoteMultipleIdentifier($table->getIdentifierColumnNames()))
                    . ' = ?';
      
              return $this->exec($sql, $params);
          }
      

        Activity

        guitio2002 created issue -
        guitio2002 made changes -
        Field Original Value New Value
        Description When trying to update a field to NULL in a MSSQL database, Doctrine generated the following request:
        {noformat}
            UPDATE table SET fieldThatMustBeNull = , anotherField = 'blabla';
        {noformat}

        therefore generating a syntax error.

        A fix would be to override the update method in the Doctrine_Connection_Mssql and add the following behavior:

        {code:title=Doctrine_Connection_Mssql|borderStyle=solid}
            public function update(Doctrine_Table $table, array $fields, array $identifier)
            {
                if (empty($fields)) {
                    return false;
                }

                $set = array();
                foreach ($fields as $fieldName => $value) {
                    if ($value instanceof Doctrine_Expression) {
                        $set[] = $this->quoteIdentifier($table->getColumnName($fieldName)) . ' = ' . $value->getSql();
                        unset($fields[$fieldName]);
                    } else if (is_null($value)) {
                        $set[] = $this->quoteIdentifier($table->getColumnName($fieldName)) . ' = NULL';
                        unset($fields[$fieldName]);
                    } else {
                        $set[] = $this->quoteIdentifier($table->getColumnName($fieldName)) . ' = ?';
                    }
                }

                $params = array_merge(array_values($fields), array_values($identifier));

                $sql = 'UPDATE ' . $this->quoteIdentifier($table->getTableName())
                      . ' SET ' . implode(', ', $set)
                      . ' WHERE ' . implode(' = ? AND ', $this->quoteMultipleIdentifier($table->getIdentifierColumnNames()))
                      . ' = ?';

                return $this->exec($sql, $params);
            }
        {code}
        When trying to update a field to NULL in a MSSQL database, Doctrine generates the following request:
        {noformat}
            UPDATE table SET fieldThatMustBeNull = , anotherField = 'blabla';
        {noformat}

        therefore generating a syntax error.

        A fix would be to override the update method in the Doctrine_Connection_Mssql and add the following behavior:

        {code:title=Doctrine_Connection_Mssql|borderStyle=solid}
            public function update(Doctrine_Table $table, array $fields, array $identifier)
            {
                if (empty($fields)) {
                    return false;
                }

                $set = array();
                foreach ($fields as $fieldName => $value) {
                    if ($value instanceof Doctrine_Expression) {
                        $set[] = $this->quoteIdentifier($table->getColumnName($fieldName)) . ' = ' . $value->getSql();
                        unset($fields[$fieldName]);
                    } else if (is_null($value)) {
                        $set[] = $this->quoteIdentifier($table->getColumnName($fieldName)) . ' = NULL';
                        unset($fields[$fieldName]);
                    } else {
                        $set[] = $this->quoteIdentifier($table->getColumnName($fieldName)) . ' = ?';
                    }
                }

                $params = array_merge(array_values($fields), array_values($identifier));

                $sql = 'UPDATE ' . $this->quoteIdentifier($table->getTableName())
                      . ' SET ' . implode(', ', $set)
                      . ' WHERE ' . implode(' = ? AND ', $this->quoteMultipleIdentifier($table->getIdentifierColumnNames()))
                      . ' = ?';

                return $this->exec($sql, $params);
            }
        {code}
        Guilherme Blanco made changes -
        Fix Version/s 1.2.4 [ 10063 ]

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DC-1007, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            guitio2002
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: