[Info-Ingres] ingres nested procedure

[Peter Gale]

Hi DT,

Sorry, you cant do that. From the SQL reference manual

Row producing procedures must only be called directly from an embedded SQL
host program (not using dynamic SQL, a terminal monitor, or by nesting a
call
in another database procedure).

PG

On 29/11/2007, DT <pwadas at jewish.org.pl> wrote:
>
> Hello,
> I'm using ingres 9.2.0.
>
> As explained on
>
> http://docs.ingres.com/sqlref/ExecuteProcedure#o1541
>
> "Database procedures can be executed from interactive SQL (the
> Terminal Monitor), an embedded SQL program, or from another database
> procedure. The first execution of the database procedure can take
> slightly longer than subsequent executions. For the first execution,
> the DBMS Server must create a query execution plan."
>
> The question is how to create a procedure, which
> executes another procedure ?
>
> I got e.g.
>
> EXEC SQL CREATE PROCEDURE bb RESULT ROW(INTEGER) AS
>   DECLARE
>                 retrow INTEGER;
>   BEGIN
>                 retrow = 10;
>                 RETURN ROW (:retrow);
>                 RETURN;
>   END;
>
> and then
>   EXEC SQL BEGIN DECLARE SECTION;
>         int i;
>   EXEC SQL END DECLARE SECTION;
>   EXEC SQL EXECUTE PROCEDURE bb RESULT ROW (:i);
>   EXEC SQL BEGIN;
>         printf("\nXXX %d\n", i);
>   EXEC SQL END;
>
> And this works as expected.
>
> Now, I want to have another procedure "cc", which
> should execute bb and use its results...
>
> EXEC SQL CREATE PROCEDURE cc RESULT ROW(INTEGER) AS
>   DECLARE
>                 another_ret INTEGER;
>   BEGIN
>                 EXECUTE PROCEDURE bb RESULT ROW (:i);
>                 BEGIN
>                       bb = :i;
>                 END;
>                 RETURN ROW (:retrow);
>                 RETURN;
>   END;
>
> and creating such procedure does not work.
> I tried also with WHILE and FOR-EndFor loops - no success.
> Aborting because of error: E_US09C4 (illegal syntax)
>
> Thanks in advance,
> DT.
> _______________________________________________
> Info-Ingres mailing list
> Info-Ingres at kettleriverconsulting.com
> http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres
>



-- 
Peter Gale
pgale61 at gmail.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.kettleriverconsulting.com/mailman/private/info-ingres/attachments/20071129/901d27d3/attachment.html