Use C to operate MySQL

Introduction
C APIS is included in the mysqlclient library file and released together with the MySQL source code for connecting to the database and performing database queries. There are some examples in the clients directory of the original MySQL code.

MySQL C variable type
The following variable types are defined in the MySQL database. We need these variables to use MySQL functions. These variables are explained in detail, but they are not important for writing code.

MySQL
The following code block is the communication process used to connect to the database. To connect to MySQL, you must establish a MySQL instance and initiate the connection through mysql_init initialization. This will be discussed later.

Typedef struct st_mysql {
Net net;/* Communication parameters */
Gptr connector_fd;/* connectorfd for SSL */
Char * Host, * user, * passwd, * unix_socket,
* Server_version, * host_info, * info, * dB;
Unsigned int port, client_flag, server_capabilities;
Unsigned int protocol_version;
Unsigned int field_count;
Unsigned int server_status;
Unsigned long thread_id;/* ID for connection in server */
My_ulonglong affected_rows;
My_ulonglong insert_id;/* ID if insert on table with nextnr */
My_ulonglong extra_info;/* used by mysqlshow */
Unsigned long packet_length;
Enum mysql_status status;
Mysql_field * fields;
Mem_root field_alloc;
My_bool free_me;/* If free in mysql_close */
My_bool reconnect;/* set to 1 If Automatic reconnect */
Struct st_mysql_options options;
Char scramble_buff [9];
Struct charset_info_st * charset;
Unsigned int server_language;
} MySQL;

Mysql_res
This structure returns the results of a query (select, show, describe, or explain) of a row. The returned data is called a "dataset". Friends who have used the database should be familiar with the result set obtained after the database is queried. In api c, the returned data is mysql_res, read data from the database, and finally read data from mysql_res.

Typedef struct st_mysql_res {
My_ulonglong row_count;
Unsigned int field_count, current_field;
Mysql_field * fields;
Mysql_data * data;
Mysql_rows * data_cursor;
Mem_root field_alloc;
Mysql_row row;/* If unbuffered read */
Mysql_row current_row;/* buffer to current row */
Unsigned long * lengths;/* column lengths of current row */
MySQL * handle;/* For unbuffered reads */
My_bool EOF;/* used my mysql_fetch_row */
} Mysql_res;

Mysql_row
This is a type-safe representation of row data. Currently, it is implemented as a string array that counts bytes. (If the field value may contain binary data, you cannot regard it as a null termination string because such a value can contain null bytes internally) rows are obtained by calling mysql_fetch_row.

Typedef char ** mysql_row;

Mysql_field
This structure contains field information, such as field name, type, and size. Its members are described in more detail below. You can call mysql_fetch_field () repeatedly to obtain the mysql_field structure for each column. Field Values are not part of this structure; they are contained in a mysql_row structure.

Typedef struct st_mysql_field {
Char * Name;/* Name of column */
Char * Table;/* Table of column if column was a field */
Char * def;/* default value (set by mysql_list_fields )*/
Enum enum_field_types type;/* type of field. Se mysql_com.h for types */
Unsigned int length;/* width of column */
Unsigned int max_length;/* max width of selected set */
Unsigned int flags;/* Div flags */
Unsigned int Decimals;/* Number of decimals in field */
} Mysql_field;

 

 

 

My_ulonglong
Typedef unsigned long my_ulonglong;

 

This type is used for row numbers, mysql_affected_rows (), mysql_num_rows (), and mysql_insert_id (). This type provides a range from 0 to 1.84e19. In some systems, trying to print the value of the my_ulonglong type will not work. To print this value, convert it to unsigned long and use a % lu print format. For example:

Printf (number of rows: % lu/N ", (unsigned long) mysql_num_rows (result ));

Connect to MySQL and query data
Now we assume that MySQL has been installed, and users and data tables are created in the database. Please refer to www.mysql.com to prevent any unknown issues.

As mentioned above, the MySQL library file is in mysqlclient. Therefore, it is necessary to add the-lmysqlclient compilation option when compiling the MySQL program. The MySQL header file is in the/usr/include/MySQL directory (this directory varies depending on the Linux release version). Therefore, your program header looks a bit like this:

# Include <mysql. h>

MySQL variable types and functions are included in this header file.

Then, we need to create the variables to connect to the database. You can simply do this:

MySQL;

Before connecting to the database, we need to call the following function to initialize this variable:

Mysql_init (& MySQL );

Then, call the mysql_real_connect function:

MySQL * stdcall 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 int clientflag );

This function is called to connect to the database. Host is the host name of the MySQL server, user is the login user name, passwd is the login password, DB is the database to be connected, port is the TCP/IP Port of the MySQL server, and unix_socket is the connection type, clientflag indicates that MySQL runs as an ODBC database. In this article, the mark is set to 0. After the connection is established, this function returns 0.

Now you can connect to the database for query:

Char * query;

With this string, we can create any SQL query statement for query. The function that executes this query is:

Int stdcall mysql_real_query (MySQL * MySQL, const char * q, unsigned int length );

MySQL is the variable we used earlier, Q is the SQL query statement, and length is the length of the query statement. If the query is successful, the function returns 0.

After the query, we need a variable mysql_res to use the query results. This variable is created in the following line:

Mysql_res * res;

Then

Res = mysql_store_result (& MySQL );

There are two methods to process the result set for the client. One way is to call mysql_store_result () to retrieve all results immediately. This function retrieves all rows returned by the query from the server and stores them on the client. The second method is to call mysql_use_result () to initialize a row-by-row query of the result set. This function initializes the search, but does not actually get any rows from the server.

In either case, you can use mysql_fetch_row () to access the row. Use mysql_store_result () and mysql_fetch_row () to store the rows that have been removed from the server. Use mysql_use_result () and mysql_fetch_row () to actually retrieve rows from the server. Call mysql_fetch_lengths () to obtain the data value size in each row.
After you use up a result set, call mysql_free_result () to release the memory used by it.

The two retrieval mechanisms are complementary. The customer program should select the most suitable path for their requirements. In practice, customers prefer mysql_store_result ().

This function reads the query results.

Although it is easy to query, other functions are required to use the query results. The first one is:

Mysql_row stdcall mysql_fetch_row (mysql_res * result );

This function converts the result to an array ". You may have noticed that this function returns the mysql_row variable type. The following statement creates a variable:

Mysql_row ROW = mysql_fetch_row (RES)

As previously explained, the variable row is a string array. That is to say, row [0] is the first value of the array, and row [1] is the second value of the array... when mysql_fetch_row is used, the variable row obtains the next data set of results. At the end of the result, the function returns a negative value.

After using the dataset, remember to release the dataset. Otherwise, memory leakage may occur. The dataset function is released as follows:

Void mysql_free_result (mysql_res * result)

Releases memory allocated for a result set, such as mysql_store_result (), mysql_use_result (), and mysql_list_dbs. When you have used up a result set, you must call mysql_free_result () to release its memory.

Close the connection:

Mysql_close (& MySQL );

Example Program
Execute a select operation, fetch data from the database, execute an insert operation, and insert data into the database. Based on these two operations, you can freely expand to any database operation,

Prerequisites
1. MySQL has been installed, and the database test has been installed. If it is not executed

Create Databse Test

Create a database

2. The test database has table T1. If not, run

Create Table 't1 '(
'Id' int (11) default null,
'Name' varchar (100) default null
)

Create Table T1

Testsql. C:

/* Testsql. c
** An example to use MySQL C API
** Copyright 2004 coon Xu.
** Author: coon Xu
** Date: 05 Nov 2004
*/

# Include <mysql. h>
# Include <stdio. h>
Int main (){
MySQL; // need a instance to init
Mysql_res * res;
Mysql_row row;
Char * query;
Int T, R;
// Connect the database
Mysql_init (& MySQL );
If (! Mysql_real_connect (& MySQL, "localhost", "mmim", "mmim", "test", 0, null, 0 ))
{
Printf ("error connecting to database: % s/n", mysql_error (& MySQL ));
}
Else printf ("connected.../N ");

// Get the result from the executing SELECT query
Query = "select * from T1 ";

T = mysql_real_query (& MySQL, query, (unsigned INT) strlen (query ));
If (t)
{
Printf ("error making query: % s/n ",
Mysql_error (& MySQL ));
}
Else printf ("[% s] made.../N", query );
Res = mysql_store_result (& MySQL );
While (ROW = mysql_fetch_row (RES ))
{
For (t = 0; t <mysql_num_fields (RES); t ++)
{
Printf ("% s", row [T]);
}
Printf ("/N ");
}

Printf ("mysql_free_result.../N ");
Mysql_free_result (RES); // free result after you get the result

Sleep (1 );

// Execute the insert Query
Query = "insert into T1 (ID, name) values (3, 'kunp ')";
T = mysql_real_query (& MySQL, query, (unsigned INT) strlen (query ));
If (t)
{
Printf ("error making query: % s/n ",
Mysql_error (& MySQL ));
}
Else printf ("[% s] made.../N", query );

Mysql_close (& MySQL );

Return 0;
}

Compile
Assume that the MySQL header file is in/usr/include/MySQL, and the library file is in/usr/lib/MySQL. Run the following command to compile the file:

GCC testsql. C-I/usr/include/MySQL-L/usr/lib/MySQL-lmysqlclient