php 5.6 - connecting at oracle DB using pdo and specify charset/encoding

Question

I'm not sure what i could be missing, but currently with this working code I'm able to connect, query and return the results from the db table properly.

But how could show the results from the table with the correct encoding? since special chars like ç, á are being displayed sometimes like question mark or a square box.

Bellow, all the information that i have so far, but I believe definition must be set on the PDO because if i simply do an echo "ç -á"; on the page, the chars will be displayed properly.

Oracle 11 Table charset:

Query: SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

Result: WE8MSWIN1252

php.ini file encoding settings:

default_charset = "UTF-8"
internal_encoding = "UTF-8"
input_encoding = "UTF-8"
output_encoding = "UTF-8"

Code example:

#
# PDO Connect example
#
$tns           = "
(DESCRIPTION=
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA=
        (SID=the_database)
    )
)
";
$username = "x";
$password = "x";
try {
    $conn = new PDO("oci:dbname=" . $tns, $username, $password );
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

#
# Fetch results and build html table
#
$sql = "select * table";

$result = $conn->query($sql);
echo '<table>';
echo '<thead>';
echo '<tr>';
echo '<th>Info</th>';
echo '</tr>';
echo '</thead>';
echo '<tbody>';
foreach ($conn->query($sql) as $row) {
    echo '<tr>';
    echo '<td >' . $row['INFO'] . '</td>';
    echo '</tr>';
}
echo '</tbody>';
echo '</table>';

Looks like your table charset is Windows 1252 (basically Latin 8859-1); you need your tables to use UTF8 as well. — CD001, Oct 04 '16 at 09:38
@CD001 I believe i need to change the php side, since changing the charset of the table isn't allowed. So, i'll try to update the php side to latin to see if it will works. I'm not sure if this is the right direction, could you please elaborate so i can understand better? =) — thclpr, Oct 04 '16 at 09:42
That should work - you pretty much want to follow the advice here : http://stackoverflow.com/questions/279170/utf-8-all-the-way-through/279279 ... but switch everything to `ISO-8859-1` (Windows 1252 is a subset of that so it should be fine). — CD001, Oct 04 '16 at 09:48
@KaKa Sadly I don't have the environment to test this anymore, but the good news is, I had the approval to migrate the whole solution to Django. so the previous solution was discontinued. — thclpr, Aug 08 '17 at 13:59

score 0 · Answer 1 · answered Jun 09 '17 at 19:02

Adding :charset=utf8 to the end of your DSN string should result in:

the database session being created with UTF-8 as the session encoding
the data is stored as Windows-1252, based on that NLS WE8MSWIN1252 setting
Oracle will try to convert the raw Windows-1252 value in the column into an equivalent UTF-8 string as requested by your database session's UTF-8 setting
if and only if that raw Windows-1252 raw byte value correctly matches a valid UTF-8 character (which I'm pretty sure all 1252 chars have valid UTF-8 matches), then the value returned should be visible to you as the UTF-8 character

Since you said that a raw echo of those special characters does appear correctly in your browser, then most likely your "viewer" settings (browser) are such that echoing the returned UTF-8 characters should succeed.

php 5.6 - connecting at oracle DB using pdo and specify charset/encoding

1 Answers1