Databases Reference
In-Depth Information
This will finally generate an executable access plan in the package. Once that is
done, we can execute the prepared SQL statement or declare a cursor to handle
the result set.
For example, let us say we wanted to delete a record from the INVENTORY
table. Once we prepare the SQL statement, we can execute the statement
multiple times using different parameter marker values but the same application
package.
Example 4-20 Preparing a DELETE statement
strncpy(deleteStmt,"delete from inventory where PID=?",
sizeof(deleteStmt));
EXEC SQL PREPARE stmt FROM :deleteStmt;
strncpy(value," 100-100-01",sizeof(value));
EXEC SQL EXECUTE stmt USING :value;
strncpy(value," 100-101-01",sizeof(value));
EXEC SQL EXECUTE stmt USING :value;
EXEC SQL COMMIT;
Assuming we have declared the deleteStmt and value host variables in a
DECLARE section, the code fragment in Example 4-20 prepares a statement
with a single parameter marker. The prepared statement then gets executed
multiple times using different data values. It is important to remember that a
COMMIT must be issued in order for the changes to the database to occur.
You might notice that preparing the DELETE statement in Example 4-20 is quite
unnecessary, because it can be done using static embedded SQL and host
variables since we know the complete SQL statement. It can be rewritten as
Example 4-21.
Example 4-21 Rewriting Example 4-22 as a static SQL statement
strncpy(value," 100-100-01",sizeof(value));
EXEC SQL DELETE FROM INVENTORY WHERE PID=:value;
strncpy(value," 100-101-01",sizeof(value));
EXEC SQL DELETE FROM INVENTORY WHERE PID=:value;
EXEC SQL COMMIT;
If a statement can be written as static SQL, you might want to consider avoiding
preparing a statement dynamically using parameter maker to avoid run-time
costs incurred with a PREPARE. However, if static SQL is run on databases,
which are continuously changing (which means the statistics keep changing),
dynamic SQL might be more suitable. Also, dynamic SQL offers more flexibility
than static SQL has to offer. In your application development, you need to keep
these things in mind and choose your approaches wisely.
Search WWH ::
Custom Search