Encoding is not proper when query from PHP script

[SiliconCraftsman]

Hi all!

I have some problem with connection to my Vertica.
I use PHP and PDO ODBC driver (thrue unixODBC).
When I connect to Vertica via isql and select records from table all data are fine encoded - fields with UTF-8 (non ASCII symbols, for example cyrillic) correctly prints.
If connect from PHP script with PDO and execute same query that fields has some unicode characters like this \u001A\u001A\u001A\u001A
Each \u001A represent a one non ASCII symbol and can't be printed.
How to retrieve normal UTF-8 field with non ASCII symbols from PHP script?

[id10t]

HI!

Please read this - PHP Unicode Support

[SiliconCraftsman]

I read it but don't understand how get proper encoded data
Will I must encode data by PHP with utf8_encode() before insert it to Vertica or use it after select?
Using it after select did not take effect because field data contents \u001A\u001A\u001A\u001A

[id10t]

Hi!

encode - means encode to UTF-8 (must apply on data before INSERT)
decode - means decode from UTF-8 (must apply on data after SELECT)
---
http://php.net/manual/en/function.utf8-encode.php

PS:
If your data is saved as UTF-8 so it is already encoded in UTF-8. No further action is necessary.
Probably your environment isn't configured properly.
Simple script:

Code: Select all

<?php
# Turn on error reporting
error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);


# A simple function to trap errors from queries
function errortrap_odbc($conn, $sql) {
    if(!$rs = odbc_exec($conn,$sql)) {
        echo "Failed to execute SQL: $sql" . odbc_errormsg($conn);
    } else {
        echo "Success: " . $sql;
    }
    return $rs;
}


# Connect to the Database
$dsn = "Vertica";
$conn = odbc_connect($dsn,'','') or die ("CONNECTION ERROR");
echo "Connected with DSN: $dsn";


# Get the data from the table and display it
$sql = "SELECT * FROM foo";
if($result = errortrap_odbc($conn, $sql)) {
    while($row = odbc_fetch_array($result) ) {
    		print_r($row);
    }
}

# Close the ODBC connection
odbc_close($conn);
?>

Output:

Code: Select all

daniel@synapse:~$ php pdo.php 
Connected with DSN: VerticaSuccess: SELECT * FROM fooArray
(
    [v] => привет
)

[SiliconCraftsman]

All data encoded to UTF8 before insert.
My config is

Code: Select all

[Driver]

ODBCInstLib = /usr/lib/i386-linux-gnu/libodbcinst.so
ErrorMessagesPath = /opt/vertica/lib
DriverManagerEncoding = UTF-16
LogPath = /tmp
LogLevel = 0

DriverManagerEncoding = UTF-16 but I try and UTF-8 - same result \u001A\u001A\u001A\u001A

sKwa
Can I see your config?

[id10t]

Hi!

I'm afraid it's a PHP issue : https://community.vertica.com/vertica/t ... r_position
Or it is issue with 32 bit architecture. Are your system is 32 bit architecture?
Why this: /usr/lib/i386-linux-gnu/libodbcinst.so ?

It's doesn't belong to driver configuration, but ...

Code: Select all

daniel@synapse:~$ cat /etc/odbc.ini | grep -A 10 '\[Vertica\]'
[Vertica]
Description = Vertica Database
Driver = Vertica
Database = sampler
Servername = localhost
UID = daniel
PWD = 
Port = 5433
ConnSettings =

Code: Select all

daniel@synapse:~$ cat /opt/vertica/config/vertica.ini
[Driver]
DriverManagerEncoding=UTF-16
ODBCInstLib=/usr/lib/x86_64-linux-gnu/libodbcinst.so
ErrorMessagesPath=/opt/vertica/lib64
LogLevel=4
LogPath=/tmp

Code: Select all

daniel@synapse:~$ cat /etc/odbcinst.ini | tail -4

[Vertica]
Description = Vertica ODBC Driver
Driver = /opt/vertica/lib64/libverticaodbc.so

What is your locale?

Code: Select all

daniel@synapse:~$ locale
LANG=en_US.UTF-8
LANGUAGE=en_US:en
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

[SiliconCraftsman]

Or it is issue with 32 bit architecture. Are your system is 32 bit architecture?

Yes, I use 32 bit architecture.

Why this: /usr/lib/i386-linux-gnu/libodbcinst.so ?

The library located in this path on my system

What is your locale?

en_US.UTF-8