PHP sqlsrv 2 and UTF-8 insertion

Question

Please, what should I do to store data with UTF-8 into SQL Server (2005) right? My html page is UTF-8 encoded, column in table is type nvarchar, connection charset is set to UTF-8, but russian string 'Логистика Другое' is stored as '????????? ??????' into table (by stored procedure). If I store right string to table manually, it is displayed well. I am using sqlsrv v2. Here is connection params:

$dbConnParams = array('server' => "server",
                      'params' => array("Database"      => "dbname",
                                        "UID"           => 'user',
                                        "PWD"           => 'pass',
                                        "CharacterSet"  => 'UTF-8'));

$dbh = sqlsrv_connect($dbConnParams['server'], $dbConnParams['params']);

Answer 1

Hi

I understand your table has column type NVARCHAR.

Is it possible that your stored procedure is converting the NVARCHAR value to VARCHAR, in some way?

This example shows some ways it could happen:

<?php

header('Content-type: text/plain; charset=utf-8');
$conn = sqlsrv_connect('.\SQLEXPRESS',array('Database'=>'Tests','CharacterSet'=>'UTF-8'));

$tsql = <<<SQL
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[names]') AND type in (N'U'))
CREATE TABLE names
(
	[name] [nvarchar](100) NOT NULL
);
truncate table names;

insert into names([name]) values(?);

declare @myName varchar(100)
set @myName = ?		/* Accidentally converted to varchar here... */ 
insert into names([name]) values(@myName);

/* Also another accident here: no 'N' prefix in 'values'  */
exec(   N'insert into names([name]) values(''' + ? + ''')'   );

select * from names;
SQL;

$params=array('First: Логистика Другое', 'Second: Логистика Другое', 'Third: Логистика Другое');
$ct=0;

if ( ($stmt = sqlsrv_query($conn, $tsql, $params)) )
{
	do 
	{ 
		++$ct;
		echo "Result $ct processed: ";
		if ( sqlsrv_num_fields($stmt) > 0 )
		{
			$row_num=0;
			while ( ($row=sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) )
			{
				echo 'Row ' . ++$row_num . ': ' . print_r($row, true);  
			}
			echo "$row_num row(s) processed.\r\n";
		}
		else
		{
			$affected = sqlsrv_rows_affected($stmt);
			echo $affected . " row(s) affected.\r\n";
		}
	} while ( ($next=sqlsrv_next_result($stmt)) ) ;
	
	if ( $next === false )
	{
		echo "Error in result...\r\n" . print_r(sqlsrv_errors(), true);
	}
	sqlsrv_free_stmt($stmt);
}
else
{
	echo 'Statement query error: ' . print_r(sqlsrv_errors(), true);
}
sqlsrv_close($conn);

echo "Finished.\r\n";

?>

The result is this:

Result 1 processed: 1 row(s) affected.
Result 2 processed: 1 row(s) affected.
Result 3 processed: 1 row(s) affected.
Result 4 processed: Row 1: Array
(
    [name] => First: Логистика Другое
)
Row 2: Array
(
    [name] => Second: ????????? ??????
)
Row 3: Array
(
    [name] => Third: ????????? ??????
)
3 row(s) processed.
Finished.

---

Rob

Answer 2

Hi,

Did Robert's answer help you troubleshoot your T-SQL? Is it possible for you to show us what your T-SQL is doing?

Thanks,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.