Considerations for mixing embedded SQL and CLI

You can use CLI in conjunction with embedded static SQL in an application. Consider the scenario where the application developer wishes to take advantage of the ease of use provided by the CLI catalog functions and maximize the portion of the application's processing where performance is critical. In order to mix the use of CLI and embedded SQL, the application must comply with the following rules:

• All connection management and transaction management must be performed completely using either CLI or embedded SQL - never a mixture of the two. Two options are available to the application:
  • it performs all connects and commits/rollbacks using CLI calls, and then calls functions written using embedded SQL;
  • or it performs all connects and commits/rollbacks using embedded SQL, and then calls functions that use CLI APIs, notably, a null connection.
• Query statement processing cannot straddle CLI and embedded SQL interfaces for the same statement. For example, the application cannot open a cursor using embedded SQL, and then call the CLI SQLFetch() function to retrieve row data.