Develop database applications with Visual C 2002-03-19 · ZZH ··
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 application is written directly 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 an ODBC. 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 treatment
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 (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. * /
Retcode = sqlallochandle (SQL_HANDLE_STMT, HDBC, & HSTMT);
IF (Retcode == SQL_Success || Retcode == SQL_SUCCESS_WITH_INFO) {
/ * Process data after successful connection * / ...
SQLFreeHandle (SQL_HANDLE_STMT, HSTMT);
Sqldisconnect (HDBC);}}
SQLFreeHandle (SQL_HANDLE_DBC, HDBC);}}
SQLFreeHandle (SQL_HANDLE_ENV, HENV); 2.24 SQL operation
Construct and execute SQL statement
Construct SQL statement
SQL statements can be constructed in three ways: determine in the program development phase, determine or by the user input SQL statement at runtime.
The SQL statement determined at the time of program development, which is easy to implement, and can be tested when program encoding.
Determine the SQL statement at runtime provides great flexibility, but is difficult to give the program, and more processing time is required. The SQL statement entered by the user enhances the functionality of the program, however, the program must provide a friendly man-machine interface and perform a certain program's syntax check to report the user error. Execute SQL statement
Most of the application's database access work is done by executing the SQL statement. Before performing the SQL statement, you must assign a statement handle, then set the statement properties of the corresponding statement, and then execute the SQL statement. When a statement handle is complete, the call function SQLFreeHandle () releases the handle.
SQLEXECUTE ()
SQLEXECUTE is used to perform a preparative language, when there is a parameter in the statement, the value of the currently bound parameter variable.
Format:
SQLRETURN SQLEXECUTE (SQLHSTMT StatementHandle);
parameter:
The StatementHandle identifies the handle of the execution SQL statement, you can get it with sqlallochandle ().
return value:
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, SQL_STILL_EXECUTING, SQL_ERROR, SQL_NO_DATA, 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.
SQLEXECDIRET ()
SQLEXECDirect executes the SQL statement directly, which is the fastest way to perform only one operation.
Format:
SQLRETURN SQLEXECDirect (Sqlhstmt StatementHandle, Sqlchar * StatementText, SQLINTEGER TEXTLENGTH);
parameter:
STATEMENTHANDLE statement handle
STATEMENTTEXT to be executed SQL
The length of the STATEMENTTEXT SQL statement.
return value:
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, SQL_STILL_EXECUTING, SQL_ERROR, SQL_NO_DATA, 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.
SQLPripare ()
For SQL statements that require multiple executions, in addition to using the SQLExECDirect function, we can prepare the execution of the SQL statement before performing the SQL statement. This can greatly improve the program execution speed for the statement that uses parameters.
Format:
SQLRETURN SQLPREPARE (Sqlhstmt StatementHandle, Sqlchar * StatementText, Sqlinteger TextLength);
parameter:
STATEMENTHANDLE statement handle
STATEMENTTEXT to be executed SQL
The length of the STATEMENTTEXT SQL statement.
return value:
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, SQL_STILL_EXECUTING, SQL_ERROR, SQL_NO_DATA, 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. Use parameters
Use the parameters to make a SQL statement multiple times, get different results
SQLBINDPARAMETER
The function sqlbindparameter is responsible for defining variables for parameters to implement the passage of parameter values.
The function format is as follows:
SQLRETURNSQLBindParameter (SQLHSTMT StatementHandle, SQLUSMALLINT ParameterNumber, SQLSMALLINT InputOutputType, SQLSMALLINT ValueType, SQLSMALLINT ParameterType, SQLUINTEGER ColumnSize, SQLSMALLINT DecimalDigits, SQLPOINTER ParameterValuePtr, SQLINTEGER BufferLength, SQLINTEGER * StrLen_or_IndPtr);
parameter:
STATEMENTHANDLE statement handle
ParameterNumber Binding parameters The serial number in the SQL statement, in SQL, all parameters are numbered from left to right order, starting from 1. Before the SQL statement is executed, the function SQLBINDPARAMETER is bundled to a program variable for each parameter.
InputOutputType parameter type, can be SQL_PARA_INPUT, SQL_PARAM_INPUT_OUTPUT, SQL_PARAM_OUTPUT.
ParameterType parameter data type
COLMNSIZE parameter size
Decimaldigits parameter accuracy
ParameterValutePtr pointing to pointers of buffers that store parameter values in the program
The number of bytes of the buffer that stores the parameter value in the bufferLength program
Strlen_or_indptr point to the buffer pointer to the storage parameter parametervaluePtr
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.
Transfer parameters during execution
For some text documents or bitmap files, you should take up a lot of storage space. Therefore, when the data source passes this data, it can be passed separately. There are two functions to complete this work.
Format:
SQLRETURN SQLPUTDATA (SQLHSTMT StatementHandle,
SQLPointer DataPtr, Sqlinteger Strlen_or_ind);
parameter:
StatementHandle parameter handle
DataPTR points to buffer pointers that contain actual data.
Strlen_or_lnd buffer 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.
Format:
Sqlreturnsqlparamdata (SQLHSTMT StatementHandle, Sqlpointer * ValuePtrptr);
parameter:
StatementHandle parameter handle
ValuePtrptr pointing to the pointer of the buffer address
return value:
SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE successfully returns 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 shows how to use these two functions
#define max_data_len 1024
SQLINTEGER CBPARTID = 0, CBphotoparam, cbdata;
SQLUINTEGER spartid; szphotofile;
Sqlpointer ptoken, initvalue;
SQLCHAR DATA [MAX_DATA_LEN];
SQLRETURN RETCODE;
SQLHSTMT HSTMT;
Retcode = SQLPRepare (HSTMT, "Insert Into Pictures (Partid, Picture) Values
(?,?) ", SQL_NTS);
IF (Retcode == SQL_Success || Retcode == SQL_SUCCESS_WITH_INFO) {
/ * Bind the parameters. For parameter 2, pass * /
/ * The Parameter Number in ParameterValuePtr Instead of a buffer * /
/ * address. * / SQLBINDPARAMETER (HSTMT, 1, SQL_PARAM_INPUT, SQL_C_ULONG,
SQL_INTEGER, 0, 0, & spartid, 0, & cbpartID);
SQLBINDPARAMETER (HSTMT, 2, SQL_PARAM_INPUT,
SQL_C_BINARY, SQL_LONGVARBINARY,
0, 0, (SQLPointer) 2, 0, & CBphotoparam;
/ * SET VALUES SO DATA for Parameter 2 Will Be * /
/ * Passed at Execution. Note That the length parameter in * /
/ * The macro sql_len_data_at_exec is 0. this assumes That * /
/ * The Driver Returns "N" for the sql_need_long_data_len * /
/ * Information Type in SqlgetInfo. * /
CBphotoparam = SQL_LEN_DATA_AT_EXEC (0);
Spartid = getNextId (); / * get next available Employee ID * /
/ * Number. * / retcode = SQLEXECUTE (HSTMT);
/ * For data-at-execution parameters, call sqlparamdata to * /
/ * Get the parameter number set by SQLBINDPARAMETER. * /
/ * Call InituserData. Call getuserdata and sqlputdata * /
/ * REPEATEDLY TO GET and PUT All Data for the parameter. * /
/ * Call Sqlparamdata to Finish Processing this parameter * /
While (retcode == SQL_NEED_DATA) {
Retcode = SQLParamdata (HSTMT, & PTOKEN); if (retcode == sql_need_data) {
InituserData ((Sqlsmallint) ptoken, initvalue;
While (GetUserData (Sqlsmallint) Ptoken, Data,
& cbdata))
Sqlputdata (HSTMT, DATA, CBDATA);}}}
Void Inituserdata (Spram, InitValue) Sqlpointer initValue; {
SQLCHAR SZPHOTOFILE [MAX_FILE_NAME_LEN];
/ * Prompt user for bitmap file containing urge Yele * /
/ * Photo. OpenPhotofile Opens the file and returns the * / / / * file handle. * /
Promptphotofilename (szphotofile);
OpenPhotofile (Szphotofile, (file *) initvalue; Break;}
Bool GetUserData (InitValue, Spram, Data, CBDATA) SQLPointer InitValue;
SQLCHAR * DATA; SQLINTEGER * CBDATA; BOOL DONE;
/ * GetNextPhotodata Returns the next piece of photo * /
/ * Data and the number of bytes of data returned * /
/ * (Up to max_data_len). * / done = getNextPhotodata (File *) Initvalue, Data,
Max_Data_len, & cbdata); if (done) {
ClosePhotofile ((file *) initvalue;
Return (True);
Return (false);} Add, delete, and update
The application of the data source can be implemented in three ways: use the corresponding SQL statement to execute on the data source; call the function SQLSETPOS implementation of the definition update of the record set; call the function SQLBULKOPERATIONS to update the data.
The way the SQL statement is executed directly on the data source, which can be applied to any ODBC data source, but for the latter two updates, some data sources do not support, the application can call the function SQLGETINFO to determine if the data source supports these two A way.
Location update and delete
To use the positioning update and delete, you should follow the order:
1) Remove the record set:
2) Locate the line to be updated or deleted
3) Perform an update or delete operation
Refer to the following code:
#define rows 20 # Define Status_Len 6
SQLCHAR SZSTATUS [ROWS] [Status_len], Szreply [3];
SQLINTEGER CBSTATUS [ROWS], CBORDERID;
SQLUSMALLINT RGFROWSTATUS [ROWS];
SQLUINTEGER SORDERID, CROW = ROWS, IROW;
SQLHSTMT HSTMTS, HSTMTU
SQLSETSTMTATTR (HSTMTS, SQL_ATTR_CONCURRENCY, (SQLPointer) SQL_CONCUR_ROWVER, 0);
SQLSETSTMTATTR (HSTMTS, SQL_ATTR_CURSOR_TYPE, (SQLPointer) SQL_CURSOR_KEYSET_DRIVEN, 0); SQLSETSTMTATTR (HSTMTS, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPointer) ROWS, 0);
SQLSETSTMTATTR (HSTMTS, SQL_ATTR_ROW_STATUS_PTR, (SQLPointer) RGFrowStatus, 0);
SQLSETCURSORNAME (HSTMTS, "C1", SQL_NTS);
SQLEXECDirect (HSTMTS, "SELECT ORDERID, STATUS from Orders", SQL_NTS);
SQLBINDCOL (HSTMTS, 1, SQL_C_ULONG, & SorderID, 0, & CBorderID);
Sqlbindcol (HSTMTS, 2, SQL_C_CHAR, SZSTATUS, STATUS_LEN, & CBSTATUS);
While (Retcode == SQLFETCHSCROLL (HSTMTS, SQL_FETCH_NEXT, 0))! = SQL_ERROR) {
IF (Retcode == SQL_NO_DATA_FOUND) BREAK;
For (irow = 0; irow IF (rgfrowstatus [row]! = SQL_ROW_DELETED) Printf ("% 2D% 5D% * S / N", IROW 1, sorderid, name_len-1, szstatus [iRow]); }} whiling {printf ("/ nrow number to update?"); Gets (szreply); irow = ATOI (Szreply); IF (Irow> 0 && IROW <= CROW) {Printf ("/ NNEW STATUS?"); Get (Szstatus [IROW-1]); SQLSETPOS (HSTMTS, IROW, SQL_POSITION, SQL_LOCK_NO_CHANGE); Sqlprepare (HSTMTU, "Update Orders Set Status =? WHERE CURRENT OF C1", SQL_NTS); SQLBINDPARAMETER (HSTMTU, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, Status_len, 0, Szstatus [IROW], 0, NULL; SQLEXECUTE (HSTMTU);} else if (irow == 0) {Break;} } } Update data with SQLBulkOperations () The operation of the function SQLBULKOPERATIONS is based on the current row, and the function SQLFETCH or SQLFETCHSCROLL must first be modified or deleted before calling the function SQLBulkOperations. Format: SQLRETURN SQLBULKOPERATIONS (SQLHSTMT StatementHandle, SQLUSMALLINT OPERATION); parameter: StatementHandle parameter handle The Operation Identification The type of operation is performed, which can be one of the following: SQL_ADD SQL_UPDATE_BY_BOOKMARKSQL_DELETE_BY_BOOKMARK SQL_FETCH_BY_BOOKMARK return value: SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, SQL_STILL_EXECUTING, 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. Retrieve the query results Bind column The data retrnigned from the data source is stored in the variable defined by the application, so we must first assign variables corresponding to the recordset field, and then bind the record field with the program variable with the program variable through the function sqlbindcol. For long record fields, you can retrieve the data directly by calling the function sqlgetdata (). The bind field can be bound according to your own needs, and you can also bind some of the fields. The fields in the record can be bound at any time, but the new binding is only suburbs that the operation performed when the data is taken from the data source, and the data that has been retrieved is affected. Format: SQLRETURN SQLBINDCOL (SQLHSTMT StatementHandle, SQLUSMALLINTCOLUMNUMBER, SQLSMALLINT TARGETTYPE, SqlpointerTargetValueptr, SqlintegerbufferLength, SQLINTEGER * STRLEN_OR_INDPTR); parameter: STATEMENTHANDLE statement handle ColumnNumber identifies the number to be bound. The data column number starts ascending from 0, where 0 columns are used as bookmarks. If there is no bookmark, the number starts from 1. Targettype data type TargetValuePtr Binds to the address of the buffer to the data field BufferLength buffer length Strlen_or_indptr pointing to the length of the length used by the binding data column. 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. SQLFETCH () The function sqlfetch is used to turn the next row in the record set to the current line and copy the data of all bundled data fields to the corresponding buffer. Format: SQLRETURN SQLFETCH (SQLHSTMT StatementHandle) parameter: STATEMENTHANDLE statement handle return value: SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA, SQL_STILL_EXECUTING, 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. cursor The application acquisition data is implemented through the cursor, in ODBC, there are three types of cursors: One-way cursor: The one-way cursor can only move forward, to return the start position of the recordset, the program must turn off the cursor first, and then open the cursor, which is very useful for applications that only need to browse once, and the efficiency is high. One-way optical village is not applicable for moving before and after the cursor is required. Rolling cursor The canable cursor is typically based on the program of the graphical user interface, the user scrolls forward or backwards through the screen, browsing the data in the recordset. Block cursor The so-called block cursor can be understood as a multi-line cursor, and it is guided to the rows. For applications in a network environment, use block cursors to mitigate network loads on a certain program. To use the block cursor, you should do the following: 1) Set the line set size 2) Binding line set buffer 3) Set the statement handle properties 4) Take the line set result Since the block cursor returns multi-line records, the application must bind this multi-line data into some data groups, which are named routine buffers. There are two ways to bind: column methods and line mode. ODBC optical label library Some applications do not support rolling cursors and block cursors, ODBC SDK provides a optical label (ODBCCR32.DLL), which can activate the cursor library in the application (SQL_STR_ODBC_CURSORS). Refer to the following code: #define name_len 50 #define Phone_Len 10 Sqlchar szname [name_len], szphone [phone_len]; SQLINTEGER SCUSTID, CBNAME, CBCUSTID, CBPHONE SQLHSTMT HSTMT; SQLRETURN RETCODE; RETCODE = SQLEXECDIRECT (HSTMT, "Select Custid, Name, Phone from Customers Order By 2, 1, 3", SQL_NTS); IF (Retcode == SQL_Success || Retcode == SQL_SUCCESS_WITH_INFO) { / * Bind Column 1, 2, and 3 * / SQLBINDCOL (HSTMT, 1, SQL_C_ULONG, & SCUSTID, 0, & CBCustid); SQLBINDCOL (HSTMT, 2, SQL_C_CHAR, SZNAME, NAME_LEN, & CBNAME); SQLBINDCOL (HSTMT, 3, SQL_C_CHAR, Szphone, Phone_len, & Cbphone); / * Fetch and print each row of data. On * / / * an error, Display a message and exit. * / while (true) { Retcode = SQLFETCH (HSTMT); IF (Retcode == SQL_ERROR || RETCODE == SQL_SUCCESS_WITH_INFO) { Show_error (); IF (Retcode == SQL_Success || Retcode == SQL_SUCCESS_WITH_INFO) { FPRINTF (OUT, "% - * S% -5d% * S", Name_len-1, Szname, ScustId, Phone_Len-1, Szphone);} else {breaf; } } 2.25 Disconnect the connection with the data source When you complete the database operation, you can call the SQLDisConnect function to close the same data source connection. When the transaction operation of the handle is not completed, the application calls SQLDISCONNECT, at this time, the drive returns SQLState 25000, indicating that the transaction does not change and the connection is turned on. When the application calls SQLDisconnect, the driver automatically releases the parameters, descriptors, and the like when the driver is automatically released when the data source is disconnected before SQLDisConnect. However, when a parameter connected to it is still asynchronous operation, SqlDisconnect returns SQL_ERROR, and the value of SQLState is set to HY010.2.26 Release ODBC Environment. The final step is to release the ODBC environment parameters. 2.3 ODBC API Programming Summary Research and application of relational databases is one of the most active fields in the computer world. There are colorful colors of various database products. There are thousands of autumn; this situation brings advantage to users, on the other hand, also give the application transplant Difficulties. Especially in the client / server architecture, when the user has access to different servers from the client, the database system of these servers is different, and the interconnect between the database is a problem, so Microsoft ODBC is proposed. Due to the advanced nature of ODBC and its Microsoft's open strategy, ODBC has now become in fact industrial standards. It is currently a strong solution in database applications, which is gradually become a standard interface on the Windows platform. ODBC is a standard application design interface (API) used to disable data in the associated or unrelated database management system (DBMS). Its basic idea is to provide users with simple, standard, transparent, unified database-coupled public programming interfaces, which can provide users with consistent application development interfaces, so that applications are independent of database products, implementing various Communication between the database. Developers realize the underlying driver based on the ODBC standard, which is transparent to the user. As a standard technology, ODBC has the following main features: ODBC: 1 · ODBC is a program design interface using SQL; 2 · ODBC design is based on the client / server architecture; 3 · ODBC enables application developers to avoid complexity with data source; 4 · The structure of the ODBC allows multiple applications to access multiple data sources, that is, the relationship between the application and the data source is a multi-to-many relationship. 3, use the MFC to access the ODBC data source 3.1 Overview The Visualc MFC class library defines several database classes. When programming with ODBC, you are often used to use CDATABASE, CRecordSet (Record Class), and CRecordView. among them: The CDATABASE class object provides a connection to the data source, and you can operate the data source by it. The CRecordView class object can display database records in the form of control. This view is a table view that is directly connected to a CRecordset object. The CRecordset class object provides a recordset that extracted from the data source. CRecordset objects are usually used in two forms: dynamic rows (Dynastes) and snapshots. Dynamic rows can keep synchronization with changes made with other users. The snapshot set is a static view of the data. Each form provides a set of records when the recording set is opened, and the difference is that when you scroll to a record in a dynamic row, other users or other records in your application are The changes made by the record will be displayed accordingly. Visual C provides several records that can be used to customize the application's way. The fastest way to see these different options should take care of speed and feature. You will find that in many cases, if you want to add a feature, you must pay the price of the program to perform speed reduction. Let's tell you some of the records that can be dominated. More importantly, to tell you that you can get faster speed or more features from this option. 1, Snapshot (Snapshot) This option To download the entire query in a snapshot of Visual C . In other words, the content of the database is quickly taken in time, and it is the foundation of future work. This method has three shortcomings. First, you can't see the updates of others on the network, which may mean that your decision is based on old information. Second, download all these records once, which means adding a heavy burden to the network during download. Third, the user will end when the download is downloaded, which means that the call performance of the network becomes lower. However, this approach has two advantages. First, once the record is downloaded, the network activity required by the station is almost no date, which releases the bandwidth for other requests. In short, you will see that the throughput of the network is increased. Second, because all the records of the application are in the user's machine, the user actually obtains better overall performance. You may want to limit the method of snapshots on a smaller database because snapshots apply to user request information and do not apply to data editing sessions. 2, DYNASET (Dynamic Set) Use this option, Visual C creates the actual pointer to each record of the requested. In addition, only records actually needed when filling the screen is downloaded from the server. The advantage of this method is obvious. Almost immediately can see the record on the screen. And you will also see other users made changes to the database. Finally, other users will also see what you do, because the dynamic set is uploaded to the server when you change your record. Obviously, this method requires real-time access to the server, which reduces the total network throughput and reduces the performance of the application. This option is suitable for creating a user to take a lot of time to edit the data. At the same time, it is also the best choice for large databases because only information actually needs. 3.2 Applying ODBC Programming AppWizard can be applied to create an ODBC application framework, or you can use ODBC to program programming directly. At this time, the header file AFXDB.H should be included. Applying ODBC programming two most important classes are CDatabase and CRecordset, but in the application, you should not use the CRecordset class directly, and you must generate an export class from the CRecordset class and add a member variable corresponding to the fields in the database table. Subsequently, the member function of the CRecordset class DOFIELDEXCHANGE, which completes the database field with the RFX function and the data exchange of the recorded domain data member variable, the RFX function is similar to the dialog box data exchange (DDX) mechanism, responsible for completing the database and members Data exchange between variables. The following example shows the programming skills of ODBC in the Visualc environment: 3.21 Database connection Define a member variable m_pdatabase in the CRecordset class: CDATABASE * m_PDATABASE; It is a pointer to the object database class. If you pass a open CDatabase class object norm to M_PDatabase, you can share the same CDatabase class object before the CRecordset class object calls the Open () function. Such as: CDATABASE M_DB; CRecordset m_set1, m_set2; m_db.open (_t ("super_es")); // Building ODBC connection m_set1.m_pdatabase = & m_db; // m_set1 multiplex m_db object m_set2.m_pdatabase = & m_db; // m_set2 multiplex m_db object Or as follows: CDATABASE DB; DB.Open ("Database"); // Establish an ODBC connection CRecordset M_Set (& DB); // Constructs record set objects, making database points to DB 3.22 query record The query record uses CRecordset :: Open () and CRecordset :: Requery () member functions. Before using the CRecordset class object, you must use the CRecordset :: Open () function to get a valid recordset. Once you have used the CRecordset :: Open () function, you can apply the CRecordset :: Requery () function when you query. When calling the CRecordset :: Open () function, if you have passed a M_PDatabase member variable that has been passed to the CRecordset class object, use the database object to create an ODBC connection; otherwise, if m_pdatabase is an empty pointer, you will create a new one. The CDATABASE class object is connected to the default data source and then performs the initialization of CRecordset class objects. The default data source is obtained by a getDefaultconnect () function. You can also provide the SQL statement you need, and call CRecordset :: open () functions, for example: m_set.open (AFX_DATABASE_USE_DEFAULT, STRSQL); If you do not specify a parameter, the program uses the default SQL statement to operate the SQL statement specified in the getDefaultsql () function: CString CTESTRECORDSET :: getDefaultsql () {RETURN _T ("[BasicData], [MAINSIZE]"); For the table name returned for the getDefaultsql () function, the corresponding default operation is a SELECT statement, namely: Select * from BasicData, MAINSIZE The CRECORDSET's member variable M_Strfilter and M_STRSORT can also be used in the query process. m_strfilter is a filter string, stores the condition string after WHERE in the SQL statement; m_strsort is a sort string, stores the string after ORDERBY in the SQL statement. Such as: m_set.m_strfilter = "Type = 'Motor'"; m_set.m_strsort = "Voltage"; m_set.Requery (); The corresponding SQL statement is: Select * from BasicData, MAINSIZE Where type = 'electric machine ORDER by Voltage In addition to the direct assignment to m_strfilter, parameterization can be used. Using parameterization can be more intuitive and more convenient to complete the conditional query task. The steps to use parameterization are as follows: (1). Disclaimer the parameters: CSTRING P1; Float p2; (2). Initialize the amount of parameters in the constructor P1 = _t (""); P2 = 0.0F; m_nparams = 2; (3). Bind the parameters with the corresponding column PFX-> setfieldType (cfieldexchange :: param) RFX_Text (PFX, _T ("P1"), P1); RFX_SINGLE (PFX, _T ("p2"), p2); After completing the above steps, you can use the parameters to check: m_pset-> m_strfilter = "type =? Andvoltage =?" M_pset-> p1 = "electric motor"; m_pset-> p2 = 60.0; m_pset-> Requory (); The value of the parameter is replaced in the "?" Adaptifier in the query string according to the order of the bound. If the result of the query is a plurality of records, you can use the CRecordset class to move the cursor using the CRecordset class. Move (), MoveFirst (), MoveLast (). 3.23 Add record Increase records use the addNew () function, requiring the database to be opened in a way that is allowed: m_pset-> addnew (); // Add a new record at the end of the table m_pset-> setfieldnull (& (M_Pset-> M_Type), False); m_pset-> m_type = "electric motor"; ... // Enter a new field value m_pset-> Update (); // Deposit a new record into the database m_pset-> Requory (); // Reconstruction record set 3.24 Delete Record Use the delete () function directly, and do not call the Update () function after calling the delete () function: m_pset-> delete (); IF (! m_pset-> ISEOF ()) m_pset-> moext (); Else m_pset-> movelast (); 3.25 Modify Record Modify the record using the edit () function: m_pset-> edit (); // Modify the current record m_pset-> m_type = "Generator"; / / Modify the current record field value ... m_pset-> update (); // Deposit the modification into the database m_pset-> Requory (); 3.26 statistical record The statistical record used to count the total number of records. You can declare a CRecordset object m_pset first. Bind a variable m_lcount to count the total number of records. Perform the following statement: m_pset-> open ("SELECT Count (*) from Name WHERE Limit Conditions"); Recordcount = m_pset-> m_lcount; m_pset-> close (); RecordCount is the number of records to be statistical. Or as follows: CRecordset M_Set (& DB); // DB is CDATABASE object CString Strvalue; m_set.open (Select Count (*) from FROM Name WHERE Limited Conditions "); m_pset.getfieldValue ((int) 0, strvalue); Long count = atol (strValue); m_set.close (); COUNT is the total number of records. 3.27 Execute SQL statement Although we can do most query operations through the CRecordset class, and in the CRecordset :: Open () function, we can also provide SQL statements, but sometimes we want to make some other operations, such as establishing a new table, deleting a table, Establish a new field, etc., then you need to use the mechanism of direct SQL statements to the CDATABASE class. Direct execution of the SQL statement is completed by the calling cDatabase :: executesql () function: As shown below Bool CDB :: EXECUTESQLANDREPORTFAILURURE (Const Cstring & Strsql) { Try { m_pdb-> executesql (strsql); // Execute SQL statement directly } Catch (cdbexception, e) { CString strmsg; STRMSG.LOADSTRING (IDS_EXECUTE_SQL_FAILD); STRMSG = strsql; Return False; } END_CATCH Return True; } It should be noted that since the data operation statement provided by different DBMS is not the same, the SQL statement directly executes the SQL statement may destroy the software's DBMS independence, so this type of operation should be used with caution. 3.28 Note If the data exported from the CRecordset, if the data contains the DataTime type, in the VC is replaced with a CTIME type, at this time, the constructor does not mean the default. At this time, we should assign a value. As follows: CTIME M_TIME; M_TIME = NULL; 3.3 Summary The ODBC class library in Visualc can help programmers complete the vast majority of database operations. Using ODBC technology, the programmer explained from the specific DBMS, thereby greatly reduced the workload of software development, shortening the development cycle, and improving efficiency and software reliability. 4, use DAO 4.1 Overview Visual C provides a package of DAO, MFC DAO classes encapsulated most of the DAO (Database Access Object), which can use Visual C programs to use the MFC DAO classes provided by Visual C to easily access Microsoft Jet databases, compile, There are Visaul C Database applications. The Database Access Object (DAO) provides a mechanism for creating and manipulating a database through program code. Multiple DAO objects constitute an architecture, in which each DAO object works together. DAO supports the following four database options: Open Access Database (MDB file) - The MDB file is a self-contained database, which includes query definition, security information, index, relationship, and of course there are actual data sheets. Users only specify the path name of the MDB file. Open the ODBC data source directly - there is a very important limit here. You cannot find an ODBC data source as a driver as a driver; you can only use the data source with your own ODBC driver DLL. Use the JET engine to open the ISAM type (index order access method) data source (including DBASE, FoxPro, Paradox, Btrieve, Excel or text file) - Even if an ODBC data source has been set, use the JET engine to access these file types One of them must also be found in the way ISAM data source, not the way ODBC data source. Additional external tables to the Access database - this is actually the preferred method of accessing the ODBC data source with DAO. First use Access to add the ODBC table to an MDB file, and then find this MDB file with DAO according to the method described in the first option. Users can also use Access to attach the IASM file to an MDB file. 4.2 Application DAO Programming 4.21 Open Database The CDAOWORKSpace object represents a DAO Workspace object, which is at the highest in the MFC DAO architecture, defines a session of a user with a database, and contains the open database, responsible for completing the transaction processing of the database. We can use an implicit Workspace object. The CDAODatabase object represents a connection to the database, in the MFC, is encapsulated by CDAODatabase. There are two ways when constructing a CDAODatabase object: Create a CDAODATABASE object and deliver a pointer to a CDAOWORKSPACE object that has been found. Create a CDAODATABASE object without explicitly specifying Workspace, at this point, MFC will create a new temporary CDAOWORKSPACE object. The following code shows: CDAODATABASE DB; DB.Open ("Test.mdb", False, False, _T (""); The parameter includes the full path name of the file to be opened. 4.22 query record A DAO Recordset object represents a collection of data records, which is a library table or a full record in a query run. There are three types of CDAORECORSET objects: tables, dynamic sets, snapshots. Typically, we can use CDAORECORDSET in your application, which is usually generated by ClassWizard or AppWizard. But we can also use the object generated by the CDaorecordset class directly. At this point, we can dynamically bind the data members of the Recordset object. The following code shows: Colevariant var; Long id; CString Str; CDAORECORDSET M_SET (& DB); m_set.open ("SQL statement"); While (! m_set.iseof ()) { / * deal with m_set.getfieldValue ("ID", var); ID = V_I4 (var); m_set.getfieldValue ("Name", var); Str = var.pbval; * / m_set.movenext (); } m_set.close (); 4.23 Add Record Add a record with the AddNew function, at this point with SetFieldValue to assign. The following code shows: m_pdaorecordset-> addnew (); Sprintf (strValue, "% s",> m_username); m_pdaorecordset-> setfieldValue ("UserName", Strvalue; Sprintf (strValue, "% d", m_pointid); m_pdaorecordset-> SetFieldValue ("PointID", Strvalue; DataSrc.SetDateTime (m_updatetime .getyear), m_updatetime .getmonth, m_updatetime .getday (), M_updatetime .gethour (), m_updatetime .getminute (), m_updatetime .getsecond ()); Valvalue = datasrc; m_pdaorecordset-> setfieldValue ("UpdateTime", Valvalue); Sprintf (strValue, "% f", m_precordset-> m_oldvalue); m_pdaorecordset-> SetFieldValue ("OldValue", Strvalue; Sprintf (strValue, "% f", m_precordset-> m_newvalue); m_pdaorecordset-> SetFieldValue ("NewValue", Strvalue; m_pdaorecordset-> Update (); At this point, it is important to note that the datetime data is to be assigned with the setDataTime function, which is used to use ColeVariant type data, and the specific usage can be referred to the help. 4.24 Modify Record Modify the record with the edit () function, position the record to the location you want to modify, call the Edit function, and then call the Update function after the modification is complete. The following code shows: m_set.edit (); m_set.setfieldValue ("Column Name", "String"); m_set.update (); 4.25 Delete Record Deleting a record with a delete () function, does not need to call the Update () function after use. 4.26 statistical record You can use the following code to count the number of records: Colevariant Varvalue; CDAORECORDSET M_SET (& DB); m_set.open (DBOPENDYNASET, "SQL statement"); Varvalue = m_set.getfieldValue (0); M_Lmaxcount = V_I4 (& Varvalue); m_set.close (); If you are a total record in a table, you can use the CDaOTableDef object, as shown below: CDaotableDef m_set (& gusedb); Count = m_set.getRecordcount (); m_set.close (); The number of records cannot be obtained with the getRecordCount () of the CDaorecordSet object. 4.3 Summary With DAO technology, we can easily access the Microsoft Jet Engine database. Since Microsoft Jet does not support multi-thread, all DAOs that are called to the application main thread must be restricted. 5 use OLE DB 5.1 Overview The presence of OLE DB provides users with a unified approach to access all kinds of data sources. OLE DB can be converted in different data sources. With OLE DB, the client's developer only needs to focus on some of some details when performing data access, without having to understand a large number of different database access protocols. OLE DB is an ActiveX interface accessing data via COM interface. This OLE DB interface is quite common enough to provide a unified means of accessing data regardless of the method used by storing data. At the same time, OLE DB also allows developers to continue to use the advantages of underlying database technology without having to remove data in order to utilize these advantages. 5.2 Using the ATL Using OLE DB Data Usage Due to the direct use of OLE DB objects and interface design database applications need to write a lot of code. To simplify the programming, Visual C provides an ATL template for designing OLE DB data applications and data providers. Using the ATL template can easily combine the OLE DB with MFC to simplify complex programming of the parameter query of the database. The database class provided by the MFC makes the OLE DB programming more object-oriented features. VIUAL C provides an ATL template for OLE DB can be divided into a template for data providers and data using a data usage program. Creating a data application using an ATL template usually has the following steps: Create an application framework Join the template class generated by ATL Use the generated data access object in the application No ATL uses OLE DB data usage The use of the ATL template to generate data usage is simpler, but the applicability is not wide, and it cannot be dynamically adapted to changes in the database. Below we introduce to generate data usage programs directly using the MFC OLE DB class. Template OLE DB Data User Templates consists of some templates, including some templates, which are described below for some common classes. Session CDataSource class The CDataSource class corresponds to the data source object of the OLE DB. This class represents the connection between the OLE DB data provider and the data source. Only when the connection of the data source is established, the session object can be generated, and open the Open to open the connection of the data source. CSession The object created by CSession represents a session accessed separately. A data source object generated by a CDataSource class can create one or more sessions, to generate a session object on the data source object, need to call the function open () to open. At the same time, the session object can also be used to create transaction operations. CENUMERACCESSOR class The CenumeratorAccessor class is an accessor for information on the available data providers generated by the enumerator queries to provide currently available data providers and visible accessors. Accessor class CACESSOR class Caccessor class represents the type of accessor. This class can be used when the user knows the type and structure of the database. It supports multiple accessors to a rowset, and the buffer that stores data is assigned by the user. CDynamicAccessor class The CDynamicAccessor class is used to create an accessor when the program is running. When the system is run, you can dynamically obtain the information of the column, and the accessor can be dynamically created according to this information. Cmanualaccessor class In the CmanualAccessor class, the columns are binded to the variables or the parameters to the variables when the program is running. Row Crowset class The Crowset class encapsulates the ranked object and the corresponding interface, and provides some methods for querying, setting data, etc. You can use the MOVE () and other functions to be recorded, read data with the getData () function, update the data with INSERT (), Delete (), setData (). CBULKROWSET class The CBULKROWSET class is used to retrieve multiple row seductions or operate multiple rows in a call. CarrayRowset class The CarrayRowSet class provides data access with the array subscript. Command class CTABLE class The CTABLE is used to obtain a database with a simple access to the database, and the data source is obtained, resulting in data. CCOMMAND class CCOMMAND class is used to support the data source of the command. You can use the open () function to perform the SQL command, or you can prepare the command before the PREPARE () function, which can improve the flexibility and robustness of the program for the data sources of the command. In the stdafx.h header file, add the following code. #include EXTERN CCOMMODULE _MODULE; #include #include #include // if you are us for schema templates In the stdafx.cpp file, add the following code. #include CCOMMODULE _MODULE; Decide what type of access procedures and rows are used. retrieve data After opening the data source, session, and ranking objects can get data. The acquired data type depends on the access program used, may need to be bound columns. Follow the steps below. Use the correct command to open the rowset object. If you use CManualAccessor, bind to the corresponding column before use. To bind columns, you can use functions getColumnInfo, as shown below: // Get the column information Ulong ulcolumns = 0; DBColumnInfo * pcolumninfo = null; LpoLESTR PSTRINGS = NULL; IF (gcolumn, & pcolumninfo, & pstrings)! = S_OK) AFXTHROWOLEDBEXCEPTION (rs.m_prowset, IID_ICOLumnSINFO); struct mybind * pbind = new mybind [ulcolumn]; Rs.createAccessor (ulcolumns, & pbind [0], sizeof (mybind) * ulcolumns); For (Ulong L = 0; L Rs.addbindentry (L 1, DBTYPE_STR, SIZEOF (TCHAR) * 40, & PBIND [L] SzValue, Null, & Pbind [l] .dwstatus); Rs.bind (); Use the While loop to take the data. In the loop, call MOVENEXT to test whether the return value of the cursor is S_OK, as shown below: While (rs.movenext () == s_ok) { // Add code to fetch data here // if you are not using an auto accessor, call getdata () } In the While cycle, data can be obtained through different access programs. If you are using the Caccessor class, you can access direct access by using their data members. As follows: If you are using a CDynamicAccessor or a CDynamicParameterAccessor class, you can get data by getValue or getColumn function. You can use getType to get the data type used. As follows: While (rs.movenext () == s_ok) { // Use the Dynamic Accessor functions to retrieve Your // data Ulong ulcolumns = rs.getColumnCount (); For (ulong i = 0; i { Rs.getValue (i); } } If you are using CManualAccessor, you can specify your own data member to bind them. You can access directly. As follows: While (rs.movenext () == s_ok) { // use the data members you specified in the calls to // addbindentry. WSPRINTF ("% s", SZFOO); } Decide the data type of the row Data types are determined at runtime, with dynamic or manual access programs. If you use a manual access program, you can use the getColumnInfo function to get the column information of the rowset. From here you can get the data type. 5.4 Summary Since there are now a variety of data sources, the only way to access these data will be implemented by some similar mechanisms, such as OLE DB. The advanced OLE DB structure is divided into two parts: customers and providers. The customer uses the data generated by the provider. Just like other COM-based most structures, OLE DB developers need to implement a lot of interfaces, most of which are template files. When generating a customer object, you can create a simple customer with a data source via the ATL Object Wizard. The ATL Object Wizard will check the data source and create a client agent for the database. From there, you can use the standard browsing function through the OLE DB customer template. When generating a provider, the wizard provides a good start that only generates a simple provider to list files in a certain directory. Then, the provider template contains full complementary content supported by OLE DB. With this support, users can create OLE DB providers to implement rowset positioning strategies, data read and write, and build bookmarks. 6, use ADO 6.1 Overview ADO is an ActiveX Data Object, which is an object-oriented new interface for Microsoft Develop Database Applications. The ADO Access Database is performed by accessing the OLE DB data provider, providing a simple high-level access interface for the OLE DB data provider. ADO technology simplifies OLE DB operation, and a large number of COM interfaces are used in OLE DB programs, while ADO encapsulates these interfaces. Therefore, ADO is a high-level access technology. ADO technology is based on General Object Model (COM), which provides access technology for multiple languages, and because ADO provides access to automation interfaces, ADO can use the description scripting language to access VBScript, VCScript, etc. 6.2 Using ADO in VC You can use the ActiveX control for VC6 to develop applications, and you can develop applications with ADO objects. Developing applications using ADO objects allows program developers to control access to databases, resulting in database access programs that meet user needs. Developing applications using ADO objects is similar to other technologies, you need to generate connections to data sources, create records, but different from other access technologies, the levels and sequential relationship requirements between ADO technologies are not too strict. During the program development, you don't have to select a connection before you can generate a record object. You can use the record object directly in the use of records, and the program automatically establishes a connection with the data source while creating a recording object. This model has strong simplified programming and enhances the flexibility of the program. The following describes how to use ADO objects. 6.21 Introducing an ADO library file Before using ADO, you must introduce the STDAFX.H file in the project to introduce the ADO library file to enable the compiler to compile correctly. The code is as follows: #define initguid #import "c: / program files / commit files / system / ado / msado15.dll" no_namespace rename ("EOF", "endoffile") #include "icrsint.h" This list of statements declare use ADO in the project, but does not use ADO's namespace, and rename EOF to endoffile in order to avoid conflicts. 6.22 Initializing the ADO environment The COM environment must be initialized before using the ADO object. Initialization COM environment can be done with the following code: :: Coinitialize (NULL); After initializing the COM environment, you can use the ADO object. If this code is not added in front of the program, a COM error will be generated. After using the ADO object, you need to use the following code to release the initialized object: :: Couninitialize (); This function clears the COM environment prepared for the ADO object. 6.23 Introduction The ADO library contains three basic interfaces: __ConnectionPTR interface, __Commandptr interface, __Recordsetptr interface, __ConnectionPTR interface returns a recordset or an empty pointer. It is usually used to create a data connection or perform a SQL statement that does not return any result, such as a stored procedure. Returning a recordset with the __connectionptr interface is not a good way of use. Usually, like CDATABASE, use it to create a data connection, then use other objects to perform data input output operations. __Commandptr The interface returns a recordset. It provides a simple way to perform the stored procedures and SQL statements that returns the record set. When using the __commandptr interface, you can use the global __connectionptr interface, or you can use the connection string directly in the __commandptr interface. If only one or more data access is performed, the latter is a better choice. But if you want to access the database frequently and return a lot of record sets, you should use the global __connectionptr interface to create a data connection, then use the __commandptr interface to perform the stored procedure and SQL statement. __Recordsetptr is a recordset object. Compared with the above two objects, it provides more control functions to records, such as record lock, cursor control, and the like. Like the __commandptr interface, it does not have to use a created data connection, you can use a connection string instead of the Connection member variable assigned to __recordsetptr, let it create data connections yourself. If you want to use multiple record sets, the best way is to use the Global-ConnectionPTR interface that has created a data connection as the Command object, and then use the __recordse7tptr to perform the stored procedure and SQL statement. 6,24 Using ADO Access Database __ConnectionPtr is a connection interface, first create a __connectionPTR interface instance, then point to and open an ODBC data source or OLE DB data provider (Provider). The following code creates a DSN and non-DSN-based data connection. // Use __connectionptr (based on DSN) __ConnectionPtr MyDB; Mydb.createInstance (__ uuidof (connection)); MYDB-> Open ("DSN = SAMP; UID = admin; pwd = admin", "", ", - 1); // Use -ConnectionPTR (based on non-DSN) __ConnectionPtr MyDB; Mydb.createInstance (__ uuidof (connection)); Mydb.open ("provider = sqloledb; server = server; database = SAMP; UID = admin; pwd = admin", ",", ", - 1); // Use __recordsetptr to execute SQL statements __Recordsetptr myset; MySet.createInstance (__ uuidof (recordset)); MySet-> Open ("SELECT * from Some__Table", Mydb.GetInterfacePtr (), AdoPENDYNAMIC, ADLOCKTIMISTICISTICISTIC, ADCMDTEXT Now we already have a data connection and a recordset, and you can use data. As can be seen from the following code, using the __recordsetptr interface, it is not necessary to frequently use the large and complex data structure Variant like Dao, and forced to convert various data types, this is also one of the advantages of ADO. Assume that the program has a listbox control named m__list. The following code we use the __recordsetPtr interface to get recordset data and populate this ListBox control: __variant__t Holder Try {while (! myset-> adoEof) {Holder = MySet-> getCollect ("Field__1"); if (Holder.Vt! = VT__NULL) m__list.addstring ((char ) __ bstr__t (Holder); MySet-> MoveNext ();}} Catch (__ com__error e) {Cstring error = e-> errorMessage (); AfxMessageBox (E-> ErrorMessage ()); } catch (...) {MessageBox ("ADO error!"); You must always use TRY and CATCH to capture an ADO error in your code, otherwise the ADO error will make your application crash. When the ADO runs an error (such as the database does not exist), the OLE DB data provider will automatically create a __com_rm_rror object and populate the error message to the member variable of this object. 6.25 Type Conversion Since the COM object is a cross-platform, it uses a generic method to handle various types of data, so the CSTRING class and COM object are incompatible, we need a set of APIs to convert COM objects and C types of data. __vatiant__t and __BSTR_T are the two objects. They provide a general method to convert COM objects and C types of data. 6.3 Using ADO in VB ADO provides ways to do the following: 1. Connect to the data source. At the same time, it is determined whether all changes to the data source have been successful or not. 2. Specify the command to access the data source and can be used with variable parameters, or optimize it. 3, execute the command. 3. If this command returns the data in the form of rows in the table, these rows are stored in a cache that is easy to check, operation, or changes. 4. Under appropriate cases, the data source can be updated using the change of the cache line. 5. Provide conventional methods to detect errors (usually caused by establishing a connection or execution command). In a typical case, all of these steps are required in the programming model. However, since ADO has strong flexibility, you can do some useful work at the end only need to perform some modules. The following elements are key parts in the ADO programming model: 6.31 connection With "connection", you can access the data source from the application, the connection is the environment necessary to exchange data. The object model uses the Connection object to make the connection concept avatar. "Transaction" is used to define the beginning and end of a series of data access operations that occur during the connection. ADO can clarify changes or successful changes in the operation in the transaction, or have not occurred at all. If the transaction is canceled or its operation fails, the final result will seem that the operation in the transaction has not occurred, and the data source will remain the state before the transaction begins. The object model cannot clearly reflect the concept of transactions, but in a group of Connection object methods. ADO Accesses data and services from the OLE DB provider. The Connection object is used to specify a special provider and any parameters. 6.32 command The "command" emitted by the established connection can operate the data source in some way. In general, the command can add, delete, or update data in the data source, or retrieve data in the form of rows. Object models reflect command concept with Command objects with Command objects. Use the Command object to enable the execution of the ADO optimization command. parameter Typically, the variable part required by the command is "parameter" can be changed before the command is released. For example, you can repeat the same data retrieval command, but each time you can change the specified search information. The parameter is very useful for the executable commands as the function activity, so you know what the command is doing, but you don't have to know how it works. For example, a bank transfer command can be issued, from one party to the other party. You can set the amount of payee to the parameter. Object models use parameter objects to reflect parameters concept. 6.33 record set If the command is a query that returns data in the table (row return query), these rows will be stored locally. The object model refers to the storage as a Recordset object. However, there is no object that represents only a single RECORDSET line only. The recordset is the most important method for checking and modifying data in the row. 6.34 field A record collection line contains one or more "fields". If the record set is regarded as a two-dimensional grid, the field will be arranged to "column". Each field (column) contains the properties of the name, data type, and value, which is in this value that contains real data from the data source. The object model reflects fields in the field object. To modify the data in the data source, you can modify the value of the Field object in the record collection line, and the change to the record set is ultimately transmitted to the data source. As an option, the transaction management method of the Connection object can reliably guarantee that the changes are all successful, or all failed. 6.35 error Error can occur in the application at any time, usually due to the operation of the connection, execution command, or some state (for example, trying to use a recordset without initialization). The object model reflects an error in Error object. Any given error generates one or more Error objects, and the resulting error will give up the previous ERROR object group. 6.36 attribute Each ADO object has a set of unique "properties" to describe or control the behavior of the object. The attribute has two types of built-in and dynamics. The built-in attribute is part of the ADO object and is available at any time. Dynamic properties are added by the special data provider to the properties of the ADO object, only when the provider is used. The object model reflects attributes in a Property object. 6.37 collection ADO provides a "collection", which is convenient to include object types of other special type objects. Use the collection method to retrieve the objects in the collection according to the name (text string) or serial number (integer). ADO provides four types of sets: The Connection object has an Erroors collection that includes all Error objects created for a single error associated with the data source. The Command object has a parameters collection that contains all Parameter objects that are applied to the Command object. The Recordset object has a Fields collection that contains all Field objects that define the Recordset object column. In addition, Connection, Command, Recordset, and Field objects have a Properties collection. It contains all Property objects that belong to each containing objects. The ADO object has an attribute that can set or retrieve values such as "Integer", "Character", or "Boolean" that can be used thereon. However, it is necessary to see some properties as the return value of the data type "Collection Object". Correspondingly, the collection object has a method of storing and retrieving other objects suitable for this collection. 6.38 incident ADO 2.0 supports events, events are notifications for some operations or have already occurred. There are two types of events: ConnectionEvent and RecordseTevent. The Connection object produces a ConnectionEvent event, and the Recordset object produces a RecordseTevent event. The event is handled by an event handler routine, which is called before or after an operation begins. Some events are paired. The event name format called before starting operation is Willevent (Will Event), and the event name format called after the operation is EventComplete (Complete event). The rest of the incident only occurs after the end of the operation. (The name does not have any fixed mode.) The event handler is controlled by the status parameter. Additional information is provided by errors and object parameters. You can request an event handler to do not accept any notifications after the first notification. For example, you can choose to receive only WILL events or Complete events. The following code shows an example of using ADO. First join Microsoft ActiveX Data Object 2.0 library reference. DIM DB AS Connection SET DB = New Connection db.cursorlocation = aduseclient DB.Open "provider = msdasql; dsn = testdatabase", "sa", "", -1 DIM I as long DIM ID as long DIM Value As Single DIM RST AS New Recordset Set RST = New Recordset Rst.open "SELECT * FROM Mechanistic Change Historical Table", DB, AdoPENDYNAMIC, ADLOCKOPTIMISTIC Rst.Movefirst For i = 0 to Rst.Recordcount - 1 ID = RST.Fields ("ID") Value = Rst.fields ("Value") Rst.movenext Next I Rst.Close SET RST = Nothing Db.close 6.4 Summary ADO technology is a new technique for accessing the database, which is easy to use, flexible, and widely used. The characteristics of accessing the data source with ADO can be summarized as follows: Easy to use This is the most important feature of ADO technology. Since ADO is a high-level application, it has an object-oriented property relative to OLE DB or ODBC. At the same time, in the object structure of the ADO, the hierarchical relationship between its objects is not obvious. In terms of access techniques such as DAO, there is no need to care for the structure of the object and the construction level. For objects to be used, you don't have to select an object such as a connection, a session, etc., you can easily construct it, it is convenient for the application. High speed access data source Since the ADO technology is based on OLE DB, it also inherits the high speed of the OLE DB access to the database. Can access different data sources ADO technology can access all file systems including relational databases and non-relational databases. This feature makes the application much more flexibility and versatility. Can be used for Microsoft ActiveX Pages ADO technology can appear in the form of ActiveX controls, so that can be used for Microsoft ActiveX pages, this feature simplifies the programming of the web page. Program occupation memory Since ADO is a component object model (COM) access technology, the application generated by ADO occupies less memory. 7, summary It is not easy to judge which technology should be used when accessing the data. A common utility may require a utility to handle multiple database types; some data may appear on the local hard drive, partially on the network, and some on the host. Even products that customers install on the device will make this choice more difficult. For example, what you expect ODBC support may depend on the version installed by Microsoft Office because this product does not provide ODBC support. You will also find that the objects and methods provided by the ADO class are more than the ODBC class. Ado can provide some feature programs that must be absolutely programs. For example, you will find that OLE-DB and ADO support DFX_CURRENCY, but there is no corresponding function in ODBC, but you have to master them must pay a certain effort. When OLE-DB or ODBC, there are several general rules. Because ADO is actually only the package of OLE-DB, these rules apply to it. Some basic principles are provided below, which can be used to help you decide to choose OLE-DB or ODBC. Non-OLE Environment If you want to access a database that supports ODBC, the database is not supported on the OLE server, then ODBC is the best choice. Non-SQL Environment ODBC is very outstanding when processing SQL. When processed non-SQL databases, OLE-DB has a very significant advantage. OLE Environment For support OLE's servers, select OLE-DB or ODBC may be a half. If there is an ODBC driver to be available, use ODBC is a good idea; otherwise, only OLE-DB is selected. The required interoperability If you need interoperable database parts, only OLE-DB is selected.
