Laravel charset UTF-8 with sqlsrv

Question

What should I do to properly store data with special characters (UTF-8) in SQL Server (2014)? My HTML page is encoded in UTF-8, the column in the database table is of type nvarchar, the encoding of the connection is set to UTF-8, but the line

Haïti couplées à la capacité

stored as

慈 꿃 楴 挠 畯 汰 ꧃ 獥 쌠 ₠ 慬 挠 灡 捡 瑩 ꧃

or sometimes

????????????????

in the table. I am using Laravel 5 with a wandering estate. Here are the connection options:

'sqlsrv' => array(
        'driver'   => 'sqlsrv',
        'host'     => 'DB_HOST',
        'database' => 'DB_DATABASE',
        'username' => 'DB_USERNAME',
        'password' => 'DB_PASSWORD',
        'prefix'   => '',
        'charset' => 'utf8',
        'collation' => 'utf8_unicode_ci',
    ),

and set the following in freetds.conf:

[global]
tds version = 8.0
client charset = UTF-8

Answer 1

You can try the MS driver for Linux, which was released in January 2017.

https://docs.microsoft.com/en-us/sql/connect/odbc/linux/microsoft-odbc-driver-for-sql-server-on-linux

#### Script to Install MS SQL Server driver on Ubuntu:
#!/bin/bash
# SQL Server Driver Installer for Laravel Homestead
#
# This script downloads, compiles, and installs the PHP 7 extension
# files for both the native sqlsrv and the PDO pdo_sqlsrv drivers.

echo '=================================================================='
echo ' Get the Microsoft Driver Source Code from Github'
echo '=================================================================='

cd ~
git clone https://github.com/Microsoft/msphpsql.git
cd msphpsql
git checkout PHP-7.0-Linux

echo '=================================================================='
echo ' Run the ODBC Installer Script'
echo '=================================================================='

sh ODBC\ install\ scripts/installodbc_ubuntu.sh

echo '=================================================================='
echo ' Compile and Install the Native sqlsrv Driver'
echo '=================================================================='

cd ~/msphpsql/source/sqlsrv
phpize && ./configure CXXFLAGS=-std=c++11 && make
sudo make install
sudo echo "extension=sqlsrv.so" | sudo tee /etc/php/7.0/mods-available/sqlsrv.ini
phpenmod sqlsrv

echo '=================================================================='
echo ' Compile and Install the pdo_sqlsrv Driver'
echo '=================================================================='

cd ~/msphpsql/source/pdo_sqlsrv
phpize && ./configure CXXFLAGS=-std=c++11 && make
sudo make install
sudo echo "extension=pdo_sqlsrv.so" | sudo tee /etc/php/7.0/mods-available/pdo_sqlsrv.ini
sudo phpenmod pdo_sqlsrv

echo '=================================================================='
echo ' Restart the PHP-FPM Service'
echo '=================================================================='

sudo systemctl restart php7.0-fpm.service

echo '=================================================================='
echo ' Clean Up'
echo '=================================================================='
echo "Done."

pull install :

wget https://github.com/Microsoft/msphpsql/releases/download/4.0.8-Linux/Ubuntu16.tar
tar -xf Ubuntu16.tar
sudo cp ./Ubuntu16/*nts*.so /usr/lib/php/20151012
sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql mssql-tools
sudo touch /etc/php/7.0/mods-available/pdo_sqlsrv.ini

dblib:

sudo phpdismod pdo_dblib
sudo service php7.0-fpm restart