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.
Embedded SQL (Static SQL) in DBMS

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:

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.

3 thoughts on “Embedded SQL (Static SQL) in DBMS”

Leave a Comment

Your email address will not be published. Required fields are marked *