Examples: Execute
1. Although the COMMIT statement can be prepared, once the statement is executed, the prepared statement becomes invalid. For example, the following code causes an error on the second execute statement:
statement_buffer = 'commit';
exec sql prepare s1 from :statement_buffer;
process and update data;
exec sql execute s1; /* Once committed, 's1' is lost */
process and update more data;
exec sql execute s1;
/* 's1' is NOT a valid statement name */
2. When leaving an application, each user deletes all their rows from a working table. User rows are identified by their different access codes. One user may have more than one access code.
read group id from terminal;
statement_buffer = 'delete from ' + group_id + '
where access_code = ?';
exec sql prepare s2 from :statement_buffer;
read access_code from terminal;
loop while (access_code <> 0)
exec sql execute s2 using :access_code;
read access_code from terminal;
end loop;
exec sql commit;
3. This example uses the OpenSQL forms system and Dynamic OpenSQL. The program reads the forms descriptions using the formdata statement and then uses that information to fill an input SQLDA for a variety of statements. For details about forms programming, see the Forms-based Application Development Tools User Guide.
In preparation, the program must allocate a large local SQLDA, called local_sqlda. At the start of form display, the program must retrieve descriptive information into the local SQLDA. The form name is only known at runtime through a command line flag.
exec frs formdata :form_name;
exec frs begin;
Using inquire_frs statements, retrieve the type
and length information from the form and fill the
corresponding element in the sqlvar. For each
field on the form set the sqltype, sqllen and
sqldata fields. If the type is negative (nullable)
set the sqlind field too.
Build 3 dynamic statements into 3 statement
buffers to execute the insert, update and delete
operations, using the field names returned by inquire_frs.
exec frs end;
At this point, the program has built a SQLDA that it will use for input, and three statement buffers, each with a full list of field names and parameter markers. The insert statement buffer, insert_buffer, may look like:
'insert into table1 (field1, field2) values (?, ?)'
while the delete statement buffer, delete_buffer, may look like:
'delete from table1 where field1 = ? and field2 = ?'
Now prepare the statements:
exec sql prepare insert_stmt from :insert_buffer;
exec sql prepare update_stmt from :update_buffer;
exec sql prepare delete_stmt from :delete_buffer;
Run the form allowing the user to enter data and execute an operation. Supply the menu items, Insert, Update, and Delete, as well as others. For example:
...
exec frs activate menuitem 'Insert';
exec frs begin;
Get values from the form and point the sqldata and
sqlind fields of local_sqlda to those values;
exec sql execute insert_stmt
using descriptor :local_sqlda;
exec frs end;
...