The use of dynamic SQL statements has many characteristics in common with cursors, with the additional complexity that temporary stored procedures are sometimes placed into Adaptive Server. The use of dynamic SQL is not recommended in transactional applications, but if they are used, the following guidelines must be adhered to:
In Embedded SQL use “Method 3: Prepare and Fetch with a Cursor” (see the ESQL document or a description of this method) if possible. When this method is used, Embedded SQL places information in the system which allows the XA Interface to locate and deallocate all dynamic SQL and cursors.
In all other cases, the dynamic SQL statements and all associated cursors must be closed and deallocated to avoid adverse effects on other transactions. Any associated Client-Library command structures should be dropped to avoid memory leaks. See the Open Client and ESQL documentation for information on how to drop these command structures.
