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