How to Connect R to SQL Server With RODBC

R programming language is used for statistical computing. In this article, we will learn how to connect an SQL server with RODBC.

What is DBMS?

DBMS refers to Database Management System. It is a software for storing and retrieving the data. There are various types of DBMS they are.

1. Hierarchical Database
2. Network Database
3. Relational Database
4. Object Oriented Database

Our SQL Server comes under the relational model So, let’s understand what the relational model is..

RDBMS

RDBMS refers to Relational Database Management System. In very simple words RDBMS model stores data in the form of tables with normalization. It means in the form of rows and columns.

What is SQL?

SQL refers to Structured Query Language that is used to interact with relational databases. It contains DDL, DML, DCL, DQL, and TCL commands.

Popular SQL’s:

1. MySQL
2. PostgreSQL
3. Oracle Database
4. SQL Server (Microsoft SQL Server)
5. SQLite
6. MariaDB
7. Sybase

What is SQL Server?

SQL Server is a relational database management system, or RDBMS developed and marketed by Microsoft.

Similar to other RDBMS software, SQL Server is built on top of SQL, a standard programming language for interacting with relational databases. 

What is SSMS:

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure. We use SSMS to access, configure, manage, administer, and develop all components of SQL Server.

Database Connectivity with RODBC:

What is ODBC:

ODBC refers to Open Database Connectivity. It is an API used for accessing a database.

What is RODBC:

RODBC is an R package that helps us to implement database connectivity.

Example-1:

Here, We are using a very simple database for ease of understanding..
DatabaseNameused: Students TableName used: Names (columns: FirstName, LastName, Email, Phone)

R

# Once RODBC is installed, import the library using the 'library()' function.
library(RODBC)

# Define your SQL Server information: replace 'your_server_name' and 'your_database_name.
server_name <- "your_server_name"
database_name <- "your_database_name"

# Creating the Connection String

# Create the connection string to establish a connection to the SQL Server database.
# The 'trusted_connection=yes' indicates Windows Authentication (

connection_string <- paste0("driver={SQL Server};server=", server_name,
                            ";database=", database_name, ";trusted_connection=yes;")

# Establishing the Connection

# Attempt to connect to the SQL Server database using the connection string.
conn <- odbcDriverConnect(connection_string)

# Check if the connection is successful.
if (is.null(conn)) {
  cat("Connection failed\n")
} else {
  cat("Connection successful!\n")

# Executing an SQL Query


  # Replace 'your_table_name' with the name of the table you want to query.
  query <- "SELECT * FROM your_table_name"
  result <- sqlQuery(conn, query)

# Closing the Connection

# Close the connection to the database once the data is retrieved.
  odbcClose(conn)

# Display the Query Results

  # Print the query result
  print(result)
}

Output:

SQL Server with RODBC

• library(): In R, the library() function is used to load and make available packages (libraries) that contain additional functions and datasets not included in the base R installation.
• paste0(): It is a function in R that concatenates multiple character strings into a single string with no separator between them. It is similar to the paste() function, but it uses an empty string as the separator.
• odbcDriverConnect(): It is a function from the RODBC package in R, which is used to create a connection to a database using an ODBC (Open Database Connectivity) driver.
• cat(): It is an R function used to concatenate and print its arguments. It stands for “concatenate and print.” The primary purpose cat() is to print text or variable values to the console.
• SQL query (): It is a function from the RODBC package in R, which is used to execute SQL queries on a database and retrieve data as a result.
• trusted connection: It means Windows Authentication (i.e. a Windows login). SQL Server has two Authentication modes: Mixed and Windows Authentication Mode. Mixed has the option of SQL server logins (username and password) and Windows Authentication. We do not specify the username and password explicitly. it relies on the credentials of the currently logged-in Windows user to authenticate and authorize access to the database.

This is a basic example of how you can connect R to SQL Server with RODBC. We can add more queries and we can also expand our database.

Example-2:

Finding the average age.

R

# Finding the Average Age of Students in the "Students" Table of "StudentsDB" database
# load the RODBC
library(RODBC)

# Replace 'your_server_name' with your actual SQL Server name
server_name <- "your_server_name"
database_name <- "StudentDB"

# Create the connection string with trusted connection
connection_string <- paste0("driver={SQL Server};server=", server_name,
                            ";database=", database_name, ";trusted_connection=yes;")

# Establish the connection
conn <- odbcDriverConnect(connection_string)

# Check if the connection is successful
if (is.null(conn)) {
  cat("Connection failed\n")
} else {
  cat("Connection successful!\n")

  # Execute SQL query to find the average age of students
  query <- "SELECT AVG(age) AS average_age FROM Students"
  result <- sqlQuery(conn, query)

  # Close the connection
  odbcClose(conn)

  # Display the average age of students
  cat("Average Age of Students:", result$average_age, "\n")
}

Output:

Connect R to SQL with RODBC

• First, load the RODBC package.
• Second, Define the server_name and database_name variables so that we can use the variable names inside our connection string combined with the paste0() function.
• Third, establish the connection using “odbcDriverConnect() function”.
• Check the connection status using an if-else block.
  1. if the connection failed the if-block should be executed
  2. else the else-block will be executed. (it can be interchanged too!)
• Use the AVG() function to find the average age and execute the query using the “sqlQuery() function”
• Close the connection after retrieving the data using odbcClose().
• Display the result using the cat().

Example-3:

Getting user input for the student data…

R

# Transferring User-Entered Data to SQL Server and Displaying All Records

library(RODBC)

server_name <- "your_server_name"
database_name <- "StudentDB"

# Create the connection string with trusted connection
connection_string <- paste0("driver={SQL Server};server=", server_name,
                            ";database=", database_name, ";trusted_connection=yes;")

# Establish the connection
conn <- odbcDriverConnect(connection_string)

# Check if the connection is successful
if (is.null(conn)) {
  cat("Connection failed\n")
} else {
  cat("Connection successful!\n")

  # Prompt the user
  new_student <- data.frame(
    id = readline("Enter ID: "),
    firstname = readline("Enter First Name: "),
    lastname = readline("Enter Last Name: "),
    age = as.numeric(readline("Enter Age: "))
  )

  # Execute SQL query to insert the new record into the 'Students' table
  query <- paste("INSERT INTO Students (id, firstname, lastname, age) VALUES (",
                 new_student$id, ", '", new_student$firstname, "', '",
                 new_student$lastname,
                 "', ", new_student$age, ")")
  # the $ symbol is used for extracting a specific variable or column from a data frame.
  # Each column in a data frame can be considered as a variable, and we can access these
  #variables using the $ symbol followed by the column name.
  sqlQuery(conn, query)

  # Execute SQL query to retrieve all records from the 'Students' table
  all_records_query <- "SELECT * FROM Students"
  all_records <- sqlQuery(conn, all_records_query)

  # Close the connection
  odbcClose(conn)

  cat("New student record inserted successfully!\n")

  # Display all records from the 'Students' table
  cat("\nRecords in 'Students' table:\n")
  print(all_records)
}

Output:

User entered data stored into our DB

1. The first few steps remains the same (same till the if-else block)
2. Let’s continue after that… We prompt the user to enter the student record, after the user enters the data we construct the SQL Query to insert the newly entered data.
3. then, we execute the query using the sqlQuery() function.
4. Next, to check whether the insert query has executed correctly, we use another SQL query to fetch all records from the ‘Students’ table. The sqlQuery() function is used again to execute this query, and the fetched data is stored in a variable.
5. Once we have all the records, we close the connection to the SQL Server database.
6. Finally, we display a message to inform the user that the new student record has been inserted successfully. Then, we display all the records from the ‘Students’ table, including the newly inserted record.

Note: In the above R code, the $ symbol is used for extracting a specific variable or column from a data frame. Each column in a data frame can be considered as a variable, and we can access these variables using the $ symbol followed by the column name.

Summary of the functions used:

1. library(RODBC): Loads the RODBC package, enabling database connection functionality.
2. paste0(): Concatenates strings without any separator.
3. odbcDriverConnect(): Establishes a connection to the SQL Server database using a connection string.
4. is.null(): Checks if an object is NULL (used to verify the success of the database connection).
5. readline(): Prompts the user to enter data from the console.
6. sqlQuery(): Executes an SQL query on the database and retrieves data as a result.
7. odbcClose(): Closes the connection to the SQL Server database.
8. data.frame(): In R, the data.frame() function is used to create a data frame, which is a two-dimensional tabular data structure similar to a table in a database.

Deeper look into the data.frame() function:

Data frames are used to store and organize data in a structured manner, where each column can have a different data type.

In our code we created a new data frame named new_student by prompting the user to enter data for the ‘id’, ‘firstname’, ‘lastname’, and ‘age’ columns using readline(). The as.numeric() function ensures that the entered age is treated as a numeric value.

Main Features of SQL Server in R and vice-versa:

Using R, we can easily work with large datasets by directly retrieving and analyzing data stored and managed in tables by SQL Server.

Using R, we have the ability to perform a multitude of calculations and aggregations on the data. One example of this is the ability to calculate statistical measures such as the average or sum.

To better understand and communicate insights derived from data, R in SQL Server provides the capability to create graphical visualizations such as charts and plots.

Even for complex analyses, SQL Server’s powerful engine and R’s computational abilities guarantee efficient and scalable data processing.

Repetitive tasks like data analysis and reporting can be easily automated by scheduling R scripts within SQL Server.

Hope this helps, Happy Coding!