Conectare
 
Pagina de pornireBibliotecăInformațiiFişiere de descărcatSuportComunitateForumuri
Resurse pentru profesionişti IT >
> 
> 
PHP sqlsrv 2 and UTF-8 insertion

Answered PHP sqlsrv 2 and UTF-8 insertion

  • 24 aprilie 2012 10:46
     
      Are cod
    Sign in pentru a vota
    0

    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']);
     

Toate mesajele

  • 24 aprilie 2012 14:20
     
     Răspuns Are cod
    Sign in pentru a vota
    0

    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

     
  • 25 aprilie 2012 17:19
     
     
    Sign in pentru a vota
    0

    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.