Connect to MySQL database in C Language

/*

* 1. compiling environment

* Operating System ubuntu10.04
* GCC 4.4.3
* First, make sure that the following software packages are installed on your system:
* 1. mysql-client, libmysqlclient-Dev, and libmysqlclient15off
* Sudo apt-Get install mysql-client libmysqlclient-dev libmysqlclient15off
* PS: if you cannot find libmysqlclient15off, add it to the software source.
* Deb http://security.ubuntu.com/ubuntu hard-security main
* 2. mysql-Server
* Sudo apt-Get install mysql-Server
*

* 2. Create Database Batman in MySQL before use;

* Create two tables in Batman: tcppackets and udppackets.
* Create Table udppackets (MB double, kbpersec double, seconds double, percents double );
* Create Table tcppackets (MB double, kbpersec double );
*

* 3. Compilation options:

* Gcc-O c_mysql./c_mysql.c-I/usr/include/MySQL-L/usr/lib/MySQL-l mysqlclient-STD = c99
*

* 4. Usage:

*./C_mysql <FILENAME>-TCP |-udp

*/

The content of the source file c_mysql.c is as follows:

# If defined (_ Win32) | defined (_ win64) // to support Compilation on Windows platforms # include <windows. h> # endif # include <stdio. h> # include <stdlib. h> # include <ctype. h> # include <string. h> # include <MySQL. h> // on my machine, the file is stored in the macro of/usr/include/MySQL // defines database operations, you can also write the code # define insert_udp "insert into udppackets values (% F, % F) without defining it ); "# define insert_tcp" insert into tcppackets values (% F, % F); "// input file type # define tcp_data 1 # define u Dp_data 2 typedef struct _ udpdata {double MB; double kbpersec; double seconds; double percents;} udpdata, * pudpdata; typedef struct _ tcpdata {double MB; double kbpersec;} tcpdata, * ptcpdata; int Readline (File * FD, char * BUF) {char C; C = fgetc (FD); While (C! = '\ N' & C! = EOF) {* (BUF ++) = C; C = fgetc (FD);} * Buf = '\ 0'; If (C = EOF) return EOF; else return 0;} void paresudprow (char * Buf, pudpdata pudp) {int Index = 0, strindex = 0; char STR [20]; char * model [] = {"Sec", "Mbytes", "kbits/sec", "("}; double dd [4]; for (INT I = 0; I <4; I ++) {Index = strstr (BUF, model [I])-Buf; index + = strlen (model [I]); while (BUF [Index] = '') index ++; strindex = 0; while (isdigit (BUF [in Dex]) | Buf [Index] = '. ') {STR [strindex ++] = Buf [index ++];} STR [strindex] = 0; dd [I] = atof (STR );} pudp-> MB = dd [0]; pudp-> kbpersec = dd [1]; pudp-> seconds = dd [2]; pudp-> percents = dd [3];} void parestcprow (char * Buf, ptcpdata ptcp) {int Index = 0, strindex = 0; char STR [20]; char * model [] = {"Sec", "Mbytes"}; double dd [2]; for (INT I = 0; I <2; I ++) {Index = strstr (BUF, model [I])-Buf; index + = ST Rlen (model [I]); While (BUF [Index] = '') index ++; strindex = 0; while (isdigit (BUF [Index]) | Buf [Index] = '. ') {STR [strindex ++] = Buf [index ++];} STR [strindex] = 0; dd [I] = atof (STR );} ptcp-> MB = dd [0]; ptcp-> kbpersec = dd [1];} int main (INT argc, char ** argv) // char ** argv is equivalent to char * argv [] {MySQL, * sock; // defines the database connection handle, which is used by almost all MySQL functions mysql_res * res; // query result set, structure type char Buf [256]; char qbuf [256]; // stores the query SQL statement string char * filename = NULL; int filetype = tcp_data; file * fudp; udpdata UDP; tcpdata TCP; If (argc! = 3) {// check the input parameter fprintf (stderr, "Usage :. /c_mysql <FILENAME>-TCP |-udp \ n "); exit (1);} filename = argv [1]; If (strcmp (argv [2], "-TCP ")! = 0 & strcmp (argv [2], "-udp ")! = 0) {fprintf (stderr, "Usage:./c_mysql <FILENAME>-TCP |-udp \ n"); exit (exit_failure);} filetype =! Strcmp (argv [2], "-udp ")? Udp_data: tcp_data; mysql_init (& MySQL); If (! (Sock = mysql_real_connect (& MySQL, "localhost", "root", "123456", "Batman", 0, null, 0) {fprintf (stderr, "couldn't connect to engine! \ N % s \ n ", mysql_error (& MySQL); perror (" "); exit (1) ;}fudp = fopen (filename," R "); if (fudp = NULL) {fprintf (stderr, "couldn't open file % s \ n", filename); exit (1 );} while (Readline (fudp, Buf )! = EOF) {If (filetype = udp_data) {paresudprow (BUF, & UDP); sprintf (qbuf, insert_udp, UDP. MB, UDP. kbpersec, UDP. seconds, UDP. percents) ;}else {parestcprow (BUF, & TCP); sprintf (qbuf, insert_tcp, TCP. MB, TCP. kbpersec);} fprintf (stdout, qbuf); fprintf (stdout, "\ n"); Res = mysql_query (sock, qbuf); If (RES) {fprintf (stderr, "update failed (% s) \ n", mysql_error (sock); exit (1) ;}} mysql_close (sock); fclose (fudp); exit (0 ); return 0 ;//. to be compatible with most compilers, add this line}

The above code is mainly used to save the result data of the test Batman protocol to MySQL. The results include the results of the TCP test and the results of the UDP test. The two data formats are as follows:
 TCP:

[  4]  0.0-24.9 sec  2.22 MBytes    747 Kbits/sec[  5]  0.0-1576.9 sec    125 MBytes    665 Kbits/sec[  4]  0.0-33.6 sec  2.00 MBytes    499 Kbits/sec[  5]  0.0-11.9 sec  1.05 MBytes    735 Kbits/sec[  4]  0.0-26.7 sec  1.98 MBytes    621 Kbits/sec[  5]  0.0-23.1 sec  1.76 MBytes    638 Kbits/sec

UDP:

[  3]  0.0-20.6 sec  2.08 MBytes    850 Kbits/sec  43.392 ms   36/ 1521 (2.4%)[  4]  0.0-20.9 sec  2.13 MBytes    853 Kbits/sec  45.070 ms    0/ 1517 (0%)[  3]  0.0-20.5 sec  1.80 MBytes    734 Kbits/sec  41.463 ms 9021/10302 (88%)[  4]  0.0-20.9 sec  2.13 MBytes    855 Kbits/sec  41.880 ms    0/ 1517 (0%)[  3]  0.0-20.6 sec  2.13 MBytes    869 Kbits/sec  12.608 ms    0/ 1521 (0%)[  4]  0.0-20.8 sec  2.13 MBytes    857 Kbits/sec  41.295 ms    0/ 1517 (0%)[  3]  0.0-733.7 sec  15.4 MBytes    176 Kbits/sec  359.811 ms 2913/13907 (21%)[  4]  0.0-10.3 sec  1.00 MBytes    818 Kbits/sec  22.526 ms    0/  713 (0%)[  3]  0.0-34.1 sec  2.00 MBytes    493 Kbits/sec  3.466 ms    0/ 1427 (0%)[  4]  0.0-12.2 sec  1.00 MBytes    688 Kbits/sec  84.498 ms    0/  714 (0%)

This is not the focus, but the focus is on MySQL operations. But it's already a basket of crap...

1. The first structure to be used:

MySQL

This structure represents a database connection handle. Almost all MySQL functions use it. Do not try copying MySQL

Structure. It is not guaranteed that such copying results will be useful. It should be used as a parameter in subsequent database operations.

2. After declaring a MySQL-type variable MySQL, you must first call mysql_init (& MySQL). The prototype of this function is MySQL * mysql_init (MySQL * MySQL)

Description

Allocate or initialize MySQL objects that are compatible with mysql_real_connect. If MySQL is a null pointer

The function allocates, initializes, and returns a new object. Otherwise, the object is initialized and the address of the object is returned. If

Mysql_init () is assigned a new object. When mysql_close () is called to close the connection, the object is released.

Return Value
Initialized MySQL * handle. If there is not enough memory to allocate new objects, null is returned.

Error
If the memory is insufficient, null is returned.

3. Call mysql_real_connect to obtain a connection instance.

This function is prototype: MySQL * mysql_real_connect (MySQL * MySQL,
Const char * host,
Const char * user,
Const char * passwd,
Const char * dB,
Unsigned int port,
Const char * unix_socket,
Unsigned long client_flag)
Description

Mysql_real_connect () tries to establish a connection with the MySQL database engine running on the host. When you can execute

Before any other API functions that require a valid MySQL connection handle structure, mysql_real_connect () must be successful.

Complete.

The parameter is specified as follows:
# The first parameter must be the address of the existing MySQL structure. Before calling mysql_real_connect (), you must call

Mysql_init () to initialize the MySQL structure.

# The Value of "host" must be the host name or IP address. If "host" is null or the string "localhost", the connection will be

It is considered as a connection to the local host. If the operating system supports socket (UNIX) or named pipe (Windows ),

They will be used instead of TCP/IP to connect to the server.

# The "user" parameter contains the user's MySQL Login ID. If "user" is null or a Null String ", the user will be treated

Current user. In UNIX, it is the current login name. In Windows ODBC, you must specify

Current user name.

# The "passwd" parameter contains the user's password. If passwd is null

In the user table. In this way, the database administrator can set

The MySQL permission system has different permissions based on whether the user has the specified password.

4. Execute the add, delete, modify, and query operation mysql_query:

This function is prototype: int mysql_query (MySQL * MySQL, const char * query)
Description

# Execute the SQL query pointed to by the "null-terminated string" query. Normally, the string must contain one SQL statement.

Statement, and should not add an end semicolon (';') or "\ G" for the statement ". If multiple statements are allowed, strings can contain multiple

Statements separated by semicolons.

# Mysql_query () cannot be used to query binary data. Replace it with mysql_real_query ().

(Binary data may contain the character '\ 0'. mysql_query () will interpret this character as the end of the query string ).

# If you want to know whether the query should return a result set, you can use mysql_field_count () to check.

Return Value
If the query is successful, 0 is returned. If an error occurs, a non-zero value is returned.

Summary: The above only introduces the MySQL data structures and functions used in this program. These introductions are from the MySQL manual,

For more information, see MySQL c api.