Bill,

You've got a couple of problems. One is that there are two types of
stored procedures, executable and selectable. The distinction is subtle
and in some cases a single procedure can be used in either mode, but to
do so is to play with fire.

Essentially, an executable stored procedure returns its declared return
values once - what SQL would call a row. A selectable stored procedure
returns a java result set or a SQL table. The normal tip-off is that a
selectable procedure has a loop that contains a suspend statement.

Your procedure is a select procedure, though it works as an execute
procedure in mode 2. My first reaction was that it was designed as a
hybrid - intended to be invoked with a select in mode 1 and an execute
in mode 2. If that were the case, the "if" outside the loop would exit
in mode 1 rather than conditionally suspending in mode 2.

For better or for worse, and for reasons of backward compatibility,
Firebird allows hybrid procedures - meaning that execute procedure and
select both work on all procedures - they just get unexpected results
when used on the wrong type. *

If you use select on a purely execute procedure - one with no suspend at
all - you get no results. If you use execute in a select procedure -
one with a loop - you get the first row of results and nothing further.

Execute procedures should be invoked with execute procedure, and are
typically used with an execute immediate in DSQL. Select procedure
should be invoked with a select statement and require a fetch loop in DSQL.

Another problem is that your program uses execute procedure syntax,
fails to print the output from that statement, then calls fetch after
the single row of data is gone. Change this line:

sprintf (sql, "execute procedure factorial (?, ?);");

to this:
sprintf (sql, "select * from factorial (?, ?);");

and things will work better.

Regards,


Ann