Exec BladeLet

The Exec BladeLet is a UDR that provides dynamic SQL functionality within an SPL procedure. It is a pair of user-defined functions -- a simple UDF and an Iterator -- that take arbitrary SQL statements, execute them, and return a result to whatever invoked it in the first place. This BladeLet is also a useful example of how to issue and handle SQL queries using SAPI.


Introduction and Overview.

Often, it is desirable to execute a SQL query that is generated at run-time within the ORDBMS. For example, a developer may not know the name of the temporary table they wish to run the query against, or they might want to append predicates to a query. In external programs, this can be accomplished using the ESQL/C SQLCA and DESCRIPTOR facilities.

Unfortunately the INFORMIX Stored Procedure Language (SPL) does not support dynamic SQL. Queries must be hard-coded into the SPL logic.

The objective of the Exec BladeLet is to remedy this. Exec consists of some user-defined functions (UDFs) that take a SQL query as an argument, execute it, and return a result (the format of which varies depending on the function and the kind of query). The Exec functions can handle most Data Definition Language (DDL) statements, and all Data Manipulation Language (DML) queries.


Design Details.

There are three UDRs in the Exec BladeLet. Two of them are 'C' EXTERNAL FUNCTIONS that use the Server API (SAPI). These must be compiled into shared libraries on the target machine, and you need to declare them to the server using CREATE FUNCTION statements (which are shipped as part of the BladeLet). The third UDF is an SPL routine that uses the first two UDFs to do useful things; implement a general UDF that returns a MULTISET of rows. It is intended as an example.

The bulk of the 'C' code is to be found in ./src/exec.c and ./src/exec/h. This wad included makefiles for Unix and NT. The SQL registration script is ./install/register.sql, and there is a ./install/regression_tests.sql file that you can use to test any changes that you may be considering. Exec is a rather simpler beast than other Blades: there are no errors to register (all errors are generated directly from the code), and there is an explanation of each UDF's functionality included as part of the CREATE FUNCTION.

An important part of the Bladelet's code is the large scale testing you can do using the scripts in ./install. Whenever you change anything in this BladeLet, I recommend running the regression_tests.sql against an engine with several CPU-VPs active. While this is completing, set off a couple of mu_tests.sql in parallel.

The reason for this is that one of the trickiest aspects of developing this blade has been getting the memory management code correct. The Iterator function must hold memory allocated within SAPI between calls. This means that I must run most of the code at PER_COMMAND memory duration, which makes memory leaks a problem. Using a tail on the online.log, check for the allocation of additional memory segements. At the moment, the code is clean. Change with caution.


List of User-Defined Functions.


Terms and acronyms used by this tech note include:

Blade Set of semantically related extensions to the ORDBMS.
COLLECTION Non-first normal form object. That is, a set of data values that can be considered as a single data value for some purposes (variables). COLLECTIONS can also be thought of as small, in-memory, temporary tables for the purpose of querying.
Iterator An iterator is a special kind of UDF that returns more than one result. Implementing Iterators raises conceptual and engineering difficulties. This Bladelet contains an example of a quite complex Iterator.
User-defined Function (UDF) Module of procedural logic that extends SQL. This Bladelet included UDFs implemented in 'C' and SPL. Through out this document I use the term Routine synonymously with UDF.

Last updated 31-March-2000.