Embedded SQL

From Wikipedia, the free encyclopedia

Embedded SQL is a method of combining the computing power of a programming language (like C/C++, Pascal, etc.) and the database manipulation capabilities of SQL.

Embedded SQL statements are SQL statements written within application programming languages and preprocessed by an SQL preprocessor before the application program is compiled. There are two types of embedded SQL: static and dynamic.

The SQL standard defines embedding of SQL as embedded SQL and the language in which SQL queries are embedded is referred as host language.

[edit] Notes

• 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.
• The SQL standard defines embedding of SQL as embedded SQL and the language in which SQL queries are embedded is referred as host language.
• The result of the query is made available to the program one tuple (record) at a time.
• 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.

• 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

• Embedded SQL can execute any valid update, insert, or delete statements.
• Dynamic SQL component allows programs to construct and submit SQL queries at run time
• SQL-92 also contains a module language, which allows procedures to be defined in SQL.

Embedded SQL statements are SQL statements written within application programming languages such as C and preprocessed by an SQL preprocessor before the application program is compiled. There are two types of embedded SQL: static and dynamic.