In our commercial app, we are using master/slave feature in database configuration and running a MSSQL(business requirement) db as the master and MySQL db as a slave. We want to do read requests from the slave(MySQL) and write requests to the master(MSSQL). We have configured a background job to copy data from master to slave.
I tried to do an update to the master and it failed since the SQL generated was having MySQL syntax but not the MSSQL syntax. The connection is switching correctly to the master. When I checked update() function in the db/QueryBuilder.php file,
public function update($table, $columns, $condition, &$params)
{
if (($tableSchema = $this->db->getTableSchema($table)) !== null) {
$columnSchemas = $tableSchema->columns;
} else {
$columnSchemas = [];
}
$lines = [];
foreach ($columns as $name => $value) {
if ($value instanceof Expression) {
$lines[] = $this->db->quoteColumnName($name) . '=' . $value->expression;
foreach ($value->params as $n => $v) {
$params[$n] = $v;
}
} else {
$phName = self::PARAM_PREFIX . count($params);
$lines[] = $this->db->quoteColumnName($name) . '=' . $phName;
$params[$phName] = !is_array($value) && isset($columnSchemas[$name]) ? $columnSchemas[$name]->dbTypecast($value) : $value;
}
}
$sql = 'UPDATE ' . $this->db->quoteTableName($table) . ' SET ' . implode(', ', $lines);
$where = $this->buildWhere($condition, $params);
return $where === '' ? $sql : $sql . ' ' . $where;
}
$this->db->quoteColumnName($name)
always gets the MySQL format. We need to have it with MSSQL format added in the QueryBuilder class in mssql/QueryBuilder.php file. I’m bit confused on how the schema is selected in this scenario for
$this->db
.
Please let me know how to fix this issue. Thanks in advance!
Sam