MSSQL + PHP - Error to insert oriental char

Question

I'm having a problem to insert a oriental character with bind variables in SQL Server. i'm using MSSQL commands and PHP.

My PHP code is like this:

$sql = "
    CREATE TABLE table_test
      (  id                int
        ,nvarchar_latin    nvarchar(255) collate sql_latin1_general_cp1_ci_as 
      );";

    $stmt = mssql_query($sql);

    $conn = mssql_connect("server","user","pass");
    mssql_select_db('test')

    $stmt = mssql_init('test..sp_chinese', $conn); 

    $id      = 1;      
    $nvarchar_latin  = '重建議';

    mssql_bind($stmt, '@id'          , $id            , SQLINT1);  
    mssql_bind($stmt, @nvarchar_latin, $nvarchar_latin, SQLVARCHAR); 

    mssql_execute($stmt);

My procedure is like this:

ALTER PROCEDURE sp_chinese
  @id               int
 ,@nvarchar_latin   nvarchar (255) 
AS 
 BEGIN
 INSERT INTO char_chines (id, nvarchar_latin) 
      VALUES (@id, @nvarchar_latin);
 END

this work if I change the oriental characters for normal one. if I run directly this insert, it work's fine:

INSERT INTO table_test (id, nvarchar_latin) 
     VALUES (1, '重建議');

So, cleary the problem is when I send the variable from PHP to SQL Server.

Anyone have a clue how to make this works? some casting or something?

Thanks!

Blizzardengle · Answer 1 · 2014-09-25T16:18:16.643

A solution that uses just the PHP (or even JavaScript) is to convert the character to its HEX value and store that. I don't know if you want to go this route but and I don't have time to show you the code but here is the full theory:

A non-English character is detected, like so: 重

Convert to HEX value (Look here for starters. But a search for Javascript will help you find better ways to do this even in PHP): 14af

NOTE: That is not what 重 really is in HEX

Store in a way that you can convert back to its original value. For example how can you tell what this is: 0d3114af is it 0d - 31 - 14 - af OR is it 0d31 - 14af. You can use deliminators like | or a . but one way is to provide padding of 00 in front. An English character would be only 2 characters long like 31 or af non-English will be 4 like 14af. Knowing this you can just split every 4 characters and convert to their values.

Downside is you will need to change your Database to accommodate these changes.

[ UPDATE ] -----

Here is some JavaScript code to send you off in the right direction. This is completely possible to replicate in PHP. This does not search for characters though, its part of an encryption program so all it cares about is turning everything into HEX. English characters will be padded with 00 (This is my own code hence no link to source):

function toHex(data) {
   var result = '';
   // Loop through entire string of data character by character
   for(var i=0;i<data.length;i++) {
     // Convert UTF-16 Character to HEX, if it is a 2 chracter HEX add 00 padding in front
     result += (data.charCodeAt(i) + 0x10000).toString(16).slice(1);
   }
   // Display the result for testing purposes
   document.getElementById('two').value = result;
}

function fromHex(data) {
   var result = '', block = '', pattern = /(00)/;  // Pattern is the padding
   for(var i=0;i<data.length;i = i+4) {
      // Split into separate HEX blocks
      block = data.substring(i,i+4);
      // Remove 00 from a HEX block that was only 2 characters long
      if(pattern.test(block)){
         block = block.substring(2,4);
      }
      // HEX to UTF-16 Character
      result += String.fromCharCode(parseInt(block,16));
   }
   // Display the result for testing purposes
   document.getElementById('two').value = result;
}

MSSQL + PHP - Error to insert oriental char

1 Answers1