MySQL works well with many programming languages, such as C, Java and python, etc. This section shows how to program MySQL in C. Interface between C programs and MySQL is supported by a set of MySQL C API functions (C API 2018a, b) in the mysqlclient library.
1. Build MySQL Client Program in C
Consider the following C program, which prints the libmysqlclient library version.
// client.c file
#include <stdio.h>
#include <my_global.h>
#include <mysql.h>
int main(int argc, char *argc[])
{
printf(“MySQL client version is : %s\n”, mysql_get_client_info());
}
To compile the program, enter
gcc client.c -I/usr/include/mysql/ -lmysqlclient
Note that the -I option specifies the include file path as /usr/include/mysql and the -l option specifies the mysqlclient library. Then, run a.out. It should print
MySQL client version is : version_number, e.g. 5.5.53
2. Connect to MySQL Server in C
The following C program C14.2 shows how to connect to a MySQL server.
// c14.2.c file: connect to MySQL server
#include <stdio.h>
#include <stdlib.h>
#include <my_global.h>
#include <mysql.h>
int main(int argc, char *argv[ ])
{
// 1. define a connection object
MYSQL con;
// 2. Initialize the connection object
if (mysql_init(&con)) { // return object address printf(“Connection handle
initialized\n”);
} else {
printf(“Connection handle initialization failed\n”);
exit(1);
}
// 3. Connect to MySQL server on localhost
if (mysql_real_connect(&con, “localhost”, “root”, “root_password”,
“cs360”, 3306, NULL, 0)) {
printf(“Connection to remote MySQL server OK\n”);
}
else {
printf(“Connection to remote MySQL failed\n”); exit(1);
}
// 4. Close the connection when done
mysql_close(&con);
}
The program consists of 4 steps.
(1). Define a MYSQL object con as the connection handle. Its role is similar to a socket in network programming. Almost all mysql C API functions needs this object pointer as a parameter.
(2). Call mysql_init(&con) to initialize the con object, which is required. It returns the address of the initialized object. Most other mysql API functions return 0 for success and nonzero for error. In case of error, the functions unsigned int mysql_errno(&con) returns an error number, constant char *mysql_error(&con) returns a string describing the error. In case of error, the functions
unsigned int mysql_errno(&con) returns an error number,
constant char *mysql_error(&con) returns a string describing the error
(3). Call mysql_real_connect() to connect to a remote server. The general syntax is mysql_real_connect() is
MYSQL *mysql_real_connect(MYSQL *mysql,
const char *host, // server hostname or IP
const char *user, // MySQL user name
const char *passwd, // user password
const char *db, // database name
unsigned int port, // 3306
const char *unix_socket, // can be NULL
unsigned long client_flag); //0
It can be used to connect to any MySQL server on the Internet, provided that the user has access to that MySQL server. For simplicity, we assume that both the MySQL client and server are on the same localhost. After connecting to the server, the client program can start to access the database, which will be shown next.
(4). The client program should close the connection before exits .
3. Build MySQL Database in C
This section shows how to build MySQL databases in C programs. The following C program C14.3 builds the students table in a database cs360 exactly the same way as in previous sections using MySQL commands or scripts.
// C14.1.c: build MySQL database in C
#include <stdio.h>
#include <stdlib.h>
#include <my_global.h>
#include <mysql.h>
MYSQL *con; // connection object pointer
void error()
{
printf(“errno = %d %s\n”, mysql_errno(con), mysql_error(con));
mysql_close(con);
exit(1);
}
int main(int argc, char *argv[ ])
{
con = mysql_init(NULL); // will allocate and initialize it
if (con == NULL)
error();
printf(“connect to mySQL server on localhost using database cs360\n”);
if (mysql_real_connect(con, “localhost”, “root”, NULL,
“cs360”, 0, NULL, 0) == NULL)
error();
printf(“connection to server OK\n”);
printf(“drop students table if exists\n”);
if (mysql_query(con, “DROP TABLE IF EXISTS students”))
error();
printf(“create students tables in cs360\n”);
if (mysql_query(con, “CREATE TABLE students(Id INT NOT NULL
PRIMARY KEY AUTO_INCREMENT, name CHAR(20) NOT NULL, score INT)”))
error();
printf(“insert student records into students table\n”);
if (mysql_query(con, “INSERT INTO students VALUES(1001,’Baker’,50)”))
error();
if (mysql_query(con, “INSERT INTO students VALUES(1002,’Miller’,65)”))
error();
if (mysql_query(con, “INSERT INTO students VALUES(2001,’Miller’,75)”))
error();
if (mysql_query(con, “INSERT INTO students VALUES(2002,’Smith’,85)”))
error();
printf(“all done\n”);
mysql_close(con);
}
After connecting to the MySQL server, the program uses the mysql_query() function to operate on the database. The second parameter of the mysql_query() function is exactly the same as an ordinary MySQL command. It can be used for simple MySQL commands but not for commands if the parameter contains binary values or null bytes. To execute commands that may contain binary data, the program must use the mysql_real_query() function, as shown in the next program.
// C14.2 Program: build MySQL database with mysql_real_query()
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <my_global.h>
#include <mysql.h>
#define N 5
MYSQL *con;
void error()
{
printf(“errno = %d %s\n”, mysql_errno(con), mysql_error(con));
mysql_close(con);
exit(1);
}
int main(int argc, char *argv[ ])
{
int i;
char buf[1024]; // used to create commands for MySQL
con = mysql_init(NULL); // MySQL server will allocate and init con
if (con == NULL)
error();
printf(“connect to mySQL server on localhost using database cs360\n”);
if (mysql_real_connect(con, “localhost”, “root”, NULL,
“cs360”, 0, NULL, 0) == NULL) error();
printf(“connected to server OK\n”);
printf(“drop students table if exists\n”);
if (mysql_query(con, “DROP TABLE IF EXISTS students”))
error();
printf(“create students tables in cs360\n”);
if (mysql_query(con, “CREATE TABLE students(Id INT NOT NULL PRIMARY
KEY AUTO_INCREMENT, name CHAR(20) NOT NULL, score INT)”)) error();
printf(“insert student records into students table\n”);
for (i=0; i<N; i++){
printf(“id=%8d name=%10s, score=%4d\n”,id[i],name[i],score[i]);
sprintf(buf, “INSERT INTO students VALUES (%d, ‘%s’, %d);”,
id[i], name[i], score[i]);
if (mysql_real_query(con, buf, strlen(buf)))
error();
}
printf(“all done\n”);
mysql_close(con);
}
For comparison purpose, we have highlighted the new features in the C14.3 program. In order to insert a large number of rows into the students table, it is better to issue the INSERT commands in a loop over the number of rows to be inserted. Since the simple mysql_query() function can not take binary data, we must use the mysql_real_query() function. First, we define id[], name[] and score[] as arrays with initialized student data. For each student record, we use sprinf() to create a MySQL INSERT command line in a char buf[ ], and calls mysql_real_query(), passing the string in buf[] as a parameter. In practice, student records are usually from a data file. In that case, the C program can open the data file for read, read each student record, perform some preprocessing on the data before issuing commands to the MySQL server. We leave this as a programming exercise in the problem section.
4. Retrieve Results of MySQL Queries in C
MySQL queries may return results, e.g. SELECT, DESCRIBE, etc. A result is a set of columns and rows. The MySQL C API provides two ways of fetching a result set from the MySQL server: all rows at once or row by row.
The function mysql_store_result() is used to fetch the set of rows from the MySQL server all at once and store it in memory locally. Its usage is
MYSQL_RES *mysql_store_result(MYSQL *mysql);
The function mysql_use_result() is used to initiate a row-by-row result set retrieval.
MYSQL_RES *mysql_use_result(MYSQL *mysql);
After each invocation of mysql_query() or mysql_real_query() with MySQL statements that return results, call one of these functions to retrieve the result set. Both functions return the result set represented by an object of type MYSQL_RES. The returned result set object is used by other API functions to fetch the set of columns and rows. When the result set is no longer needed, the function mysql_free_result() is used to free the result object resource. The following program C14.3 shows how to retrieve the result sets and display the columns or rows of a table
// C14.3 file: Retrieve MySQL query results
#include <my_global.h>
#include <mysql.h>
#include <string.h>
#define N 5
MYSQL *con;
void error()
{
printf(“errno = %d %s\n”, mysql_errno(con), mysql_error(con));
mysql_close(con);
exit(1);
}
int main(int argc, char **argv)
{
int i, ncols;
MYSQL_ROW row;
MYSQL_RES *result;
MYSQL_FIELD *column;
char buf[1024];
con = mysql_init(NULL);
if (con == NULL)
error();
printf(“connect to mySQL server on localhost using database cs360\n”);
if (mysql_real_connect(con, “localhost”, “root”, NULL,
“cs360”, 0, NULL, 0) == NULL)
error();
printf(“connected to server OK\n”);
printf(“drop students table if exists\n”);
if (mysql_query(con, “DROP TABLE IF EXISTS students”))
error();
printf(“create students tables in cs360\n”);
if (mysql_query(con, “CREATE TABLE students(Id INT NOT NULL
PRIMARY KEY AUTO_INCREMENT, name CHAR(20) NOT NULL, score INT, grade CHAR(2))”))
error();
printf(“insert student records into students table\n”);
for (i=0; i<N; i++){
printf(“id =%4d name =%-8s score =%4d %c\n”,
id[i], name[i], score[i], grade[i]);
sprintf(buf, “INSERT INTO students VALUES (%d,’%s’, %d, ‘%c’);”,
id[i], name[i], score[i], grade[i]);
if (mysql_real_query(con, buf, strlen(buf)))
error();
}
printf(“retrieve query results\n”);
mysql_query(con, “SELECT * FROM students”);
result = mysql_store_result(con); // retrieve result
ncols = mysql_num_fields(result); // get number of columns in row
printf(“number of columns = %d\n”, ncols);
for (i=0; i<ncols; i++){
column = mysql_fetch_field(result); // get each column
printf(“column no.%d name = %s\n”, i+1, column->name);
}
mysql_query(con, “SELECT * FROM students”);
result = mysql_store_result(con);
ncols = mysql_num_fields(result);
printf(“columns numbers = %d\n”, ncols);
while( row = mysql_fetch_row(result) ){
for (i=0; i<ncols; i++) printf(“%-8s “, row[i]);
printf(“\n”);
}
printf(“all done\n”);
mysql_close(con);
}
In the C14.3 program, the lines of code that focus on retrieving results are highlighted for clarity. In addition to the mysql_store_result() function, it also uses the following functions.
MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result): Returns the definition of one column of a result set as a MYSQL_FIELD structure. Call this function repeatedly to retrieve information about all columns in the result set. It returns NULL when no more fields are left.
unsigned int mysql_num_fields(MYSQL_RES *result): Returns the number of columns in a result set.
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result): Retrieves the next row of a result set. Returns NULL when there are no more rows to retrieve.
The following shows the outputs of running the C14.3 program
connect to mySQL server on localhost using database cs360
connected to server OK
drop students table if exists
create students tables in cs360
insert student records into students table
In summary, accessing MySQL through C programming is fairly simple. It only requires the user to learn how to use a few MySQL API functions in C. In general, running compiled binary executables of C programs is faster and more efficient than interpretive script programs. Running compiled C programs are more suitable for handling large amounts of data processing. Perhaps the main drawback of running C programs is the lack of a GUI interface. For that purpose, we turn to the GUI based programming environment of PHP.
Source: Wang K.C. (2018), Systems Programming in Unix/Linux, Springer; 1st ed. 2018 edition.