4

I have a Codeigniter project on my Ubuntu Linux server. I don't use MySQL because I am connecting Microsoft SQL 2014 Express Server. (I am using FreeTDS on my Linux server) The FreeTDS is working, I can connect MS SQL server, but I have a problem with character coding. I am using Hungarian_CI_AS collaction in my MS SQL server and UTF-8 on FreeTDS (client charset = UTF-8) and Codigniter.

The problem: I have MS SQL field content: Igazgatósági előterjesztések And this shows after SQL query: Igazgat�s�gi el?terjeszt�sek (It doesn't show hungarian character ó, á, ő, é, etc.) I think this is UTF-8 problem. I looked for this problem but I don't find any good tip. I tried ini_set('mssql.charset', 'UTF-8'); on the php.ini but this is not working. I tried convert the string after the query. Example: UTF-8 to ISO8859-1 and UCS2 to UTF-8 and UCS2 to ISO8859-1 etc. The best result when í, ó, ú, é character is working, but ő charater is not working.

What is the solution? Which charater coding does MS SQL Server use? How to convert this string in order to work?

user3740961
  • 329
  • 1
  • 5
  • 17
  • Possible duplicate of [UTF-8 all the way through](http://stackoverflow.com/questions/279170/utf-8-all-the-way-through) – André Ferraz Nov 03 '15 at 20:48
  • Where could I duplicate UTF-8? Because MS SQL Server doesn't use UTF-8. – user3740961 Nov 03 '15 at 21:07
  • 1
    @AndreFerraz That's for MySql not MS SQL. Some better links: http://stackoverflow.com/questions/3951722/whats-the-difference-between-unicode-and-utf8 , http://stackoverflow.com/questions/12512687/sql-server-utf8-howto – Adam Martin Nov 03 '15 at 21:57
  • Sorry, but it is not working. The í, ó, ú, é good...the ő, ű is not good. (I show ? character) – user3740961 Nov 04 '15 at 08:07
  • My bad, didn't pay much attention. – André Ferraz Nov 04 '15 at 10:15
  • php.ini: comment the line `extension=php_mssql` and add `extension=php_dblib` – cetver Feb 16 '21 at 11:13
  • Have you set the charset in database.php (config) for codeigniter as well? Some drivers you need to have in php.ini the connection. May help if you paste you connection config (less host/user/pass) in the question to help rule that out. – Robbie Feb 17 '21 at 00:38
  • @user3740961 Can you post a sample code with the issue from the question? Thanks. – Zhorov Feb 17 '21 at 09:28
  • Did you try to set client charset in freetds.conf http://www.freetds.org/userguide/Localization.html ? – ExploitFate Feb 17 '21 at 16:06
  • one question, you insert the "strange" characters, directly from sql management, or similar in a table , and shows that, or you insert via your php application?, if you try to make it, both ways show different results? – Juan Ruiz de Castilla Feb 17 '21 at 21:35
  • second question, can you please confirm is the character you lost is this: LATIN SMALL LETTER O WITH DOUBLE ACUTE (U+0151) - in the following link https://www.fileformat.info/info/charset/UTF-8/list.htm – Juan Ruiz de Castilla Feb 17 '21 at 21:40
  • Can you share your `CREATE TABLE` statement, please? – gvee Feb 18 '21 at 17:13
  • 1
    [Karcan](https://stackoverflow.com/users/15245589) posted an [Answer](https://stackoverflow.com/a/66286595) saying "can u try alter column as nvarchar? i dont have charset problem on ubuntu/mssql with laravel." – Scratte Feb 20 '21 at 10:42
  • 1
    Can you upgrade the database server to MS SQL 2019? UTF-8 support introduced in this version. – adampweb Feb 20 '21 at 18:29

1 Answers1

0

First, please check if you can type that character since some tool, different of your application, for example SQL Server Management Studio, then save it in the table and create an stored procedure with a variable, for example, that saves, that character, in both cases if you can see the character correctly, please follow the next steps:

  • Put some identifier for the special characters to replace the special character since your application, for example, if you need to save that "o" with double acute, replace for "_1" (think a better idea :P for the identifier).
  • Write a stored procedure to replace that "_1" again for that "o" with double acute but, since SQL Server, you write the character directly in the code of the stored procedure.

Is a little dirty work around and depends if you can type that character directly to SQL Server. I have had that problems before, and I don't remember exactly the final solution, but I remember this trick that let me continue working to find the final solution.

Plus: Did you check the type of the column that stores the character?, whatever, start trying that requested at the beginning.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Juan Ruiz de Castilla
  • 964
  • 14
  • 25