Executing an SQL Statement

Once a connection to a data source has been established, an application can execute SQL statements to interact with the databases managed by the data source. Executing an SQL statement involves the following steps.

Allocating a Statement Handle

Before executing an SQL statement, an application needs to allocate a statement handle by calling SQLAllocHandle(), passing SQL_HANDLE_STMT to the first argument and an existing connection handle to the second. A connection to a data source must already be established on the connection handle in order to allocate a statement handle on it.

Preparing an SQL Statement

An application can "prepare" an SQL statement before actually executing it. Preparing an SQL statement means SQL API parses the statement and stores the information necessary to execute it later. This process can also detect any errors that are in the statement, such as syntax errors and references to tables/columns that do not exist. An application can prepare a statement by calling SQLPrepare().

Executing an SQL Statement

Once an SQL statement has been prepared, an application can call SQLExecute() to perform the actual execution of the statement.

A prepared statement can be executed multiple times without being prepared again. This is useful when executing a statement with dynamic parameter binding. For details about dynamic parameter binding, see the Working with Parameters section.

If an application needs to execute the same SQL statement only once, the preparation and execution steps can be combined into a single call to SQLExecDirect(). SQLExecDirect() prepares and executes the specified SQL statement at once.

The following example illustrates how an application can allocate a statement handle and execute a statement that opens the NSFAWARDS database.

Example 1. Opening a database

#include "sqlrdm.h"

SQLRETURN runDbOperations (SQLHDBC hDbc)
{
    SQLRETURN rc;
    SQLHSTMT hStmt;

    /* Allocate a statement handle */
    rc = SQLAllocHandle (SQL_HANDLE_STMT, hDbc, &hStmt);
    if (SQL_SUCCEEDED (rc))
    {
        rc = SQLExecDirect (hStmt, (SQLCHAR *) "OPEN nsfawards", SQL_NTS);

        (void) SQLFreeHandle (SQL_HANDLE_STMT, hStmt);
    }

    return rc;
}

Committing or Rolling Back Transactions

Statements, such as INSERT, UPDATE and DELETE may make changes to the data stored in the data source. Those statements are called "data manipulation language (DML) statements." When executing a DML statement, SQL API automatically starts a transaction so that the changes can be committed (i.e. saved) or rolled back (i.e. aborted).

The following examples illustrate how an INSERT statement can be executed including transaction processing. It attempts to insert a new row into the PERSON table of the NSFAWARDS database.

Example 1. Inserting a new row

#include "sqlrdm.h"

SQLRETURN runDbOperations (SQLHDBC hDbc)
{
    SQLRETURN rc;
    SQLHSTMT hStmt;
    const char *pszStmt = "INSERT INTO person VALUES "
                          "'Houglum, Bill', 'M', '1'";

    /* Allocate a statement handle */
    rc = SQLAllocHandle (SQL_HANDLE_STMT, hDbc, &hStmt);
    if (SQL_SUCCEEDED (rc))
    {
        rc = SQLExecDirect (hStmt, (SQLCHAR *) "OPEN nsfawards", SQL_NTS);
        if (SQL_SUCCEEDED (rc))
        {
            /* Execute an INSERT statement */
            rc = SQLExecDirect (hStmt, (SQLCHAR *) pszStmt, SQL_NTS);

            if (SQL_SUCCEEDED (rc))
            {
                /* INSERT successful; commit the change */
                (void) SQLEndTran (SQL_HANDLE_DBC, hDbc, SQL_COMMIT);
            }
            else
            {
                /* INSERT failed; abort the change */
                (void) SQLEndTran (SQL_HANDLE_DBC, hDbc, SQL_ROLLBACK);
            }
        }
        (void) SQLFreeHandle (SQL_HANDLE_STMT, hStmt);
    }

    return rc;
}