How You Can Use the Execute Immediate Statement
You can use the execute immediate statement to perform any of the following tasks:
• Execute a variety of SQL statements that the 4GL code constructs at runtime.
• Execute SQL statements that contain legal SQL syntax that is not recognized by the OpenROAD compiler, such as:
– Creating a database procedure
– Issuing statements to a non-Ingres server or a version of the Ingres server that supports different SQL syntax
The syntax of the execute immediate statement is:
execute immediate statement_string [into variable {, variable}]
statement_string
Specifies a string constant, a varchar expression, or a StringObject reference variable value. It must adhere to the following restrictions:
• It must evaluate to a single SQL statement.
• It cannot contain an open or fetch statement or any of the exceptions for the execute immediate statement.
Assume, for example, that you want to create a temporary table with a unique name at runtime. Do the following to create the table:
1. Declare two variables, one to contain the table name (tname) and one to contain the I parameter (vstring):
tname = varchar(32);
vstring = varchar(500);
2. Build the temporary table name from the user's login, followed by numbers:
select :user_name = dbmsinfo('username');
tname = user_name + '123';
3. Load the create table statement into vstring and run it using the execute immediate statement:
vstring = 'create table ' + tname +
'(name varchar(50), addr varchar(100))';
execute immediate :vstring;
When the program runs the execute immediate statement in Step 3, it executes the statement contained in vstring, creating the specified table.
For more examples of using the execute immediate statement, see the Language Reference Guide online help.
How You Can Use the Into Clause with a Select Statement
You must use the into clause if the statement_string is a select statement. When the statement is executed, the values retrieved by the select statement are placed in the specified variables. The data types of the variables must match those in the target list of the select statement. The number of columns selected must also match the number of targets specified.
For example, assume that your application contains the following code:
statement = 'Select title, vidno from video';
The following execute immediate statement runs the select statement and puts the values returned in the vidtitle and vidno variables:
execute immediate :statement into :vidtitle, :vidno;
If you do not know the names and data types of the target variables until runtime, create an SQLSelect or a query object to build the retrieval statement dynamically. For more information about dynamic query creation, see
How You Can Access a Database with DataStream Objectsvand
How You Can Use Query Objects.