CODEWITHSUNDEEP
sql-server-2008pdoyiifreetds
Vahid Chakoshy
Vahid Chakoshy

Reputation: 1527

pdo dblib stored procedure insert wrong characters

i'm using microsoft sql server as database and my php code on centos server. using freetds and dblib to connect from yii framework to mssql.

everything's are fine. after insert into db by Stored Procedure, data saved but in database query we have ??????? in this NVARCHAR column.

my data in utf-8 arabic. this is my configuration and code:

fretds.conf 

[mss]  
        host = 172.31.1.2  
        ip = 172.31.1.2  
        port = 1433  
        tds version = 7.0

yii main configuration

'db'=>array(
        'connectionString' => 'dblib:host=mss;database=XXXX;charset=utf8',
        'username' => 'XXX',
        'password' => 'XXXXXXXX',
        'charset' => 'utf8',
        'tablePrefix' => 'tbl_',
        'enableProfiling' => true,
        'schemaCachingDuration' => 5 * 60 * 60,
        ),

my model Stored Procedure

$builder=$this->getCommandBuilder();
$table=$this->getMetaData()->tableSchema;

$command=$builder->createSqlCommand('EXEC dbo.sp_link_comment_insert  :link_id, :cmnt_parent_id, :user_id, :cmnt_status, :cmnt_text, :cmnt_thread',
                    array(
                        ':link_id'=>58829,
                        ':cmnt_parent_id'=>'',
                        ':user_id'=>9,
                        ':cmnt_status'=>1,
                        ':cmnt_text'=>'تست ارسال comment',
                        ':cmnt_thread'=>0,
                    )
                );
$command->execute();

Upvotes: 0

Views: 2110

Answers (2)

lubosdz
lubosdz

Reputation: 4500

Your driver (freeTDS) does not support UTF-8 for MSSQL. The only driver that natively supports UTF-8 for MSSQL is SQLDRV driver available only for windows platform.

To store data in UTF-8 using any other than SQLDRV driver (SQLDRV is not available for linux at the moment), the application must take care of converting from source charset into UTF-8 and vice versa. Therefore, setting "charset=UTF8" in your connection string has no effect.

In PHP, you should convert data before storing into MSSQL like so:

$dataUTF8 = iconv('windows-1250', 'utf-8', $data);
$sql = "INSERT INTO table (value) VALUES ($dataUTF8)";

and convert them back when reading from MSSQL:

$sql = "SELECT dataUTF8 FROM table;";
$data = iconv('utf-8', 'windows-1250', $dataUTF8);

Upvotes: 0

John P
John P

Reputation: 15245

A late reply. Try specifying the encoding in the freetds configuration using the option client charset = UTF-8. Your config file would look like this after the change:

[mss]  
    host = 172.31.1.2  
    ip = 172.31.1.2  
    port = 1433  
    tds version = 7.0
    client charset = UTF-8

According to the docs chars not recognized are converted to ?.

If FreeTDS runs into a character it can not convert, its behavior varies according to the severity of the problem. On retrieving data from the server, FreeTDS substitutes an ASCII '?' in the character's place, and emits a warning message stating that some characters could not be converted.

Reference: http://freetds.schemamania.org/userguide/localization.htm

Upvotes: 1

Related Questions