ODBC Driver

Step 1: Connect to the Data Source

The first step in any application is to connect to the data source. This phase, including the functions it requires, is shown in the following illustration.

The first step in connecting to the data source is to load the Driver Manager and allocate the environment handle with SQLAllocHandle. For more information, see Allocating the Environment Handle.

The application then registers the version of ODBC to which it conforms by calling SQLSetEnvAttr with the SQL_ATTR_APP_ODBC_VER environment attribute. For more information, see Declaring the Application’s ODBC Version and Backward Compatibility and Standards Compliance.

Next, the application allocates a connection handle with SQLAllocHandle and connects to the data source with SQLConnect, SQLDriverConnect, or SQLBrowseConnect. For more information, see Allocating a Connection Handle and Establishing a Connection.

The application then sets any connection attributes, such as whether to manually commit transactions.

Step 2: Initialize the Application

The second step is to initialize the application, as shown in the following illustration. Exactly what is done here varies with the application.

At this point, it is common to use SQLGetInfo to discover the capabilities of the driver. For more information, see Considering Database Features to Use.

All applications need to allocate a statement handle with SQLAllocHandle, and many applications set statement attributes, such as the cursor type, with SQLSetStmtAttr.

Step 3: Build and Execute an SQL Statement

The third step is to build and execute an SQL statement, as shown in the following illustration. The methods used to perform this step are likely to vary tremendously. The application might prompt the user to enter an SQL statement, build an SQL statement based on user input, or use a hard-coded SQL statement.

If the SQL statement contains parameters, the application binds them to application variables by calling SQLBindParameter for each parameter. For more information, see Statement Parameters.

After the SQL statement is built and any parameters are bound, the statement is executed with SQLExecDirect. If the statement will be executed multiple times, it can be prepared with SQLPrepare and executed with SQLExecute. For more information, see Executing a Statement.

The application might also forgo executing an SQL statement altogether and instead call a function to return a result set containing catalog information, such as the available columns or tables. For more information, see Uses of Catalog Data.

The application’s next action depends on the type of SQL statement executed.

Step 4a: Fetch the Results

The next step is to fetch the results, as shown in the following illustration.

If the statement executed in “Step 3: Build and Execute an SQL Statement” was a SELECT statement or a catalog function, the application first calls SQLNumResultCols to determine the number of columns in the result set. This step is not necessary if the application already knows the number of result set columns, such as when the SQL statement is hard-coded in a vertical or custom application.

Next, the application retrieves the name, data type, precision, and scale of each result set column with SQLDescribeCol. Again, this is not necessary for applications such as vertical and custom applications that already know this information. The application passes this information to SQLBindCol, which binds an application variable to a column in the result set.

The application now calls SQLFetch to retrieve the first row of data and place the data from that row in the variables bound with SQLBindCol. If there is any long data in the row, it then calls SQLGetData to retrieve that data. The application continues to call SQLFetch and SQLGetData to retrieve additional data. After it has finished fetching data, it calls SQLCloseCursor to close the cursor.

For a complete description of retrieving results, see Retrieving Results (Basic) and Retrieving Results (Advanced).

The application now returns to “Step 3: Build and Execute an SQL Statement” to execute another statement in the same transaction; or proceeds to “Step 5: Commit the Transaction” to commit or roll back the transaction.

Step 4b: Fetch the Row Count

The next step is to fetch the row count, as shown in the following illustration.

If the statement executed in Step 3 was an UPDATE, DELETE, or INSERT statement, the application retrieves the count of affected rows with SQLRowCount. For more information, see Determining the Number of Affected Rows.

The application now returns to step 3 to execute another statement in the same transaction or proceeds to step 5 to commit or roll back the transaction.

Step 5: Commit the Transaction

The next step is to commit the transaction, as shown in the following illustration.

The fifth step is to call SQLEndTran to commit or roll back the transaction. The application performs this step only if it set the transaction commit mode to manual-commit; if the transaction commit mode is auto-commit, which is the default, the transaction is automatically committed when the statement is executed. For more information, see Transactions.

To execute a statement in a new transaction, the application returns to step 3. To disconnect from the data source, the application proceeds to step 6.

Step 6: Disconnect from the Data Source

The final step is to disconnect from the data source, as shown in the following illustration. First, the application frees any statement handles by calling SQLFreeHandle.

Next, the application disconnects from the data source with SQLDisconnect and frees the connection handle with SQLFreeHandle. For more information, see Disconnecting from a Data Source or Driver.

Finally, the application frees the environment handle with SQLFreeHandle and unloads the Driver Manager.