Preparing and Executing a Non-select Statement
The PREPARE and EXECUTE statements can also be used to execute dynamic non‑select statements. These two statements, working together, allow your program to save a statement string and execute it as many times as necessary. However, a prepared statement is discarded when the transaction in which it was prepared is rolled back or committed. Also, if a statement is prepared with the same name as an existing statement, the new statement supersedes the old statement.
The following example demonstrates how a runtime user can prepare (save) a dynamically specified OpenSQL statement and execute it a specific number of times:
read OpenSQL statement from terminal into buffer;
exec sql prepare s1 from :buffer;
read number in N
loop N times
exec sql execute s1;
end loop;
The next example illustrates a dynamically prepared query. This example creates a table whose name is the same as the user's name, and inserts into the table a set of rows with fixed‑typed parameters (the user's children):
get user name from terminal;
buffer = 'create table ' + user_name + '(child
character(15), age integer)';
exec sql execute immediate :buffer;
buffer = 'insert into ' + user_name + '(child, age)
values (?, ?)';
exec sql prepare s1 from :buffer;
read child's name and age from terminal;
loop until no more children
exec sql execute s1 using :child, :age;
read child's name and age from terminal;
end loop;
Some statements cannot be executed using PREPARE and EXECUTE, as described in
Dynamic OpenSQL Statements (see page
Dynamic OpenSQL Statements).