CodeIgniter Forums
Troubles saving strings with special chars in MS SQL - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Development (https://forum.codeigniter.com/forumdisplay.php?fid=6)
+--- Forum: Issues (https://forum.codeigniter.com/forumdisplay.php?fid=19)
+--- Thread: Troubles saving strings with special chars in MS SQL (/showthread.php?tid=75810)

Troubles saving strings with special chars in MS SQL - davide65 - 03-19-2020

Hi,
in my Raspberry 3 (raspbian 10 buster) I'm using CodeIgniter (3.1.9) connected to a MS SQL Server via FreeTDS.
If I save a string containing special chars, they are transformed.
For example this string "Pipe 1m Ø30 cm. max. temp 90 °C" is saved as "Pipe 1m Ø30 cm. max. temp 90 °C"

CodeIgniter sample code:

PHP Code:
$DB = $this->load->database($MSSQL_DB, true);
$desc = "Pipe 1m Ø30 cm. max. temp 90 °C"
$sql = "INSERT INTO MYTABLE (ID, DESC) VALUES (1, '$desc')";
$result= $DB ->query($sql); 


In php.ini tried to set default_charset = "" but nothing changed.

How can I solve the problem, avoiding that special characters are changed?

Database collaton is Latin1_General_CI_AS and can't be changed.

Here are some configurations:

Header:
Code:
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />


CodeIgniter database.php:

Code:
$db['MYDB'] = array(
'dsn' => 'DRIVER=FreeTDS;SERVER=.....;Database=MYDB;UID=myUser;PWD=myPWD;TDS_Version= Auto;Port=1433;',
'hostname' => '',
'username' => '',
'password' => '',
'database' => '',
'dbdriver' => 'odbc',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => (ENVIRONMENT !== 'production'),
'char_set' => 'utf8',
'cache_on' => FALSE
);


/etc/freetds/freetds.conf

Code:
[MYSERVER]
host = 192.168.1.200
port = 1433
tds version = 7.2  # 7.2 seems to be the maximum allowed
client charset=UTF-8


/etc/odbcinst.ini

Code:
[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
Setup=/usr/lib/arm-linux-gnueabihf/odbc/libtdsS.so
FileUsage=1
dontdlclose=1
UsageCount=1


/etc/odbc.ini

Code:
[MYDB]
Description    = Database demo MYDB
Driver          = FreeTDS
Database        = MYDB
Servername      = MYSERVER
Port = 1433



Thanks a lot

RE: Troubles saving strings with special chars in MS SQL - jreklund - 03-19-2020

Latin1 dosen't contain all symbols, so if you can't change your database structure. You need to save your data as base64 or binary and decode it after retrieving it.

RE: Troubles saving strings with special chars in MS SQL - davide65 - 03-20-2020

The database is of a Windows ERP so I cannot save data in base64 or binary. In Windows I can successfully use special characters. In CodeIgniter the data returned by queries that contain special characters are displayed correctly, it is only when saving (INSERT or UPDATE) that the problem occurs.
I tried to update Raspbian (sudo apt-get update and sudo apt-get upgrade).
Unfortunately I'm not an expert programmer in php and unable to understand if there is any limitation or what I need to configure to solve the problem.
What can I do ?

RE: Troubles saving strings with special chars in MS SQL - davide65 - 03-20-2020

Just tried to execute a query update using tsql, special chars are successully saved.
After connection tsql says:
locale is "it_IT.UTF-8"
locale charset is "it_IT.UTF-8"
using default charset "it_IT.UTF-8"
Hope this can help to get a solution.

RE: Troubles saving strings with special chars in MS SQL - jreklund - 03-20-2020

I can see that you are using an utf8 connection instead of latin in CodeIgniter, so you need to change that. You also need to make sure your application runs on latin, so the html header may needs to change too.

Code:
'char_set' => 'latin1',


RE: Troubles saving strings with special chars in MS SQL - davide65 - 04-02-2020

I created the following PHP example which reproduces the problem without using CodeIgniter.

PHP Code:
<?php
$odbc=odbc_connect("DRIVER=FreeTDS;SERVER=PRAXY\SQLEXPRESS;Database=DBTEST;UID=sa;PWD=myPWD;TDS_Version= Auto;Port=1433;", "","") or die(odbc_errormsg());
$value = 'Hello °±Øòàèé?° world';
$q="UPDATE MYTABLE set MYFIELD = N'$value' WHERE ID = 1";
odbc_exec($odbc, $q) or die("<p>".odbc_errormsg());
odbc_close($odbc);
?>


Thanks to the help of the FreeTDS team, I solved the issue changing the odbc connection string. Now I use SERVERNAME parameter instead of SERVER, so FreeTDS can load configuration from freetds.conf file.

PHP Code:
<?php
$odbc=odbc_connect("DRIVER=FreeTDS;SERVERNAME=PRAXY;Database=DBTEST;UID=sa;PWD=myPWD;TDS_Version= Auto;Port=1433;", "","") or die(odbc_errormsg());
$value = 'Hello °±Øòàèé?° world';
$q="UPDATE attdocteste set MYFIELD = N'$value' WHERE ID = 1";
odbc_exec($odbc, $q) or die("<p>".odbc_errormsg());
odbc_close($odbc);
?>


I tried to update CodeIgniter database configuration (pasting the new connection string) from:

Code:
$db['GALAXYNG_SVILUPPO'] = array(
'dsn' => 'DRIVER=FreeTDS;SERVER=192.168.1.100\SQLEXPRESS;Database=DBTEST;UID=sa;PWD=masterkey;TDS_Version= Auto;Port=1433;',
    'hostname' => '',
'username' => 'sa',
'password' => 'masterkey',
    'database' => 'DBTEST',
'dbdriver' => 'odbc',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => (ENVIRONMENT !== 'production'),
'cache_on' => FALSE
);


to: 

Code:
$db['GALAXYNG_SVILUPPO'] = array(
    'dsn' => 'DRIVER=FreeTDS;SERVERNAME=PRAXY;Database=DBTEST;UID=sa;PWD=myPWD;TDS_Version= Auto;Port=1433;',
    'hostname' => '',
    'username' => 'sa',
    'password' => 'myPWD',
    'database' => 'DBTEST',
    'dbdriver' => 'odbc',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE
);


I have no connection errors, but now I've lot of trouble with queries: no data are returned or if joins are present, I have no connection errors, but now I've lot of troubles with queries: no data are returned or if joins are present, I get only the fields of the "main" table while  fields of the linked tables are not.

This is a sample function for retriving data from MSSQL.
PHP Code:
public function getTableData($ID) {
   $DB = $this->load->database('GALAXYNG_SVILUPPO', true);
   $sql = "SELECT * FROM MYTABLE WHERE ID = $ID";    
   $query = $DB->query($sql);
   if($query->num_rows() > 0){
       return $query->result();
   }else{
       return false;
   }
} 


This is my /etc/freetds/freetds.conf

Code:
[PRAXY]
                host = 192.168.1.100
                port = 1433
                tds version = 7.2


This is my /etc/odbc.ini file:

Code:
[DBTEST]
Description    = Database DBTEST
Driver          = FreeTDS
Database        = DBTEST
Servername      = PRAXY
Port            = 1433
TDS_Version    = 7.2
ClientCharset  = CP1252

Any suggestion?
Thanks a lot

RE: Troubles saving strings with special chars in MS SQL - InsiteFX - 04-02-2020

For database to use all extended characters you need to use utf8_unicode_ci
you might be able to get away with just utf8_general_ci but the above one will
show all characters.

The database table DEFAULT Char= utf8 COLLATE=utf8_unicode_ci