Embedded SQL in DBMS

Introduction

Database languages are meant for dealing with databases. They are used only to query tables / views, manipulate the data values using insert/update/ delete, or set of transactions can be executed one after the other to modify set of tables/view. But all these transactions can be performed by a good database developer or a SQL programmer who has good knowledge about database as well as SQL. But in real time, these developer or programmers will not be using the database. Actual use of DB is for normal user for whom DB is a black box.  In order to make easy for the normal user, applications, UI or forms are created where user can enter his values or requirement. The underlying application program will manipulate his request. Hence user need not have any knowledge about the DB. But how application communicates with DB? Applications are developed using some general purpose languages like C, C++, JAVA, etc. These languages are used to get UIs, forms etc. They are not meant for any DB activities. Similarly DB languages like SQL, SQL servers etc are meant for only DB activities. Hence both application language and DB language are completely different from each other. But in order to make application programs to work, DB is compulsory; and for any DB to be used by the user application/UI is a must. Hence both are dependent on each other too.

This gap between application programs and SQL is bridged by the use of embedded SQL and dynamic SQLs. These SQLs provide the utility to use SQLs inside the application language like C, C++, Java etc, and make these applications to communicate with DB. Hence when user submits a request or enters values in the form, he gets the result what he is requested.

Embedded SQL

Like we said above, embedded SQL is the one which combines the high level language with the DB language like SQL. It allows the application languages to communicate with DB and get requested result. The high level languages which supports embedding SQLs within it are also known as host language. There are different host languages which support embedding SQL within it like C, C++, ADA, Pascal, FORTRAN, Java etc. When SQL is embedded within C or C++, then it is known as Pro*C/C++ or simply Pro*C language. Pro*C is the most commonly used embedded SQL. Let us discuss below embedded SQL with respect to C language.

When SQL is embedded within C language, the compiler processes the compilation in two steps. It first extracts all the SQL codes from the program and the pre-compiler will compile the SQL code for its syntax, correctness, execution path etc. Once pre-compilation is done, these executable codes are embedded into the C code. Then the C compiler will compile the code and execute the code. Thus the compilation takes place in two steps – one for SQL and one for application language. Hence these types of compilation require all the query, data value etc to be known at the compilation time itself to generate the executable code. Otherwise C or any high level language cannot compile the code. Hence the SQL codes written are static and these embedded SQL is also known as static SQL. Thus SQLs know how to access DB, which queries to execute, which values to be inserted/ deleted/updated etc.

When SQL is embedded in C programming language, the C compiler will not understand which the syntax of C is and which syntax of SQL is. It needs to be clearly differentiated and compiler should know which is C and SQL. This is very important as pre-compiler will first extract all the SQLs embedded in it to compile it at DB level. Then it will be embedded in the C code which will be compiled by the C compiler to get executable code. All the embedded SQLs are preceded by ‘EXEC SQL’ and ends in semicolon (;). We can have these SQLs placed anywhere in the C code, provided it is placed in the correct order- declaration, execution and end. Let us see below how C code is differentiated from SQL code.

Structure of Embedded SQL

Structure of embedded SQL defines step by step process of establishing a connection with DB and executing the code in the DB within the high level language.

Connection to DB

This is the first step while writing a query in high level languages. First connection to the DB that we are accessing needs to be established. This can be done using the keyword CONNECT. But it has to precede with ‘EXEC SQL’ to indicate that it is a SQL statement.

EXEC SQL CONNECT db_name;

EXEC SQL CONNECT HR_USER; //connects to DB HR_USER

Declaration Section

Once connection is established with DB, we can perform DB transactions. Since these DB transactions are dependent on the values and variables of the host language. Depending on their values, query will be written and executed. Similarly, results of DB query will be returned to the host language which will be captured by the variables of host language. Hence we need to declare the variables to pass the value to the query and get the values from query. There are two types of variables used in the host language.

  • Host variable : These are the variables of host language used to pass the value to the query as well as to capture the values returned by the query. Since SQL is dependent on host language we have to use variables of host language and such variables are known as host variable. But these host variables should be declared within the SQL area or within SQL code. That means compiler should be able to differentiate it from normal C variables. Hence we have to declare host variables within BEGIN DECLARE and END DECLARE section. Again, these declare block should be enclosed within EXEC SQL and ‘;’.
EXEC SQL BEGIN DECLARE SECTION;
	int STD_ID;
	char STD_NAME [15];
	char ADDRESS[20];
EXEC SQL END DECLARE SECTION;

We can note here that variables are written inside begin and end block of the SQL, but they are declared using C code. It does not use SQL code to declare the variables. Why? This is because they are host variables – variables of C language. Hence we cannot use SQL syntax to declare them. Host language supports almost all the datatypes from int, char, long, float, double, pointer, array, string, structures etc.

When host variables are used in a SQL query, it should be preceded by colon – ‘:’ to indicate that it is a host variable. Hence when pre-compiler compiles SQL code, it substitutes the value of host variable and compiles.

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

In above code, :STD_ID will be replaced by its value when pre-compiler compiles it.

Suppose we do not know what should be the datatype of host variables or what is the datatype in oracle for few of the columns. In such case we can allow the compiler to fetch the datatype of column and assign it to the host variable. It is done using ‘BASED ON’ clause. But format of declaration will be in host language.

EXEC SQL BEGIN DECLARE SECTION;
	BASED ON STUDENT.STD_ID sid;
	BASED ON STUDENT.STD_NAME sname;
	BASED ON STUDENT.ADDRESS saddress;
EXEC SQL END DECLARE SECTION;
  • Indicator Variable : These variables are also host variables but are of 2 byte short type always. These variables are used to capture the NULL values that a query returns or to INSERT/ UPDATE any NULL values to the tables. When it is 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 though the host variable has value. If we have to capture the NULL values for each host variable in the code, then we have to declare indicator variables to each of the host variables. These indicator variables are placed immediately after the host variable in a query or separated by INDICATOR between host and indicator variable.
EXEC SQL SELECT STD_NAME INTO :SNAME :IND_SNAME 
			FROM STUDENT WHERE STUDENT_ID =:STD_ID;

Or

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

	INSERT INTO STUDENT (STD_ID, STD_NAME) 
		VALUES (:SID, :SNAME INDICATOR :IND_SNAME); --Sets NULL to STD_NAME
	
	UPDATE STUDENT 
	    SET ADDRESS = :STD_ADDR :IND_SADDR; --Sets NULL to ADDRESS

 Though indicator variable sets/gets NULL values to the column, it passes/ gets different integer values. When SELECT query is executed, it gets 4 different integer values listed below :

When insert / update statement is executed along with indicator variable, then it can pass two values to indicate to assign or not to assign NULL values.

Execution Section

This is the execution section, and it contains all the SQL queries and statements prefixed by ‘EXEC SQL’.

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

	INSERT INTO STUDENT (STD_ID, STD_NAME) 
		VALUES (:SID, :SNAME);  
	
	UPDATE STUDENT 
	    SET ADDRESS = :STD_ADDR
	WHERE STD_ID = :SID;

Above examples show simple SQL queries/statements. But we can have complex queries too.
In this embedded SQL, all the queries are dependent on the values of host variable and queries are static. That means, in above example of SELECT query, it always pulls student details for the student Id inserted. But suppose user enters student name instead of student ID. Then these SQLs are not flexible to modify the query to fetch details based on name. Suppose query is based on name and address of a student. Then code will not modify the query to fetch details based on name and address of a student. That means queries are static and it cannot be modified based on user input. Hence this kind of SQLs is known as static SQLs.

Error Handling

Like any other programming language, in embedded SQL also we need to handle errors. Error handling method would be based on the host language. Here we are using C language and we use labeling method, i.e.; when error occurs we stop the current sequence of execution and ask the compiler to jump to error handling section of the code to continue. In order to handle error, C programs require separate error handling structure which holds different variables to capture different set of errors. This structure is known as SQL Communication Area or SQLCA. Below is the structure of SQLCA.

struct sqlca {
    /* ub1 */ char sqlcaid [8];
    /* b4 */ long sqlabc;
    /* b4 */ long sqlcode;
    struct {
        /* ub2 */ unsigned short sqlerrml;
        /* ub1 */ char sqlerrmc[70];
    } sqlerrm;
….
long sqlcode; //returns the error code
…
char sqlstate [6]; //returns predefined error statements
….
}

If we have to use this error handling structure, then we have to include sqlca.h header file in the program, using #include directives. In this structure mainly SQLCODE and SQLSTATE are used to see the type of error. SQLCODE returns different values for different types of errors.

Whenever error occurs in the code, then we have to redirect the execution of code to handle the error rather than executing further. This is done using WHENEVER statement.

EXEC SQL WHENEVER condition action;

The condition in WHENEVER clause can be

  • SQLWARNING – indicates SQL warning. It indicates the compiler that when SQL warning occurs perform action.
  • SQLERROR – indicates SQL Error. The SQLCODE will have negative value.
  • NOT FOUND – SQLCODE  will have positive value indicating no records are fetched.

On receiving error or warning, action can be any one of the following:

  • CONTINUE – indicates to continue with the normal execution of the code.
  • DO – it calls a function and hence program will move to execute this error handling function.
  • GOTO <label> – Program will jump to the location <label> to execute error handling.
  • STOP – it immediately stops the execution of the program by calling exit (0) and all the incomplete transactions will be rolled back.

EXEC SQL WHENEVER SQLWARNING DO display_warning();

EXEC SQL WHENEVER SQLERROR STOP;

EXEC SQL WHENEVER NOT FOUND GOTO lbl_no_records;

Whenever we use ‘WHENEVER’ clause,  first statement should be ‘EXEC  SQL INCLUDE SQLCA;’ in the code. This is to indicate compiler that error handling needs to be done for the following code.

Consider a simple Pro*C program to illustrate embedded SQL. This program below accepts student name from the user and queries DB for his student id.

#include <stdio.h>
#include <sqlca.h>

int main(){
	EXEC  SQL INCLUDE SQLCA;

	EXEC SQL BEGIN DECLARE SECTION;
		BASED ON STUDENT.STD_ID SID; // host variable to store the value returned by query
		char *STD_NAME; // host variable to pass the value to the query
		short ind_sid;// indicator variable
	EXEC SQL END DECLARE SECTION;

	//Error handling
	EXEC WHENEVER NOT FOUND GOTO error_msg1;
	EXEC WHENEVER SQLERROR GOTO error_msg2;

	printf("Enter the Student name:");
	scanf("%s", STD_Name);

	// Executes the query
	EXEC SQL SELECT STD_ID INTO : SID INDICATOR ind_sid FROM STUDENT WHERE STD_NAME = : STD_NAME;
 	
	printf("STUDENT ID:%d", STD_ID); // prints the result from DB
	exit(0);

// Error handling labels
error_msg1:
	printf("Student Id %d is not found", STD_ID);
	printf("ERROR:%ld", sqlca->sqlcode);
	printf("ERROR State:%s", sqlca->sqlstate);
	exit(0);

error_msg2:
	printf("Error has occurred!");
	printf("ERROR:%ld", sqlca->sqlcode);
	printf("ERROR State:%s", sqlca->sqlstate);
	exit(0);
}

 

 

 

 

 

Translate »