Embedded SQL

Embedded SQL is a method of combining the computing power of a high-level language like C/C++ and the specialized database manipulation capabilities of SQL. It allows for SQL statements to be embedded within a procedural programming language.

In embedded SQL, SQL statements are embedded within a program written in another language (the host language), such as C, C++, Java, COBOL, or Fortran. These embedded statements are then processed by a precompiler, which is a tool provided by the database manufacturer, that transforms the embedded SQL and host language code into a pure host language program. After precompilation, the code can be compiled and run as a normal application in the host language.

An important part of embedded SQL is the use of SQL descriptors, also known as SQLDAs (SQL Descriptor Areas). These are data structures that provide the flexibility to describe the number, type, and other properties of placeholders and query result columns at runtime. SQLDAs make it possible for an application to process SQL statements where the number or type of variables is not known until the program is run.

Here's an example of what embedded SQL in C might look like:

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

void main() {
    /* Declare a host variable*/
    char employee_name[50];

    /* Connect to the database */
    EXEC SQL CONNECT TO my_database USER my_user USING my_password;
    
    /* Declare a cursor for retrieving employee names */
    EXEC SQL DECLARE emp_cursor CURSOR FOR
        SELECT name FROM employees;

    /* Open the cursor */
    EXEC SQL OPEN emp_cursor;

    printf("Employee Names:\n");

    /* Fetch rows from the result set one at a time */
    while (1) {
        EXEC SQL FETCH emp_cursor INTO :employee_name;
        if (sqlca.sqlcode != 0) {
            break;
        }
        printf("%s\n", employee_name);
    }

    /* Close the cursor and disconnect from the database */
    EXEC SQL CLOSE emp_cursor;
    EXEC SQL DISCONNECT my_database;
}

In this example, the EXEC SQL directive is used to denote the beginning of an SQL statement. The :employee_name syntax is used to denote a host variable (a variable in your program) in an SQL statement.

Embedded SQL allows programmers to use the full power of their procedural programming language for computations, loops, conditional processing, and other complex tasks, while still using SQL for what it excels at: querying and manipulating data in a database.