5.1 Example - Executing a Non-query Statement Without Parameters

Table of Contents Previous Next


5 Building and Executing Dynamic SQL Statements : 5.1 Example - Executing a Non-query Statement Without Parameters

The following example demonstrates how to use the EXECUTE IMMEDIATE command to execute a SQL statement where the text of the statement is not known until you run the application. You cannot use EXECUTE IMMEDIATE to execute a statement that returns a result set. You cannot use EXECUTE IMMEDIATE to execute a statement that contains parameter placeholders.
The EXECUTE IMMEDIATE statement parses and plans the SQL statement each time it executes, which can have a negative impact on the performance of your application. If you plan to execute the same statement repeatedly, consider using the PREPARE/EXECUTE technique described in the next example.
The code sample begins by including the prototypes and type definitions for the C stdio, string, and stdlib libraries, and providing basic infrastructure for the program:
The example then sets up an error handler; ECPGPlus calls the handle_error() function whenever a SQL error occurs:

EXEC SQL CONNECT :argv[1];

Next, the program uses an EXECUTE IMMEDIATE statement to execute a SQL statement, adding a row to the dept table:
If the EXECUTE IMMEDIATE command fails for any reason, ECPGPlus will invoke the handle_error() function (which terminates the application after displaying an error message to the user). If the EXECUTE IMMEDIATE command succeeds, the application displays a message (ok) to the user, commits the changes, disconnects from the server, and terminates the application.
ECPGPlus calls the handle_error() function whenever it encounters a SQL error. The handle_error() function prints the content of the error message, resets the error handler, rolls back any changes, disconnects from the database, and terminates the application.

5 Building and Executing Dynamic SQL Statements : 5.1 Example - Executing a Non-query Statement Without Parameters

Table of Contents Previous Next