SQL statements that are not known at compile time

An SQL statement that is not known at compile time is usually one that the user enters in an interactive application.

When you write an interactive database-query application like DB-Access, you do not know in advance which databases, tables, or columns the user wants to access, or what conditions the user might apply in a WHERE clause. If the application interprets and runs SQL statements that the user enters, this application does not know what type of information is to be stored in host variables until after the user enters the statement at run time.

For example, if a program contains the following DELETE statement, you know the number of values and the data types that you receive, based on the affected columns:

DELETE FROM customer WHERE city = ? AND lname > ?

You can define host variables whose data types are compatible with the data they receive. However, suppose your program provides a prompt for the user such as:

Enter a DELETE statement for the stores7 database:

In this case, you do not know until run time either the name of the table on which the DELETE takes place or the columns that are listed in the WHERE clause. Therefore, you cannot declare the necessary host variables.

You can dynamically determine a prepared SQL statement and information about the tables and columns it accesses with the DESCRIBE statement and the dynamic-management structures.