Monday, September 5, 2016

Trick to querying a SAP SQL Anywhere Server database with R

As previously posted you should have the RODBC package in your library. Once you have this it is very easy.

You also need to have an instance of SAP SQL Anywhere  or access to it on/from your computer. An installation of SAP SQL Anywhere 17 (latest developer version) also installs DSN's for accessing the sample database using ODBC.

 Since you will be querying from R, you have R installed on your computer.

Some relevant references to the above can be found here:
------------------------------------------------------
[Windows 10 ODBC : http://hodentekmsss.blogspot.com/2015/08/windows-10-odbc-data-base-adminsitrators.html
 SQL Server 2012 ODBC DSN: http://hodentekmsss.blogspot.com/2013/08/how-do-you-create-odbc-dsn-to-sql.html
SQL Server 2012 Installation: http://hodentekmsss.blogspot.com/2014/07/sql-server-2012-developer-edition-is.html 
Installing R: http://hodentekhelp.blogspot.com/2015/09/can-you-install-r-version-322.html ]--------------------------------------------------------
You bring the necessary libraries with these statements in R Gui (x64bit):
> library(RODBC)
> ch <-odbcConnect("SQL Anywhere 17 Demo")
SQL Anywhere 17 Demo is a demonstration sample database. After installing SQL Anywhere you can find it in the ODBC Database DataSources window.

After defining the channel as shown above, when you run the above you will be presented with this screen.
The database name is 'demo' and type over the password field with the password 'sql'. Click OK and you get a prompt in RGUI.

Now you can query the database as shown here Only 6 six out of 60 shown):
> sqlQuery(ch, "Select * from Contacts")

   ID    Surname          GivenName    Title          Street                                City               State
1   1     Hildebrand      Jane                ma             280 Washington St.          Kanata         CA
2   2     Simmon          Larry              sa               343 Granville St.              Kitchener     TX
3   3     Critch              Susan             pd              457 Center St.                   Yale             WY
4   4     Lambert          Terry              ad               20434 Page St.                 Phillipsburg  CO
5   5    Sullivan           Dorothy          cs               541 Minuteman Dr.          Uxbridge      ME
6   6      Paull              Rose               fi                718 Bay St.                       Huntsville     AZ

However, a SELECT query such as the one shown below spawns an error. 


> sqlQuery(ch,"Select Surname, City, Country from Contacts where state='CA'")
Error in .Call(C_RODBCFetchRows, attr(channel, "handle_ptr"), max, buffsize,  :
  negative length vectors are not allowed

In fact any WHERE clause produces this error.

The trick is to start the connection as shown here,

 >chs <-odbcConnect("SQL Anywhere 17 Demo", believeNRows=FALSE)

Now if you run the query you will not get an error.

> sqlQuery(chs, "Select Surname, City, Country from Contacts where state='CA'")
 
       Surname        City        Country
1     Hildebrand    Kanata     USA
2     Lyman           Kanata     USA
3     Davidson       Kanata     USA
4     Pettengill       Kanata     USA
5     Romeo           Sheffield  USA

I am indebted to an answer to this error at stack overflow site:
http://stackoverflow.com/questions/3407015/querying-oracle-db-from-revolution-r-using-rodbc

and

a question here:
http://sqlanywhere-forum.sap.com/questions/25826/querying-sql-anywhere-using-rodbc