Querying SQL Anywhere using RODBC

[Former Member]

I am using the RODBC library and the following query has no problem:

sqlQuery(channel,"Select Surname, City, Country from Contacts", max=5)

 Surname         City Country

1 Hildebrand Kanata USA 2 Simmon Kitchener USA 3 Critch Yale USA 4 Lambert Phillipsburg USA 5 Sullivan Uxbridge USA

However the modified query has error (runs ok in iSQL)

> sqlQuery(channel,"Select Surname, City, Country from Contacts where state='CA'", max=5)

Error in .Call(C_RODBCFetchRows, attr(channel, "handle_ptr"), max, buffsize, : negative length vectors are not allowed

I am trying to see if there is a way to fix it.

[Vlad]

I don't know the answer, but I can Google: http://stackoverflow.com/questions/3407015/querying-oracle-db-from-revolution-r-using-rodbc Since you haven't said that you already tried this option and/or your OS is not Linux, I would suggest you to try: believeNRows=FALSE.

update #1: This is what I found in the RODBC.pdf: believeNRows - logical. Is the number of rows returned by the ODBC connection believable? Not true for some Oracle and Sybase drivers, apparently, nor for Actual Technologies’ SQLite driver for Mac OS X.

[Vlad]

By the way, I forgot to add: if the driver executes the query successfully, you should be able to see this on the Sql Anywhere side, if you trace the DB calls. At least this will help us to understand whether the DB server processes the query, or not.

[Former Member]

I am sorry. This was on a Windows 7 (x64) SP1 on a Toshiba Laptop. By the way a similar query posed to SQL Server 2012 had no problem. The odbcQuery(channel, sqlqry) also returned the same error with SQL Anywhere 16.

[VolkerBarth]

So did you try Vlad's suggestion?

[Vlad]

Probably not. To be honest, I do not want to learn R just to reproduce the issue and test it 🙂

[VolkerBarth]

@Vlad: Sorry, my comment had addressed @mysorian, not you:) - I think it's worthwhile for the person who asked the question to respond whether a suggestion (here from you) has helped or not.

Volker aka The Forum Nanny

[Former Member]

Thanks vlad (late but never too late) for the suggestion. This finally works at least for SQL Anywhere 17 on Windows 10. http://hodentekmsss.blogspot.com/2016/09/trick-to-querying-sap-sql-anywhere.html