I have a 64-bit Windows 7 machine with HANA Client installed and an ODBC connection to an SAP HANA database. In the ODBC Data Source administrator, I have added a connection to the required database and can successfully connect.
I am trying to use RStudio to retrieve data for analysis using R. I am finding that queries that return a handful of rows ("TOP 1" to "TOP 17") successfully return all 71 columns of data for the requested number of rows, but when I query with "TOP 18" or higher number of rows, I get all column titles, but 0 rows returned.
So the query:
res<-sqlQuery(ch, 'SELECT TOP 17 * FROM "SAPL2P"."/BIC/PZRPA_CNO" WHERE "/BIC/ZRPA_DCD"=\'CONFIRMED\'')
results in 17 rows of data, but
res2<-sqlQuery(ch, 'SELECT TOP 18 * FROM "SAPL2P"."/BIC/PZRPA_CNO" WHERE "/BIC/ZRPA_DCD"=\'CONFIRMED\'')
has 0 rows of data.
Any ideas what could be causing data not to be returned for more than 17 rows?
Ok, the problem here really is how R on Windows handles UTF data from ODBC (as already has been described). A quick search around SO shows that this problem is pretty common for R on Windows with a lot of different DBMS.
For SAP HANA what worked for me was to add the following parameter to the ODBC DSN (in the ODBC driver settings -> Settings... -> Special property settings):
CHAR_AS_UTF8 | TRUE
This makes SAP HANA ODBC to handle SQL_C_CHAR as UTF8.