Encoding is not proper when query from PHP script
Moderator: NorbertKrupa
-
- Newbie
- Posts: 8
- Joined: Wed Jul 24, 2013 11:25 am
Encoding is not proper when query from PHP script
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?
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?
-
- Newbie
- Posts: 8
- Joined: Wed Jul 24, 2013 11:25 am
Re: Encoding is not proper when query from PHP script
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
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
Re: Encoding is not proper when query from PHP script
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:
Output:
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);
?>
Code: Select all
daniel@synapse:~$ php pdo.php
Connected with DSN: VerticaSuccess: SELECT * FROM fooArray
(
[v] => привет
)
- Attachments
-
- vf.png (16.24 KiB) Viewed 18619 times
-
- Newbie
- Posts: 8
- Joined: Wed Jul 24, 2013 11:25 am
Re: Encoding is not proper when query from PHP script
All data encoded to UTF8 before insert.
My config is
DriverManagerEncoding = UTF-16 but I try and UTF-8 - same result \u001A\u001A\u001A\u001A
sKwa
Can I see your config?
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
sKwa
Can I see your config?
Re: Encoding is not proper when query from PHP script
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 ...
What is your locale?
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
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=
-
- Newbie
- Posts: 8
- Joined: Wed Jul 24, 2013 11:25 am
Re: Encoding is not proper when query from PHP script
Yes, I use 32 bit architecture.Or it is issue with 32 bit architecture. Are your system is 32 bit architecture?
The library located in this path on my systemWhy this: /usr/lib/i386-linux-gnu/libodbcinst.so ?
en_US.UTF-8What is your locale?