MySQL C API programming tutorial

Request: need to study it.

pronunciation：

MySQL (/maɪ ˌɛskjuːˈɛl/ "My S-Q-L",^[4] officially, but also called /maɪ ˈsiːkwəl/ "My Sequel")

MySQL C API programming tutorial

http://zetcode.com/db/mysqlc/

看这篇文章，学习。

use 

$ sudo apt-get install libmysqlclient-dev

To be able to compile C examples, we need to install the MySQL C development libraries. The above line shows how we can do it on Debian based Linux.

First example

Our first example will test one MySQL function call.

编译错误，找不到头文件？原来是没有加编译选项（头文件路径）

user@ubuntu-64bit:~/mysql_study$ find / -name my_global.h 2>/dev/null

/usr/include/mysql/my_global.h

$ gcc version.c -o version  `mysql_config --cflags --libs`

user@ubuntu-64bit:~/mysql_study$ ./version 

MySQL client version: 5.5.34

Creating a database

The next code example will create a database. The code example can be divided into these parts:

Initiation of a connection handle structure
Creation of a connection
Execution of a query
Closing of the connection

我说为什么创建了的database看不到，原来需要指定user为root才能看见root创建的database.

user@ubuntu-64bit:~/mysql_study$ gcc createdb.c -o createdb -std=c99  `mysql_config --cflags --libs`

user@ubuntu-64bit:~/mysql_study$ mysql -u root -p -h localhost

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 56

Server version: 5.5.34-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

mysql> SHOW DATABASES;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| test22db           |

| testdb             |

+--------------------+

6 rows in set (0.04 sec)

Creating and populating a table

Before we create a new table, we create a user that we will use in the rest of the tutorial.

mysql> CREATE USER user12@localhost IDENTIFIED BY '34klq*';

We have created a new user user12.

mysql> GRANT ALL ON testdb.* to user12@localhost;

Here we grant all priviliges to user12 on testdb database.

The next code example will create a table and insert some data into it.

user@ubuntu-64bit:~/mysql_study$ gcc createtable.c -o createtable -std=c99 `mysql_config --cflags --libs`

mysql> USE testdb;
mysql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| Cars             |
+------------------+
1 row in set (0.00 sec)

We show tables in the database.

mysql> SELECT * FROM Cars;
+------+------------+--------+
| Id   | Name       | Price  |
+------+------------+--------+
|    1 | Audi       |  52642 |
|    2 | Mercedes   |  57127 |
|    3 | Skoda      |   9000 |
|    4 | Volvo      |  29000 |
|    5 | Bentley    | 350000 |
|    6 | Citroen    |  21000 |
|    7 | Hummer     |  41400 |
|    8 | Volkswagen |  21600 |
+------+------------+--------+
8 rows in set (0.00 sec)

We select all data from the table.

Retrieving data from the database

In the next example, we will retrieva data from a table.

Steps:

• Create a connection
• Execute query
• Get the result set
• Fetch all available rows
• Free the result set

if (mysql_query(con, "SELECT * FROM Cars")) 
{
    finish_with_error(con);
}

We execute the query, that will retrieve all data from the Cars table.

MYSQL_RES *result = mysql_store_result(con);

We get the result set using the mysql_store_result() function. MYSQL_RES is a structure for holding a result set.

int num_fields = mysql_num_fields(result);

We get the number of fields (columns) in the table.

MYSQL_ROW row;

while ((row = mysql_fetch_row(result))) 
{ 
    for(int i = 0; i < num_fields; i++) 
    { 
        printf("%s ", row[i] ? row[i] : "NULL"); 
    } 
        printf("\n"); 
}

We fetch the rows and print them to the screen.

user@ubuntu-64bit:~/mysql_study$ gcc retrieve_data.c -o retrieve_data -std=c99 `mysql_config --cflags --libs`

user@ubuntu-64bit:~/mysql_study$ ./retrieve_data

1 Audi 52642

2 Mercedes 57127

3 Skoda 9000

4 Volvo 29000

5 Bentley 350000

6 Citroen 21000

7 Hummer 41400

8 Volkswagen 21600 

Last inserted row id

Sometimes, we need to determine the id of the last inserted row. We can determine the last inserted row id by calling the mysql_insert_id() function. The function only works if we have defined an AUTO_INCREMENT column in the table.

A new table is created. Three rows are inserted into the table. We determine the last inserted row id.

char *sql = "CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name TEXT)";

The Id column has an AUTO_INCREMENT type.

int id = mysql_insert_id(con);

The mysql_insert_id() function returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement.

$ ./last_row_id 
The last inserted row id is: 3

Column headers

In the next example, we will retrieve data from the table and its column names.

We print the first three rows from the Cars table. We also include the column headers.
MYSQL_FIELD *field;


The MYSQL_FIELD structure contains information about a field, such as the field's name, type and size. Field values are not part of this structure; they are contained in the MYSQL_ROW structure.
if (i == 0) 
{              
    while(field = mysql_fetch_field(result)) 
    {
        printf("%s ", field->name);
    }
    
    printf("\n");           
}


The first row contains the column headers. The mysql_fetch_field() call returns a MYSQL_FIELD structure. We get the column header names from this structure.
$ ./headers 
Id Name Price 
1  Audi  52642  
2  Mercedes  57127  
3  Skoda  9000