Differences between C UDRs and UDRs written in SPL

C UDRs are compiled to machine code. You can use the C programming language to manipulate data at the level of bytes and bits and access data in efficient data structures such as array, hash, linked list, or tree.

DAPI provides many functions that are not available in SPL or SQL, including the Informix® ESQL/C function library for manipulating data in C. Any C library that follows the guidelines of the DataBlade API can also be included. For example, a C UDR has random access to data within a smart large object.

C UDRs can dynamically build arbitrary SQL query strings at runtime and execute them. In SPL, the CLOSE, DECLARE, EXECUTE IMMEDIATE, FETCH, FREE, OPEN, and PREPARE statements support runtime replacement of question mark ( ? ) placeholders with specific input parameter values, but some dynamic SQL syntax features and some cursor management statements of ESQL/C are not supported in SPL. For example, HCL Informix Version 11.50 only supports sequential cursors. C UDRs can have other types of cursors such as scroll and hold. The FOREACH statement of SPL declares a direct cursor, but its associated SQL statement must have hardcoded names of database objects, such as tables, columns, and functions because SPL variables can only represent values, not SQL identifiers. (The EXEC Bladelet also supports some dynamic SQL features in SPL routines, but its programming interface is more complex and less intuitive than when SPL is used directly.)

You can use C UDRs to define user-defined data types (UDTs), user-defined aggregates, and user-defined access methods (for example, to access stream data outside HCL Informix) to return data on the selectivity and cost of another UDR to the optimizer and to access data of a ROW type that was unknown at compile time.

For example, in SPL it takes only a few lines to execute SQL and fetch results in a loop. In C, it takes many lines to define and prepare the statement, execute it with a cursor, fetch rows, fetch columns, close the cursor, close the statement, and check for errors during the process.

In SPL, any embedded SQL statements are parsed, prepared, and optimized when the SPL routine is created and compiled. In a C UDR, if you want to execute SQL repeatedly and efficiently, you must prepare it explicitly. (SPL can only use the PREPARE statement to prepare a query or a call to a routine, but it can then use EXECUTE IMMEDIATE to execute the prepared statement.)

A C UDR must follow the documented guidelines of the DataBlade API in areas that include yielding the processor, allocating memory and variables, performing I/O, and making system calls that block. Failure to follow the guidelines can cause problems for the HCL Informix instance, although you can mitigate this risk by registering the C UDR to run on a user-defined VP class.

Beginning with HCL Informix Version 11.50, SPL supports only an EXECUTE IMMEDIATE noncursor statement that does not return any row. However, ESQL/C also supports the noncursor EXECUTE ... INTO statement. The query in this statement can return a single row that is assigned to the SPL variables listed after the INTO clause. Although SPL in HCL Informix Version 11.50, or later does not support multiple statements within the noncursor EXECUTE IMMEDIATE statement, this restriction reduces the risk of the insertion of unwanted SQL statements.