5. Working with Embedded SQL : Dynamic Programming : Execute a Dynamic Non-select Statement : Preparing and Executing a Non-select Statement
 
Share this page                  
Preparing and Executing a Non-select Statement
The PREPARE and EXECUTE statements can also execute dynamic non-select statements. These statements enable your program to save a statement string and execute it as many times as necessary. A prepared statement is discarded when the transaction in which it was prepared is rolled back or committed. If a statement with the same name as an existing statement is prepared, the new statement supersedes the old statement.
The following example demonstrates how a runtime user can prepare a dynamically specified SQL statement and execute it a specific number of times:
read SQL 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 following example creates a table whose name is the same as the user name, and inserts a set of rows with fixed-typed parameters (the user’s children) into the table:
get user name from terminal;
buffer = 'create table ' + user_name +
     '(child char(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;
A list of statements that cannot be executed using PREPARE and EXECUTE can be found in Prepare and Execute Statements (see page Prepare and Execute Statements).