Connecting MySQL database with Linux (C + +)

1, Environmental installation

The first is the installation of MySQL, which is directly installed only with apt get command (this method is recommended here, because it does not need to link the relevant path when compiling later programs)

sudo apt-get install mysql-server

After my installation, the relevant header files and dynamic libraries are in the following directory:

// Header file
/usr/include/mysql/ 
// Dynamic library
/usr/lib/x86_64-linux-gnu/

Finally, ensure that the MySQL service is running. The relevant commands are as follows:

// Start MySQL
service mysqld start 
// Stop MySQL
service mysqld stop 
// Restart MySQL
service mysqld restart
// View MySQL running status
systemctl status mysql.service

2, Disposition

After installing MySQL for the first time, you can log in directly with the root user in the same way, and the error Access denied for user 'root' @ 'localhost' will be reported. At this point, we first check the MySQL configuration file to obtain the default user name and password:

// command
sudo cat /etc/mysql/debian.cnf
// Profile content
[client]
host     = localhost
user     = debian-sys-maint
password = ***(Here I hide a string of English and numbers)
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = ***(Here I hide a string of English and numbers)
socket   = /var/run/mysqld/mysqld.sock

We directly use the user name Debian sys maint and password provided in the configuration file to log in to MySQL

$ mysql -udebian-sys-maint -p
Enter password: (Enter the password in the configuration file here)

After entering MySQL, configure the password of the root account

mysql> update user set password=password('password') where user='root' and host='localhost'
mysql> flush privileges; //Don't forget to let MySQL reload the permission data in this step

At this point, exit mysql, and we can normally enter MySQL with the root account and the set password.

3, Header file

#include<mysql/mysql.h>

4, Function

1. Allocate or initialize MYSQL objects

MYSQL *mysql_init(MYSQL *mysql)

Parameter: MySQL instance
For the MYSQ object to be initialized, pass in the object address and NULL pointer. The function will allocate, initialize and return a new object. Otherwise, the object is initialized and the address of the object is returned.

MYSQL mysql;
mysql_init(&mysql); 
// perhaps
MYSQL mysql = mysql_init(NULL);

2. Connect to a mysql server

MYSQL *mysql_real_connect (MYSQL *mysql,
							const char*host,
							const char*user,
							const char*passwd,
							const char*db,
							unsigned intport,
							const char*unix_socket,
							unsigned long client_flag)

Parameter: mysql instance handle returned by the previous function of mysql.
Host the host of the database to which you want to connect, which can be an ip address or host name.
User indicates the user name to log in to the database.
passwd login password.
db is the database accessed.
The tcp/ip port of port mysql is 3306 by default.
unix_socket indicates the connection type.
client_flag is temporarily 0.
If the connection is successful, the MYSQL * connection handle is returned. NULL if the connection fails. For a successful connection, the return value is the same as the value of the first parameter

if(mysql_real_connect(&mysql,"localhost","root","dxm242012","Student",0,NULL,0) == NULL){
	cout << "connect fial\n";
	return-1;
}

3. Execute the SQL query of the specified query statement string

int mysql_query(MYSQL *mysql,const char *query)

Parameter: MySQL instance. query statement string
Return value: 0 for success and non-0 for failure
Return a result table. Assuming the query is successful, you can call mysql_num_rows() to see how many rows are returned corresponding to the SELECT statement, or call mysql_affected_rows() to see how many rows are affected by the DELETE, INSERT, REPLACE or UPDATE statement.

string sql = "Query statement;
mysql_query(&mysql,sql.c_str());

4. Get the result set of the SQL query of the last query statement string

MYSQL_RES *mysql_store_result(MYSQL *mysql)

Retrieve the complete result set to the client. The most common way for clients to process result sets is by calling mysql_store_result() retrieves the entire result set at once. This function can get all the rows returned by the query from the server and save them on the client.
For each query that successfully retrieves data (SELECT, SHOW, DESCRIBE, EXPLAIN, CHECK TABLE, etc.), MySQL must be called_ store_ Result () or mysql_use_result() .
For other queries, you do not need to call mysql_store_result() or mysql_use_result(),
But if MySQL is called in any case_ store_ Result(), which will not cause any damage or performance degradation.
Parameter: MySQL instance. query statement string
Return value: MySQL is returned successfully_ Res structure, which stores the query results

string sql = "Query statement;
mysql_query(&mysql,sql.c_str());
MYSQL_RES * result = mysql_store_result(&mysql);
if(result == NULL)
	cout << "fail\n";

5. Return the number of rows in the result set

int mysql_num_rows(MYSQL_RES* result)

Parameter: result result set

6. Return the number of columns in the result set

int mysql_num_fields(MYSQL_RES* result)

Parameter: result result set

7. Get the type of the next column

MYSQL_FIELD* mysql_fetch_field(MYSQL_RES *result);
Parameter: result result set
Gets the type of the next column, and returns NULL at the end.

do{
	MYSQL_FIELD* field = mysql_fetch_field (result);
cout << field->name << "\t\t";
}while(row != NULL);

8. Get the next row from the result set

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

Parameter: result result set
Successfully returned an array with a value greater than 0.

do{
	MYSQL_ROW row = mysql_fetch_row(result);
for(inti=0; i<mysql_num_fields(result); i++){
cout << row[i] << "\t\t";
}
cout << endl;
}while(row != NULL);

9. Get the type of the corresponding column

MYSQL_FIELD* mysql_fetch_field_direct(MYSQL_RES *result, int i);

Given the column number, return the type of the column, and return NULL at the end.

for(int i=0; i<mysql_num_fields(result); i++){
MYSQL_FIELD* field =  field = mysql_fetch_field_direct(result,i);
cout << field->name << "\t\t";
}

10. Close MYSQL

MYSQL *mysql_close(MYSQL *mysql)

Parameter: MySQL instance

5, C + + encapsulates MyDB class

Later, these functions were simply encapsulated for direct use in the future.

class MyDB {

public:
	MyDB();
	~MyDB();
	bool InitDB(string host,string user,string pwd,string dbname);
	bool ExeSQL(string sql);
private:
	MYSQL* mysql;
	MYSQL_ROW row;
	MYSQL_RES* result;
	MYSQL_FIELD* field;
};

MyDB::MyDB() {
	mysql = mysql_init(NULL);
	if(mysql == NULL) {
		cout << "Error: "<< mysql_error(mysql);
		exit(-1);
	}
}

MyDB::~MyDB() {
	if(!mysql) {
		mysql_close(mysql);
	}
}

bool MyDB::InitDB(string host,string user,string pwd,string dbname) {
	/*Connect database*/
	if(!mysql_real_connect(mysql,host.c_str(),user.c_str(),pwd.c_str(),dbname.c_str(),0,NULL,0)) {
		cout << "connect fial: "<< mysql_error(mysql);
		exit(-1);
	}
	return true;
}

bool MyDB::ExeSQL(string sql) {
	/*Execution failed*/
	if(mysql_query(mysql,sql.c_str())) {
		cout << "query fail: "<< mysql_error(mysql);
		exit(1);
	}

	else {
		/*Get result set*/
		result = mysql_store_result(mysql);

		int fieldnum = mysql_num_fields(result);
		for(int i=0; i<fieldnum; i++) {
			row = mysql_fetch_row(result);
			if(row <= 0)
				break;
			for(int j=0; j<fieldnum; j++) {
				cout << row[j] << "\t\t";

			}
			cout << endl;

		}
		mysql_free_result(result);
	}
	return true;
}

Tags: Linux Database MySQL

Posted by posidon on Fri, 20 May 2022 01:31:35 +0300