Stored Procedures

With IBM^® Informix^® database servers, you can write stored procedures with the SPL (Stored Procedure Language) or with an external language in C or JAVA.

If you plan to support different types of database servers, you must be aware that each DB vendor has defined its own stored procedure language. In such cases, you may consider writing most of your business logic in BDL, and implementing only some stored procedures in the database, mainly to get better performance or to use database features that only exist with stored procedures.

Genero BDL partially supports SP creation, but has full support of SP invocation:

• The Genero BDL static SQL syntax does not include CREATE FUNCTION and CREATE PROCEDURE with a body block. However, you can create stored procedures with an body block by using dynamic SQL (EXECUTE IMMEDIATE), or with CREATE PROCEDURE and the FROM filename clause, which is supported by Genero BDL static SQL.
• The EXECUTE FUNCTION or EXECUTE PROCEDURE instruction is not allowed in the static SQL syntax. To invoke a stored procedure with Informix, you must use the PREPARE instruction, followed by EXECUTE or OPEN. The PREPARE instruction must initiate the EXECUTE FUNCTION/PROCEDURE instruction.

For more details about stored procedure invocation, see SQL Programming.