The first technique for sending SQL statements to the DBMS is embedded SQL. Because SQL does not use variables and control-of-flow statements, it is often used as a database sublanguage that can be added to a program written in a conventional programming language, such as C, COBOL or Fortran. This is a central idea of embedded SQL: placing SQL statements in a program written in a host programming language. The following techniques are used to embed SQL statements into a host language:
Two types of SQL can be used in an application program -- static SQL and dynamic SQL-- and each have their own advantages and disadvantages.
Static SQL statements are hardcoded in an application program when the source code is written. The source code is then processed using a SQL precompiler before it can be compiled and executed. The SQL precompiler evaluates the declared data types of all host defined variables and determines which data conversion methods need to be used when data is moved to and from the database. Additionally, the SQL precompiler performs error checking on each coded SQL statement and ensures that appropriate host variable data types are used for their respective table column values. Static SQL is called static because the SQL statements in the program do not change each time the program is run. Static SQL works well in many situations. In fact, it can be used in any application for which the data access can be determined at program design time. For example, an order entry program always uses the same statement to insert a new order, and an airline reservation system always uses the same statement to change the status of a seat from available to reserved. Each of these statements would be generalized through the use of host variables. Different values can be inserted in a sales order and different seats can be reserved. Because such statements can be hard-coded in the program, such programs have the advantage that statements need to be parsed, validated, and optimized only once, at compile time. This results in relatively fast code.
Although Static SQL statements are easy to use, they are limited because their format must be known in advance by the precompiler and because they can only work with host variables. There are many situations in which the content of an SQL statement is not known by the programmer in advance. For example, suppose a spreadsheet allows a user to enter a query, which the spreadsheet then sends to the DBMS to retrieve data. The contents of this query obviously cannot be known to the programmer when the spreadsheet program is written.
To solve this problem, the spreadsheet uses a form of embedded SQL called Dynamic SQL. Unlike static SQL statements, which are hard-coded in the program, Dynamic SQL statements can be built at run time and placed in a string host variable. They are then sent to the DBMS for processing. Because the DBMS must generate an access plan at run time for dynamic SQL statements, dynamic SQL is generally slower than static SQL. When a program containing dynamic SQL statements is compiled, the dynamic SQL statements are not stripped from the program, as in static SQL. Instead, they are replaced by a function call that passes the statement to the DBMS.
Since the actual creation of Dynamic SQL statements is based upon the flow of programming logic at application run time, they are more powerful than Static SQL statements. However, because the DBMS must go through each of the five steps of processing a SQL statement for each dynamic request, Dynamic SQL tends to be slower than Static SQL.