PHP pdo_sqlsrv：默认情况下在MS SQL Server上插入unicode字符串

Question

I have a database PHP application that can work with MySQL, PostgreSQL and SQLite. The application uses PDO and assumes the database content is Unicode.

I am adding the support for MS SQL SERVER using the pdo_sqlsrv driver and the only solution I have found is to modify all the queries (it's really a lot of work) adding the N prefix in front of the strings for the INSERT and UPDATE queries.

Is there any smarter solution to insert unicode by default? Like a PDO or SQL SERVER setting?

Since all the queries are executed by a function (it's my own layer over PDO), I have also thought about adding a regular expression that modifies the queries before the execution but I think it's quite tricky.

Thanks.

Answer 1

After you set up your connection to the DB you need to add the PDO Attributes:

$mssql_connection->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_UTF8);

Reference: https://docs.microsoft.com/en-us/sql/connect/php/pdostatement-setattribute?view=sql-server-2017