Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL DRIVER FOR PHP / MSSQL 2008 Standard Edition (64-bit) SP4 (10.0.6000.29)

Posted on 2016-07-20
9
Medium Priority
?
172 Views
Last Modified: 2016-07-21
Hello,
we have a software developped in PHP that was running under
windows server 2008 R2 64bits - MSSQL 2008 32 bits

we migrate the software yesterday from 32 bits to 64 bits.
and all the troubles began ....

Important to specify that we did the same migration a few weeks ago in our test servers and it works perfectly.

In our production server, one of our big problem is charset .
the soft is using iso-8859-1  (french accent)

we have problem with words with accent like é  (see attached file)

I compared test and production server, and I don't see any difference !
same Apache/2.2.17 (Win32) PHP/5.3.21
same sql driver for PHP:   php_pdo_sqlsrv_53_ts.dll    (3.0.3421)
same windows 2008 R2 Enterprise SP1
same MSSQL 2008 Standard Edition (64-bit) SP4 (10.0.6000.29)
same SQL Server Native CLient 11.0  2011.110.5058.00  15/05/2014

please see attached php info of both servers. do not see important difference.

for connexion I use this code
$iConnect = new PDO($connectString, NULL, NULL);
$iConnect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$iConnect->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_SYSTEM);

Open in new window


Thanks for any help, getting crazy......
production-php-info.txt
test-php-info.txt
snip_20160720165526.png
0
Comment
Question by:bruno_boccara
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 

Author Comment

by:bruno_boccara
ID: 41721295
I tested something right now.

I connected my test apache/php to sql production, and no problem of accent.

so the problem seems to be in apache or in php , not in the sql database setup.....
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41721565
This is the "signature" of a character encoding conflict.  Suggest you switch everything to UTF-8.  You must have consistency across all parts of the platform: HTML, PHP scripts, the database.

This article does not address MSSQL specifically, but I believe that the principles are shown correctly.
https://www.experts-exchange.com/articles/11880/Unicode-and-Character-Collisions.html
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 41721697
I agree with Ray.  Every time I see that symbol, I switch between UTF8 and ISO-8859 to see which character set the page is using.  I believe PHP 5.3 uses ISO-8859 by default though newer versions have switched to UTF-8.
1
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:bruno_boccara
ID: 41721775
Hi Ray, you're always the first to help me when eveything goes wrong...(:-) many thanks.

in fact I identified since the beginning that the problem is an encoding issue.

I can't change the whole soft right now from 8859 to utf-8, this is an ERP and this step cannot be done "by the way".

I'm getting crazy with this problem because we made everything to avoid issues, and the migration of the test server from SQL 32 bits to 64 bits was Ok .

this ERP is working for more than 10 years, and the last migration from sql 32 to 64 was a very minor change.  Don't you have any direction to search the issue ? I checked almost everything between the 2 servers and I don't see any difference to justify the problem.....
0
 

Author Comment

by:bruno_boccara
ID: 41721812
By the way, I listened to you and tried to change some pages to utf-8  (in the test server)

know I have the same problem with strange caracters.

even if for  the connexion I use $iConnect->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_UTF8);  instead of PDO::SQLSRV_ENCODING_SYSTEM

do no work ....

I'd really like to move to utf-8 but it seems that it's more simple to say than to do .....
0
 
LVL 83

Expert Comment

by:David Johnson, CD, MVP
ID: 41721942
as a test use mssql management studio and see if it is saved to the DB correctly. Which collation are you using for mssql ? When you installed MSSQL you will have to change the collation from the default.
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 41721970
...ERP is working for more than 10 years.
That perfectly encapsulates the problem!  Anything written 10 years ago is almost certainly obsolete today - change happens so fast, especially with character encoding.  If we're fortunate UTF-8 will be with us for a bit longer than ISO-8859-1 was.  Check this chart to see how things have changed in recent years.
https://commons.wikimedia.org/wiki/File:UnicodeGrow2b.png

It's not "easy" but all it really requires is consistency across all parts of the platform.  See if this presentation deck might be helpful.
http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
0
 

Author Closing Comment

by:bruno_boccara
ID: 41723055
I updated my ERP to utf-8.

 I also setup Apache to utf-8

everything is working fine.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41723098
Wonderful!  I'm hoping that UTF-8 answers a multitude of encoding problems.  It's so elegantly simple that there is no longer any excuse to go back to the old ways.

Cheers, ~Ray
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question