What is database connectivity?

Database connectivity allows the client software to communicate with the database server software. It is an interface that allows communication between the database and the software application. Elements of frontend applications/websites like buttons, fonts, or menus need to be connected to the database (back-end) to deliver relevant information to the end-user. Database connectivity allows this type of communication/data transfer between the frontend and backend applications.

Database connectivity can be done using different programming languages such as Java, C++, and HTML (hypertext markup language). By using these languages, one can connect a program to a particular database and can query data to access and manipulate them.

Querying the database

Organized collection of data in a computer system is a database. It stores information in the form of structured tables that contain rows and columns to organize the data. It is controlled by the management system. DBMS enables creation and maintenance of database.

SQL (Structured Query Language) is used for querying the data stored in a relational database. MySQL database and SQL select are some modified versions of SQL. The query is categorized into DDL (Data Definition Language) and DML (Data Manipulation Language).

Data Definition Language (DDL):

The statements used to create the database schema and the tables are referred to as DDL. They can also be used to modify the structure of the table. For example, CREATE, ALTER and so forth.

Data Manipulation Language (DML):

These statements are used to insert records into the table, update and delete records present in the table. For example, INSERT, UPDATE, DELETE and so forth.

Database Driver

A driver is a computer program that helps applications to establish database connection. There are two standard protocols for establishing database connectivity name Open Database Connectivity (ODBC) and Java Database Connectivity (JDBC). These enable applications to access and manipulate database contents by providing a generic interface.

Java Database Connectivity (JDBC)

JDBC is an application programming interface (API) for the Java programming language. It uses a Java-based technology to access the database. JDBC gives methods to query and update data in a database.

A JDBC API accesses data from the relational database tables. The data in these databases can be saved, updated, deleted and retrieved. JDBC uses drivers to connect with the database.

JDBC is based on an Open SQL Call Level Interface. The classes and interfaces for JDBC API are stored in the java.sql package. Some of the popular JDBC API interfaces include ResultSet interface, CallableStatement interface, Driver interface, Connection interface, Statement interface, and RowSet interface.

Example program that connects an application with the database using JDBC

package database_console;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class DBConnect {

            public static void main(String[args]){

            try{

                        String host = “jdbc:derby://localhost:1527/Students”

                        String userName = “Username”;

                        String userPassword = “Password”;

                        Connection con = DriverManager.getConnection(host, userName, Password);

}

catch (SQLException error) {

                        System.out.println(error.getMessage() );

}

}

}

Explanation:

The above program establishes a connection between the database Students and Java application. The code begins by importing the necessary Java packages. In the following line, a new class DBConnect is created. In JDBC, the connection starts with jdbc: protocol. So, in this code, inside the try function, the connection with the Java application is being made using the jdbc protocol. The next two statements are written to fetch the username and password of the user. Finally, the connection is established through the DriverManager.getConnection() function.

The try-catch block is used in database connectivity so that it can return an SQLException error if a database fails to connect to the application.

JDBC drivers

A JDBC driver is software that allows the Java application to connect with a database. The driver connects to the database and implements the protocol to transfer the query between the application and the database. JDBC drivers are of four types:

• JDBC-ODBC bridge- This bridge driver connects to the database using the ODBC driver. The driver transforms the JDBC method calls into ODBC function calls.
• Native-API driver– This driver uses client-side libraries of the database. It transforms JDBC method calls into native calls of the API.
• Network-Protocol driver– This JDBC driver uses a middleware between the calling program and database. The middleware is responsible for converting JDBC calls directly or indirectly to a database protocol call.
• Database-Protocol driver (Pure Java driver/Thin driver)– This JDBC driver transforms the JDBC calls directly into a vendor-specific database protocol call. It is the fastest JDBC driver.

Open database connectivity (ODBC)

The standard way to create an application programming interface (API) used by the system to access databases is open database connectivity (ODBC). It is supported by Microsoft and is written in C programming language. ODBC has highly efficient database connectivity with a high-performance interface. Relational data storage and relational data management systems (RDBMS) are handled using ODBC, which is low-level database connectivity.

It is an independent application that can be added to the software as ODBC drivers to form an interface that will work between the user application and particular DBMS. ODBC compliant is an application that uses an ODBC driver. ODBC is mainly used in database management systems.

Before the invention of ODBC, SQL servers were used to create APIs (Application Programming Interface). SQL language embedding and SQL statements were used in late 1980 as a potential way to create an interface. The latest version of ODBC is available on all platforms. ODBC drivers such as SQLite are to be embedded in the existing software.

Driver Manager

ODBC drivers are managed using driver managers. Functions such as spooling and enumerating drivers are done by the driver manager.

Bridge drivers

Bridge drivers are drivers that use another type of driver for operation. Depending on the drivers, bridges can be classified into:

• ODBC-JDBC bridges: Open database connectivity driver uses Java database connectivity driver functions and calls.
• JDBC-ODBC bridges: Java database connectivity driver uses Open database connectivity function calls.
• OLE DB-ODBC bridges: Object linking and embedding database (OLE DB) driver uses ODBC method calls.
• ADO.NET TO ODBC bridges: ActiveX database objects embedded in .NET uses ODBC method calls.

Context and Applications

This topic is significant in the professional exams for both undergraduate and graduate courses, especially for

• Bachelors of Science in Computer Science
• Bachelors of Science in information technology
• Masters of Science in information technology
• Masters of Science in Computer Science

Practice Problems

Q1. What is the full form of SQL?

1. Structured query language
2. Standard query logistics
3. Server-side of query logistics
4. Server-side query language

Answer- a

Explanation: SQL stands for the standard query language.

Q2. Which of the following is the fastest JDBC driver?

1. JDBC-ODBC bridge driver
2. Native-API driver
3. Network protocol driver
4. Thin driver

Answer- d

Explanation: The JDBC database protocol driver, also known as the Thin driver is the fastest. That is because it transforms JDBC method calls directly into vendor-specific database protocol calls.

Q3. What is a driver that uses another driver method called?

1. MySQL database connection object
2. MongoDB connection object
3. Bridge
4. JDBC API

Answer- c

Explanation: Bridge is the driver that needs the technology of other drivers to operate.

Q4. Which of the following is a type of ODBC bridge driver?

1. ODBC-JDBC bridge driver
2. JDBC-ODBC driver
3. OLE DB-ODBC bridge driver
4. All of the above

Answer- d

Explanation: ODBC bridge drivers are categorized as ODBC-JDBC bridges, JDBC-ODBC bridges, ADO.NET to ODBC bridges, and OLE DB-ODBC bridges. So, all of the options are correct.

Q5. Which of these drivers convert JDBC calls into database-specific calls using client-side libraries?

1. JDBC-ODBC
2. Network-protocol
3. Native-API
4. Call-level driver manager

Answer- c

Explanation: Native-API driver transforms the JDBC calls into database-specific calls. It uses client-side libraries.

Common Mistakes

Creating database connectivity requires a high level of skill and practice in programming. The prototype of methods corresponding to ODBC/JDBC API must be known in order to pass appropriate parameters so that the data can be retrieved correctly.

Related Concepts

• Java database connectivity (JDBC)
• Microsoft.NET framework
• SQL network
• Object-relational mapping