Easy data access: The advantages of a unique database connection with ODBC and DBI

September 4, 2019
By

[This article was first published on R-Bloggers – eoda GmbH, 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.

Easy data access: The advantages of a unique database connection with ODBC and DBI

Every developer has his favorite tools and frameworks to work with when connecting to the database. The number of different front end and back end combinations increases the complexity of the analysis. Any Relational Database Management System (RDBMS) that needs its own driver may also be platform and language dependent. For this reason, many types of database performance problems can arise.

By providing a server, the recurring client-side configuration effort is shifted to a one-time central configuration of different database drivers. In this way, proximity to the data is created and, finally, user-friendly access is made possible. Optimum data access is particularly important for the work of data scientists. It enables them to access the relevant data quickly and efficiently.

JDBC or ODBC as back end system?

The RBDMS is connected to the Integrated Development Environment (IDE) via a back end. This can be provided, for example, by Java Database Connectivity (JDBC) or Open Database Connectivity (ODBC). Both back ends have an API (Application Programming Interface) that implements communication with the database on the client side. Both back end systems are provided by many RDBMS providers by default, but there is one central difference: While the ODBC back end is language-independent, the JDBC depends on the Java programming language. Although the language independence of ODBC is reflected in a higher complexity of the back end, additional layers such as the communication from JDBC to MS SQL (left side) via ODBC (orange box) can be avoided. Finally, it can be said that using the ODBC back end is advantageous because it is platform- and language-independent.

DataBase Interface as a front end system for efficient database connections

As front end it is recommended to use the interlingual DataBase Interface (DBI). This interface defines a set of classes and methods like those implented in R’s DBI, Perl’s DBI and Python’s DB-API. This sends uniform commands to the ODBC back end, which translates the commands into the correct database management system logic. This approach allows a connection to a database (e.g. SQL Server, Oracle, MySQL, PostgreSQL, SQLite, …) to be established efficiently and very easily if the basic ODBC drivers are installed.

In addition, it offers the advantage to address any database with the same functions and methods.

The installation and configuration of ODBC is the work that requires the most effort.

We are happy to support you in establishing an optimal database connection so that large amounts of data can be stored efficiently and without contradiction.

To leave a comment for the author, please follow the link and comment on their blog: R-Bloggers – eoda GmbH.

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.



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Search R-bloggers

Sponsors

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)