C + + connection MySQL database two ways (ADO connection and MySQL API connection) _c language

The first method can implement my current requirements by connecting different strings to connect different databases. Only mysql,sqlserver,oracle,access is connected for the time being. For access, because it creates a table with SQL statements that are not very compatible with standard SQL statements, you need to do something about it, not yet. The second method can only be used for a connection to the MySQL database, but this method does not install the MYODBC server program.

Either way, first you need to install the MySQL database, installation method please see "MySQL installation and some attention points." It is best to install a navicat for MySQL to facilitate the operation of the MySQL database. The following are the two methods:

(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 version of MySQL, 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-> set-> Control Panel-> Admin tool-> Data Source (ODBC)-> user dsn-> add-> choose MySQL ODBC 5.1 Driver. The following figure:

Then double-click the MySQL ODBC 5.1 driver to configure it. Configure the test to be tested (as shown below) and Connection successful dialog box will pop up if you can connect.

The content in the data source Name in the previous illustration is the value that the DSN corresponds to in the code.

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

2, after the configuration, 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 change according to the actual situation. or copy Msado15.dll this file into your engineering directory, directly #import "Msado15.dll" \ No_namespace \rename ("EOF", "adoeof") included.

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

Copy Code code as follows:

{
CoInitialize (NULL);
M_pconnection.createinstance (__uuidof (Connection));
Try
{
Set connection time
M_pconnection->connectiontimeout = 5;
Open a 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 SQL statement

BOOL Cdbmanagersub::executesql (_bstr_t bstrsql)
{
_variant_t recordsaffected;
Try
{
Whether the database has been connected
if (m_pconnection = NULL)
{
Reconnect the database
Open (M_dbtype, M_strserver, M_strusername, M_strpasswor, m_strdbname);
}
The Execute method for the Connection object: (_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 that are affected after the operation completes.
Parameter options represent the type of CommandText: adcmdtext-text command; adcmdtable-table name
adcmdproc-stored procedures; 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 an input SQL statement, if true, execution succeeds, and returning flash will report the corresponding error message.

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

Copy Code code as follows:

char* pquery = "CREATE table if not EXISTS testtable (ID VARCHAR (), Name VARCHAR (255), Descs varcha (255), PRIMARY KEY (ID) )";
ExecuteSQL (Pquery);

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

Opens the start-> All Programs->mysql->mysql server 5.0->mysql Command line Client.exe, and prompts the server to start successfully if you press ENTER without setting a password.

Copy Code code as follows:

Mysql> show databases;//all the databases, be sure to knock ";" and then press ENTER
Mysql> CREATE database mydb;//creating databases
mydbmysql> use mydb;//Select the database you created
Mydbmysql> show TABLES; Displaying tables in a database
Mysql> CREATE TABLE mytable (username VARCHAR, visitelist VARCHAR (), remark VARCHAR (), PRIMARY KEY (username );//Create a table mytable: User name; Access list, primary key is username
Structure of mysql> DESCRIBE mytable;//display table

(ii) via MySQL's own API function to connect

1, use the way of the API connection, need to load MySQL header file and lib file.

Add \mysql\mysql Server 5.1\include to the VS2010 additional include directory. Find it in the directory where MySQL is installed. Copy the Libmysql.dll and Libmysql.lib files to the project directory under construction. Then include the following in the header file:

Copy Code code as follows:

MySQL Required header files and library files
#include "Winsock.h"
#include "mysql.h"
#pragma comment (lib, "LibmySQL.lib")

2, to encode

(1) Connection MySQL database

Header file definition data source pointer MYSQL M_sqlcon;

Copy Code code as follows:

Connecting to the MySQL database

Try
{
Mysql_init (&m_sqlcon);
localhost: server root for account password test for database name 3306 port
if (!mysql_real_connect (&m_sqlcon, "localhost", "root", "root", "test", 3306,null,0))
{
AfxMessageBox ("Database connection Failed!") (_t);
return FALSE;
}
return TRUE;
}
catch (...)
{
return FALSE;
}

(2) Close the database

Copy Code code as follows:

Mysql_close (&m_sqlcon);

(3) Create a table

Copy Code code as follows:

char* pquery = "CREATE table if not exists ds_building (ID VARCHAR (), Name VARCHAR (255), Descs VARCHAR (255), PRIMARY KEY (I D) "
if (Mysql_real_query (&m_sqlcon,pquery, (UINT) strlen (pquery))!=0)
{
Const char* pCh = mysql_error (&m_sqlcon);
return FALSE;
}

With MySQL API interface:

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 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, and the SQL command is used to create the DATABASE.
Mysql_data_seek () Searches for an arbitrary row in a query result collection.
Mysql_debug () makes a dbug_push with the given string.
mysql_drop_db () discards a database. This function is not recommended, and the SQL command drop DATABASE is used.
Mysql_dump_debug_info () lets the server write debug information to the log file.
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.
Mysql_errno () returns the error number of the recently invoked MySQL function.
Mysql_error () returns an error message for the recently invoked MySQL function.
Mysql_escape_string () The escape special character of a string used in an SQL statement.
Mysql_fetch_field () returns the type of the next table field.
Mysql_fetch_field_direct () returns the type of a table field, giving a number of fields.
Mysql_fetch_fields () returns an array of all the field structures.
Mysql_fetch_lengths () returns the length of all the columns in the current row.
Mysql_fetch_row () Gets the next row from the result collection.
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 for the last Mysql_fetch_field ().
Mysql_free_result () frees the memory used by a result collection.
Mysql_get_client_info () returns the customer version information.
Mysql_get_host_info () returns a string that describes the connection.
Mysql_get_proto_info () Returns the protocol version used by 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 that was generated by the previous query for a auto_increment column.
Mysql_kill () kills a given thread.
Mysql_list_dbs () returns the name of the database that matches a simple regular expression.
Mysql_list_fields () returns the name of the column that matches a simple regular expression.
Mysql_list_processes () Returns a table of the current server thread.
Mysql_list_tables () returns the name of the table that matches a simple regular expression.
Mysql_num_fields () returns the number of columns with a result set heavy.
Mysql_num_rows () returns the number of rows in a result collection.
Mysql_options () Sets the connection option for mysql_connect ().
Mysql_ping () Check to see if the connection to the server is working and reconnect if necessary.
Mysql_query () executes a SQL query that is specified as a null-terminated string.
Mysql_real_connect () connects a MySQL server.
Mysql_real_query () executes a SQL query that is specified as a string with a count.
Mysql_reload () tells the server to reload the authorization table.
Mysql_row_seek () searches the rows in the result collection, 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 () Shut down the database server.
Mysql_stat () returns the server state 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 row-by-row retrieval of the result set.
Through the introduction of this article, I hope you know the two methods of C + + connection MySQL Database