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.

Back to top

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 bladelet 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.

Back to top

List of user-defined functions

• EXEC ( LVARCHAR ) RETURNS LVARCHAR

  This function takes an LVARCHAR that it treats as a SQL query. It executes the query and returns a single, LVARCHAR result string. Depending on what kind of SQL statement is submitted, Exec() returns a different result format.

  If the query is a Data Declaration Language expression (DDL) then Exec() either returns a string "OK", or it will generate an exception. For example:

  SELECT Exec(" CREATE TABLE Foo ( A Num, B Val, C SET(INTEGER NOT NULL)); ") FROM TABLE(SET{1});

  
  

  (expression)

  OK

  
  
  

  Data Manipulation Language (DML) expressions (more typically referred to as queries) return different result formats depending on whether they are SELECT queries or write queries ( INSERT , UPDATE , or DELETE ).

  Write queries either return a single result string which indicates how many rows were affected by the query, or they generate a SQL error. For example:

  SELECT Exec(" INSERT INTO Foo VALUES (1,'Hello',SET{1,2,3}); ") FROM TABLE(SET{1});

  
  

  (expression)

  1 rows affected

  
  
  

  SELECT Exec(" INSERT INTO Foo VALUES (2,'Good-bye',SET{4,5,6}); ") FROM TABLE(SET{1});

  
  

  (expression)

  1 rows affected

  
  
  

  SELECT Exec(" UPDATE Foo SET B = 'Zap!' WHERE A < 4; ") FROM TABLE(SET{1});

  
  

  (expression)

  2 rows affected

  
  
  

  EXECUTE FUNCTION Exec(" INSERT INTO Foo SELECT ( T1.Num * 100 + T2.Num * 10 + T3.Num ):: Integer::Num, ( T1.Val || ',' || T2.Val || ',' || T3.Val )::Val, SET{ T1.Num, T2.Num, T3.Num } FROM TABLE(SET{ROW(0,'Zero'),ROW(1,'One'),ROW(2,'Two'),ROW(3,'Three'), ROW(4,'Four'),ROW(5,'Five'),ROW(6,'Six'),ROW(7,'Seven'), ROW(8,'Eight'),ROW(9,'Nine') }::SET(ROW(Num INTEGER, Val LVARCHAR) NOT NULL)) T1, TABLE(SET{ROW(0,'Zero'),ROW(1,'One'),ROW(2,'Two'),ROW(3,'Three'), ROW(4,'Four'),ROW(5,'Five'),ROW(6,'Six'),ROW(7,'Seven'), ROW(8,'Eight'),ROW(9,'Nine') }::SET(ROW(Num INTEGER, Val LVARCHAR) NOT NULL)) T2, TABLE(SET{ROW(0,'Zero'),ROW(1,'One'),ROW(2,'Two'),ROW(3,'Three'), ROW(4,'Four'),ROW(5,'Five'),ROW(6,'Six'),ROW(7,'Seven'), ROW(8,'Eight'),ROW(9,'Nine') }::SET(ROW(Num INTEGER, Val LVARCHAR) NOT NULL)) T3;");

  
  

  (expression)

  1000 rows affected

  SELECT queries submitted through Exec return a single LVARCHAR result that is the public format of an unnamed ROW TYPE corresponding to the format of the query's return result. Such a string can be cast into a named ROW TYPE and then handled intelligently by the rest of the calling program.

  Because it is a simple UDF, Exec() can only return a single result. To return more than one row you need to use the Exec_For_Rows() UDF (see below.) If the query produces more than one row, Exec() returns as its result data from the very first row. Then it terminates the query.

  SELECT Exec("SELECT COUNT(*) FROM Foo;") FROM TABLE(SET{1});

  
  

  (expression)

  ROW(1003.00000000)

  
  
  

  SELECT Exec("SELECT * FROM Foo;") FROM TABLE(SET{1});

  
  

  (expression)

  ROW(1,'Zap!',SET{1,2,3})

  
  
  

  Although Exec() returns a LVARCHAR result, this can easily be cast into a named ROW TYPE with an equivalent structure. This is likely to be very valuable in most practical applications.

  CREATE ROW TYPE Named_Row_Type ( A INTEGER, B VARCHAR(32), C SET(INTEGER NOT NULL) ); SELECT Exec("SELECT * FROM Foo WHERE A = 3;") ::Named_Row_Type FROM TABLE(SET{1});

  
  

  (expression)

  ROW(1,'Zap!',SET{1,2,3})

  
  
  

  Note that although these two examples appear identical the first is simply a string, while the result of the second query is in fact an instance of the Named_Row_Type ROW TYPE (which is then turned back into a string). When using the two UDFs in another SPL routine, this is very useful.

  As with any UDF, the Exec() function can be included in a SQL query's WHERE or SELECT list, or you can EXECUTE FUNCTION on it directly. Note that there are certain limits on the DROP statement, which cannot generally be issued inside a SELECT query, and that write queries in a SELECT statement have odd consequences on transactions. Use these with caution!

  
  
  
• EXEC_FOR_ROWS ( LVARCHAR ) RETURNS LVARCHAR WITH ( ITERATOR )

  The Exec_For_Rows() UDF is an Iterator, which means that it can return more than one result row. Of course, it only does so when it is asked to execute a SELECT . Otherwise, it behaves exactly as the Exec() UDF. For example:

  EXECUTE FUNCTION Exec_For_Rows("SELECT * FROM Foo WHERE A IN ( 1,2,3,4);") ;

  
  

  (expression)	ROW(1,'Zap!',SET{1,2,3})
  (expression)	ROW(2,'Zap!',SET{4,5,6})
  (expression)	ROW(3,'Stay Here',SET{7,8,9})
  (expression)	ROW(1,'Zero,Zero,One',SET{0,1})
  (expression)	ROW(2,'Zero,Zero,Two',SET{0,2})
  (expression)	ROW(3,'Zero,Zero,Three',SET{0,3})
  (expression)	ROW(4,'Zero,Zero,Four',SET{0,4})
  (expression)	ROW(1,'Zero,Zero,One',SET{0,1})
  (expression)	ROW(2,'Zero,Zero,Two',SET{0,2})
  (expression)	ROW(3,'Zero,Zero,Three',SET{0,3})
  (expression)	ROW(4,'Zero,Zero,Four',SET{0,4})

  
  
  

  Of course, being an iterator limits the ways in which such a UDR can be used. It can't be used in another SQL query, for example. In fact, about the only place it can be used (at the moment) is inside an SPL routine. Mind you, you can do a lot with it there. And shortly you will be able to put Iterators in the FROM clause of a query, so that ought to help.

  
  
  
• EXEC_FOR_MSET ( LVARCHAR ) RETURNS MULTISET(LVARCHAR NOT NULL)

  The Exec_for_MSet() UDF is an SPL routine that uses the Exec_For_Rows() UDF introduced above. Instead of returning a set of rows as an iterator, or a single row as the Exec() UDF does, this UDF collects the results of the SQL query together into a single object: a multi-set.

  CREATE FUNCTION Exec_for_MSet ( Arg1 lvarchar ) RETURNS MULTISET( LVarchar NOT NULL) DEFINE msLvRetVal MULTISET( LVARCHAR NOT NULL ); DEFINE lvIter LVARCHAR; FOREACH EXECUTE FUNCTION Exec_for_Rows ( Arg1 ) INTO lvIter INSERT INTO Table(msLvRetVal) VALUES ( lvIter ); END FOREACH; RETURN msLvRetVal; END FUNCTION EXECUTE FUNCTION Exec_For_Mset ( " SELECT DISTINCT A FROM Foo WHERE A < 20 AND 7 IN C; " );

  
  

  (expression)

  MULTISET{'ROW(3)','ROW(7)','ROW(17)'}

  
  
  

  Why is this interesting? Well for one thing, it lets you create a new data type called SQL (which is simply a distinct type of LVARCHAR). The results of Exec_For_Mset() run over a SQL query is a "relation" too. This raises all kinds of intriguing design possibilities.

Back to top

Glossary

Terms and acronyms used by this tech note include:

• Bladelet
  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.

Resources

Learn

• developerWorks Information Management Informix zone: Expand your skills on all the IBM Informix. IBM Informix software delivers superior database performance for transaction intensive environments.
  
  
• developerWorks Information Management zone: Expand your skills on all the IBM Information management products.
  
  
• Stay current with developerWorks technical events and Webcasts.
  
  

Get products and technologies

• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Comments

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction and overview
• Design details
• List of user-defined functions
• Glossary
• Resources
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers