accessing databases using the ODBC API

In order to access an ODBC data source, the application should include the following steps:
1. Connecting to an ODBC data source
Requires a given ODBC data source name and some other necessary information.
Before an application calls an ODBC function, you must initialize the ODB interface and create an environment handle. First declare an environment handle variable, such as:
Dim HEnv1 as Long
If you are using ODBC 2.X, call the function sqlallocenv and pass the environment variable to the function as a reference. The Driver Manager initializes the ODBC environment, allocates storage space for the environment handle, and returns an environment handle, such as:
SQLAllocEnv (HEVN1)
If you are using ODBC 3.X, then the parameter SQL_NULL_EVN calls the function sqlallochandle, such as:
Sqlallochandle (SQL_HANDLE_ENV,SQL_NULL_HANDLE,HENV1)
The above steps can only be done once in an application. However, you can connect multiple data sources in an ODBC environment. Let's look at how to connect to a data source.
Before connecting to the data source, the application assigns one or more connection handles first. First declare a connection handle variable, such as:
Dimn HDBC1 as Long
If you are using ODBC 2.x, call the function SQLAllocConnect and pass the connection handle variable to the function as a reference. The driver Manager assigns a piece of storage space to the connection and returns a connection handle, such as:
SQLAllocConnect (HENV1,HDBC1)
If you are using ODBC 3.x, use the parameter Sql_handle_dbc and the environment handle to invoke the function sqlallochandle, such as:
Sqlallochandle (SQL_HANDLE_DBC,HENV1,HDBC1)
You can now specify the driver and the data source, and use the function SQLDriverConnect to connect the specified data source. Such as:

2. Handling SQL statements
(1) The application puts the SQL statement string into a buffer. If the SQL statement contains parameters, you should also set the parameter values.
(2) If the SQL statement returns a result set, it also has a cursor name for that statement's early flight.
(3) The application submits the SQL statement in a "ready" or "execute now" manner.
(4) If the SQL statement establishes a set of result sets, the application can query the properties of the result, such as the number of columns, the data type of the column, and so on. Attach a buffer to each column and extract the results.
(5) If the SQL statement produces an error, the error message is extracted and the appropriate action is taken.
Before you work with SQL statements, you must first assign a statement handle. Declare a statement handle variable first, such as:
Dim HSTMTL as Ling
If you use the ODBC 2.X, then call the function sqlallocstmt, such as:
SQLALLOCSTMT1 (HDBC1,HSTMT1)
If you are using ODBC 3.X, call function Sqlallochandle with parameter sql_handle_stmt and parameter HDBC1, such as:
Sqlallochandle (SQL_HANDLE_STMT,HDBC1,HSTMT1)
You can then execute the SQL statement, and if you execute the SQL statement in a "ready" way, the application does the following steps:
(1) Call the SQLPrepare function to prepare an SQL statement that takes the SQL statement as a parameter to the function, for example:
SQLPrepare (HSTMT1, "select Name,age from Employee Where age=?", Sql_nts).
(2) Set the parameter value in the SQL statement. If a question mark (?) appears in the SQL statement, it indicates that the SQL statement is parameterized, such as:
SQLBindParameter (Hstmt1,1,sql_param_input),
sql_c_clong.sql_integer,0,0,age,0 name,vbnull).
This way, after each query, the Name field value is placed in the variable name.
(3) Call function SQLExecute to execute the SQL statement. such as: SQLExecute (HSTMT1)
(4) Extract the results of the query. This task is performed by function SQLFetch, such as SQLFetch (HSTMT1).
(5) These five steps may be selectively carried out in accordance with the requirements of the procedure.
If you are executing the SQL statement in the "Execute Now" way, omit the first step above and use the function sqlexecutedirect when executing the SQL statement.

But in terms of speed, the "prepare" approach is faster than "execute now", so consider using the "Execute Now" mode only in the following situations:
(1) The SQL statement of the application is executed only once;
(2) The application does not need to query the result information before executing.
3. End of transaction
You can either commit the transaction or undo the transaction. There are two modes of committing transactions: One is automatic mode and the other is manual mode.
In automatic mode, each SQL statement is considered to be submitted automatically as a complete transaction. In manual mode, a transaction consists of one or several SQL statements, and if the application submits an SQL statement without an active transaction, the driver establishes a new transaction, and the driver maintains the active transaction during subsequent SQL statement submissions. Until the application calls the function SQLTransact (ODBC2). X) or SQLEndTran (ODBC 3.X) for transaction commits or undo.
If the driver supports the SQL_AUTOCOMMIT connection option, the default transaction mode is Autocommit mode, and if the SQL_AUTOCOMMIT connection option is not supported, the default transaction mode is manual mode. The application can call function SQLSetConnectOption (ODBC2). X) or SQLSetConnectAttr (ODBC3. X) for automatic/manual commit mode switching. When mode swapping occurs, the driver automatically commits all active transactions in the current connection.
The application should use function SQLTransact or Sqlendtrans to handle transactions, rather than using a commit or rollback in an SQL statement to handle transactions, the result of a commit or rollback statement depends on the driver and the connected data source.
Note: Regardless of whether the transaction is handled in automatic mode or in manual mode, whether the transaction is committed or revoked, as long as the transaction will cause the data source to close the cursor and delete all the storage plans associated with the data source.
4. Interrupt Connection
After the database has been accessed, the connection to the data source is interrupted.
Use function SQLDisconnect to break the connection to the data source. For example, the following statement breaks the data source connection referred to by the connection handle HDBC1.
SQLDisconnect (HDBC1)
After you break the connection, you must release all the handles, including the statement handle, the connection handle, and the environment handle.
When using odbc2.x:
(1) Releasing the statement handle, using function sqlfreestmt, such as Sql_drop;
(2) Releasing the connection handle and using function sqlfreeconnect, such as SQLFreeConnect (HDBC1);
(3) Releasing the environment handle, using function sqlfreeenv, such as sqlfreeenv (HENV1).
When using ODBC3.XJF, the release of the connection handle and the environment are sqlfreehandle with the function, except that the parameters are different. When the connection handle is disposed, the parameter is SQL_HANDLE_DBC, and the parameter is sql_handle_env when the environment handle is disposed. If you use Sql_drop when you release the statement handle, you also use the Sqlfreehandle function, with the argument changed to Sql_handle_stmt, otherwise the sqlfreestmt is used like ODBC2.XG.