Develop database applications with Visual C
Email: zzh1415@21cn.com
1 Overview
1,1 Visual C Develop Database Technology
Visual C provides a variety of database access technology - IDBC API, MFC ODBC, DAO, OLE DB, ADO, etc. Each of these technologies has its own characteristics, providing simple, flexible, visiting development technologies, and scalable development technologies.
Simplicity
A series of WiZard tools such as MFC class, ATL template classes, and AppWizard, ClassWizard, etc. are available in Visual C to help users quickly build their own applications, which greatly simplifies the design of the application. With these technologies, you can develop a small code or you can develop a database application if you write very little code or you don't need to write code.
flexibility
The development environment provided by Visual C enables developers to design applications and features based on their needs, and Visual C provides a rich class library and method, which allows developers to choose according to their own application features.
Visit speed
In order to solve the database application of ODBC development, Visual C provides new access technology - Ole DB and ADO, OLE DB and ADO are COM interface-based technology, using this technology to directly Database drivers are accessed, which greatly provides access speed.
Scalability
Visual C provides OLE technology and ActiveX technology, which enhances the ability of applications. Using OLE technology and ActiveX technology can enable developers to create their own programs using components provided by Visual C to create their own programs to implement the components of the application. Using this technique allows the application to have good scalability.
Access different kinds of data sources
Traditional ODBC technology can only access relational databases, in Visual C , providing OLE DB access technology, but also access to relational databases, but also access non-relational databases.
1, 2 Visual C Development Database Technology
Visual C provides a variety of technologies that access the database, as shown below:
ODBC (Open Database CONNECTIVITY)
MFC ODBC (Microsoft Foundation Classes ODBC)
DAO (Data Access Object)
OLE DB (Object Link and Embedding Database)
ADO (ActiveX Data Object)
These technologies have their own characteristics, summarized as follows:
ODBC
ODBC is a unified interface provided by the client application access to the relational database. For different databases, ODBC provides a unified API to enable applications to apply the supplied API to access any database for ODBC drivers. Moreover, ODBC has become a standard, so all relational databases currently provide ODBC drivers, which makes ODBC's applications very wide, basically available for all relational databases.
However, since ODBC can only be used for relational databases, it is difficult to access object databases and other non-relational databases using ODBC.
Since ODBC is a bottom-level access technology, ODBC APIs can enable client applications to set up and control databases from the underlying, and complete some of the high-level database technologies that cannot be completed.
MFC ODBC
Since the ODBCAPI written application to prepare a large number of code, the MFC ODBC class is provided in Visual C , encapsulating the ODBC API, which makes it easy to use the MFC to create ODBC applications. DAO
DAO provides a mechanism for creating and manipulating databases through program code. Multiple DAOs constitute an architecture, in which each DAO object works. MFC DAO is a powerful database development tool provided by Microsoft Jet Database file (* .mdb), which provides a DAO rich operation database means by DAO packaging.
OLE DB
OLE DB is a new technique provided in Visual C development database applications, which is based on a COM interface. Therefore, OLE DB provides unified interfaces for all file systems including relational databases and non-relational databases. These features make OLE DB technology more superior to traditional database access technology.
Similar to ODBC technology, OLE DB belongs to the underlying interface in database access technology.
Use OLE DB to design a database application that requires a lot of code. ATL templates are provided in the VC to design OLE DB data applications and data providers.
ADO
ADO technology is based on OLE DB access interface, which inherits the advantages of OLE DB technology, and ADO has encapsulated the interface of OLE DB, defining ADO objects, making program development to simplify, ADO technology belongs to the high-level interface of database access .
2, use ODBC API
Microsoft Open Database Interconnect (ODBC, Open Database Connectivity) is part of the Microsoft Windows Open Services System (WOSA) and is a standard interface for database access. With this standard interface, we can do not care about the details of the specific database management system (DBMS), and as long as there is an ODBC driver of the corresponding type of database, access to the database can be implemented.
The ODBC programming interface provides us with great flexibility, we can access different types of databases through this interface. Moreover, through the corresponding ODBC driver, we can easily implement the conversion between different data types.
2.1 ODBC API Overview
ODBC is an extensive database access application programming interface (API), using standard SQL (Structured Query Language) as its database access language.
2.11 architecture
The structure of ODBC is based on the client / server architecture, which contains the following four parts:
Application:
The application is the user's application, which is responsible for the interaction between the user and the user interface, and calls the ODBC function to give SQL requests and extract the results, and perform error handling.
ODBC Driver Manager (DRIVER Manager):
The ODBC Driver Manager is loaded and called the driver for the application, which can manage multiple applications and multiple drivers at the same time. Its function is to be implemented by indirect calling functions and using dynamic link library (DLL), so it is typically included in a file called "DLL".
ODBC driver (Driver)
The ODBC driver performs an ODBC function call, which presents the SQL request to the specified data source, and returns the result to the application. The driver is also interacting with any of the necessary software layers accessing the data source, including software with the underlying network or file system interface.
data source
The data source consists of a data set and an environment associated with it, including operating systems, dbms, and networks (if present). ODBC solves the large-scale difference problem of network topology and host by introducing the "data source" concept, so that users see the name of the data source without having to care about other things. 2.12 data type
ODBC uses two types of data types: SQL data types and C data types. The SQL data type is used for data sources, and the C data type is used in application code.
2.13 handle
The means of ODBC API implements database operation is a statement, which is a powerful means. In addition to executing SQL statements and completing query operations, ODBC statements can implement most database operations.
In ODBC, different handles (HANDLE), Connection, Sponder, statement, descriptor, and the like.
The handle is an application variable that uses it to store some of the objects for the application's context information and applications. It is similar to the concept of Windows programming, but ODBC has more perfect handle.
1. The environment handle is the handle of the entire context in the ODBC, starting from the creation environment handle using the ODBC's handle to the end of the environments. All other handles (all joint handles and statement handles all of this application) are managed by the context in the environment handle. Environment handles can only create one in each application.
2, join handle management of all information about the coupling. The coupling handle can be assigned multiple, which is not only legitimate and useful; but do not generate unnecessary handles to avoid waste of resources. However, different drivers supported the coupling situation, and some drivers only support one join handle in an application, and some drivers only support one statement handle. In the application, you can join or depart from the data source when any appropriate time, but do not easily establish or disengage.
3, the statement handle is the ODBC API truly important role, it is used to process the SQL statement and the directory function, each statement handle is related to one connection. When the driver receives a function call instruction from the application, the instruction contains a statement handle, the driver manager will send this function to the appropriate driver using the coupling handle stored in the statement handle.
4, the descriptor handle is a collection of metadata, which describes the parameters of the SQL statement, and the columns of the record set. After the statement is assigned memory, the descriptor is automatically generated, called an automatic allocation descriptor. In the program, the application can also call the SQLAllocHandle Assignment Descriptor.
When the application calls the API function SQLALLOCHANDLE, the driver manager or the ODBC driver will assign internal structures to the declared handle type and return the handle value.
2.14 abnormal processing
In order to debug the program during the program development, the program error, the ODBC API returns the information about the ODBC API function in two ways: return code and diagnostic records. The return code returns the return value executed by the function, indicating that the function performs success or not. Diagnostic Record Description Function Details.
Return Code
Each ODBC API function returns a code-return code, indicating the success of the function execution. If the function call is successful, the return code is SQL_SUCCESS or SQL_SUCCESS_WITH_INFO. SQL_SUCCESS indicates that the detailed information about the operation can be obtained by diagnostic logging, and SQL_SUCCESS_WITH_INFO indicates that the application execution result with a warning message, and detailed information can be obtained by diagnostic logging. If the function call fails, the return code is SQL_ERROR.
The following code is based on the return code executed by the function SQLFETCH (), and determines the success of the function execution, thereby performing corresponding processing accordingly. SQLRETURN RTCODE;
SQLHSTMT HSTMT;
While (RTCode = SQLFETCH (HSTMT)! = SQL_NO_DATA)
{
IF (RTCode == SQL_SUCCESS_WITH_INFO)
{
// Show warning information
}
Else
{
/ / Display error information
Break;
}
// Function call success, process
}
If the program executes an error, the return code is SQL_INVALID_HANDLE, and the program cannot be executed, while other return codes have the information executed by the program.
Diagnostic records
Each ODBC API function can produce a series of diagnostic records that reflect operation information. These diagnostic records are placed in a related ODBC handle until the next function called using the same handle, the diagnostic record has always exists. The size of the diagnostic record is not limited.
Diagnostic records have two categories: Head Record and Status Record. The header is a first copyright method (RECORD 0), and the recording is recorded as a status record. Diagnostic records have a number of domains, which are different in head recording and status records.
You can use the SqlgetDiagField function to get specific domains in the diagnostic record, and SQLGETDIAGREC () can be used to obtain some common domains in diagnostic records, such as SQLSTATE, original error number, and the like.
Head record
The universal information of a function execution in each domain of the header record is included regardless of whether the function is executed, as long as SQL_INVALID_HANDLE is not returned, the head record will be generated.
Status record
Each of the status records contains a specific error or warning message returned by the drive manager, an ODBC driver, or a data source, including SQLState, original error code, diagnostic information, list number, and a line number. Diagnostic records are generated when only functions return SQL_ERROR, SQL_STILL_EXEUTING, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, or SQL_NO_DATA.
Use sqlgetdiagrec and sqlgetdiagfield
The application can call the function SQLGETDIAGREC or SQLGETDIAGFIELD to get the diagnostic information. For a given handle, these two functions returns diagnostic information that recently used the function of the handle. When there is a function using the handle, the handle record is covered by the original diagnostic information recorded. If the function is executed, a plurality of status records are generated, and the program must call these two functions multiple times to obtain information.
2.2 Applying ODBC API to establish an application
Although it is relatively cumbersome to use the ODBC API program application, it is relatively simple and efficient because the procedures written directly using the ODBC API. So, we need to learn directly using the ODBC API program.
In general, writing ODBC programs have the following steps:
Assign an ODBC environment
Assign a connection handle
Connection data source
Construct and execute SQL statement
Acquire execution results
Disconnect connection with data sources
Release the ODBC environment
2.21 Assign an ODBC environment
For any ODBC application, the first step is to load the driver manager, then initialize the ODBC environment and assign environment handles.
First, a variable of a SQLhenv type is declared, then call the function sqlallochandle, transfer the above-mentioned SQLhenv type variable address and SQL_HANDLE_ENV options allocated. The following code shows:
Sqlhenv Henv;
SQLALLOCHANDLE (SQL_HANDLE_ENV, SQL_NULL_HANDLE, & HENV) After executing the calling statement, the driver assigns a structure that stores environment information and then returns a environment handle corresponding to the environment.
2.22 Assigning Connection Handle
After allocating the environment handle, we must assign a connection handle before establishing a connection to the data source, each of which corresponds to a connection handle.
First, the program defines a variable of a SQLHDBC type, which is used to store the connection handle, and then call the SQLAllocHandle function assigning the handle. The following code shows:
SQLHDBC HDBC;
SQLACHANDLE (SQL_HANDLE_DBC, HENV, & HDBC);
HENV is a environment handle.
2.23 connection data source
When the connection handle is allocated, we can set the connection properties, all connection properties have default, but we can set the connection properties by calling the function sqlsetConnectTR (). Use a function sqlgetconnectatTR () to get these connection properties.
The function format is as follows:
SQLRETURN SQLSETCONNECTATTR (SQLHDBC ConnectionHandle, SqlPointer ValuePtr, SQLINTEGER STRINGLENGTH);
SQLRETURN SQLGETCONNECTATTR (SQLHDBC ConnectionHandle, SqlPointer ValuePtr, Sqlinteger StringLength);
Applications can set different connection properties according to their needs.
After completing the settings for the connection properties, you can build a connection to the data source. For different programs and user interfaces, you can create connections with different functions: SqlConnect, SqlDriverConnect, SQLBROWSECONNECT.
SqlConnect
This function provides the most direct program control method, and we can connect to the data source name, user ID, and password.
Format:
SQLRETURN SQLCONNECT (SQLHDBC ConnectionHandle, Sqlchar ServerName, Sqlsmallint Namelength1, Sqlchar Username, Sqlsmallint NameLength2, Sqlchar * Authentication, Sqlsmallint Namelength;
parameter:
ConnectionHandle Connection Handle
ServerName Data Source Name
NameLength1 Data Source Name Length
UserName User ID
NameLength2 user ID length
Authentication user password
NameLength3 user password length
return value:
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, OR SQL_INVALID_HANDLE.
Successfully returned SQL_SUCCESS, if the return value is SQL_ERROR or SQL_SUCCESS_WITH_INFO, you can use the function sqlgetdiagrec to get the value of the corresponding SQLState.
The following code demonstrates how to use the ODBC API's SQLConnect function to establish a connection with the data source SQLServer.
#include "sqlext.h"
Sqlhenv Henv ;;
SQLHDBC HDBC;
SQLHSTMT HSTMT;
SQLRETURN RETCODE;
/ * Allocate environment handle * /
Retcode = sqlallochandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, & HENV); if (Retcode == SQL_Success || Retcode == SQL_SUCCESS_WITH_INFO) {
/ * SET the ODBC VERSION ENVIRONMENT Attribute * /
Retcode = SQLSETENVATTR (HENV, SQL_ATTR_ODBC_VERSION, (VOID *) SQL_OV_ODBC3, 0);
IF (Retcode == SQL_Success || Retcode == SQL_SUCCESS_WITH_INFO) {
/ * Allocate Connection Handle * /
Retcode = sqlallochandle (SQL_HANDLE_DBC, HENV, & HDBC);
IF (Retcode == SQL_Success || Retcode == SQL_SUCCESS_WITH_INFO) {
/ * SET login timeout to 5 seconds. * /
SQLSETCONNECTATTR (HDBC, (void *) SQL_Login_Timeout, 5, 0);
/ * Connect to Data Source * /
Retcode = SQLCONNECT (HDBC, (Sqlchar *) "Sales", SQL_NTS,
(Sqlchar *) "johns", SQL_NTS,
(SQLCHAR *) "SESAME", SQL_NTS);
IF (Retcode == SQL_Success || Retcode == SQL_SUCCESS_WITH_INFO) {
/ * Allocate Statement Handle * /
Retcode = sqlallochandle (SQL_HANDLE_STMT, HDBC, & HSTMT);
IF (Retcode == SQL_Success || Retcode == SQL_SUCCESS_WITH_INFO) {
/ * Process data * /;
SQLFreeHandle (SQL_HANDLE_STMT, HSTMT);
}
Sqldisconnect (HDBC);
}
SQLFreeHandle (SQL_HANDLE_DBC, HDBC);
}
}
SQLFreeHandle (SQL_HANDLE_ENV, HENV);
SqldriveConnect
The function SQLDRIVECONNECT establishes the connection to the data source with a connection string. It provides more information than three parameters than the SqlConnect function, allowing users to enter the necessary connection information.
If the connection is established, the function returns a complete string, and the application can use the connection string to establish additional connections.
Format:
SQLRETURN SQLDriverConnect (SQLHDBC ConnectionHandle, SQLHWND WindowHandle, SQLCHAR InConnectionString, SQLSMALLINT StringLength1, SQLCHAR OutConnetionString, SQLSMALLINT BufferLength, SQLSMALLINT * StringLength2Ptr, SQLSMALLINT DriverCompletion);
parameter:
ConnectionHandle Connection Handle
WindowHandle window handle, the application can use the handle of the parent window, or use the NULL pointer inConnectionstring connection connection string length
OutConnectionstring a pointer to the connection character
BufferLength stores the length of the buffer connected to the string
StringLength2Ptr Number of characters returned by the connection string
Drivercompletion additional connection information, may take value: SQL_Driver_prompt,
SQL_DRIVER_COMPLETE,
SQL_DRIVER_COMPLETE_REQUIRED, OR
SQL_DRIVER_NOPROMPT.
return value:
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, OR SQL_INVALID_HANDLE.
Successfully returned SQL_SUCCESS, if the return value is SQL_ERROR or SQL_SUCCESS_WITH_INFO, you can use the function sqlgetdiagrec to get the value of the corresponding SQLState.
SQLBROWSECONNECT
The function SQLBROWSECONNECT supports the connection to the data source in a manner until the connection is finally established. It is an architecture based on the Room / Server, so the local database does not support this function.
Generally, we provide partial connection information if it is enough to establish a connection to the data source, otherwise it returns SQL_NEED__DATA and returns the required information in the OutConnectionstring parameter.
Format:
SQLRETURN SQLBrowseConnect (SQLHDBC ConnectionHandle, SQLCHAR * InConnectionString, SQLSAMLLINT StringLength1, SQLCHAR * OutConnectionString, SQLSMALLINT BufferLength, SQLSMALLINT * StringLength2Ptr);
parameter:
ConnectionHandle Connection Handle
Inconnectionstring points to the pointer to the output string
StringLength1 output string pointer length
Outconnectionstring points to the pointer to the output string
BufferLength is used to store the length of buffer in output string
StringLength2ptr actually returns the length of the string
return value:
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, OR SQL_INVALID_HANDLE.
Successfully returned SQL_SUCCESS, if the return value is SQL_ERROR or SQL_SUCCESS_WITH_INFO, you can use the function sqlgetdiagrec to get the value of the corresponding SQLState.
The following code tells how to establish a connection with the data source using the SQLBROWSECONNECT function of the ODBC API.
#define BRWS_LEN 100SQLHENV
HENV; SQLHDBC HDBC;
SQLHSTMT HSTMT;
SQLRETURN RETCODE;
Sqlchar szconnstrin [BRWS_LEN], SZConnSTrout [BRWS_LEN];
Sqlsmallint cbconnstrout; / * allocate the environment handle. * /
Retcode = sqlallochandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, & HENV);
IF (Retcode == SQL_Success || Retcode == SQL_SUCCESS_WITH_INFO) {
/ * SET the version Environment attribute. * / Retcode = sqlsetetenvattr (Henv, sql_attr_odbc_version, sql_ov_odb3, 0);
IF (Retcode == SQL_Success || Retcode == SQL_SUCCESS_WITH_INFO) {
/ * Allocate the connection handle. * /
Retcode = sqlallochandle (SQL_HANDLE_DBC, HENV, & HDBC);
IF (Retcode == SQL_Success || Retcode == SQL_SUCCESS_WITH_INFO) {
/ * Call SqlbrowSeconnect Until It Returns A Value Other Than * /
/ * SQL_NEED_DATA (PASS The Data Source Name The First Time). * /
/ * If SQL_NEED_DATA IS RETURNED, CALL GETUSERINPUT (NOT * /
/ * Shown) to build a dialog from the value in szconnstrout. * /
/ * The user-support value is returned in szconnstrin, * /
/ * Which is passed in the next call to sqlbrowseconnect. * /
LSTRCPY (SZConnstrin, "DSN = Sales"); do {
Retcode = SQLBROWSECONNECT (HDBC, SZConnstrin, SQL_NTS,
SZConnStrout, BRWS_LEN, & CBConnStrout;
IF (retcode == sql_need_data)
GetUserInput (szconnstrout, szconnstrin);
} while (retcode == sql_need_data);
IF (Retcode == SQL_Success || Retcode == SQL_SUCCESS_WITH_INFO) {
/ * Allocate the statement handle. * /
RetcoD
