ODBC Application Framework

The ultimate goal of ODBC applications is to use odbc api functions to execute SQL statements and complete various database operations. ODBC accesses to the database are implemented through handles. The following three basic handles are commonly used:

Environment handle: Establishes the connection between the application and the ODBC system. There is only one environment handle.

Connection handle: establish a connection between ODBC and the data source. You can have multiple connection handles.
Statement handle: used to establish contact with SQL statement operations to execute SQL statements. In an ODBC application, any ODBC function that processes SQL statements requires a statement handle as a parameter.
Environment handle, connection handle, and statement handle are the three most frequently used objects in ODBC applications. An application should only create one environment handle, the environment handle connects the application to the ODBC dynamic link library. However, you can create multiple connection handles under an environment handle. With these connection handles, applications can connect to multiple different data sources, provides applications with the ability to process data of different database systems at the same time. Similarly, you can apply for multiple statement handles under one connection handle to execute multiple SQL statements.
The application and release operations of the statement handle are similar to those of the environment handle and connection handle. The following describes how the application applies for and releases the statement handle and the execution process of the SQL statement. Figure 6-19 shows the process of applying for and releasing the statement handle from ODBC.
1. Application statement handle
2. Release statement handle

3. Execute SQL statements

1. Application statement handle
The sqlallocstmt function assigns a statement handle to the application in the following format:
Retcode sqlallocstmt (
Hdbc,
Hstmt far * phstmt
) Hdbc is the connection handle. Each statement handle is always associated with a connection handle. Therefore, you must provide a valid connection handle when applying for a statement handle. Otherwise, the sqlallocstmt function returns the error code SQL _invalid_handle.
Phstmt is the output parameter. It is a pointer to the statement handle variable. When the sqlallocstmt function is successfully executed, it returns SQL _success, store the statement handle allocated to the application to the specified statement handle variable. Otherwise, SQL _error is returned.

2. Release statement handle
After using the statement handle to process database operations, the application should call the sqlfreestmt function to close the statement handle cursor, release the buffer allocated for the result set, and release the statement handle, to release the system resources it occupies. The format of the sqlfreestmt function is:
Retcode sqlfreestmt (
Hstmt,
Uword foption
)
Hstmt is the statement handle to be released, and the foption parameter controls the action of the sqlfreestmt function. Sqlfreestmt is not only used to release statement handles, but also has many other functions, such as closing the cursor, releasing columns and parameter buffers. All these functions of sqlfreestmt are determined by the value of the foption parameter.
The value of the foption parameter is as follows:
. SQL _close: Close the cursor associated with the hstmt handle and abolish all processing results. After the cursor is closed, you can re-open the cursor by executing the SELECT statement again;
. SQL _drop: Close all hstmt-related cursors, abolish processing results, end all access to the hstmt handle, and release the hstmt handle;
. SQL _unbind: Remove all column buffers associated with the hstmt result set from the sqlbindcol function;
. SQL _reset_paras: Release the sqlbinparameter function as the parameter easing zone associated with the hstmt handle.

Figure 6-19 flowchart of applying for and releasing ODBC handles

3. Execute SQL statements
The ultimate goal of ODBC applications is to use odbc api functions to execute SQL statements and complete various database operations. The previous sections are all about preparing for SQL statement execution. Figure 6-20 is a simple process for ODBC to execute SQL statements.

Figure 6-20

One of the core contents of ODBC application design is to execute SQL statements. The content includes applying for and releasing statement handles, SQL statement execution methods and parameter transmission, selecting SQL statement execution methods (synchronous execution and asynchronous execution) to execute SQL statements, and transaction management. ODBC applications execute SQL statements to perform various database operations and complete user-specified program functions. There are two ways to execute SQL statements:
(1) Direct Execution: Call the sqlexecdriect () function of ODBC
If both of the following aspects are true, submit the SQL statement using direct execution:
· The SQL statement is executed only once;
· The application does not need result set information before execution.
It is relatively simple to execute an SQL statement in the immediate execution mode, and the implementation process is also intuitive. First, the SQL statement text to be executed is passed to the sqlexecdirect function. The SQL statement is submitted to the data source for execution immediately after being analyzed by the driver.
The format of the sqlexecdirect function is:
Sqlexecdirect (hstmt, uchar far * szsqlstr, scword cbsqlstr)
Where:
· Hstmt is the statement handle.
· Szsqlstr is the SQL statement text buffer;
· The cbsqlstr parameter specifies the length of the SQL statement text. When using C or C ++ programming, you can set it to SQL _cnt.
After the statement is successfully executed, the sqlexecdirect function returns SQL _success.
For example, the following code uses the sqlexecdirect function to immediately execute a database query statement:
Rc = sqlexecdirect (hstmt, 'select * from authors ', SQL _nt );
(2) preparation and execution: It takes two steps for the application to execute an SQL statement. First, call the sqlprepare () function of ODBC to prepare the SQL statement, and then call the sqlexecute () of ODBC () prepare SQL statements for function execution.
If one of the following conditions is true, submit the SQL statement in the preparation and execution mode:
* The SQL statement must be executed multiple times;
* The application must know the result set before execution.
During preparation, the driver converts the SQL statement to the local SQL format of the data source (some drivers also perform Syntactic Analysis on the SQL statement) and then submits it to the data source, the data source then compiles the SQL statement, designs the access plan, and finally returns the access plan descriptor to the driver.
Compared with immediate execution, the sqlexecute function executes a prepared SQL statement faster than the former, because it saves the time for the driver and data source to process SQL statements. The method used by the application to execute the SQL statement mainly determines whether the statement needs to be executed multiple times.
The format of the sqlprepare function is:
Retcode sqlprepare (hstmt, uchar far * szsqlstr, scword cbsqlstr)
Where:
· Hstmt is the statement handle.
· Szsqlstr is the SQL statement text buffer;
· The cbsqlstr parameter specifies the length of the SQL statement text.
The prepared SQL statement is executed through the sqlexecute function. The format of the sqlexecute function is relatively simple. It only needs a statement handle used for the prepared statement, that is:
Retcode sqlexecute (hstmt)
SQL statements prepared by the sqlprepare function can be executed multiple times by the sqlexecute function. Different statement parameters can be used for each execution. However, when you call the sqlexecute function to execute another SELECT statement, the application must first use the SQL _close parameter to call the sqlfreestmt function and close the cursor associated with the statement handle, abolish the result set it is processing, and then execute the SELECT statement to generate a new result set.
Compared with immediate execution, the sqlexecute function executes a prepared SQL statement faster than the former, because it saves the time for the driver and data source to process SQL statements. The method used by the application to execute the SQL statement mainly determines whether the statement needs to be executed multiple times.
Note:
· The benefits of Heterogeneous Database Access and ODBC technologies to developers and users are obvious. However, there are still some shortcomings in using ODBC technology to access heterogeneous databases:
· Each DBMS provides its own ODBC interface, its own data type, independent SQL version, and functions. Many interfaces do not comply with the standards, so that when the same code Accesses Different databases, the results will be inconsistent;
· The efficiency of applications using ODBC interfaces is reduced.