Embedded SQL in DBMS

In this course, we will study Embedded SQL in DBMS (Database Management System), its needs and its structure and Connection to DB with an example.

What is embedded SQL in DBMS

We use this method for combining the data manipulation capabilities of SQL along with the computing power of any programming language.
The embedded statements are always in line with the source code of the host language.

The code of embedded SQL is parsed through a preprocessor which is then embedded and replaced by the host language called for the code library it is then compiled by using the compiler of the host.

When SQL is embedded within languages like C or C++, then it is called up as Pro*C/C++ or simply Pro*C language. Pro*C is the most commonly used embedded SQL.

Embedded SQL in DBMS

What is the need for embedded SQL in DBMS

What is the structure of embedded SQL

Connection to DB

This is the first step while we write a query in high-level languages. This is done by using the keyword CONNECT, preceding ‘EXEC SQL’ to indicate that it is a SQL statement.

EXEC SQL CONNECT database_name;
EXEC SQL CONNECT HR_USER

Declaration Section

Once the connection is established with DB, DB transactions can be performed. As the DB transactions depend on the values and variables of the host language so the query will be written and executed depending upon the values.
Similarly, the results of the DB query will be returned and will be captured by the variables of the host language. Hence the declaration of the variables has to be done to pass the value to the query and to get the values from the query. There are two types of variables that are used in the host language.

Host variables

These are the variables of host language that are used to pass the value to the query and to capture the values returned by the query. As SQL is dependent on the host language we have to use variables of the host language and such variables are called host variables.

These host variables need to be declared within the SQL area or within SQL code. That means the compiler should be capable of differentiating them from normal C variables. Hence the host variables need to be declared within BEGIN DECLARE and END DECLARE section. And these declare blocks should be enclosed within EXEC SQL and ‘;’.

EXEC SQL BEGIN DECLARE SECTION;
int STU_ID;
char STU_NAME [20];
char ADDRESS[20];
EXEC SQL END DECLARE SECTION;

Indicator Variable

These variables are 2-byte short type always. These variables are used for capturing the NULL values that a query returns or to INSERT/ UPDATE any NULL values to the tables. When used in a SELECT query, it captures any NULL value returned for any column. When used along with INSERT or UPDATE, it sets the column value as NULL, even if the host variable has value.

For capturing the NULL values for each host variable, a declaration of the indicator variables should be done to each of them. The indicator variables are placed right after the host variable in a query or we can say that they are separated using the INDICATOR between the host and the indicator variables.

EXEC SQL SELECT STD_NAME INTO :SNAME :IND_SNAME
FROM STUDENT WHERE STUDENT_ID =: STD_ID;