Embedded SQL

1. SQL provides a powerful declarative query language. However, access to a database from a general-purpose programming language is required because,
   • SQL is not as powerful as a general-purpose programming language. There are queries that cannot be expressed in SQL, but can be programmed in C, Fortran, Pascal, Cobol, etc.
   • Nondeclarative actions -- such as printing a report, interacting with a user, or sending the result to a GUI -- cannot be done from within SQL.
2. The SQL standard defines embedding of SQL as embedded SQL and the language in which SQL queries are embedded is referred as host language.
3. The result of the query is made available to the program one tuple (record) at a time.
4. To identify embedded SQL requests to the preprocessor, we use EXEC SQL statement:

    EXEC SQL embedded SQL statement END-EXEC
   
   

   Note: A semi-colon is used instead of END-EXEC when SQL is embedded in C or Pascal.

5. Embedded SQL statements: declare cursor, open, and fetch statements.

    EXEC SQL
   
   		 		 		 declare c cursor for
   
   		 		 		 select cname, ccity
   
   		 		 		 from deposit, customer
   
   		 		 		 where deposit.cname = customer.cname
   and deposit.balance > :amount
   
   		 END-EXEC
   
   

   where amount is a host-language variable.

    EXEC SQL open c END-EXEC
   
   

   This statement causes the DB system to execute the query and to save the results within a temporary relation.

   A series of fetch statement are executed to make tuples of the results available to the program.

    EXEC SQL fetch c into :cn, :cc END-EXEC
   
   

   The program can then manipulate the variable cn and cc using the features of the host programming language.

   A single fetch request returns only one tuple. We need to use a while loop (or equivalent) to process each tuple of the result until no further tuples (when a variable in the SQLCA is set).

   We need to use close statement to tell the DB system to delete the temporary relation that held the result of the query.

    EXEC SQL close c END-EXEC
   
   

6. Embedded SQL can execute any valid update, insert, or delete statements.
7. Dynamic SQL component allows programs to construct and submit SQL queries ar run time (see p. 147 of the textbook for details).
8. SQL-92 also contains a module language, which allows procedures to be defined in SQL (see pp. 147-148 of the textbook for details).