Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Syntax error - "UPDATE users SET UserFirstName = WHERE UserId = " (ORM, SQL Anywhere)
  • I'm now successfully using ORM to read data from a Sybase SQL Anywhere database. With MySQL, I can also update records like so:

      $entry = Model_User::find('first');
      $entry->UserFirstName = 'Testing';
      $entry->save();

    But with SQL Anywhere, I get this error:

    Fuel\Core\Database_Exception [ 42000 ]: SQLSTATE[42000]: Syntax error or access violation: 0 [Sybase][ODBC Driver][SQL Anywhere]Syntax error near 'WHERE' on line 1 (SQLPrepare[0] at ext\pdo_odbc\odbc_driver.c:206) with query: "UPDATE users SET UserFirstName = WHERE UserId = "

    COREPATH/classes/database/pdo/connection.php @ line 175

    170                        \Profiler::delete($benchmark);
    171                    }
    172
    173                    // Convert the exception in a database exception
    174                    $error_code = is_numeric($e->getCode()) ? $e->getCode() : 0;
    175                    throw new \Database_Exception($e->getMessage().' with query: "'.$sql.'"', $error_code, $e);
    176                }
    177            }
    178        }
    179        while ($attempts-- > 0);
    180

    Backtrace
    COREPATH/classes/database/query.php @ line 256
    PKGPATH/orm/classes/query.php @ line 1237
    PKGPATH/orm/classes/model.php @ line 1234
    PKGPATH/orm/classes/model.php @ line 1110
    APPPATH/classes/controller/test.php @ line 34
    COREPATH/classes/request.php @ line 423
    DOCROOT/index.php @ line 38

    It looks to me like the values are missing from the query.

    Any suggestions on how to debug or fix this?
  • The PDO driver is a generic driver, and used by default in every app. The Query Builder, that is responsible for generating all SQL, is the same for all database drivers.  So I doubt it has a bug this big, the forum would be flooded.

    Did you extend or modify any of the ORM code?
  • Thanks. I haven't modified any ORM code. If it helps, my actual code is below. 

    db.php:

    return array(
      'default' => array(
        'connection'     => array(
          'dsn' => 'odbc:Driver={SQL Anywhere 12};Server=EnergyManagementDev;Database=EnergyManagementDev;Uid=dba;Pwd=*******;Host=192.168.1.10',
          'username' => 'EM',
          'password'  => '*******',
        ),
        'identifier'   => '',
        'table_prefix'   => '',
    ),
    );

    The model:

    class Model_User extends Orm\Model
    {
      protected static $_table_name = 'users';
      protected static $_primary_key = array('UserId');
      protected static $_properties = array(
        'UserId',
        'UserFirstName',
        'UserLastName',
      );
    }

    The controller:

    class Controller_Test extends Controller
    {
      public function action_index()
      {
        $entry = Model_User::find('last');
      
        echo $entry->UserFirstName; //this works

       $entry->UserFirstName = "TestName"; 
       $entry->save(); //this triggers the error
       }
    }

  • The only think I can think of is that it's ODBC related, I don't have a Windows box here, nor SQL anywhere, so I can't debug it for you.

    Start in Database_PDO_Connection::query(), and work your way back.
  • I've now connected to MySQL using ODBC instead of the native driver. I get the same issue, which eliminates SQL Anywhere as the cause.

    $entry = Model_User::find('first') sends this query to the database (successful): 
      SELECT `t0`.`id` AS `t0_c0`, `t0`.`name` AS `t0_c1`, `t0`.`description` AS `t0_c2`, `t0`.`company` AS `t0_c3` FROM `users` AS `t0` ORDER BY `t0`.`id` ASC LIMIT 1

    $entry->save() sends this query to the database (obviously broken):
      UPDATE `users` SET `name` = WHERE `id` = 

    I'll try to keep stepping back and inform you if I find out more.
  • Check out core/classes/database/query/builder/.php. The _compile_set() method will construct the SET part of the UPDATE query, you can check if you still have valid data at that point.
  • The issue is with the 'escape' method in database/connection.php. This seems to be an abstract method overridden by each connection type.

    In database/mysql/connection.php we have:

     public function escape($value)
     {
      // Make sure the database is connected
      $this->_connection or $this->connect();

      if (($value = mysql_real_escape_string((string) $value, $this->_connection)) === false)
      {
        throw new \Database_Exception(mysql_error($this->_connection), mysql_errno($this->_connection));
      }

      // SQL standard is to use single-quotes for all values
      return "'$value'";
     }
     
    But in database/pdo/connection.php we have:

     public function escape($value)
     {
      // Make sure the database is connected
      $this->_connection or $this->connect();

      return $this->_connection->quote($value);
     }

    I'm not sure on the rationale for the different return statements, but '$this->_connection->quote($value)' is unexpectedly returning a boolean type.

    I don't know the correct or recommended fix. If I make the following change to database/pdo/connection.php, things seem to work for my purposes (both MySQL and SQL Anywhere):

     public function escape($value)
     {
      // Make sure the database is connected
      $this->_connection or $this->connect();

      return "'$value'";
     }

    Is this a bug? If so, I'd be grateful if you could register a ticket.
  • HarroHarro
    Accepted Answer
    Every RDBMS has it's own way of escaping en quoting strings in the SQL. For platform specific drivers, it's simple, as we could hardcode it. So for MySQL, a double quote is used for quoting, and a backtick for escaping.

    With PDO, that's different, as we don't know for which RDMBS you are going to use it for. So we reply on the PDO driver used to deal with this, through the PDO method quote() (see http://php.net/manual/en/pdo.quote.php).

    This normally works, but in your case, you have inserted a second abstraction layer in the form of ODBC. And as the docs state: "Not all PDO drivers implement this method (notably PDO_ODBC). Consider
    using prepared statements instead.".

    Which basically means you'll have to write your own connection driver, (Database_SQLAnywhere_Connection perhaps) that extends Database_PDO_Connection, and in which you overload the escape/quote methods.

    Given the fact more drivers don't support this (but they don't say which), perhaps we would update the PDO driver to deal with this internally. Could you create an issue for this on github?
  • Thanks - ticket registered: https://github.com/fuel/core/issues/1237

    I'll try your suggestions in the meantime.
  • Hi mtmacdonald: I've tried to reproduce the error without success.

    I've modified my connection with this:

    return array(
        'default' => array(
            'type'        => 'pdo',
            'connection'  => array(
                'dsn'        => 'mysql:host=localhost;dbname=mydb',
                'username'   => 'root',
                'password'   => 'root',
            ),
            'profiling'   => true,
        ),
    ); 

    The I've tried to perform the code you posted and everything worked fine. Am I missing something?
    I'm using PHP 5.4.4.


  • @iiNotturno he is using a ODBC driver in PDO for SQLAnywhere, MySQL works fine.
  • Yes - you need to use ODBC to reproduce the issue.

    E.g. for MySQL connected with ODBC:

    'default' => array(
      'type'  => 'pdo',
      'connection' => array(
      'dsn' => 'odbc:Driver={MySQL ODBC 5.1 Driver};Server={localhost};Database={fuel_test};Uid={fuel};Pwd={****}',
      'username' => 'fuel',
      'password' => '****',
      ),
      'identifier'   => '`',
    )

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion