Query SAP SQL Anywhere 17 using 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

SAP SQL Anywhere 17 on Windows 10

SAP SQL Anywhere 17 was announced in June 2015. It is a nice database and I always liked it, because it just works! I have written a number of articles on the earlier versions and of course I wanted to try this latest version.

This version comes with a lot of enhancements. I recommend this site where you can read lot more and focus on installing it on a Windows 10 machine.

Here is an excerpt from that site on developer related features of Version 17:

Developer Enhancements

• OData Enhancements - The SQL Anywhere database server can act as an OData server.  This functionality replaces the OData Server utility.  In addition, the The OData producer now supports a greater subset of the OData Service Definition Language (OSDL). 
• JavaScript external environment - SQL Anywhere now includes support for JavaScript stored procedures and functions.
• Node.JS and Python drivers - The SQL Anywhere Node.js driver allows users to connect and perform queries on the database using JavaScript on Joyent’s Node.js Software platform.   In addition, the three SQL Anywhere python drivers are now available through PyPI, the official Python package index.
• Consolidated db – MobiLink has support for SAP ( ASE, IQ, SQLA), Oracle, Microsoft, IBM, MySQL back-end databases.  We continue to add support for newer versions of these databases.  In v17, we added support for Oracle 12.1, ASE 16, SS 2014,MySQL 5.6.20, IBM DB2 10.5. 
• HANA Integration – with our SAP HANA Remote Data Sync product, MobiLink becomes more tightly integrated into the HANA platform infrastructure (assigned port numbers, lifecycle management, name server integration, license management, monitoring integration).
• SAP Passport support and NCSLib logging

You need to download the installation file from here after registering here:

https://go.sap.com/cmp/syb/crm-xu15-int-sqldevft/index.html

You can download both SAP SQL Anywhere 16 which requires a key that you get after registering or SAP SQL Anywhere 17 which does not require a key. The product you get is a developer edition good for developing on your machine and not for production.

If you do not know about SAP SQL Anywhere may be this is a good place to start:



It is available for Linux as well as Windows. I downloaded the Windows version (x64 bit).
As the ad says, it just Works.

After installation you should be seeing these on your Windows 10 computer.

There are a number of built-in tools that you can use.

Get dozens of tutorial articles on Sybase/SAP SQL Anywhere from here.

It has a nice management interface called Sybase Central shown here: