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 :
 SQL Server 2012 ODBC DSN:
SQL Server 2012 Installation: 
Installing R: ]--------------------------------------------------------

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:


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

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

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

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

Click Next.
Details of ODBC Datasource is displayed.

Click  Test and test result is displayed.


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:

Finding Primary Keys
> sqlPrimaryKeys(channel, "Order Details")
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)

Read this important document.

PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...