Here are the steps involved in embedded SQL development process:
1) Embedded SQL source program is sent to SQL precompiler. The source program is scanned by the SQL precompiler. Then, SQL statements are located and processed.
2) The result of processing by the SQL precompiler is 2 files. One is the stripped source program and the other file is the DBRM (database request module). In the first file, SQL statements are stripped from the source program and in place of that calls to the private DBMS routines are placed. These routine provide a runtime link between program and the database management system. In the second file DBRM, a copy of all embedded SQL statements are kept.
3) Then, the first file is sent to the complier for the particular host programming language. The file is processed and the object code is generated as the output.
4) DBRM is sent to a BIND program. This program processes the SQL statements by parsing, validating, optimizing to generate an application plan for each statement.
5) Linker takes the object code from step 3 and links it with library routines from DBMS library, and generates an executable program. Then the executable program is stored in the database.
Advantages of embedded SQL:
1) In embedded SQL, host language takes care of the variables and other input and output functions whereas SQL is used to handle the database. Thus, tasks are carried out effectively.
2) High overhead tasks like parsing and optimizing are done in the development cycle which increases efficiency of CPU resources.
3) By using DBRM, portability can be achieved. In one system precompiling can be done and it can be be moved to the other system for further processing.
4) The programmer will not know the private complex DBMS routines. He just has to create the embedded SQL source program code.