> […] but the call to odbc_fetch_array() only returns the first 2
> rows and returns FALSE for the 3rd row.

Doesn't it also raise a warning?

Anyhow, I cannot reproduce this (I haven't a SAP HANA DB
available, though), so please provide an ODBC trace so we can see
what's actually happening.  If possible, put it somewhere on the
Web (e.g. gist.github.com, pastebin.com) and only post a link to
the trace here.

> Doesn't it also raise a warning?
How do I detect that? There is no ODBC function to retrieve warnings.

The trace log can be downloaded from http://www.tonymarston.net/odbc.log

I have also tried running the same test on my SQL Server database, but while it does return all 9 rows the currency_symbol column for 'THB' and 'YUAN' are being returned as '?' instead of the expected UTF-8 symbol.

>> Doesn't it also raise a warning?
> How do I detect that? There is no ODBC function to retrieve
> warnings.

I was referring to the general PHP error reporting mechanisms.

Anyway, the ODBC trace shows whats going on:

| General error;-10427 Conversion of parameter/column (3) from
| data type NVARCHAR to ASCII failed (-10427)

Conversion to ASCII can't succeed, but PHP does not enforce that,
but rather requests binding to SQL_C_CHAR.  Is there a respective
setting to convert to UTF-8 in the driver options?

With SQL Server (ODBC Driver 17 for SQL Server) all currency
symbols are properly retrieved and displayed for me.  Watch out
for font issues (call bin2hex() to see the byte values).

No notice or warning is generated on the call to odbc_fetch_array().

I have CHAR_AS_UTF8=TRUE in the additional connection properties in the ODBC Data Source Administrator.

When I access my SQL Server database via ODBC the currency symbol returned for THB appears as '?' in the PHP array and also when I display it in the HTML output. However, when I copy it to my text editor it is shown as '?' in ASCII and 'EFBBBF3F' in hexadecimal. Why does it show up as '?'

> […] and 'EFBBBF3F' in hexadecimal. Why does it show up as '?'

Because that bytes are an UTF-8 BOM followed by a question mark.
Displaying this as ? is correct, but I wonder where that BOM comes
from.

> I have CHAR_AS_UTF8=TRUE in the additional connection properties
> in the ODBC Data Source Administrator.

Did you try with CHAR_AS_UTF8=FALSE?

Also, I suggest that you check whether that would work with
PDO_ODBC.  I assume that you'd get the same results as with ODBC by
default, but setting the PDO::ODBC_ATTR_ASSUME_UTF8 attribute may
yield the desired results.

> 'EFBBBF3F' in hexadecimal is a UTF-8 BOM followed by a question mark.

Byte Order Marks should *NEVER* be added to database columns, only files.

> Did you try with CHAR_AS_UTF8=FALSE?

Yes, but the results were the same.

> would this work with PDO_ODBC?

Accessing the SAP HANA database had the same result - it stopped fetching rows after the 2nd row.

Access the SQL Server database had a different result - the symbol for 'THB' was returned as '?' on its own without the UTF-8 BOM.

> Byte Order Marks should *NEVER* be added to database columns, […]

I suggest you tell that whoever is responsible for inserting the
BOM.  It certainly is not PHP.  It *might* be the ODBC driver.

Anyhow, if PDO_ODBC with PDO::ODBC_ATTR_ASSUME_UTF8 yields the
same broken results, I don't think this is a PHP issue.  It might
be an issue with the driver, or some configuration issue.  I
wouldn't know where to look further.  Maybe some SAP HANA ODBC
driver support channel can be more helpful.

I have tried using the PDO ODBC driver both with and without the PDO::ODBC_ATTR_ASSUME_UTF8 option with mixed results. I have also run tests by accessing the same data in a SQL Server database via the PDO ODBC driver as a comparison.

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.

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

If it is the driver, there is not we can do about that, can't we?

I do not know which driver is fault, so I have also reported this bug to the SAP HANA community at https://answers.sap.com/questions/13322291/odbc-fetch-array-will-not-fetch-a-row-containing-a.html, but as they have not tried accessing their database via PHP running on Windows they are unable to reproduce this fault and there remain clueless.

I do not have the resources to try the same code using PHP on Linux, so I don't know if that ODBC driver is any better. All I can say at the moment is that trying to access a SAP HANA database from PHP is not a viable proposition.

A developer at SAP has been investigating this problem and has discovered that the PHP odbc module is not formatting the UCS-2 text correctly on Windows for the HANA ODBC driver. You can read his full response at https://answers.sap.com/questions/13231775/hana-utf8-characters-being-garbled-when-using-the.html?childToView=13407210

Hello, I had the same issue have you found a workaround solution ?
Thanks

No, I have not found a workaround for this problem. This means that I cannot use SAP HANA with my multi-currency ERP application.

> A developer at SAP has been investigating this problem and has
> discovered that the PHP odbc module is not formatting the UCS-2
> text correctly on Windows for the HANA ODBC driver.

Ah, I see.  The basic problem is that PHP has no notion of the
charset encoding of strings.  Thus, it treats all strings as byte
sequences.  Therefore, we can't do any automatic conversion to
UCS-2 and use SQLExecDirectW().  Well, actually we could, at least
on Windows, where PHP 7.1.0 introduced UTF-8 support for file
paths, and the respective framework (sapi_windows_cp_get|set).
However, automatically doing the conversion would almost certainly
break existing code for many other ODBC drivers (which do not
automatically convert to UCS-2), so we would need to make that
optional, and an INI setting might not be a good solution.  Also,
what to do for non Windows systems is unclear.

Anyhow, this would be more like a new feature, and as such can't
target any of the stable branches (PHP 7.4, PHP 8.0).

The issue you are facing with the missing rows in the result set might be related to the encoding used for the currency symbols in the SAP HANA database and how it is handled in your PHP program. SAP HANA uses Unicode for character data, and it's important to ensure that the encoding is correctly handled in your PHP environment.

To resolve the problem, try the following:

Ensure that your PHP environment is properly configured to handle Unicode characters. Check that your PHP installation has the necessary extensions (e.g., mbstring) and is set to use UTF-8 encoding.
(https://github.com)(https://www.ballsportsgames.com/)
Update your connection settings to specify UTF-8 encoding for the ODBC connection:

$conn = odbc_connect("Driver=$driver;ServerNode=$server;charset=UTF-8",$user,$pswd) OR die('Connection to DB via ODBC failed');

Check the data stored in the SAP HANA database table to ensure that the currency symbols are stored as valid Unicode characters.

Update your SAP HANA database connection settings if necessary to ensure that it handles Unicode data correctly.

Consider using the odbc_fetch_row function instead of odbc_fetch_array to fetch rows one by one and then manually convert the currency symbol to its UTF-8 representation in PHP if needed.

By following these steps, you should be able to retrieve the currency symbols correctly from the SAP HANA database and display them in your PHP program.

If you look at my initial report you will see that I have set all columns to NVARCHAR, so they should be able to hold UTF8 values.

My PHP environment can handle UTF8 characters as I have exactly the same table and data in a MySQ:L, PostgreSQL, Oracle and SL Server database, and they work as expected.

When I view the contents of that table using the Eclipse IDE all the currency symbols are shown in the correct UTF8 characters.

I have CHAR_AS_UTF8=TRUE in my ODBC settings, and I am not aware of any other settings to enable UTF8 characters.

I never get the change to obtain a record and manually convert the characters to UTF8 as HANA simply will not return a row containing UTF8 characters.

None of your suggestions address my problem.