I am working with codeigniter / SQL. Where I want to insert unicode string with 'N' Prefix the insert. How can I achieve it in regular insert.
Regular Code:
$data = array(
'title' => 'My title' ,
'name' => 'My Name' ,
'data' => 'My data'
);
$this->db->insert('mytable', $data);
INSERT Trying to Achieve:
INSERT INTO mytable (title, name, data)
VALUES ('My title','My Name',N'My data');
Is there any way to achive the above insert instead of manually writing the Query.?
I was having problems with Unicode using Codeignter 3, driver sqlsrv and I managed to get it working modifying the System/database/DB_Driver.php. Probably there is a better solution out there, it's not beautiful but it's working, just modify the functions _insert and _update.
protected function _insert($table, $keys, $values)
{
foreach($values as $key => $value)
{
if(substr($value,0,1) == "'")
{
$values[$key] = "N". $value;
}
}
return 'INSERT INTO '.$table.' ('.implode(', ', $keys).') VALUES ('.implode(', ', $values).')';
}
protected function _update($table, $values)
{
foreach($values as $key => $value)
{
if(substr($value,0,1) == "'")
{
$values[$key] = "N". $value;
}
}
foreach ($values as $key => $val)
{
$valstr[] = $key.' = '.$val;
}
return 'UPDATE '.$table.' SET '.implode(', ', $valstr)
.$this->_compile_wh('qb_where')
.$this->_compile_order_by()
.($this->qb_limit ? ' LIMIT '.$this->qb_limit : '');
}
N is used for SQL queries.. can use it in MySQL as well so if we change the core file then this will get affected.
if ($this->dbdriver == 'sqlsrv') it wont affect other db connections.This is the simplest way to do it.
$data = array(
'title' => 'My title' ,
'name' => 'My Name' ,
'data' => 'N'.'My data'
);
$this->db->insert('mytable', $data);
Or, if the prefix needs to more dynamic do this
$pre = foo(bar);
$data = array(
'title' => 'My title' ,
'name' => 'My Name' ,
'data' => $pre.'My data'
);
$this->db->insert('mytable', $data);
Thanks Eggy and Leandro for their suggestion. I use CI 3.1.9 and here is my solution:
public function escape($str)
{
...
elseif (is_string($str) OR (is_object($str) && method_exists($str, '__toString')))
{
if ($this->dbdriver == 'sqlsrv')
{
return "N'".$this->escape_str($str)."'";
}
return "'".$this->escape_str($str)."'";
}
...
}
Just add the following code to both
_insert($table, $keys, $values) and _update($table, $values) just on the first line of each function in system\database\DB_driver.php.
if ($this->dbdriver == 'sqlsrv')
{
foreach($values as $key => $value)
{
if(substr($value,0,1) == "'")
{
$values[$key] = "N". $value;
}
}
}
_insert()method and make your own that checks every value and use it as converted to N prefix where needed. Check lines 1396 - 1399.Nbefore your input string isn't actually what you are doing in your project, but we need to better understand your intent. Are you trying to add a prefix to the string value or are you trying to add a SQL-specific function call before the value? We don't know and can only assume.