Skip to content

Microsoft SQL Server (MSSQL) Collation and Unicode Support

← Datagrid Display

Note: This KB only applies to phpGrid commercial licenses users who have the full source code access.

Database Type:

First of all, use "odbc_mssql" or generic "odbc" as database type (DB_TYPE) in conf.php using your database is SQL Server. 

Unicode:

Secondly check if the target column types are nvarchar (or nchar) to store Unicode string data.

Collation:

Current ADOdb driver has limited MSSQL support for non-latin characters collation such as Japanese, Croatian, or Chinese etc. If your SQL Server database collation is not the default SQL_Latin1_General_CP1_C, the string contains any special Unicode characters (e.g. german), like: üäöß, may be stored as gibberish such as üäöß.  If so, you may need to create PHP characters mapping function to decode data during view (data.php) and encode data during save (edit.php).  

Example:

Below is the example to create character mapping for Croatian collation (e.g. Croatian_CI_AI). 

In data.php create characters mapping array and function for decoding:

$cp1252_map = array( 
"\xc2\x80" => "\xe2\x82\xac", /* EURO SIGN */ 
"\xc2\x82" => "\xe2\x80\x9a", /* SINGLE LOW-9 QUOTATION MARK */ 
"\xc2\x83" => "\xc6\x92", /* LATIN SMALL LETTER F WITH HOOK */ 
"\xc2\x84" => "\xe2\x80\x9e", /* DOUBLE LOW-9 QUOTATION MARK */ 
"\xc2\x85" => "\xe2\x80\xa6", /* HORIZONTAL ELLIPSIS */ 
"\xc2\x86" => "\xe2\x80\xa0", /* DAGGER */ 
"\xc2\x87" => "\xe2\x80\xa1", /* DOUBLE DAGGER */ 
"\xc2\x88" => "\xcb\x86", /* MODIFIER LETTER CIRCUMFLEX ACCENT */ 
"\xc2\x89" => "\xe2\x80\xb0", /* PER MILLE SIGN */ 
"\xc2\x8a" => "\xc5\xa0", /* LATIN CAPITAL LETTER S WITH CARON */ 
"\xc2\x8b" => "\xe2\x80\xb9", /* SINGLE LEFT-POINTING ANGLE 
QUOTATION */ 
"\xc2\x8c" => "\xc5\x92", /* LATIN CAPITAL LIGATURE OE */ 
"\xc2\x8e" => "\xc5\xbd", /* LATIN CAPITAL LETTER Z WITH CARON */ 
"\xc2\x91" => "\xe2\x80\x98", /* LEFT SINGLE QUOTATION MARK */ 
"\xc2\x92" => "\xe2\x80\x99", /* RIGHT SINGLE QUOTATION MARK */ 
"\xc2\x93" => "\xe2\x80\x9c", /* LEFT DOUBLE QUOTATION MARK */ 
"\xc2\x94" => "\xe2\x80\x9d", /* RIGHT DOUBLE QUOTATION MARK */ 
"\xc2\x95" => "\xe2\x80\xa2", /* BULLET */ 
"\xc2\x96" => "\xe2\x80\x93", /* EN DASH */ 
"\xc2\x97" => "\xe2\x80\x94", /* EM DASH */

"\xc2\x98" => "\xcb\x9c", /* SMALL TILDE */ 
"\xc2\x99" => "\xe2\x84\xa2", /* TRADE MARK SIGN */ 
"\xc2\x9a" => "\xc5\xa1", /* LATIN SMALL LETTER S WITH CARON */ 
"\xc2\x9b" => "\xe2\x80\xba", /* SINGLE RIGHT-POINTING ANGLE 
QUOTATION*/ 
"\xc2\x9c" => "\xc5\x93", /* LATIN SMALL LIGATURE OE */ 
"\xc2\x9e" => "\xc5\xbe", /* LATIN SMALL LETTER Z WITH CARON */ 
"\xc2\x9f" => "\xc5\xb8" , /* LATIN CAPITAL LETTER Y WITH 
DIAERESIS*/

"\xc3\x88" => "\xc4\x8c", /* Croatian letters */ 
"\xc3\xa8" => "\xc4\x8d", /* Croatian letters */ 
"\xc3\x86" => "\xc4\x86", /* Croatian letters */ 
"\xc3\xa6" => "\xc4\x87", /* Croatian letters */ 
//"\xd0" => "\xc4\x90", /* Croatian letters */ 
"\xc3\xb0" => "\xc4\x91", /* Croatian letters */ 
);

function cp1252_to_utf8($str) { 
  global $cp1252_map; 
  return strtr(utf8_encode($str), $cp1252_map); 
}
After line: 
$col_name = $db->field_name($result, $j);
change to 
$data[] = cp1252_to_utf8($row[$col_name]);



In edit.php create characters mapping array and function for encoding:

function utf8_to_cp1252($string) '
{
static $transform = array( 
"\xE2\x82\xAC" => "\x80", 
"\xE2\x80\x9A" => "\x82", 
"\xC6\x92" => "\x83", 
"\xE2\x80\x9E" => "\x84", 
"\xE2\x80\xA6" => "\x85", 
"\xE2\x80\xA0" => "\x86", 
"\xE2\x80\xA1" => "\x87", 
"\xCB\x86" => "\x88", 
"\xE2\x80\xB0" => "\x89", 
"\xC5\xA0" => "\x8A", 
"\xE2\x80\xB9" => "\x8B", 
"\xC5\x92" => "\x8C", 
"\xC5\xBD" => "\x8E", 
"\xE2\x80\x98" => "\x91", 
"\xE2\x80\x99" => "\x92", 
"\xE2\x80\x9C" => "\x93", 
"\xE2\x80\x9D" => "\x94", 
"\xE2\x80\xA2" => "\x95", 
"\xE2\x80\x93" => "\x96", 
"\xE2\x80\x94" => "\x97", 
"\xCB\x9C" => "\x98", 
"\xE2\x84\xA2" => "\x99", 
"\xC5\xA1" => "\x9A", 
"\xE2\x80\xBA" => "\x9B", 
"\xC5\x93" => "\x9C", 
"\xC5\xBE" => "\x9E", 
"\xC5\xB8" => "\x9F", 
"\xC2\xA0" => "\xA0", 
"\xC2\xA1" => "\xA1", 
"\xC2\xA2" => "\xA2", 
"\xC2\xA3" => "\xA3", 
"\xC2\xA4" => "\xA4", 
"\xC2\xA5" => "\xA5", 
"\xC2\xA6" => "\xA6", 
"\xC2\xA7" => "\xA7", 
"\xC2\xA8" => "\xA8", 
"\xC2\xA9" => "\xA9", 
"\xC2\xAA" => "\xAA", 
"\xC2\xAB" => "\xAB", 
"\xC2\xAC" => "\xAC", 
"\xC2\xAD" => "\xAD", 
"\xC2\xAE" => "\xAE", 
"\xC2\xAF" => "\xAF", 
"\xC2\xB0" => "\xB0", 
"\xC2\xB1" => "\xB1", 
"\xC2\xB2" => "\xB2", 
"\xC2\xB3" => "\xB3", 
"\xC2\xB4" => "\xB4", 
"\xC2\xB5" => "\xB5", 
"\xC2\xB6" => "\xB6", 
"\xC2\xB7" => "\xB7", 
"\xC2\xB8" => "\xB8", 
"\xC2\xB9" => "\xB9", 
"\xC2\xBA" => "\xBA", 
"\xC2\xBB" => "\xBB", 
"\xC2\xBC" => "\xBC", 
"\xC2\xBD" => "\xBD", 
"\xC2\xBE" => "\xBE", 
"\xC2\xBF" => "\xBF", 
"\xC3\x80" => "\xC0", 
"\xC3\x81" => "\xC1", 
"\xC3\x82" => "\xC2", 
"\xC3\x83" => "\xC3", 
"\xC3\x84" => "\xC4", 
"\xC3\x85" => "\xC5", 
"\xC3\x86" => "\xC6", 
"\xC3\x87" => "\xC7", 
"\xC3\x88" => "\xC8", 
"\xC3\x89" => "\xC9", 
"\xC3\x8A" => "\xCA", 
"\xC3\x8B" => "\xCB", 
"\xC3\x8C" => "\xCC", 
"\xC3\x8D" => "\xCD", 
"\xC3\x8E" => "\xCE", 
"\xC3\x8F" => "\xCF", 
"\xC3\x90" => "\xD0", 
"\xC3\x91" => "\xD1", 
"\xC3\x92" => "\xD2", 
"\xC3\x93" => "\xD3", 
"\xC3\x94" => "\xD4", 
"\xC3\x95" => "\xD5", 
"\xC3\x96" => "\xD6", 
"\xC3\x97" => "\xD7", 
"\xC3\x98" => "\xD8", 
"\xC3\x99" => "\xD9", 
"\xC3\x9A" => "\xDA", 
"\xC3\x9B" => "\xDB", 
"\xC3\x9C" => "\xDC", 
"\xC3\x9D" => "\xDD", 
"\xC3\x9E" => "\xDE", 
"\xC3\x9F" => "\xDF", 
"\xC3\xA0" => "\xE0", 
"\xC3\xA1" => "\xE1", 
"\xC3\xA2" => "\xE2", 
"\xC3\xA3" => "\xE3", 
"\xC3\xA4" => "\xE4", 
"\xC3\xA5" => "\xE5", 
"\xC3\xA6" => "\xE6", 
"\xC3\xA7" => "\xE7", 
"\xC3\xA8" => "\xE8", 
"\xC3\xA9" => "\xE9", 
"\xC3\xAA" => "\xEA", 
"\xC3\xAB" => "\xEB", 
"\xC3\xAC" => "\xEC", 
"\xC3\xAD" => "\xED", 
"\xC3\xAE" => "\xEE", 
"\xC3\xAF" => "\xEF", 
"\xC3\xB0" => "\xF0", 
"\xC3\xB1" => "\xF1", 
"\xC3\xB2" => "\xF2", 
"\xC3\xB3" => "\xF3", 
"\xC3\xB4" => "\xF4", 
"\xC3\xB5" => "\xF5", 
"\xC3\xB6" => "\xF6", 
"\xC3\xB7" => "\xF7", 
"\xC3\xB8" => "\xF8", 
"\xC3\xB9" => "\xF9", 
"\xC3\xBA" => "\xFA", 
"\xC3\xBB" => "\xFB", 
"\xC3\xBC" => "\xFC", 
"\xC3\xBD" => "\xFD", 
"\xC3\xBE" => "\xFE", 
"\xC3\xBF" => "\xFF", 
"\xc4\x90" => "\xd0", 
"\xc4\x91" => "\xF0", 
"\xc4\x8c" => "\xC8", 
"\xc4\x8d" => "\xE8", 
"\xc4\x86" => "\xC6", 
"\xc4\x87" => "\xE6"

); 
return strtr($string, $transform); 
}
Then change line : 
$arrFields[$key] =$value;
to 
$arrFields[$key] = utf8_to_cp1252($value);

Feedback and Knowledge Base

(thinking…)