0

I read data from a Netezza Database with PHP using ODBC.

System: Red Hat Linux 7, Netezza ODBC drivers, unixODBC, httpd with several packages installed.

Database: IBM Netezza, data is stored as latin9 (the database configuration is given, can't be changed). Data:

NAME
----
Frank
Fränk

When I read data from the database in PHP, special characters are replaced with question marks (not these ? in diamonds, that would be ok). PHP Code:

$nz = odbc_connect('DRIVER={NetezzaSQL};SERVER=x.x.x.x;PORT=5480;DATABASE=database;','username','password');
$result = odbc_exec($nz, 'select * from CUSTOMERS');
while(odbc_fetch_row($result)) {
    echo odbc_result($result, 'NAME').'<br>'.PHP_EOL;
}

Result:

Frank
Fr?nk

What I tried so far:

  • When I use str_replace('?','X',...), I even get FrXnk. This means ä becomes a real question mark, further conversion/encoding of the string (e.g. utf8_encode() ) does not help.
  • Options in the DBS string: Client_CSet=latin9, Client_CSet=utf8, Server_CSet=latin9, Server_CSet=utf8, charset=latin9, charset=utf8, etc. do not help.
  • PDO instead of odbc_connect() produces the same result: Fr?nk
  • On a Windows system with XAMPP, the same code works. odbc_result() returns a latin9-encoded string, utf8_encode() returns Fränk.
  • I can also use R on the Linux system using the same ODBC connections. R reads Fränk correctly.

For me it's clear, the ODBC driver is working and returns a latin9-encoded string. PHP on the Linux system is doing some internal conversion that replaces characters with question marks. Any ideas what I could do?

PS: This is not a duplicate of "UTF-8 all the way through". First, I cannot change the database encoding. Second, specifying the charset in the connection string does not help. Third, it is a Netezza database and not MYSQL, I cannot use any of those MYSQL functions, Netezza is a bit weird when it comes to settings. However, I found ways to retrieve data correctly from the database, just PHP on Linux does not work.

Flo
  • 1
  • 2
  • Why are you storing in Latin-9. I would advise that you keep everything in UTF-8. – Dragonthoughts Jun 05 '18 at 09:36
  • I am not operating this Netezza database (which stores a lot of company-wide data, for a long time already). It is a given thing. I can only read from this data. – Flo Jun 05 '18 at 09:41
  • 1
    I found a solution: When the option FastSelect = true in the ODBC driver and the columns in the NETEZZA are VARCHAR (or casted to VARCHAR), PHP receives the charaters correctly (latin9-encoded), then the string can be converted using utf8_encode(). Other setting fail and produce question marks as special characters. In particular, it does not work for NVARCHAR columns in the Netezza. However, any general solution for NVARCHAR columns would be appreciated. Since R is able to receive Strings from NVARCHAR columns without the FastSelect-Option, PHP should be able to to this also. – Flo Jun 07 '18 at 07:33

0 Answers0