insertig arabic text with in ODBC PHP Driver with MSSQL

Question

In our application, we are using Ms Sql database and it is hosted on sqladmin.winhost.com. We have PHP install with ODBC driver along with Windows server. The database is Using the Collation "Arabic_CI_AI".

Below is the code for simple inserting data to a Table. The query is executed success fully but arabic data lost and insted some random string is table.

When the Value of $request['username'] is "احمد علي عريبي" in dtabase it is received with some random string.

<?php 

$connectionString = 'DRIVER={SQL Server};SERVER=' . SERVER_NAME . ';DATABASE=' . DB_NAME;
$conn = odbc_connect($connectionString, USER_NAME, PASSWORD);
  $request=$_POST;      
echo $sql="INSERT INTO MobileNotificaiton.emp_user VALUES('[email protected]','MXI',200,'{$request['username']}','123','12365897452',1,'','','','')";        

        $results = odbc_exec($conn, $sql);

    $close = odbc_close($conn);

    ?>

Can any one guid me?

Thanks in Advance.

Answer 1

After some research, I found the solution. I converted the Arabic text to binary in PHP and then while inserting data to MSSQL using CAST function of MSSQL convert this binary data to its desired data type.

$connectionString = 'DRIVER={SQL Server};SERVER=' . SERVER_NAME . ';DATABASE=' . DB_NAME;
$conn = odbc_connect($connectionString, USER_NAME, PASSWORD);
$request=$_POST;      
$data = iconv("UTF-8", "UCS-2LE", $request['username']);
$unpacked = unpack('H*hex', $data);
$username='0x' . strtoupper($unpacked['hex']);

$sql="INSERT INTO MobileNotificaiton.emp_user VALUES('[email protected]','MXI',200,'{$username}','123','12365897452',1,'','','','')";        
$results = odbc_exec($conn, $sql);
$close = odbc_close($conn);

While fetching the data from the database we use the same process in reverse order. This way data is compatible in .NET application and in PHP application. That is what my requirement was.