In most database applications, the operations to be performed on the database are known beforehand. When you are writing an application, you probably know in advance the names of the tables, and the number and names of attributes in each table. This knowledge allows you to write the exact SQL statements required by the application, and they do not change while the application is running. In such an application, static SQL statements (i.e., statements that do not change) are embedded in the application, and the Precompiler converts them to C routines.
However, under some circumstances, you may not know the exact composition of the SQL statements before precompilation. If you are writing a general-purpose application, for example, you may not want to make any assumption on the number of attributes in a table, or the criteria for selecting records. Such an application must be able to build and process the SQL statements at execution time. Since these statements can change while the application is running, they are dynamic SQL statements. These statements cannot be precompiled to C routines. They are parsed and executed while the application is running.
Commands described in the previous chapter are the static SQL commands. The dynamic SQL commands are described separately under the section "Dynamic SQL Command Reference".
The composition of static SQL statements are known at precompiled time, allowing the Precompiler to parse the statements and replace them by C routines. While the structure of the statements cannot change at execution time, the names of tables (or views) and attributes can change, if the embedded SQL statements use variables instead of hard-coding the table and attribute names. You can also use variables in WHERE clauses, so that attributes and the values they are compared to can be determined at execution time. No parsing is performed at execution time.
For example, the following statement can be embedded using static SQL. (Variable names are preceded with ":")
SELECT :att1, :att2 FROM :tab1 WHERE :att1 = :val1;
But if, for instance, you do not know the number of attributes to be accessed or the number of tables to join together, you will need to use dynamic SQL statements.
Applications using dynamic SQL are more versatile than those using static SQL. Since the dynamic SQL statements are created at execution time, they can change while the application is running.
Dynamic SQL allows statements to be built from strings at run time. For example, a SELECT statement can be built from strings containing fragments of the statement, part of which could be entered by the user from the keyboard, allowing the user to specify the attributes to select or the WHERE clause.
While dynamic SQL provides flexibility, building the SQL statements may require complex coding. As dynamic SQL statements are parsed at run time, they usually require more processing time than static SQL statements.
Use static SQL if all the following conditions apply:
If any of the above conditions do not apply, use dynamic SQL.
Note that an application can use a mix of static and dynamic commands.
In static SQL, the C data types for embedded variables have a corresponding Empress data type. If you use the incorrect data type, you will obtain wrong results. For example, if you use a 4-byte C int variable to read an Empress INTEGER value (which consists of 2 bytes), the value read will be incorrect.
In dynamic SQL, Empress will perform any data type conversion required. For example, you can use a C float variable to assign values to a DECIMAL attribute. Empress will attempt to convert the float value. The conversion may fail, if the value is outside the valid range for the attribute. However, the data type for a control variable must always be a C short integer.
You can use character strings for retrieving or assigning values to any of the Empress data types. In this case, Empress will use the external format for the data.
The following keywords (spelled in any combination of upper- and lowercase letters) cannot be used as the names of embedded SQL variables. They can be used however for any other purpose, for example, regular C variables, function names, etc. For a complete list of all reserved keywords, please refer to our Reserved Keyword document.
In addition, it is strongly advised that developers do not use the following standard SQL keywords:
| type indicator length name data |
While these are not reserved keywords currently in Empress embedded SQL, it is expected they may be in the future.