Dynamic SQL

Dynamic SQL

Our model of SQL embedded in a host language has been that of specific SQL queries and commands within a host-language program. An alternative style

Reading MovieExec tuples backwards

of embedded SQL has the statements themselves be computed by the host language. Such statements are not known at compile time, and thus cannot be handled by an SQL preprocessor or a host-language compiler.

An example of such a situation is a program that prompts the user for an SQL query, reads the query, and then executes that query. The generic interface for ad-hoc SQL queries that we assumed in The Database Language SQL is an example of just such a program; every commercial SQL system provides this type of generic SQL interface. If queries are read and executed at run-time, there is nothing that can be done at compile-time. The query has to be parsed and a suitable way to execute the query found by the SQL system, immediately after the query is read.

The host-language program must instruct the SQL system to take the character string just read, to turn it into an executable SQL statement, and finally to execute that statement. There are two dynamic SQL statements that perform these two steps.

1. EXEC SQL PREPARE, followed by an SQL variable V, the keyword FROM, and a host-language variable or expression of character-string type. This statement causes the string to be treated as an SQL statement. Presumably, the SQL statement is parsed and a good way to execute it is found by the SQL system, but the statement is not executed. Rather, the plan for executing the SQL statement becomes the value of V.

2. EXEC SQL EXECUTE followed by an SQL variable such as V in (1). This statement causes the SQL statement denoted by V to be executed.

Both steps can be combined into one, with the statement:


Followed by a string-valued shared variable or a string-valued expression. The disadvantage of combining these two parts is seen if we prepare a statement once and then execute it many times. With EXECUTE IMMEDIATE the cost of preparing the statement is borne each time the statement is executed, rather than borne only once, when we prepare it.

Example 1: In Figure 2 is a sketch of a C program that reads text from standard input into a variable query, prepares it, and executes it. The SQL variable SQLquery holds the prepared query. Since the query is only executed once, the line:


could replace lines (6) and (7) of Figure 2.

Preparing and executing a dynamic SQL query