Two ways to connect MySQL database with C + +

Now you are doing an interface that operates on different databases with different connection strings. To use MySQL database, this database has not been used before, with access and SQL Server more. Through some information on the internet and their own groping, a general understanding of the C + + connection to MySQL method. Can be achieved in 2 ways.

The first method is to make use of the ADO connection,

The second approach is to connect using MySQL's own API functions.

The first approach is to implement my current requirements by connecting different strings to different databases. Only the mysql,sqlserver,oracle,access is connected temporarily. For access, because the SQL statement that created the table is less compatible with standard SQL statements, there is some processing that needs to be done here, for the time being. The second method can only be used for MySQL database connection, but this method does not have to install the MYODBC server program.

Either way, you first need to install the MySQL database, installation method see "MySQL installation and some points of attention." It is best to install a navicat for MySQL, easy to operate MySQL database. Here are the two ways:

(i) Connect MySQL database via ADO

1, through ADO connection MySQL database, first have to install MYODBC server program.

The MYODBC version corresponds to the MySQL version, otherwise it will not connect to the database. The versions I used were Mysql-5.1.48-win32.msi and Mysql-connector-odbc-5.1.5-win32.msi.

After installation, click Start menu, Settings, Control Panel, management tools, data Source (ODBC), User dsn-> Add, select MySQL ODBC 5.1 Driver. Such as:

Then double-click MySQL ODBC 5.1 driver to configure it. The configuration can point to test (for example), if you can connect it will pop up the connection successful dialog box.

The content in data source Name: Is the value of the DSN in the code.

For example: "Dsn=mysqlodbc;server=localhost;database=test".

2, the configuration is good, you can start coding.

(1) First import the ADO type library. #import "C:\Program Files\Common Files\system\ado\msado15.dll" No_namespace rename ("EOF", "adoeof"). Msado15.dll in your environment is not necessarily in this directory, please modify it as the actual situation. or copy the Msado15.dll file to your project directory, directly #import "Msado15.dll" \ No_namespace \rename ("EOF", "adoeof") included.

(2) Create a Connection object and connect to the database

2. {
4. CoInitialize (NULL);
6. M_pconnection.createinstance (__uuidof (Connection));
8. Try
10. {
12. Setting the connection time
14. M_pconnection->connectiontimeout = 5;
16. Open a database connection
18. HRESULT hr = M_pconnection->open ("Dsn=mysqlodbc;server=localhost;database=test","root", "root",  adModeUnknown);
20. }
22. catch (_com_error &e)
24. {
26. MessageBox (NULL, E.description (), _t (""), MB_OK);
28. return FALSE;
30. }
32. return TRUE;
34. }

(3) Execute SQL statement

2. BOOL Cdbmanagersub::executesql (_bstr_t bstrsql)
4. {
6. _variant_t RecordsAffected;
8. Try
10. {
12. Whether the database is already connected
14. if (m_pconnection = = NULL)
16. {
18. Reconnect the database
20. Open (M_dbtype, M_strserver, M_strusername, M_strpasswor, m_strdbname);
22. }
24. The Execute method for the Connection object: (_bstr_t CommandText,
26. VARIANT * RecordsAffected, long Options)
28. Where CommandText is a command string, usually an SQL command.
30. The parameter recordsaffected is the number of rows affected after the operation is completed,
32. The parameter options represent the type of CommandText: adcmdtext-text command; adcmdtable-table name
34. adcmdproc-stored procedure; adcmdunknown-unknown
36. _RecordsetPtr hr = M_pconnection->execute (bstrsql,null,adcmdtext);
38. return true;
40. }
42. catch (_com_error e)
44. {
46. MessageBox (NULL, E.description (), _t (""), MB_OK);
48. return false;
50. }
52. }

_bstr_t bstrsql is the input SQL statement, if it returns true, the execution succeeds, and Flash returns to the corresponding error prompt.

For example: The following SQL statement to create the TestTable table:

1. char* pquery = "CREATE table if not exists testtable (ID varchar), Name varchar (255), Descs varcha (255), PRIMARY KEY (ID)) ";
2. ExecuteSQL (Pquery);

3, attached MySQL database operation, convenient does not install navicat for MySQL friend reference.

Open Start All Programs->mysql->mysql server 5.0->mysql Command line Client.exe, if you press ENTER directly without setting a password, you will be prompted to start the server successfully.

1. Mysql> show databases;//Show all the databases, be sure to knock ";" and then press ENTER
2. mysql> CREATE database mydb;//
3. mydbmysql> use mydb;//Select the database you created
4. Mydbmysql> SHOW TABLES; Displaying tables in a database
5. mysql> CREATE TABLE mytable (username varchar), visitelist varchar ($ ), remark varchar (200) ,PRIMARY key (username));//Create a table mytable: User name; Access list, primary key is username
6. Mysql> DESCRIBE mytable;//shows the structure of the table

(b) Connect via MySQL's own API function

1, using the way the API connection, need to load MySQL header files and Lib files.

Add \mysql\mysql Server 5.1\include in the additional include directory of VS2010. Look in the directory where MySQL is installed. Copy the Libmysql.dll and Libmysql.lib files to the project directory you are building. The following content is then included in the header file:

1. MySQL Required header files and library files
2. #include "Winsock.h"
3. #include "Mysql.h"
4. #pragma comment (lib,"LibmySQL.lib")

2, the Code

(1) Connect MySQL database

Header file defines the data source pointer in MYSQL M_sqlcon;

Connect to MySQL Database

2. Try
4. {
6. Mysql_init (&m_sqlcon);
8. localhost: server root for account password test for database name 3306 port
10. if (!mysql_real_connect (&m_sqlcon, "localhost","root","root","test", 3306,null,0))
12. {
14. AfxMessageBox (_t ("Database connection failed!"));
16. return FALSE;
18. }
20. return TRUE;
22. }
24. catch (...)
26. {
28. return FALSE;
30. }

(2) Closing the database

1. Mysql_close (&m_sqlcon);

(3) Create a table

1. char* pquery = "CREATE table if not exists ds_building (ID varchar), Name varchar (255), Descs varchar (255), PRIMARY KE Y (ID)) "
2. if (Mysql_real_query (&m_sqlcon,pquery, (UINT) strlen (pquery))!=0)
3. {
4. Const char* pCh = mysql_error (&m_sqlcon);
5. return FALSE;
6. }

API interface with MySQL:

2. Mysql_affected_rows () returns the number of rows affected by the latest update, Delete, or insert query.
4. Mysql_close () closes a server connection.
6. Mysql_connect () connect a MySQL server. This function is not recommended; use Mysql_real_connect () instead.
8. Mysql_change_user () Changes the user and database on an open connection.
10. mysql_create_db () creates a database.  The function is not recommended, and the SQL command is used to CREATE DATABASE.
12. Mysql_data_seek () Searches for an arbitrary row in a query result collection.
14. Mysql_debug () makes a dbug_push with the given string.
16. mysql_drop_db () discards a database.  The function is not recommended, and the SQL command is used to DROP DATABASE.
18. Mysql_dump_debug_info () lets the server write debug information to the log file.
20. Mysql_eof () determines whether the last row of a result collection has been read. This function is objected to; Mysql_errno () or mysql_error () can be used instead.
22. Mysql_errno () returns the error number of the most recently called MySQL function.
24. Mysql_error () returns an error message for the most recently called MySQL function.
26. Mysql_escape_string () escapes special characters for strings that are used in SQL statements.
28. Mysql_fetch_field () returns the type of the next table field.
30. Mysql_fetch_field_direct () returns the type of a table field, giving a number of fields.
32. Mysql_fetch_fields () returns an array of all the field structures.
34. Mysql_fetch_lengths () returns the length of all columns in the current row.
36. Mysql_fetch_row () Gets the next line from the result collection.
38. Mysql_field_seek () Places the column cursor on a specified column.
40. Mysql_field_count () returns the number of results columns for the most recent query.
42. Mysql_field_tell () returns the position of the field cursor used for the last Mysql_fetch_field ().
44. Mysql_free_result () frees the memory used by a result collection.
46. Mysql_get_client_info () returns the customer version information.
48. Mysql_get_host_info () returns a string that describes the connection.
50. Mysql_get_proto_info () Returns the protocol version used by the connection.
52. Mysql_get_server_info () returns the server version number.
54. Mysql_info () returns information about the most recently executed query.
56. Mysql_init () Gets or initializes a MySQL structure.
58. MYSQL_INSERT_ID () returns the ID of the previous query that was generated for a auto_increment column.
60. Mysql_kill () kills a given thread.
62. Mysql_list_dbs () returns the name of the database that matches a simple regular expression.
64. Mysql_list_fields () returns the name of a column that matches a simple regular expression.
66. Mysql_list_processes () Returns a table for the current server thread.
68. Mysql_list_tables () returns the name of the table that matches a simple regular expression.
70. Mysql_num_fields () returns the number of columns that a result collection is heavy.
72. Mysql_num_rows () returns the number of rows in a result set.
74. Mysql_options () Sets the connection options for mysql_connect ().
76. Mysql_ping () Check that the connection to the server is working and reconnect if necessary.
78. Mysql_query () executes an SQL query that is specified as a null-terminated string.
80. Mysql_real_connect () connect a MySQL server.
82. Mysql_real_query () executes an SQL query that is specified as a string with a count.
84. Mysql_reload () tells the server to reload the authorization table.
86. Mysql_row_seek () Searches for a row in the result set, using the value returned from Mysql_row_tell ().
88. Mysql_row_tell () returns the row cursor position.
90. mysql_select_db () connects a database.
92. Mysql_shutdown () Shut down the database server.
94. Mysql_stat () returns the server state as a string.
96. Mysql_store_result () Retrieves a complete set of results to the customer.
98. MYSQL_THREAD_ID () returns the ID of the current thread.
100. Mysql_use_result () Initializes a row of rows to retrieve the result collection.

Through the introduction of this article, I hope you have a C + + connection MySQL database two ways to understand, for reference.

Two ways to connect to MySQL database in C + +