Embedded SQL in DBMS

Embedded SQL is a powerful method that allows the integration of high−level programming languages with database management systems (DBMS). It acts as a bridge between applications and databases which helps in data manipulation and communication. Various database management systems offer embedded SQL, giving developers the freedom to select the one that best serves their requirements.

Popular DBMS that support Embedded SQL are Altibase, IBM Db2, Microsoft SQL Server, Mimer SQL, Oracle Database, PostgreSQL, and SAP Sybase.

Need of Embedded SQL

The goal to make database interactions simpler for application developers and end users determines the demand for embedded SQL. Users often enter values or submit requests while interacting with an application, which requires accessing and changing data contained in a database. Developers may conduct these database tasks without burdening the user with SQL complexities by integrating SQL queries directly into the application code.

Structure of Embedded SQL

Connection to the Database

The first step in using embedded SQL is establishing a connection to the database. This is done using the CONNECT keyword, preceded by EXEC SQL to indicate that it is a SQL statement

Example

EXEC SQL CONNECT HR_USER;

Declaration Section

The declaration section is used to declare variables that will be used in SQL queries and to capture the results of those queries. The host language and its variable will get and capture the result. Types of variables used in the host language are:

Host Variable: Host variables are variables of the host language that are used to supply data and store the results of SQL queries. The BEGIN DECLARE and END DECLARE sections are defined within EXEC SQL and should be used to declare the variables within the SQL code and;

Example

EXEC SQL BEGIN DECLARE SECTION;
int id;
int roll_no;
char name[10];
char address[30];
EXEC SQL END DECLARE SECTION;

Indicator variables: Indicator variables are also host variables but they are used to capture NULL values which are returned by queries or to set NULL values in INSERT or UPDATE statements. Indicator variables are always of the 2−byte short type. With SELECT statement, indicators capture any NULL values returned for a column and with INSERT OR UPDATE statement, the common value is set to be NULL regardless of the value present for a host variable.

Example

EXEC SQL SELECT NAME INTO :NAME :IND_NAME FROM STUDENT WHERE ID = :ID;
INSERT INTO STUDENT (ID, SNAME) VALUES (:ID, :NAME :IND_NAME);
UPDATE STUDENT SET ADDRESS = :ADDRESS :IND_ADDRESS;

Execution Section

The execution section contains all the SQL queries and statements that are prefixed by EXEC SQL. This is where you can write your SQL queries and perform database operations.

Example

EXEC SQL SELECT * FROM STUDENT WHERE STUDENT_ID = :STD_ID;

Error Handling

Error handling is essential in embedded SQL. In embedded SQL, error handling is based on the host language. In C programs, error handling is typically done using labels and the WHENEVER statement. The WHENEVER statement is used to define the action to be taken when a certain condition occurs. The condition can be SQLWARNING, SQLERROR, or NOT FOUND. The action can be CONTINUE, DO <function>, GOTO , or STOP.

Example

EXEC SQL WHENEVER SQLWARNING DO display_warning();
EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL WHENEVER NOT FOUND GOTO lbl_no_records;

Advantages of Embedded SQL

Ease of Database Usage

Accessing databases is made simple with embedded SQL, which eliminates the need for lengthy coding or other programs. It offers a simple method for getting data by feeding it into the database, doing away with the need for complex and complicated programming.

Security and authorization

With embedded SQL, developers may specify and implement appropriate authorization procedures for database access. This helps prevent unauthorized access to sensitive data and ensures that only users with the proper authorization can execute certain tasks.

Integration of Frontend and Backend

By permitting a direct connection between the user interface and the database, embedded SQL makes it easier to integrate the front end and back end of an application. This improves the overall user experience by enabling real−time data retrieval and changes.

Error prevention

Developers can prevent logical mistakes from happening when processing database transactions by integrating SQL queries into the application code. The integration of SQL with the host language guarantees security and improves the application's dependability.

Disadvantages of Embedded SQL

Knowledge of the Host Language Required

Developers must have a solid understanding of the host language in which the SQL is contained to use embedded SQL effectively. This can require more programming language knowledge or training.

Complex Development Model

By using embedded SQL, the development process becomes even more difficult. It may be difficult for developers to handle the integration of SQL statements within host language code, especially for larger systems.

Limited SQL Flexibility

SQL statements must be declared at design time for embedded SQL, which means that only predefined queries may be used in application code. The application's ability to react to shifting requirements may be hampered by the absence of dynamic SQL flexibility.

Conclusion

This article consists of embedded SQL in dbms which helps high−level languages to communicate with the database and simplify the integration of SQL in application code. The structure of embedded SQL contains a connection to the database, a declaration statement(host variable and indicator variable), an execution statement, and error handling. Advantages are ease of database usage, security, authorization, integration of frontend and backend, and error prevention. Disadvantages are knowledge of host language requirements, complex development model, and limited sql flexibility.