Implementation and Application of MySQL database interface VC

MySQL database interface VC implementation and application-general Linux technology-Linux programming and kernel information, the following is a detailed description. Implementation and Application of MySQL database interface VC
Baidu

Abstract: MySQL database has excellent performance as a network database, but it is rarely used in application software. This article will mainly discuss the application of two database interfaces (odbc api and c api) provided by MySQL in VC, and form a class to encapsulate the function of c api database interface.

Key words: MySQL; database interface; odbc api; C API


Introduction

With the development of modern computer software and hardware and network technology, searching for information on the internet has become one of the most important methods to obtain information. As we all know, all online information is stored in the website database. The functions of such operations are provided by the database server. Obviously, the performance of the database server is directly related to the survival of the website. The most frequently used database servers in website construction are oracle and MySQL. The former is powerful and belongs to the flagship database server, but it has invested too much in the early stage. The latter features are constantly improved, easy to use, and without losing performance, and can be obtained for free. Therefore, MySQL is used for database servers of many small and medium websites. In addition, MySQL is also used for some large websites due to its outstanding performance.

It can be seen that the performance of the MySQL database server in website construction is outstanding. However, because of this, few people use MySQL for application software development. This article will introduce in detail the application of different interfaces of MySQL database server in VC environment, and finally generate a CDatabase class to encapsulate the MySQL database interface function.

Computer environment for database interface Implementation and Application: hardware, C466, 128 M, 15G; software and operating system, visual c ++ 6.0, MySQL 3.23.22-beta for win32, WIN98.

1. MySQL database Overview

MySQL is a real multi-user, multi-thread SQL database server. SQL (Structured Query Language) is the world's most popular and standardized database language. It makes it easier to store, update, and access information. MySQL is the implementation of a Client/Server structure. It consists of a server daemon mysqld and many different client programs and libraries.

MySQL is designed to be fast, robust, and easy to use. It was initially because the founder of MySQL needed such an SQL server that could process large databases on an order of magnitude with any manufacturers providing databases on non-expensive hardware platforms, but the speed was faster, mySQL is developed as a result. It provides APIs for C, C ++, JAVA (JDBC), Perl, Python, PHP and TCL. It supports multiple platforms, including Solaris, SunOS, BSDI, sgi irix, AIX, dec unix, Linux, FreeBSD, SCO OpenServer, NetBSD, OpenBSD, HPUX, Win9x and NT; diverse data types, including/unsigned 1, 2, 3, 4, 8-byte integers, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, DATETIME, YEAR, SET, ENUM; flexible and secure permission system, password encryption; provides ODBC interfaces for Windows, access can be used to connect to the table. A third-party developer provides a variety of ODBC drivers. in MySQL 3.23, the maximum table size of MyISAM can be 8 million TB (2 ^ 63 bytes ); supported in multiple languages, but not in Chinese. However, for fast, robust, and easy-to-use purposes, MySQL must sacrifice some flexible and powerful functions, such as subqueries, stored procedures and triggers, foreign keys, and transaction processing. Therefore, MySQL lacks flexibility in some applications, but it also makes it faster to process data than other database servers at least 2 ~ 3 times.

MySQL itself does not support Windows graphical interface, therefore, all the database operations and management functions can only be completed in the MS-DOS mode. First, you must log on to the MySQL server, that is, enter MySQL at the prompt \ MYSQL \ BIN>? H host? U user? Ppassword. When the prompt is changed to mysql>, the logon is successful. Then, select the database to be operated on, that is, mysql> USE databasename. Then you can use the SQL statement to query. To exit the database server, run the QUIT command to exit the server. Of course, due to the increasing popularity of MySQL, many third-party software companies have released a graphical interface support software for MySQL in Windows, for example, EMS mysql manager of EMS company provides the MySQL database operation function in Windows format.

2. Use ODBC APIs to call MySQL database functions

ODBC (Open Database Connectivity) is an Open Database interconnection. As an important part of the Windows Open structure, it is already familiar to many Windows programmers. ODBC depends on the drivers provided by Database manufacturers, when using ODBC APIs, the Windows ODBC management program passes database access requests to the correct driver, and the driver then uses SQL statements to indicate the DBMS (Database Management System) therefore, the existence of ODBC provides us with powerful capabilities and flexibility to develop database applications. In Windows, you can install an ODBC database for multiple databases to connect to the database and access their data.

For MySQL database server, its manufacturer also provides the corresponding ODBC driver, the latest version of which is the myodbc-2.50.31-win95. After installation in the WIN98 environment, you can use the programming language to perform operations supported by ODBC on the MySQL database through the odbc api. Because ODBC shields the differences between various databases, the program originally used to operate other databases can also perform operations on the MySQL database through ODBC. That is to say, ODBC improves the universality of the program to the database.

To program a MySQL database through the odbc api using VC, you must set the MySQL Data Source. Specific settings 1:


(400) {this. resized = true; this. width = 400; this. alt = 'click here to open new window';} "onmouseover =" if (this. resized) this. style. cursor = 'hand'; "onclick =" window. open ('HTTP: // www.linuxdiyf.com/bbs/attachments/forumid_9/zt6x6szi_8vvhmrkdzpms.bmp '); ">

Data Source settings

Then we can program the MySQL database according to the general ODBC database programming method.

3. Use the c api function provided by MySQL to call database functions

Because of the differences between databases, the database functions they provide are also different. In this way, the odbc api cannot have all the database functions, thus affecting the Database Control Function of the program, and thus the database capabilities cannot be fully utilized. In addition, this unified interface is based on the loss of efficiency, which prolongs the database operation time. To solve the preceding problems, MySQL manufacturers also provide APIs in various programming environments, including C APIs, based on the ODBC driver. Obviously, these API functions can exert the database capabilities as much as possible and reduce the extended time of database operations, but the universality of the program is seriously affected.

MySQL provides a set of c api functions, which are composed of a group of functions and a set of data types used for functions. These functions communicate with the MySQL server and access the database, allowing you to directly manipulate the database, therefore, the control efficiency is significantly improved.

C api data types include MYSQL, MYSQL_RES, MYSQL_ROW, MYSQL_FIELD, and MYSQL_FIELD_OFFSET), my_ulonglong (custom unsigned integer number), etc. functions provided by c api include: mysql_close (), mysql_connect (), mysql_query (), mysql_store_result (), mysql_init () among them, mysql_query () is the most important and can complete most of the database operations.

The following describes the implementation of database operation class CDatabase through c api and the Application in VC.

3.1 Implementation of the CDatabase class

The CDatabase class encapsulates the functions of the MySQL database, so it is not universal and can only be used in MySQL applications. The following describes the structure of the CDatabase class based on the requirements and specifications of C ++:



Class CDatabase
{
Public:
BOOL UnLockTable (); // unlock
BOOL LockTable (char * TableName, char * PRIORITY); // lock
Int Reload (); // re-login, non-zero return error message
Char * GetState (); // server status
Char * GetServerInfo (); // server information
Int GetProtocolInfo (); // protocol information
Char * GetHostInfo (); // host information
Char * GetClientInfo (); // client information
Char * GetFieldName (int FieldNum); // field name
BOOL IsEnd (); // whether it is the last
Int DropDB (char * db); // deletes the database. If the value is not zero, an error message is returned.
Void SeekData (int offset); // you can call this operation to query specified data.
Int CreateDB (char * db); // creates a database. If the value is not zero, an error message is returned.
Void FreeRecord (); // release the result set
Unsigned int GetFieldNum (); // obtain the number of fields
BOOL ConnectDB (Database_Param * p); // connect to the database
MYSQL_ROW GetRecord (); // get the result (one record)
My_ulonglong GetRowNum (); // obtain the number of records
BOOL SelectDB (Data_Param * para); // select a database
BOOL UpdateRecord (Data_Param * para); // update a record
BOOL SelectRecord (Data_Param * para); // select a record
BOOL InsertRecord (Data_Param * para); // insert a record
BOOL DelRecord (Data_Param * para); // delete a record
BOOL SelectAll (Data_Param * para); // select all records
Char * OutErrors (); // output error message
CDatabase (); // initialize the database
Virtual ~ CDatabase (); // close the database connection

Private:
MYSQL mysql; // database connection handle
MYSQL_RES * query; // result set
MYSQL_ROW row; // record set
MYSQL_FIELD * field; // field information (struct)
BOOL FindSave (char * str); // find and save the result set
};

Through the functions defined in the CDatabase class, we can remotely or locally perform most of the operations on the MySQL database, and because of the locks and locks defined, this allows applications to access the database with multiple threads or processes, greatly improving the efficiency. The specific functions of the above functions are implemented by calling the c api function.
3.2 application of the CDatabase class in VC

The first step is to create and initialize the MySQL object. Define the CDatabase class object, so that the program will automatically call the constructor CDatabase () to complete initialization.

The constructor implementation is as follows:



CDatabase: CDatabase ()
{
Mysql_init (& mysql );
}

After initialization, you only need to define the CDatabase class object, that is

CDatabase base;


Step 2 connect to the server and connect to the required database. You can call the databdb (Database_Param * p) function to store database parameters, including the host name, user name, password, and database name, in the Database_Param structure. If this function returns TRUE, the connection is successful. Otherwise, the connection fails.

The connection function is implemented as follows:

BOOL CDatabase: ConnectDB (Database_Param * p)
{
If (! Mysql_real_connect (& mysql, p-> host, p-> user, p-> password, p-> db, p-> port, p-> unix_socket, p-> client_flag ))
{
OutErrors (); // output error message
Return false;
}
Return true;
}

Step 3: Lock the database. That is, the LockTable (char * TableName, char * PRIORITY) is called to lock the corresponding TableName attribute PRIORITY, so that the program is compatible with the multi-thread function. The locking function is implemented as follows:

BOOL CDatabase: LockTable (char * TableName, char * PRIORITY)
{
Char str [50];
Sprintf (str, "lock tables % s", TableName, PRIORITY );
If (mysql_query (& mysql, str ))
Return false;
Return true;
}

Step 4: complete database operations. UpdateRecord (Data_Param * para), SelectRecord (Data_Param * para), InsertRecord (Data_Param * para), and DelRecord (Data_Param * para) are called as needed. The struct Data_Param stores database operation parameters. The two struct types are defined in global. h. The implementation of the InsertRecord function is as follows. Other implementation methods are similar:

BOOL CDatabase: InsertRecord (Data_Param * para)
{
Char str [80];
Sprintf (str, "insert into % s values (% s)", para-> tab_name, para-> insert_val );
If (mysql_query (& mysql, str ))
Return false;
Return true;
}

Step 5 unlock the database. You can call UnLockTable () to unlock the table that has been locked. The unlock function is implemented as follows:

BOOL CDatabase: UnLockTable ()
{
If (mysql_query (& mysql, "unlock tables "))
Return false;
Return true;
}

Step 6 Close the database connection. Call the destructor ~ CDatabase (), shut down the database, and automatically release the CDatabase class objects defined during initialization. The Destructor is as follows:

CDatabase ::~ CDatabase ()
{
If (query)
Mysql_free_result (query );
Mysql_close (& mysql );
}

Note: When compiling a program, you must add the MySQL library file libmySQL. lib.

4. MySQL Performance Testing

4.1 ODBC API
Read 2 million rows through indexes: 528 seconds
Insert 0.35 million rows: 750 seconds

4.2 C API

Read 2 million rows through indexes: 412 seconds
Insert 0.35 million rows: 435 seconds

4.3 Other databases (taking MS_ SQL as an example, WINNT environment)

Read 2 million rows through indexes: 1634 seconds
Insert 0.35 million rows: 4012 seconds

Note: The above data is the average value of multiple measurements and an error is inevitable for reference only.

5 conclusion

As a free SQL database, MySQL is not powerful enough and has poor flexibility. However, it can meet the requirements of general application software, and MySQL database developers are striving to improve it. In addition, the MySQL performance test shows that it processes data faster than other database servers. Therefore, if the application software has high requirements on database performance and does not have high requirements on database operation functions and flexibility, and there is little investment in the early stage, mySQL database server is the best choice.

Source code:
Http://www.gbunix.com/bbs/download.php? Id = 339