Asked  7 Months ago    Answers:  5   Viewed   56 times

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.

 Answers

51

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>
Wednesday, March 31, 2021
 
KHM
answered 7 Months ago
KHM
12

Add utf8 in your PDO connection as

 $this->objDB = new PDO("mysql:host=".$this->strHost.";port=3306;dbname=".$this->strDB, $this->strUser, $this->strPass,
 array( PDO::ATTR_PERSISTENT => true,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));

Read http://php.net/manual/en/pdo.construct.php

Saturday, May 29, 2021
 
rorymorris
answered 5 Months ago
75
Driver={Microsoft Access Driver(*.mdb, *.accdb)}

is not a valid ODBC driver name because it is missing a space. The correct name for the newer "ACE" ODBC driver is

Driver={Microsoft Access Driver (*.mdb, *.accdb)}

However, in this case PHP is running in the 32-bit environment and trying to open an .mdb database so the older "Jet" ODBC driver ...

Driver={Microsoft Access Driver (*.mdb)}

... will work, too.

Saturday, May 29, 2021
 
cegfault
answered 5 Months ago
95

This is really annoying problem to fix but you can try these.

First of all, make sure the file is actually saved in UTF-8 format.

Then check that you have <meta http-equiv="Content-Type" content="text/html;charset=UTF-8"> in your HTML header.

You can also try calling header('Content-Type: text/html; charset=utf-8'); at the beginning of your PHP script or adding AddDefaultCharset UTF-8 to your .htaccess file.

Saturday, July 3, 2021
 
Sufi
answered 4 Months ago
64

After lots and lots of reading, I discovered I was using the wrong version of the Microsoft Access Database Engine: The 64-bit version doesn't have the 32-bit driver for *.accdb format.

Thanks again for your enlightening support.

Saturday, August 7, 2021
 
jab
answered 3 Months ago
jab
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :