3

I am working with PHP on a MSSQL database and sometimes I need to import some SQL queries for an intranet.

The SQL queries are saved in a .sql file, something simple like

INSERT INTO table VALUES ('Do this and that');
INSERT INTO table VALUES ('This has 10% or 20€ discount');

The problem is that sometimes the importing breaks because of unsupported characters (% and €). I have tried setting the connection to use UTF-8, but it's still not working... I guess that something is wrong in the method I am using:

$connExchange = new PDO('dblib:version=7.0;charset=UTF-8;host=' . $host . ';dbname=' . $db, $user, $pass);
$connExchange->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

How can I set the connection to use the right collection?

I am using php7.0.30, coming from with ubuntu 16.04 repositories.

The MSSQL database is using SQL_Latin1_General_CP1_CI_AS.

ToX 82
  • 1,064
  • 12
  • 35
  • 1
    All it takes, is one wrong charset setting in your application - *everything* needs to be the same charset! I have previously written [**an answer about UTF-8 encoding**](https://stackoverflow.com/a/31899827/4535200) that contains a little checklist, that will cover *most* of the charset issues in a PHP/MySQL application. – Qirel Jul 11 '18 at 14:11
  • I am on sql server, not mysql... but yeah I'm trying to set the same encoding everywhere indeed. I can't change anything on the database itself, so I guess that I should change the charset in the php script, right? Setting UTF8 in the DSN doesn't seem to be working though – ToX 82 Jul 11 '18 at 14:21
  • The database must be set to UFT8 too. Although the values already inserted will have a broken encoding and must be updated or re-inserted to be updated. The database itself and its tables must be set to UFT8, as the PHP header and PDO connection (in the DSN). – Qirel Jul 11 '18 at 14:23
  • Since I can't change anything in the database (I really can't, because I am working on a really tiny part of it and I can't affect the rest of it), can I set PDO to use the database's charset instead? – ToX 82 Jul 11 '18 at 14:27
  • You can and should, but that might not be enough. You have to set at least the table you are working on to UTF8. Mixing charsets is a bad idea, and will always lead to problems. Like I said in my initial comment, all it takes is one different/broken character setting, and it won't work. And also, data that already exists will not be magically repaired, it must be re-inserted or updated. – Qirel Jul 11 '18 at 14:36
  • What is the type of the column, where you insert values - char, nchar, varchar, nvarchar, ...? – Zhorov Jul 11 '18 at 20:12
  • The data type for that field is varchar – ToX 82 Jul 11 '18 at 20:27
  • @ToX82 Sorry for that delay. Column type varchar in MS SQL Server is variable-length, non-Unicode character data. The collation controls the code page that is used to store the character data. I'am not able to reproduce your case (because I don't have Linux machine) and I'm not familiar with FreeTDS, but if your .sql files are UTF-8 encoded, then convertion must be done. Depends on settings, driver can do this convertion for you, or you must do it with iconv() for example. That's what I do in similar situaition (MSSQL with Cyrillic_General_CS_AS collation, PHP Driver for SQL Server). – Zhorov Jul 12 '18 at 09:19

0 Answers0