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

Viewing tables on SQL Server 2016 Developer using R GUI

In this post we will use ODBC Connectivity to connect to SQL Server 2016. We create a new ODBC UserDSN or modify an existing ODBC DSN to connect to the 'pubs' database on SQL Server 2016 (the modification in the link changed the database from 'master' to 'Adventureworks2014').

You need to user the RODBC package to connect to SQL Server if you are using the R from CRAN.

From R GUI (x64bit) click Packages and choose to install packages.

rodbc_00

You may be directed to a list of mirror sites from where you can get the package.

rodbc_00_1
Click "OK" to download the package to your computer.

I had earlier chosen California site and the package has RODBC.

robdc_01

Click on it and click OK.
The package gets downloaded to local file system.

rodbc_02

Load the library for RODBC so that you can use it in the session.

rodbc_03

Connect to the ODBC User DSN "HSQL" you created earlier. Then take a look at all the tables (this is a long list and only few pieces are shown).

rodbc_04

You can also use sqlTables() and sqlFetch() functions with a table name (such as 'employee' in this case) to get relevant items as shown.

rodbc_05

In case you are using a DSN with a username and passwordd, you can connect to it as shown here:
         odbcConnect("dsn", uid="user", pwd="***")

You can close the conneciton with:
         odbcClose(ch)

You will not get a response to the above.

It is quite easy if you have the DSN and the RODBC library.