odbc_fetch_array() will not fetch a row containing a UTF-8 currency code symbol

[tony_marston25]

I have a table in a SAP HANA database which contains currency codes and their symbols, but when I try to read them in my PHP program via ODBC the rows containing symbols are not returned in the result set.

Test script:
---------------
CREATE TABLE currency (
currency_code nvarchar(16) NOT NULL,
currency_name nvarchar(255) NOT NULL,
currency_symbol nvarchar(4) DEFAULT NULL,
PRIMARY KEY (currency_code)
);

INSERT INTO currency (currency_code, currency_name, currency_symbol) VALUES ('AUD', 'Australian Dollars', NULL);
INSERT INTO currency (currency_code, currency_name, currency_symbol) VALUES ('CAD', 'Canadian Dollars', NULL);
INSERT INTO currency (currency_code, currency_name, currency_symbol) VALUES ('EUR', 'Euros', '€');
INSERT INTO currency (currency_code, currency_name, currency_symbol) VALUES ('GBP', 'UK Pounds', '£');
INSERT INTO currency (currency_code, currency_name, currency_symbol) VALUES ('JPY', 'Japanese Yen', '¥');
INSERT INTO currency (currency_code, currency_name, currency_symbol) VALUES ('SGD', 'Singapore Dollar', '$');
INSERT INTO currency (currency_code, currency_name, currency_symbol) VALUES ('THB', 'Thai Baht', '฿');
INSERT INTO currency (currency_code, currency_name, currency_symbol) VALUES ('USD', 'US Dollars', '$');
INSERT INTO currency (currency_code, currency_name, currency_symbol) VALUES ('YUAN', 'Chinese Yuan Renminbi', '元');

<?php
$driver = 'HDBODBC';
$server = 'hxehost:39015';
$user = '****';
$pswd = '****';
$conn = odbc_connect("Driver=$driver;ServerNode=$server",$user,$pswd) OR die('Connection to DB via ODBC failed');
$query = "SELECT * FROM test.currency ORDER BY currency_code";
!$result = odbc_exec($conn, $query) OR die("ERRORNO=".odbc_error().", ERRORMSG=".odbc_errormsg());
$numrows = odbc_num_rows($result);
while ($row = odbc_fetch_array($result)) {
$array[] = array_change_key_case($row, CASE_LOWER);
echo "<p>id={$row['CURRENCY_CODE']}, name={$row['CURRENCY_NAME']}, symbol={$row['CURRENCY_SYMBOL']}</p>\n";
} // while
odbc_free_result($result);
?>

Expected result:
----------------
The call to odbc_num_rows() returns '9' which is correct, but the call to odbc_fetch_array() only returns the first 2 rows and returns FALSE for the 3rd row.

[tony_marston25]

I could not find that page very easily as it is buried deep in the documentation. I tried doing a search on 'UTF8' but got nothing. A poster in another newsgroup advised using the Windows ODBC Data Source Administrator to put 'CHAR_AS_UTF8=TRUE' in the connection properties, but this did not fix the issue.

Unless you can duplicate this issue by running PHP on Windows to test the ODBC driver I fear that this problem will not be fixed, in which case I will not be able to add SAP HANA to the list of databases on which my ERP application can run. That will be your loss, not mine.

[Vitaliy-R]

What if you try

$conn = odbc_connect("Driver=$driver;ServerNode=$server;char_as_utf8=true",$user,$pswd)...

in your code?

[tony_marston25]

This is not documented anywhere. I found a post on stackoverflow which said I should put CHAR_AS_UTF8=TRUE in the additional connection properties of the ODBC Data Source Administrator, but this didn't make a difference. I have tried your suggestion of putting it in the connection string and while it does make a difference in that it allows all records to be retrieved, but it is still screwing up certain UTF8 values.

I have reported this as a PHP bug, but I have been told that the PHP ODBC driver does not manipulate any values, it simply passes through whatever values it has been given. I set up a test with exactly the same data on a SQL Server database which I then accessed via ODBC, and that worked without any errors.

I was also asked to try using the PDO-ODBC driver, which I did, but this produced different results depending on whether the data was input via PHP or a JBDC client, and whether I had the PDO::ODBC_ATTR_ASSUME_UTF8 attribute set to TRUE or false.

I have captured the output from various tests which you can download from http://www.tonymarston.net/test-odbc-utf8.zip

TEST-1 uses the ODBC driver using data which was inserted using JDBC (the Eclipse plugin)

TEST-2 uses the ODBC driver using data which was inserted using PHP and the ODBC driver

TEST-3 uses the PDO-ODBC driver without the UTF8 attribute using data which was inserted using JDBC

TEST-4 uses the PDO-ODBC driver without the UTF8 attribute using data which was inserted using PHP

TEST-5 uses the PDO-ODBC driver with the UTF8 attribute using data which was inserted using JDBC

TEST-6 uses the PDO-ODBC driver with the UTF8 attribute using data which was inserted using PHP

These tests clearly show that the HANA ODBC driver mangles UTF8 values both when written and when read.

The test using the PDO-ODBC driver with the UTF8 attribute turned ON produced the worst results as it totally mangled all the non-UTF8 characters.

[Vitaliy-R]

Hi tony_marston25.

Unfortunately I am completely unfamiliar with PHP 🙂

You said "This is not documented anywhere.", but actually I just went to HANA Client's documentation for ODBC when I saw your question to check if there is something for such a case. There I found a parameter `char_as_utf8` which sounded like something to used in your case.

Regards.

Cc daniel.vanleeuwen