1

I am working with php to insert a line in sql server with odbc but I have this problem with french accents ( é , â , ô , î , è ,...), when I insert inside sql server it works and also php reads it well, but when inserting using odbc it is read as a special character

Example:

$sujet = 'BEM relevé bancaire';

$sql ="INSERT into matrice values('$groupe','$sujet','$nom','$ref','$ref','$next')";

odbc_exec($connection, $sql);

Output:

ID: 542

groupe: Groupe_name

CI: BEM relevé bancaire //THE PROBLEM

Nom: Name

affectationRef: 1

restAffect: 1

nextAffect: 0

How can I make it reads the french accents ?

The possible duplicate doesn't answer my question unfortunately

Creeper
  • 386
  • 3
  • 16
  • What is the data type of `CI` column? – Zhorov May 29 '19 at 13:46
  • You're directly putting your data into the query. Not only is that unsafe, it could create the problem you're trying to fix. Consider [using ODBC prepared statements](https://stackoverflow.com/questions/5756369/odbc-prepared-statements-in-php) – Machavity May 29 '19 at 13:47
  • @Zhorov varchar – Creeper May 29 '19 at 13:48
  • @Machavity I edited the code to prepared statements but that didn't fix my problem. – Creeper May 29 '19 at 14:02
  • Post the schema of your table, as well as the collation. Also, have you ensured everything is in UTF8? [That's another common problem here](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) – Machavity May 29 '19 at 14:26
  • @Machavity it's sql server there's no UTF8. The database collation is SQL_Latin1_General_CP1_CI_AI. – Creeper May 29 '19 at 15:20
  • Is your PHP script written in UTF8? Does your website serve up UTF8 pages? – Machavity May 29 '19 at 15:21
  • @Machavity yes it does. my PHP reads special characters the problem is when inserting to sql server, the special characters aren't read. And it's not problem of sql server I think it is from odbc, cause I insert in sql server and they are read well, but from odbc they aren't. – Creeper May 29 '19 at 15:57
  • Have you tried [PDO](https://www.php.net/manual/en/book.pdo.php) by any chance then? – Machavity May 29 '19 at 16:00
  • I'm familiar with PDO but the client I'm working with insists on odbc. – Creeper May 29 '19 at 16:04

1 Answers1

0

I would need to see the schema of the table to provide a definite answer. SQL Server data types of char and varchar do not support double-byte characters sets. Ensure that your table column, that you wish to insert to, has nchar or nvarchar as the data type.

You can test this with a simple table, with multiple columns. Inserting the same value into column A and column B, will return different results.

Create table test (A varchar(20), B nvarchar(20))
Insert into test (A,B) values ('â',N'â')

If this does not solve the issue, then I think the client code page differs from that of the sql server code page. You can read more about this here: You cannot correctly translate character data from a client to a server by using the SQL Server ODBC driver if the client code page differs from the server code page

nealkernohan
  • 788
  • 1
  • 6
  • 15