Unable to retrieve UTF-8 accented characters from Access via PDO_ODBC

Question 1

I am trying to get an Access DB converted into MySQL. Everything works perfectly, expect for one big monkey wrench... If the access db has any non standard characters, it wont work. My query will tell me:

Incorrect string value: 'xE9d'

If I directly echo out the rows text that has the 'invalid' character I get a question mark in a black square in my browser (so é would turn into that invalid symbal on echo).

NOTE: That same from will accept, save and display the "é" fine in a textbox that is used to title this db upload. Also if I 'save as' the page and re-open it up the 'é' is displayed correctly....

Here is how I connect:

$conn = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=$fileLocation;SystemDB=$securefilePath;Uid=developer;Pwd=pass;charset=utf;");

I have tried numerous things, including:

$conn -> exec("set names utf8");

When I try a 'CurrentDb.CollatingOrder' in access it tells me 1033 apparently that is dbSortGeneral for "English, German, French, and Portuguese collating order".

What is wrong? It is almost like the PDO is sending me a collation my browser and PHP does not fully understand.

Question 2

The Problem

When using native PHP ODBC features (PDO_ODBC or the older odbc_ functions) and the Access ODBC driver, text is not UTF-8 encoded, even though it is stored in the Access database as Unicode characters. So, for a sample table named "Teams"

Team
-----------------------
Boston Bruins
Canadiens de Montréal
????????? ?????? ??????

the code

<?php
header('Content-Type: text/html; charset=utf-8');
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Access character test</title>
</head>
<body>
<?php
$connStr = 
        'odbc:' .
        'Driver={Microsoft Access Driver (*.mdb)};' .
        'Dbq=C:\Users\Public\__SO\28311687.mdb;' .
        'Uid=Admin;';
$db = new PDO($connStr);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "SELECT Team FROM Teams";
foreach ($db->query($sql) as $row) {
    $s = $row["Team"];
    echo $s . "<br/>n";
}
?>
</body>
</html>

displays this in the browser

Boston Bruins
Canadiens de Montr?al
????????? ?????? ??????

The Easy but Incomplete Fixes

The text returned by Access ODBC actually matches the Windows-1252 character encoding for the characters in that character set, so simply changing the line

$s = $row["Team"];

to

$s = utf8_encode($row["Team"]);

will allow the second entry to be displayed correctly

Boston Bruins
Canadiens de Montréal
????????? ?????? ??????

but the utf8_encode() function converts from ISO-8859-1, not Windows-1252, so some characters (notably the Euro symbol '€') will disappear. A better solution would be to use

$s = mb_convert_encoding($row["Team"], "UTF-8", "Windows-1252");

but that still wouldn't solve the problem with the third entry in our sample table.

The Complete Fix

For full UTF-8 support we need to use COM with ADODB Connection and Recordset objects like so

<?php
header('Content-Type: text/html; charset=utf-8');
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Access character test</title>
</head>
<body>
<?php
$connStr = 
        'Driver={Microsoft Access Driver (*.mdb)};' .
        'Dbq=C:\Users\Public\__SO\28311687.mdb';
$con = new COM("ADODB.Connection", NULL, CP_UTF8);  // specify UTF-8 code page
$con->Open($connStr);

$rst = new COM("ADODB.Recordset");
$sql = "SELECT Team FROM Teams";
$rst->Open($sql, $con, 3, 3);  // adOpenStatic, adLockOptimistic
while (!$rst->EOF) {
    $s = $rst->Fields("Team");
    echo $s . "<br/>n";
    $rst->MoveNext;
}
$rst->Close();
$con->Close();
?>
</body>
</html>

Question 3

For your second question:

You need to use fetchAll() instead of fetch(), which only gives you one row at a time.

In your code, try: $result = $stmt->fetchAll(PDO::FETCH_ASSOC); (though that will change what your foreach loop looks like).

Alternatively, you can use a while loop to fetch each row as you need it:

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    //Do something with $row
}

Question 4

This is an edit to my original answer:

After a days of hard struggles, I finally figured out a working solution for your Thread's subject (MDBTools driver not returning string values with PHP MS-Access)

In addition to my old answer which is very limited to the 127 field size for the Text datatype, Here is my new attempt for a solution.

Solution:

Instead of using the PDO Class in manipulating the access db, I recommend using ODBC Functions to do the job.

Example:

In your code block

$qry = "SELECT transactionId, aDate, aDateTime, EmpCode, EmpName, ControllerNum FROM Transactions"; 
$dbqryprep = $dbconn->prepare($qry);
$dbqryprep->execute();
$result = $dbqryprep->fetchall(PDO::FETCH_ASSOC);

Change it to

$connection = odbc_connect("YourDSN","admin","pswd"); 
$sql = "SELECT transactionId, aDate, aDateTime, EmpCode, EmpName, ControllerNum FROM Transactions";
$result = odbc_exec($connection,$sql);
while($row = odbc_fetch_array($result))
{   echo "<pre>";
    print_r($row);
    echo "</pre>";
}

Where "YourDSN" is a DSN(Data Source Name) that needs to be created in the odbc.ini file in your Ubuntu Server which can be found on the /etc folder. Type the DSN format below in your odbc.ini file.

The DSN is made in this format:

[MyDataSource]

Description = The Source of My Data

Driver = MyDriver

ServerName = localhost

Database = MyDatabase/Complete path of your DB FIle

Which in my sample code is:

[YourDSN]

Description = This is the configured DSN for your access db

Driver = MDBTools

ServerName = localhost

Database = /var/www/{your dns}/{public_html}/.../.../media/winshare/attEngine.mdb

^Note(1) The Database must be the complete directory starting from the root(eg. /var/www/...)

^Note(2) The Driver must be MDBTools

That's it! Just figure out the DSN configuration and you are good to go. You can now finally retrieve Text datatypes in access with its max field size. I hope this helps everyone. Feel free to reply or comment if you have some clarifications.

OLD ANSWER:

This is to answer only your 1st question and the subject of this thread: I think there is nothing wrong with the way you use the MDBTools in your code.

After hours of searching the net. I finally found this one thread that is exactly the same as the problem that I was having(MDBTools driver not returning string values with MSACCESS using PHP running in a linux os). Maybe this only exists on accessing MS ACCESS in PHP which being run in a LINUX os? I don't know.

Luckily for us who faces this very problem, I have seem to find a work around for this.

Instead of using the prepare and execute function of PDO, try to use the query one.

EXAMPLE

Change these lines of code:
$qry = "SELECT transactionId, aDate, aDateTime, EmpCode, EmpName, ControllerNum FROM Transactions;"; 
$dbqryprep = $dbconn->prepare($qry);
$dbqryprep->execute();
$result = $dbqryprep->fetchall(PDO::FETCH_ASSOC);
to this:
$qry = "SELECT transactionId, aDate, aDateTime, EmpCode, EmpName, ControllerNum FROM Transactions;"; 
$result = $dbconn->query($qry)->fetchAll(PDO::FETCH_ASSOC);
And then:

In your MSACCESS DB file(.mdb, .accdb), Just change the Field Size of the Text Data Type to 127 or less.

Remember that this work around only works if the values in your Text columns have max. characters of 127 only.

Thus, the Text Datatype must be limited to 127 characters inorder for MDBTools to retrieve the text in PHP.

I don't see this as a solution but rather than a bug discovered. I hope somebody notice this. It would help us a lot. Specially those who will encounter this in the future.

Question 5

Try this:

$str = preg_replace_callback('/\\u([0-9a-fA-F]{4})/', function ($match) {
    return mb_convert_encoding(pack('H*', $match[1]), 'UTF-8', 'UCS-2BE');
}, $str);

In case it's UTF-16 based C/C++/Java/Json-style:

$str = preg_replace_callback('/\\u([0-9a-fA-F]{4})/', function ($match) {
    return mb_convert_encoding(pack('H*', $match[1]), 'UTF-8', 'UTF-16BE');
}, $str);

Question 6

I agree with the previous answers that UTF-8 is a good choice for most applications.

Beware the traps that might be awaiting you, though! You'll want to be careful that you use a consistent character encoding throughout your system (input forms, output web pages, other front ends that might access or change the data).

I have spent some unpleasant hours trying to figure out why a simple β or é was mangled on my web page, only to find that something somewhere had goofed up an encoding. I've even seen cases of text that gets run through multiple encoders--once turning a single quotation mark into eight bytes.

Bottom line, don't assume the correct translation will be done; be explicit about character encoding throughout your project.

Edit: I see in your update you've already started to discover this particular joy. :)

Unable to retrieve UTF-8 accented characters from Access via PDO_ODBC

Answers

The Problem

The Easy but Incomplete Fixes

The Complete Fix

php,ms-access,pdo,utf-8,odbc