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
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.
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.
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?
Donate
