Summary: 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. Keywords: MySQL, database interface, odbc api, and C API Download the source code 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, which is guarded by a serverProgramMysqld is composed of many different customer 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-H host-u user-ppassword at the prompt \ mysql \ bin>. If the prompt is changed to MySQL>, the login 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 useProgramming LanguageUse the odbc api to perform operations supported by ODBC for MySQL databases. 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:
Figure 1 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 (); // log on again, 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 the last int dropdb (char * dB); // deletes the database and returns the error message void seekdata (int o Ffset); // query the specified data int createdb (char * dB); // create a database and return the error message void freerecord () when the value is not zero (); // release the result set unsigned int getfieldnum (); // obtain the number of fields bool connectdb (database_param * P); // connect to the database mysql_row getrecord (); // obtain the result (one record) my_ulonglong getrownum (); // obtain the number of records bool selectdb (data_param * para); // select database bool UpdateRecord (data_param * para ); // update the record bool selectrecord (data_param * para); // select the record bool insertrecord (data_param * para ); // Insert record bool delrecord (data_param * para); // Delete record bool selectall (data_param * para); // select all record char * outerrors (); // output the error message cdatabase (); // initialize the database Virtual ~ Cdatabase (); // close database connection PRIVATE: MySQL; // database connection handle mysql_res * query; // result set mysql_row row; // record set mysql_field * field; // field information (struct) bool findsave (char * Str); // query 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 Step 1Create and initialize a 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 2Connect 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 (); // return false;} return true ;} Step 3Locks 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 % s", tablename, priority ); if (mysql_query (& MySQL, STR) return false; return true ;} Step 4Complete 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 5Unlock 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 6Close 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 MySQL, as a free SQL database, is not powerful enough and has poor flexibility, but can already meet the requirements of general application software, in addition, MySQL database developers are also 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. |
