Embedded SQL: Use of Embedded SQL Statements

The embedded SQL statements can be put in the application program written in C, Java or any other host language. These statements may sometimes be called static. The term ‘static’ is used to indicate that the embedded SQL commands, which are written in the host program, do not change automatically during the lifetime of the program. Thus, such queries are determined at the time of database application design. For example, a query statement embedded in C to determine the status of train booking for a train will not change. However, this query may be executed for many different trains. Please note that it will only change the input parameter to the query that is train-number, date of boarding, etc., and not the query itself.

Below is the example of embedded SQL where to write a C program segment that prints the details of a student whose enrolment number is input.

Student(enrolno:char(9), name:char(25), phone:integer(12), prg-code:char(3))
/* add proper include statements */
/* declaration in C program */
Exec SQL BEGIN DECLARE SECTION;
Char enrolno[10], name[26], p-code[4];
int phone;
int SQLCODE;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;

/* The connection needs to be established with SQL */
/* Program segment for the required function */
printf(“enter the enrolment number of the student”);
scanf(“%s”, &enrolno);
EXEC SQL
SELECT name, phone, prog-code INTO
:name, :phone:, :p-code
FROM STUDENT
WHERE enrolno=:enrolno;
If(SQLCODE==0)
Printf(“%d, %s, %s”, enrolno, name, phone, p-code)
Else
Printf(“Wrong Enrolment Number”);

Please note the following points in the program above:

• The program is written in the host language ‘C’ and contains embedded SQL statements.
• Although in the program an SQL query (select) has been added. You can embed any DML, DDL, or views statements.
• The distinction between and SQL statement and host language statement is made by using the keyword EXEC SQL; thus this keyword helps in identifying the Embedded SQL statements by the pre-compiler.
• Please note that the statements including(EXEC SQL) are terminated by a semi-colon(;).
• As the data is to be exchanged between a host language and a database, there is a need of shared variables that are shared between the environments. Please note that enroll[10], name[20], p-code[4]; etc. are shared variables, colon(:) declared in ‘C’.
• The Type mapping between ‘C’ and SQL types is defined in the following table:
  

‘C’ Type SQL Type
Long INTEGER
Short SMALLINT
Float REAL
Double DOUBLE
Char[I+1] CHAR(i)

• Please also note that these shared variables are used in SQL statements of the program. They are prefixed with the colon(:) to distinguish them from database attribute and relation names. However, they are used without this prefix in any C language statement.
• Please note that these shared variables have almost the same name(except p-code) as that of the attribute name of the database. The prefix colon(:) this distinguishes whether we are referring to the shared host variable or an SQL attribute. Such similar names is a good programming convention as it helps in identifying the related attribute.
• Please note that the shared variables are declared between BEGIN DECLARE SECTION and END DECLARE SECTION and there typed is defined in ‘C’ language.