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.
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
> 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
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
No comments:
Post a Comment