next up previous
Next: Other SQL Features Up: SQL Previous: Schema definition in SQL

Embedded SQL

  1. SQL provides a powerful declarative query language. However, access to a database from a general-purpose programming language is required because,
  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.


    declare c cursor for

    select cname, ccity

    from deposit, customer

    where deposit.cname = customer.cname and deposit.balance > :amount


    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).

next up previous
Next: Other SQL Features Up: SQL Previous: Schema definition in SQL

Osmar Zaiane
Fri May 22 19:39:12 PDT 1998