RODBC helper function

[This article was first published on R Blogs – Hutsons-hacks, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

The number of times I have to connect to SQL and I forget part of the RODBC command to connect to an internal data table.

As part of a project I am working on I have been connecting to lots of different sources and became tired of typing lots of lines and repeating the same command. Therefore, I have been rescued by R’s lovely functions.

The function

The function is very simple, but extremely useful. This is contained in the code block below and I will talk you through this:

sql_connection 

The function is simple – it looks for a connection string (conn_str) and a SQL command (sql_str) and the following process is initiated:

  1. The RODBC library is loaded, please make sure this package is installed beforehand
  2. A message is sent to the Console to say that the SQL has started to load – you could add more functionality to this message, but this will suffice
  3. A variable is created called conn to initiate the connection and pass the connection string to the connection parameter
  4. Then a variable to store the sql command is created and links to the user defined function parameter of sql_str
  5. Next, a data frame is created based on the connection and SQL command
  6. Following this the data frame is returned from the function
  7. The last piece in the puzzle is to close the ODBC connection and show a message to the user to say that the SQL has been loaded

Using the function

To use the function we need to call it in our R code and pass two important pieces of information. The first is the connection string, this is the most important part of the function, as an error here will cause the connection not to be established. I provide a brilliant website I used to use when I was a DBA as reference: https://www.connectionstrings.com/sql-server/ .

The second part is the SQL command itself. This is shown in the R block below:

sql_df 

This will then create a data frame with the results of your SQL query in. Then you can use DPLYR and the Tidyverse to do the relevant data manipulation and cleaning.

I hope you find this function useful and use it at will. It has certainly helped me not have to search the functionality of this package every time.

To leave a comment for the author, please follow the link and comment on their blog: R Blogs – Hutsons-hacks.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)