Embedded SQL (Static SQL) in DBMS

The structured query language provides us 2 features:

• It allows us to write queries.
• It allows us to use it in programming languages so that
  database can be accessed through application programs also.

Due to this duality SQL is sometimes called dual mode
language.

Actually all the queries can’t be expressed in SQL alone.
There are many queries that are expressed in programming languages like C, C++,
Java but can’t be expressed in SQL. For writing such queries we need to embed
the SQL in general purpose programming languages. The mixture of SQL and
general purpose programming languages is called embedded SQL.

There are some special embedded SQL statements which are
used to retrieve the data into the program. There is a special SQL precompiler
that accepts the combined source code with other programming tools and converts
them into an executable program.

Concepts for Embedding the SQL Statements

We can mix the SQL statements directly into general purpose
programming language like C, Java or Pascal. There are some techniques to embed
SQL statements in the programming languages.

1. The programming language in which the SQL statements are
   embedded is called the host language. The SQL statements and host language
   statements make the source program which is fed to a SQL precompiler for
   processing the SQL statements.
2. The host programming languages variables can be referenced
   in the embedded SQL statements, which allows values calculated by the programs
   to be used by SQL statements.
3. There are some special program variables which are used to
   assign null values to database columns. These program variables support the
   retrieval of null values from the database.

Embedded SQL Program Development

Since the embedded SQL is a mixture of SQL and programming
language, so it cannot be fed directly to a general purpose programming
language compiler. Actually the program execution is a multi-step which is as
follows.

1. First, the embedded SQL source code is fed to the SQL
   precompiler. The precompiler scans the program and processes the embedded SQL
   statements present in the code. There can be different precompilers for
   different type of programming languages.
2. After processing the source code, the precompiler produces
   2 files as its output. The first file contains the source program without
   embedded SQL statements and the second file contains all the embedded SQL
   statements used in the program.
3. The first file prodiced by precompiler (that contains the
   source program) is fed to the compiler for the host programming language (like
   C compiler). The compiler processes the source code and produces object code as
   its output.
4. Now the linker takes the object modules produced by the
   compiler and link them with various library routines and produces an executable
   program.
5. The database request modules, produced by the precompiler
   (in steps) are submitted to a special BIND program. The BIND program examines
   the SQL statements, parse them, validates them, optimizes them and finally
   produces an application plan for each statement. The result is a combined
   application plan for the entire program, that represents a DBMS-executable
   version of its embedded SQL statements. The BIND program stores the plan in the
   database, usually assigning it the name of the application program that has
   created it.

An Embedded SQL Example in C

Although the SQL statements can be embedded in any general purpose
programming language, still we just take an example in C language so that a
clear picture can be drawn. We just take an interactive SQL statement and see
how it can be embedded in C language.

Increase the salary of teacher by 10% who are B.Tech

update teacher set salary=1.1*salary where qualification=’B.Tech’;

The embedded SQL program for above written SQL statement
will be:

main()
{
                exec sql include sqlca;
                exec sql declare table teacher (tid char(6) not null,tname char(20),sex char(1),age number(3),qualification char(7),salary number(7),city varchar(15));
 
                //Display a message to user
                printf("updating teacher salary who are B.Techn");
 
                //this code executes the SQL statement
                exec sql update teacher set salary=1.1*salary where qualification='B.Tech';
                printf(update done");
               
                exit();
}

Explanation

Although the above shown program is very easy to understand,
still we would like to discuss some very basic features of embedded SQL.

1. The embedded SQL statement can be written in any case (lower
   or upper). Although we should follow the convention of that programming
   language in which we are embedding the SQL statements. For e.g., COBOL and
   FORTRAN are written in upper case so, the SQL statements are also written in
   upper case, while in C, the SQL statements are written in lower case as shown
   in above program.
2. Each embedded SQL statement begins with an introducer which
   indicates that it is a SQL statement. For most of the programming language EXEC
   SQL is used as an introducer.
3. Each embedded SQL statement ends with a terminator. There
   can be different terminators for different programming languages. For example,
   there is END EXEC for COBOL and a semicolon (;) for C.
4. The DECLARE TABLE statement is used to declare a table. With
   the use of DECLARE TABLE statement our program specifies the column and data
   type explicitly.
5. When we type SQL statement, we may make an error. This error
   is displayed by the interactive SQL program and we are prompted to type a new statement.
   There can be two types of errors: compile time and runtime.

Error Handling with SQL Code

In this scheme the DBMS communicates the status to the
embedded SQL program through an area of program storage called the SQL
communication are or SQLCA. The SQLCA is a data structure that contains the
error variables and the status indicators. By examining the SQLCA, the
application program can determine the success or failure of its embedded SQL statements
and can take actions accordingly.

Image Credit