Example 1: Using parameters for statement and cursor names
The following example shows two procedures from the online sample
program SQL$MULTI_STMT_DYN.SQLADA. These procedures show the use
of parameters for statement and cursor names.
.
.
.
-- This procedure prepares a statement for dynamic execution from the string
-- passed to it. This procedure can prepare any number of statements
-- because the statement is passed to it as the parameter, cur_procid.
procedure PREPARE_SQL is
CUR_CURSOR : string(1..31) := (others => ' ');
CUR_PROCID : integer := 0;
CUR_STMT : string(1..1024) := (others => ' ');
begin
-- Allocate separate SQLDAs for parameter markers (sqlda_in) and select list
-- items (sqlda_out). Assign the value of the constant MAXPARMS (set in the
-- declarations section) to the SQLN field of both SQLDA structures. SQLN
-- specifies to SQL the maximum size of the SQLDA.
sqlda_in := new sqlda_record;
sqlda_in.sqln := maxparms;
sqlda_out := new sqlda_record;
sqlda_out.sqln := maxparms;
-- Assign the SQL statement that was constructed in the procedure
-- CONSTRUCT_SQL to the variable cur_stmt.
cur_stmt := sql_stmt;
-- Use the PREPARE...SELECT LIST statement to prepare the dynamic statement
-- and write information about any select list items in it to sqlda_out.
-- It prepares a statement for dynamic execution from the string passed to
-- it. It also writes information about the number and data type of any
-- select list items in the statement to an SQLDA (specifically, the
-- sqlda_out SQLDA specified).
--
-- Note that the PREPARE statement could have prepared the statement without
-- writing to an SQLDA. Instead, a separate DESCRIBE...SELECT LIST statement
-- would have written information about any select list items to an SQLDA.
EXEC SQL PREPARE :cur_procid SELECT LIST INTO :sqlda_out FROM :cur_stmt;
case sqlca.sqlcode is
when sql_success => null;
when others => raise syntax_error;
end case;
-- Use the DESCRIBE...MARKERS statement to write information about any
-- parameter markers in the dynamic statement to sqlda_in. This statement
-- writes information to an SQLDA (specifically, the sqlda_in SQLDA
-- specified) about the number and data type of any parameter markers in
-- the prepared dynamic statement. Note that SELECT statements may also
-- have parameter markers.
EXEC SQL DESCRIBE :cur_procid MARKERS INTO sqlda_in;
case sqlca.sqlcode is
when sql_success => null;
when others => raise syntax_error;
end case;
-- If the operation is "Read," create a unique name for the cursor name
-- so that the program can pass the cursor name to the dynamic DECLARE
-- CURSOR statement.
if cur_op(1) = 'R' then
cur_cursor(1) := 'C';
cur_cursor(2..name_strlng) := cur_name(1..name_strlng - 1);
-- Declare the dynamic cursor.
EXEC SQL DECLARE :cur_cursor CURSOR FOR :cur_procid;
case sqlca.sqlcode is
when sql_success => null;
when others => raise syntax_error;
end case;
end if;
number_of_procs := number_of_procs + 1;
sqlda_in_array(number_of_procs) := sqlda_in;
sqlda_out_array(number_of_procs) := sqlda_out;
procedure_names(number_of_procs) := cur_name;
procedure_ids(number_of_procs) := cur_procid;
if cur_op(1) = 'R' then
cursor_names(number_of_procs) := cur_cursor;
end if;
exception
when syntax_error =>
sql_get_error_text(get_error_buffer,get_error_length);
put_line(get_error_buffer(1..integer(get_error_length)));
put("Press RETURN to continue. ");
get_line(terminal,release_screen,last);
new_line;
end PREPARE_SQL;
.
.
.
begin -- procedure body DISPLAY_DATA
-- Before displaying any data, allocate buffers to hold the data
-- returned by SQL.
--
allocate_buffers;
-- Allocate and assign SQLDAs for the requested SQL procedure.
--
sqlda_in := new sqlda_record;
sqlda_in := sqlda_in_array(stmt_index);
sqlda_out := new sqlda_record;
sqlda_out := sqlda_out_array(stmt_index);
cur_cursor := cursor_names(stmt_index);
-- Open the previously declared cursor. The statement specifies
-- an SQLDA (specifically, sqlda_in) as the source of addresses for any
-- parameter markers in the cursor's SELECT statement.
--
EXEC SQL OPEN :cur_cursor USING DESCRIPTOR sqlda_in;
case sqlca.sqlcode is
when sql_success => null;
when others => raise unexpected_error;
end case;
-- Fetch the first row from the result table. This statement fetches a
-- row from the opened cursor and writes it to the addresses specified
-- in an SQLDA (specifically, sqlda_out).
--
EXEC SQL FETCH :cur_cursor USING DESCRIPTOR sqlda_out;
case sqlca.sqlcode is
-- Check to see if the result table has any rows.
when sql_success => null;
when stream_eof =>
put_line("No records found.");
new_line;
when others => raise unexpected_error;
end case;
-- Set up a loop to display the first row, then fetch and display second
-- and subsequent rows.
rowcount := 0;
while sqlca.sqlcode = 0 loop
rowcount := rowcount + 1;
-- Execute the DISPLAY_ROW procedure.
display_row;
-- To only display 5 rows, exit the loop if the loop counter
-- equals MAXROW (coded as 5 in this program).
if rowcount = maxrows then exit; end if;
-- Fetch another row, exit the loop if no more rows.
EXEC SQL FETCH :cur_cursor USING DESCRIPTOR sqlda_out;
case sqlca.sqlcode is
when sql_success => null;
when stream_eof => exit;
when others => raise unexpected_error;
end case;
end loop;
-- Close the cursor.
EXEC SQL CLOSE :cur_cursor;
case sqlca.sqlcode is
when sql_success => null;
when others => raise unexpected_error;
end case;
exception
when unexpected_error =>
sql_get_error_text(get_error_buffer,get_error_length);
EXEC SQL ROLLBACK;
put_line("This condition was not expected.");
put_line(get_error_buffer(1..integer(get_error_length)));
put("Press RETURN to continue. ");
get_line(terminal,release_screen,last);
-- Stop and let the user look before returning.
skip;
put_line("Press RETURN to proceed. ");
get_line(terminal,release_screen,last);
end DISPLAY_DATA;