5.2 Example - Executing a Non-query Statement with a Specified Number of Placeholders

Table of Contents Previous Next


5 Building and Executing Dynamic SQL Statements : 5.2 Example - Executing a Non-query Statement with a Specified Number of Placeholders

To execute a non-query command that includes a known number of parameter placeholders, you must first PREPARE the statement (providing a statement handle), and then EXECUTE the statement using the statement handle. When the application executes the statement, it must provide a value for each placeholder found in the statement.
When an application uses the PREPARE/EXECUTE mechanism, each SQL statement is parsed and planned once, but may execute many times (providing different values each time).
The code sample begins by including the prototypes and type definitions for the C stdio, string, stdlib, and sqlca 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:
Next, the program uses a PREPARE statement to parse and plan a statement that includes three parameter markers - if the PREPARE statement succeeds, it will create a statement handle that you can use to execute the statement (in this example, the statement handle is named stmtHandle). You can execute a given statement multiple times using the same statement handle.
After parsing and planning the statement, the application uses the EXECUTE statement to execute the statement associated with the statement handle, substituting user-provided values for the parameter markers:
If the EXECUTE 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 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.2 Example - Executing a Non-query Statement with a Specified Number of Placeholders

Table of Contents Previous Next