--/****************************************************************\
--** filename     * procedur.sql

--** description * Stored procedure creation samples.

--** Copyright (C) Solid Information Technology Ltd 1997
--\****************************************************************/

-- A simple calculator procedure.

"create procedure calc(i1 float, op char(1), 
	i2 float) 
	returns (result float)
begin
	declare i integer;

	if op = '+' then
		result := i1 + i2;
	elseif op = '-' then
		result := i1 - i2;
	elseif op = '*' then
		result := i1 * i2;
	elseif op = '/' then
		result := i1 / i2;
	else
		result := 'Error: illegal op';
	end if
end";

-- Test calc procedure
call calc(1,'/',3);


-- Procedure returning backups created from this database.

"create procedure show_backups
	returns (backup_number varchar, date_created varchar)
begin
-- First set action for failing statements.
	exec sql  whenever sqlerror rollback, abort;

-- Prepare and execute the select statement
	exec sql prepare sel_cursor select 
		replace(property, 'backup ', ''),
		substring(value_str, 1, 19) from sys_info
		where property like 'backup %';
	exec sql execute sel_cursor into (backup_number, date_created);

-- Fetch first row;
	exec sql fetch sel_cursor;
-- Loop until end of table
	while sqlsuccess loop
-- Return the fetched row 
		return row;
-- Fetch next
		exec sql fetch sel_cursor;
	end loop;
end";

call show_backups;
		
