Use ODBCAPI to write database applications

1. Overview
1. 1. Features of Visual C ++ Database Development Technology
Visual C ++ provides a variety of Database Access Technologies-odbc api, mfc odbc, DAO, ole db, and ADO. These technologies have their own characteristics. They provide simple, flexible, fast access, and scalable development technologies.
1. Simplicity
Visual C ++ provides the MFC class library, ATL template class, AppWizard, ClassWizard, and a series of other Wizard tools to help users quickly build their own applications, greatly simplifying the application design. By using these technologies, developers can develop a database application with little or no need to write code.
2. Flexibility
The development environment provided by Visual C ++ allows developers to design application interfaces and functions based on their own needs. Moreover, Visual C ++ provides a wide range of class libraries and methods, developers can choose based on their application characteristics.
3. Fast Access
To solve the slow speed for database applications developed by ODBC to access the database, Visual C ++ provides a new access technology-ole db and ADO, ole db and ADO are both based on the COM interface technology. Using this technology, you can directly access the database driver, which greatly provides access speed.
4. scalability
Visual C ++ provides OLE and ActiveX technologies, which can enhance the application capabilities. With OLE and ActiveX technologies, developers can use various components and controls provided by Visual C ++, as well as components provided by third-party developers to create their own programs, so as to implement componentization of applications. Using this technology can make applications highly scalable.
5. Access different types of data sources
Traditional ODBC technology can only access relational databases. in Visual C ++, the ole db access technology is provided to not only access relational databases, but also non-relational databases.
1. 2. Visual C ++ Development Database Technology
Visual C ++ provides a variety of Database Access Technologies, as shown below:
1. ODBC (Open DataBase Connectivity)
2. mfc odbc (Microsoft Foundation Classes ODBC)
3. DAO (Data Access Object)
4. ole db (Object Link and Embedding DataBase)
5. ADO (ActiveX Data Object)
These technologies have their own characteristics and are summarized as follows:
1. ODBC
ODBC is a unified interface provided by your applications to access relational databases. For different databases, ODBC provides a unified set of APIs, allows applications to access any database that provides the ODBC driver by using the APIS provided by the application. In addition, ODBC has become a standard. Therefore, all relational databases currently provide ODBC drivers, which make ODBC widely used and can be basically used in all relational databases.
However, because ODBC can only be used for relational databases, it is difficult to use ODBC to access object databases and other non-relational databases.
Because ODBC is an underlying access technology, ODBC APIs enable customer applications to set and control databases from the underlying layer to complete functions that cannot be completed by some advanced database technologies.
2. MFC ODBC
Because a large amount of code is required to directly write applications using ODBC APIs, the mfc odbc class is provided in Visual C ++ and ODBC APIs are encapsulated, this makes it easy to use MFC to create ODBC applications.
3. DAO
DAO provides a mechanism for creating and manipulating databases through program code. Multiple DAO forms an architecture in which each DAO object works collaboratively. Mfc dao is provided by Microsoft to access Microsoft Jet Database Files (*. mdb) is a powerful database development tool, which provides programmers with a variety of DAO database operations through DAO encapsulation.
4. OLE DB
Ole db is a new technology provided by Visual C ++ in developing database applications. It is based on the COM interface. Therefore, ole db provides a unified interface for all file systems, including relational databases and non-relational databases. These features make ole db technology superior to traditional database access technology.
Similar to ODBC technology, ole db is the underlying interface in database access technology.
Using ole db directly to design database applications requires a lot of code. An ATL template is provided in VC for designing ole db data applications and data providers.
5. ADO
ADO technology is an access interface based on ole db. It inherits the advantages of ole db technology. In addition, ADO encapsulates ole db interfaces and defines ADO objects, simplify program development. ADO technology is a high-level interface for database access.

2. Use ODBC APIs
Microsoft Open DataBase Connectivity (ODBC) is part of Microsoft Windows Open service system (WOSA) and is a standard interface for DataBase access. With this standard interface, we can ignore the details of the specific database management system (DBMS), and access to the database can be implemented as long as there is an ODBC driver for the corresponding type of database.
The ODBC programming interface provides us with great flexibility. We can use this interface to access different types of databases. In addition, through the corresponding ODBC driver, we can easily achieve conversion between different data types.
2.1 odbc api Overview
ODBC is a widely used programming interface (API) for accessing databases. It uses standard SQL (structured query language) as its database access language.
2.11 Architecture
The ODBC architecture is built on the client/server architecture. It consists of the following four parts:
Application ):
An application is a user's application. It is responsible for interaction between users and user interfaces, and calls odbc functions to provide SQL requests, extract results, and handle errors.
ODBC Driver Manager ):
ODBC driver manager loads and calls drivers for applications. It can manage multiple applications and drivers at the same time. Its function is implemented by indirectly calling a function and using a dynamic link library (DLL). Therefore, it is generally included in a file with the extension "DLL.
ODBC Driver)
The ODBC driver executes an ODBC function call, sends an SQL request to the specified data source, and returns the result to the application. Drivers are also responsible for interacting with any necessary software layer that accesses the data source, including software that interfaces with the underlying network or file system.
Data Source
A data source consists of a dataset and an associated environment, including the operating system, DBMS, and network (if any ). ODBC solves the wide range difference between the network topology and the host by introducing the "Data Source" concept. In this way, users can see the name of the data source without worrying about anything else.
2.12 Data Type
ODBC uses two types of data: SQL data type and C data type. The SQL data type is used for data sources, and the C data type is used in application code.
2.13 handle
The odbc api implements database operations by means of statements, which is a powerful means. In addition to SQL statements and query operations, ODBC statements can also perform most database operations.
In ODBC, different handles are used to indicate ENVIRONMENT (ENVIRONMENT), CONNECTION (CONNECTION), STATEMENT (STATEMENT), and DESCRIPTOR.
A handle is an application variable. The system uses it to store context information about the application and some objects used by the application. It is similar to the concept in Windows programming, but ODBC is more perfect for handling.
1. The environment handle is the entire context handle in ODBC. Each program in ODBC starts from the creation of the environment handle and ends with the release of the Environment handle. All other handles (all the connection handles and statement handles of the application) are managed by context in the Environment handle. Only one environment handle can be created in each application.
2. The connection handle manages all information about the connection. Multiple join handles can be allocated, which is not only legal but also useful. do not generate unnecessary handles to avoid resource waste. However, different drivers support different connections. Some drivers only support one connection handle in an application, and some drivers only support one statement handle. In an application, you can connect to or remove from the data source whenever appropriate, but do not create or detach the connection easily.
3. The statement handle plays an important role in ODBC APIs. It is used to process SQL statements and directory functions. Each statement handle is related to only one join. When the driver receives a function call instruction from the application and the instruction contains a statement handle, the driver Manager uses the connection handle stored in the statement handle to send this function call to the appropriate driver.
4. the descriptor handle is a collection of metadata, which describes the SQL statement parameters, record set columns, and other information. When a statement is allocated with memory, the descriptor is automatically generated. In the program, the application can also use SQLAllocHandle to allocate descriptors.
When an application calls the API function SQLAllocHandle, the driver manager or ODBC driver allocates an internal structure for the declared handle type and returns the handle value.
2.14 Exception Handling
To debug a program during program development and discover program errors, odbc api returns information about odbc api function execution in two ways: return code and diagnosis record. The return code returns the return value of the function execution, indicating whether the function execution is successful or not. Diagnostic Records describe detailed information about function execution.
1. Return Code)
Each odbc api function returns a code-return code, indicating whether the function is successfully executed. If the function is successfully called, the return code is SQL _SUCCESS or SQL _SUCCESS_WITH_INFO. SQL _SUCCESS indicates that detailed information about the operation can be obtained through the diagnosis record, SQL _SUCCESS_WITH_INFO indicates that the application execution result contains warning information, and detailed information can be obtained through the diagnosis record. If the function call fails, the return code is SQL _ERROR.
The following code checks whether the function is successfully executed based on the return code of SQLFetch (), and then processes the Code accordingly.
SQLRETURN rtcode;
SQLHSTMT hstmt;
While (rtcode = SQLFetch (hstmt )! = SQL _NO_DATA)
{
If (rtcode = SQL _SUCCESS_WITH_INFO)
{
// Display warning information
}
Else
{
// Display Error Information
Break;
}
// Process the function successfully.
}
If the program execution error occurs, the return code is SQL _INVALID_HANDLE, and the program cannot be executed. Other return codes contain the program execution information.
2. Diagnostic Records)
Each odbc api function can generate a series of diagnostic records that reflect operation information. These diagnostic records are stored in the relevant ODBC handle until the next function call using the same handle exists. There is no limit on the size of diagnostic records.
There are two types of diagnostic records: Head Record and Status Record ). The header Record is the first copyright law Record 0, and the subsequent Record is a status Record. Diagnostic records are composed of many domains, which are different in the header record and status record.
You can use the SQLGetDiagField function to obtain the specific fields in the diagnosis record. In addition, you can use SQLGetDiagRec () to obtain the diagnosis.