Saturday, October 10, 2015

Querying a SQL Server Database from 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 SQL Server (herein 2012) or access to it on/from your computer. Further it is assumed you have created a ODBC (Data Source Name) DSN. 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 ]--------------------------------------------------------

I already have a ODBC datasource (DSN) created for a SQL Server 2012 database called Feb12_15 (review next image).
In ODBC Database Manager(x64) you can trace back and see how it was constructed. The following images describe how it was constructed.



Find the DSN in ODBC Database Manager as shown:



1.png

Double click this DSN in the ODBC Data Source Adminsitrator window
SQL Server details gets dispalyed as shown.


2.png
Click Next.
The authentication used is shown (Windows Authentication)

3.png
Click Next.
Default database and related configuration information is shown.


4.png
Click Next.
Some defaults used during DSN creation are shown.


5.png
Click Next.
Details of ODBC Datasource is displayed.

6.png
Click  Test and test result is displayed.


7.png

Click OK and cancel all the open windows.
Launch the R-GUI from the short cut.
Connecting to ODBC Datasource
---------------------------
> library(RODBC)
> channel <-odbcConnect("Feb12-15")
Warning messages:
1: In odbcDriverConnect("DSN=Feb12-15") :
  [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 
2: In odbcDriverConnect("DSN=Feb12-15") : ODBC connection failed
    --an error due to wrong DSN file name corrected in the next line.
> channel <-odbcConnect("Feb12_15")  -- DSN file name is correct. If there are no errors a R prompt is returned
>
--------------------------------
Fetching a named table:
> sqlFetch(channel, "Customers")
   CustomerID                          CompanyName             ContactName
1       ALFKI                  Alfreds Futterkiste            Maria Anders
2       ANATR   Ana Trujillo Emparedados y helados            Ana Trujillo
3       ANTON              Antonio Moreno Taquería          Antonio Moreno
4       AROUT                      Around the Horn            Thomas Hardy
5       BERGS                   Berglunds snabbköp      Christina Berglund
6       BLAUS              Blauer See Delikatessen              Hanna Moos
7       BLONP             Blondesddsl père et fils      Frédérique Citeaux
8       BOLID            Bólido Comidas preparadas           Martín Sommer
(note all response wasnot copied here as there are too many columns to fit)
------------------------------------
Finding primary Keys, running SQL Queries
The following query is used as an example:


8.png
-----------
Finding Primary Keys
> sqlPrimaryKeys(channel, "Order Details")
  TABLE_CAT TABLE_SCHEM    TABLE_NAME COLUMN_NAME KEY_SEQ          PK_NAME
1  NORTHWND         dbo Order Details     OrderID       1 PK_Order_Details
2  NORTHWND         dbo Order Details   ProductID       2 PK_Order_Details
Submitting a query
> sqlQuery(channel, "Select FirstName, LastName, City from Employees")
  FirstName  LastName     City
1     Nancy   Davolio  Seattle
2    Andrew    Fuller   Tacoma
3     Janet Leverling Kirkland
4  Margaret   Peacock  Redmond
5    Steven  Buchanan   London
6   Michael    Suyama   London
7    Robert      King   London
8     Laura  Callahan  Seattle
9      Anne Dodsworth   London
Submitting a query with a WHERE clause
> sqlquery(channel, "Select FirstName, LastName from Employees where City='Seattle'")
Error: could not find function "sqlquery"
> sqlQuery(channel, "Select FirstName, LastName from Employees where City='Seattle'")
  FirstName LastName
1     Nancy  Davolio
2     Laura Callahan
>
--------------------
This a graphic from the R-GUI(64-bit)


9.png
Read this important document.
https://cran.r-project.org/web/packages/RODBC/RODBC.pdf