Encoding is not proper when query from PHP script

Moderator: NorbertKrupa

SiliconCraftsman
Newbie
Newbie
Posts: 8
Joined: Wed Jul 24, 2013 11:25 am

Encoding is not proper when query from PHP script

Post by SiliconCraftsman » Wed Jul 24, 2013 12:48 pm

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
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Encoding is not proper when query from PHP script

Post by id10t » Wed Jul 24, 2013 6:49 pm

HI!

Please read this - PHP Unicode Support

SiliconCraftsman
Newbie
Newbie
Posts: 8
Joined: Wed Jul 24, 2013 11:25 am

Re: Encoding is not proper when query from PHP script

Post by SiliconCraftsman » Wed Jul 24, 2013 8:01 pm

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
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Encoding is not proper when query from PHP script

Post by id10t » Wed Jul 24, 2013 8:50 pm

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] => привет
)
Attachments
vf.png
vf.png (16.24 KiB) Viewed 6230 times

SiliconCraftsman
Newbie
Newbie
Posts: 8
Joined: Wed Jul 24, 2013 11:25 am

Re: Encoding is not proper when query from PHP script

Post by SiliconCraftsman » Thu Jul 25, 2013 7:09 am

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
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Encoding is not proper when query from PHP script

Post by id10t » Thu Jul 25, 2013 8:01 am

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
Newbie
Newbie
Posts: 8
Joined: Wed Jul 24, 2013 11:25 am

Re: Encoding is not proper when query from PHP script

Post by SiliconCraftsman » Thu Jul 25, 2013 9:44 am

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

Post Reply

Return to “Vertica Database Development”