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.
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.
☆ The result of the given query is made available to the program which is then embedded as one tuple or record at a time.
☆ For the purpose of identification, we request to the preprocessor via EXEC SQL statement: EXEC SQL embedded SQL statement END-EXEC.
☆ It can also execute the update, insert and delete statement.
☆ By using the open statement we can evaluate the query.
☆ EXEC SQL open c END_EXEC
☆ By using the fetch statement we can place the values of one tuple in the query result onto the variables of the host language.
☆ EXEC SQL fetch c into :pn, : cp END_EXEC
☆ Repeated calls to fetch can result in successive tuples in the query result.
☆ By using the close statement we can delete the temporary relation that holds the result of the query in the database system.
☆ EXEC SQL close c END_EXEC
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
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.
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;
char STU_NAME ;
EXEC SQL END DECLARE SECTION;
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;