C++ connection to mysql database two methods of of ADO connection and mysql API connection

The first method implements my current requirement to connect different databases by concatenating different strings. Temporarily only connected to mysql,sqlserver,oracle, access. For access, because the SQL statement that it creates the table is not quite compatible with the standard SQL statement, you need to do some processing, which I won't do here. The second method can only be used for connections to the mysql database, but does not require the installation of the MyODBC server program.

Either way, you first need to install the Mysql database. See "Mysql installation and some tips" for the installation method. It is best to install a Navicat for mysql to facilitate the operation of the mysql database. Here are the two methods:

(1) connect MySql database through ADO

1, through ADO connection to MySql database, the first to install MyODBC server program.

The MyODBC version must correspond to the MySql version, otherwise the database will not be connected. I used mysql-5.1.48-win32.msi and mysql-connector-odbc-5.1.5-win32.msi, respectively.

Once installed, click start menu - > Settings - > Control panel - > Management tools - > Data sources (ODBC) > User DSN - > Add - > Select MySQL ODBC 5.1 Driver. The diagram below:

Then double-click MySQL ODBC 5.1 Driver to configure it. Once configured, you can click Test to Test (as shown below). If you can connect, the connection successful dialog box will pop up.

The Data Source Name: in the figure above is the value corresponding to the DSN in the code.

For example: "DSN = MySqlODBC; Server = localhost; The database = test ".

2. Once configured, you can start coding.

(1) first import ADO type library. #import "c:\program files\common files\system\ado\msado15.dll" no_namespace rename("EOF","adoEOF") The msado15.dll in your environment is not necessarily in this directory, please modify it as it is. Or copy the file msado15.dll to your project directory and directly include #import "msado15.dll" \ no_namespace \rename ("EOF", "adoEOF").

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

{  
CoInitialize(NULL);  
m_pConnection.CreateInstance(__uuidof(Connection));  
try 
{ 
//Set connection time
m_pConnection->ConnectionTimeout = 5; 
//Open database connection
HRESULT hr = m_pConnection->Open("DSN=MySqlODBC;server=localhost;database=test","root","root",adModeUnknown); 
} 
catch(_com_error &e) 
{ 
MessageBox(NULL, e.Description(), _T(""), MB_OK); 
return FALSE; 
}  
return TRUE; 
} 

(3) execute the SQL statement

BOOL CDBManagerSub::ExecuteSQL( _bstr_t bstrSQL ) 
{ 
// _variant_t RecordsAffected; 
try 
{ 
//Is the database already connected
if(m_pConnection == NULL) 
{ 
//Reconnect to the database
Open(m_dbType, m_strServer, m_strUserName, m_strPasswor, m_strDBName); 
} 
//The Connection object's Execute method :(_bstr_t CommandText,
// VARIANT * RecordsAffected, long Options )  
//Where CommandText is a command string, usually an SQL command.
//The parameter RecordsAffected is the number of rows affected by the completion of the operation,
//The Options parameter represents the type of CommandText: adcmdtext-text command; AdCmdTable - the name of the table
//Adcmdproc-stored procedure; AdCmdUnknown - unknown
_RecordsetPtr hr = m_pConnection->Execute(bstrSQL,NULL,adCmdText); 
return true; 
} 
catch(_com_error e) 
{ 
MessageBox(NULL, e.Description(), _T(""), MB_OK); 
return false; 
} 
}

_bstr_t bstrSQL is the input SQL statement. If it returns TRUE, it will execute successfully. If it returns FLASH, it will report the corresponding error.

For example, the following SQL statement creates the testTable table:

char* pQuery = "create table if not exists testTable( ID VARCHAR(10), Name VARCHAR(255),Descs VARCHA(255),PRIMARY KEY (ID))"; 
ExecuteSQL(pQuery); 

3. Attached is the operation of mysql database for the convenience of those friends who do not have Navicat for mysql installed for reference.

Open "go -" > All programs - > MySQL - > MySQL Server 5.0 > MySQL Command Line client.exe ", if there is no password set directly press enter, will prompt the server started successfully.

mysql> SHOW DATABASES;//Display all databases, be sure to type ";" Then press enter & NBSP; < br / >
mysql> CREATE DATABASE mydb;//Create a database & NBSP; < br / >
mydbmysql> USE mydb;//Choose the database you created & NBSP; < br / >
mydbmysql> SHOW TABLES; //Displays the tables & NBSP in the database; < br / >
mysql> CREATE TABLE mytable (username VARCHAR(100), visitelist VARCHAR(200),remark VARCHAR(200),PRIMARY KEY (username));//Create a table mytable: user name; Access list, the primary key is username  < br / >
mysql> DESCRIBE mytable;//Displays the structure of the table 

(ii) connect through MySQL's own API functions

1. To connect by API, the header file and lib file of mysql need to be loaded.

Add \MySQL\MySQL Server 5.1\include to VS2010's additional include directory. Find it in the directory where MySql is installed. Copy the libmysql.dll and libmysql.lib files into the project directory. Then include the following in the header file:

//Header and library files required for mysql & NBSP; < br / >
#include "winsock.h" 

#include "mysql.h" 

#pragma comment(lib,"libmySQL.lib") 

2. Code

(1) connect to mysql database

The header defines the data source pointer MYSQL m_sqlCon;

//Connect to MySql database

try 
{ 
mysql_init(&m_sqlCon); 
//Localhost: server root is the account password; test is the database name; 3306 is the port
if(!mysql_real_connect(&m_sqlCon, "localhost","root","root","test",3306,NULL,0)) 
{ 
AfxMessageBox(_T(" Database connection failed !")); 
return FALSE; 
}  
return TRUE; 
} 
catch (...) 
{ 
return FALSE; 
} 

(2) close the database

mysql_close(&m_sqlCon); 

(3) create the table

char* pQuery = "create table if not exists DS_Building( ID VARCHAR(10),Name VARCHAR(255),Descs VARCHAR(255),PRIMARY KEY (ID))"  
if(mysql_real_query(&m_sqlCon,pQuery,(UINT)strlen(pQuery))!=0) 
{  
const char* pCh = mysql_error(&m_sqlCon); 
return FALSE; 
} 

Attached is the API interface of MySQL:

Mysql_affected_rows () returns the number of rows affected by the latest UPDATE, DELETE, or INSERT query.  
Mysql_close () closes a server connection.  
Mysql_connect () connects to a MySQL server. This function is not recommended; Use mysql_real_connect() instead.  
Mysql_change_user () changes the user and database on an open connection.  
Mysql_create_db () creates a database. This function is not recommended; Instead, use the SQL command to CREATE DATABASE.  
Mysql_data_seek () searches for an arbitrary row in a collection of query results.  
Mysql_debug () does a DBUG_PUSH with the given string.  
Mysql_drop_db () throws away a database. This function is not recommended; Instead, DROP DATABASE using the SQL command.  
Mysql_dump_debug_info () tells the server to write debugging information to a log file.  
Mysql_eof () determines whether the last line of a result set has been read. This function is opposed; Mysql_errno () or mysql_error() can be used in reverse.  
Mysql_errno () returns the error number of the most recently called MySQL function.  
Mysql_error () returns an error message for the recently invoked MySQL function.  
Mysql_escape_string () is the escape special character for strings in SQL statements.  
Mysql_fetch_field () returns the type of the next table field.  
Mysql_fetch_field_direct () returns the type of a table field, giving a field number.  
Mysql_fetch_fields () returns an array of all field structures.  
All the columns in the mysql_fetch_lengths () returns the current line length.  
Mysql_fetch_row () gets the next row from the result set.  
Mysql_field_seek () places the column cursor on a specified column.  
Mysql_field_count () returns the number of result columns for the most recent query.  
Mysql_field_tell () returns the position of the field cursor used for the last mysql_fetch_field().  
Mysql_free_result () frees the memory used by a result collection.  
Mysql_get_client_info () returns the client version information.  
Mysql_get_host_info () returns a string describing the connection.  
Mysql_get_proto_info () returns the version of the protocol used for the connection.  
Mysql_get_server_info () returns the server version number.  
Mysql_info () returns information about the most recently executed query.  
Mysql_init () gets or initializes a MYSQL structure.  
Mysql_insert_id () returns the ID generated for an AUTO_INCREMENT column with the previous query.  
Mysql_kill () kills a given thread.  
Mysql_list_dbs () returns the database name that matches a simple regular expression.  
Mysql_list_fields () returns the column name that matches a simple regular expression.  
Mysql_list_processes () returns a table of the current server thread.  
Mysql_list_tables () returns the table name that matches a simple regular expression.  
Mysql_num_fields () returns the number of columns in a result collection.  
Mysql_num_rows () returns the number of rows in a result collection.  
Mysql_options () sets the connection options for mysql_connect().  
Mysql_ping () checks that the connection to the server is working and reconnects if necessary.  
Mysql_query () executes the SQL query specified as a null-terminated string.  
Mysql_real_connect () connects to a MySQL server.  
Mysql_real_query () executes the SQL query specified as a string with a count.  
Mysql_reload () tells the server to reload the authorization table.  
Mysql_row_seek () searches for rows in the result set, using the value returned from mysql_row_tell().  
Mysql_row_tell () returns the row cursor position.  
Mysql_select_db () connects to a database.  
Mysql_shutdown () closes the database server.  
Mysql_stat () returns the server status as a string.  
Mysql_store_result () retrieves a complete set of results to the customer.  
Mysql_thread_id () returns the ID of the current thread.  
Mysql_use_result () initializes a retrieval of the result collection line by line.
Through the introduction of this article, I hope you have some understanding of the two methods of C++ connecting to mysql database