Welcome Guest, Not a member yet? Register   Sign In
Troubles saving strings with special chars in MS SQL
#1

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_DBtrue);
$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
Reply
#2

(This post was last modified: 03-19-2020, 10:58 AM by jreklund.)

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.
Reply
#3

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 ?
Reply
#4

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.
Reply
#5

(This post was last modified: 03-20-2020, 11:02 AM by jreklund.)

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',
Reply
#6

(This post was last modified: 04-02-2020, 04:38 AM by davide65.)

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
Reply
#7

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
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply




Theme © iAndrew 2016 - Forum software by © MyBB