MSSQLSERVER 

When I am working on my personal projects, all the data I have can be easily saved as .csv or .txt files. Things are not complicated. However, when things are in scale, it is hard to not to talk about database. In my company, all the data are stored in SQL Server database. So, it is important for me to know how to connect R with SQL Server. And, also good to know how to interact with database in R by passing variables in R environment to database and then return the desired dataset. This is especially useful when I plan to do R Shiny application with database connected.

Details

  • Pull data from database
    For the sql_file.sql, try to avoid comments. Based on experience, it is easily to get errors because of that.

    #### Import Libraries ####
if (!require("RODBC")) install.packages("RODBC",repos = "http://cran.us.r-project.org")
library(RODBC)

#### Get the Product list ####
conn = odbcDriverConnect('driver={SQL Server};server=server-name;database=database-name;trusted_connection = true')
sqlcode = paste(readLines('path/to/the/sql_file.sql'), collapse='\n')
# or, 
# sqlcode = "SELECT TOP 10 * FROM table_name"

df = sqlQuery(conn, sqlcode) 

close(conn) # close the connection

  • Pass input in R to database 

    #### Import Libraries ####
if (!require("RODBC")) install.packages("RODBC",repos = "http://cran.us.r-project.org")
if (!require("RODBCext")) install.packages("RODBC",repos = "http://cran.us.r-project.org")

#### Get the Product list ####
conn = odbcDriverConnect('driver={SQL Server};server=server-name;database=database-name;trusted_connection = true')
sqlcode = paste(readLines('path/to/the/sql_file.sql'), collapse='\n')
# or, 
# sqlcode = "SELECT TOP (?) * FROM table_name "
df = sqlExecute(channel = conn, 
                      query = sqlcode,
                      data = data.frame(as.integer(num_prod_each)),
                      fetch = TRUE,
                      stringsAsFactors = FALSE)