2

I am trying to fetch our Arabic values from JDE Database using the following connection string:

$dsn = "Driver={SQL Server};Server=10.10.10.27;Database=JDE;charset=utf8";
$username = "username"; 
$password = "password";
$string = "odbc:".$dsn.";Uid=".$username.";Pwd=".$password.";";
$con = new PDO($string);

As you can see I have the charset=utf8 specified.

I also have my HTML meta present:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

I also have used arabic characters fetched from MySQL before and it is displayed correctly and I can see Arabic characters, but from JDE database I get the following:

name: ??? ???? ???? ??? ??? ??? ???? ???? ??? ???

I tried adding the following in my php code trying different output:

echo iconv('windows-1256', 'utf-8', $DataFromJDE); 
echo utf8_decode($DataFromJDE);
echo utf8_encode($DataFromJDE);

But all failed.

Is there a configuration I need to do on the server?

I am using Apache with PHP 7 on a Windows server.

The JDE is in a separate server.

What am I missing? Is it from PHP or JDE?

Sadeem M.K.
  • 61
  • 9
  • Is your PHP script saved in utf-8? – Honk der Hase Feb 28 '21 at 11:24
  • yes I made sure to copy and paste it in notepad and select UTF8 encoding with a different file name just to make sure it is working correctly and not caching. – Sadeem M.K. Feb 28 '21 at 11:25
  • Ok. Can you type some arabic characters in the script (directly) and echo them - do they appear properly (both, in the script in the output)? – Honk der Hase Feb 28 '21 at 11:30
  • Yes. The Arabic is displayed correctly when typed directly as an echo and in an html tag: This one the data from JDE: name: ??? ???? ???? ??? ??? ??? ???? ???? ??? ??? This is a direct echo **ما هذا؟** – Sadeem M.K. Feb 28 '21 at 11:33
  • thats not right – Zapps Ceo Feb 28 '21 at 11:39
  • Ok. I assume that the transport encoding is incorrect. This is the encoding which is used, when data is transferred from the database to the client. Check out this SO thread https://stackoverflow.com/questions/4475548/pdo-mysql-and-broken-utf-8-encoding – Honk der Hase Feb 28 '21 at 11:39
  • @ZappsCeo Nope, same – Sadeem M.K. Feb 28 '21 at 11:44
  • @LarsStegelitz I'm sorry which answer should I be looking at? because I came to this thread before It doesn't work for me. As I am using similar connection and using PDO::MYSQL_ATTR_INIT_COMMAND causes an error as I am not using a MYSQL connection I assume. – Sadeem M.K. Feb 28 '21 at 11:49
  • What collation does the MSSQL database / table have? – Honk der Hase Feb 28 '21 at 11:58
  • Latin1_General_CI_AS_WS – Sadeem M.K. Feb 28 '21 at 12:00
  • Well, that would explain it.. you'd have to use an arabic collation like Arabic_CI_AI_KS_WS. Latin1 is for central europe languages – Honk der Hase Feb 28 '21 at 12:05
  • we have both arabic and english .. can not change the collation – Sadeem M.K. Feb 28 '21 at 12:07
  • Then you'd have to choose a collation that supports both. But with Latin1 you can not store arabic hyphens, it just does not support it... – Honk der Hase Feb 28 '21 at 12:08
  • the problem is it is working fine from the database side.. if we select based on their thin client system arabic shows and everything is peachy – Sadeem M.K. Feb 28 '21 at 12:13

2 Answers2

0

Try something like:

echo $DataFromJDE . '<br>';

Note that you don't need to use utf8_decode(...) or anything similar, browsers support UTF-8 as is, without any need for reformatting.

Top-Master
  • 7,611
  • 5
  • 39
  • 71
  • That is the first echo-ing I did :) I added those lines when I was trying to figure out what the problem was – Sadeem M.K. Feb 28 '21 at 11:29
  • Hmm, then your database table is not `Unicode`, recreate the table (any previous `UTF-8` stored data is lost). – Top-Master Feb 28 '21 at 11:32
  • I am pretty sure (cause in `PHP` it is pretty hard to disable Unicode, but it is the default for databases). – Top-Master Feb 28 '21 at 11:33
  • I can not recreate the table, this is a database that has been used for years .. and i don't have access to. is there a work around from my side? – Sadeem M.K. Feb 28 '21 at 11:36
  • I would like to add that if we run reports in JDE the Arabic is displayed correctly. – Sadeem M.K. Feb 28 '21 at 11:37
  • Maybe first test my assumption, that about incorrect database, what is the output of `echo bin2hex($DataFromJDE);` ? – Top-Master Feb 28 '21 at 11:41
  • I get the following : 3f3f3f203f3f3f3f203f3f3f3f203f3f3f203f3f3f20202020202020202020202020202020202020 – Sadeem M.K. Feb 28 '21 at 11:46
  • The `3f` is the code for `?` and `20` is that of space, so, my assumption was correct. Now, if you can not enable Unicode on the database, there is still hope, but only if you are the one inserting Data into the Database. For example, you can use `base64` encoding when inserting (and later decode `base64` to get `UTF-8`). – Top-Master Feb 28 '21 at 11:50
  • hmm I am only trying to select those values, not insert them ... .. – Sadeem M.K. Feb 28 '21 at 11:53
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/229313/discussion-between-top-master-and-sadeem-m-k). – Top-Master Feb 28 '21 at 11:54
0

YES! Finally Cracked the code! Thank you So much for the amazing explanations and clarifications. your answers helped a lot.

So basically what I had to do is.

  1. Change my query to the following

    $con->prepare('SELECT CAST( CAST(JDEColumn AS VARBINARY(MAX)) AS varchar(120)) AS JDEColumn FROM TABLE where COLUMN = :SELECTION');

courtesy of this thread here https://stackoverflow.com/a/29975739/11835221

  1. Using iconv() as follows:

    iconv('UTF-16LE', 'utf-8', $DataFromJDE);

got the clue from this answer by reverse engineering a bit https://stackoverflow.com/a/51130953/11835221

AND Voila! I got my Arabic data!

Hoping this helps another hair pulling soul some day.

Cheers!

Sadeem M.K.
  • 61
  • 9