Database Programming - Learning C by Example (2015)

Learning C by Example (2015)

9. Database Programming

This chapter explains how to create database application using C.

9.1 Database Library for C

C can communicate with database server through database driver. We must install database driver for C before we start to develop database application.

In this chapter, I only focus on MySQL scenarios. Basically, I have already published these codes to Github, https://github.com/agusk/crud-mysql-c , last year . You can download and improve these codes.

9.2 MySQL

To install MySQL server and client in Ubuntu and its database driver for C, you can try to writ this command in terminal

$ sudo apt-get install mysql-server mysql-client

$ sudo apt-get install libmysqlclient-dev

After finished, we can do testing.

Firstly, we can start to create simple C application to check MySQL version. Write this code

#include <my_global.h>

#include <mysql.h>

int main(int argc, char **argv)

{

printf("MySQL client version: %s\n", mysql_get_client_info());

exit(0);

}

Save this program into a file, called demo-mysql-version.c .

Now you can compile and run this file.

$ gcc -c -I/usr/include/mysql demo-mysql-version.c

$ gcc -o demo-mysql-version demo-mysql-version.o -L/usr/lib/mysql -lmysqlclient

$ ./demo-mysql-version

If success, you will get MySQL client version.

ch9-1

9.3 Connection Test

In this section, we try to test a connection to MySQL server. Create a file, called demo-connection.c and write this code.

#include <my_global.h>

#include <mysql.h>

int main(int argc, char **argv)

{

MYSQL *con = mysql_init(NULL);

if (con == NULL)

{

fprintf(stderr, "%s\n", mysql_error(con));

exit(1);

}

printf("connecting to mysql server...\r\n");

// change host, username, and password

if (mysql_real_connect(con, "localhost", "root", "password",

NULL, 0, NULL, 0) == NULL)

{

printf("error: %s\r\n", mysql_error(con));

mysql_close(con);

exit(1);

}

printf("connected.\r\n");

printf("closing connection...\r\n");

mysql_close(con);

printf("closed.\r\n");

exit(0);

}

Save this program.

Now you can compile and run this file.

$ gcc -c -I/usr/include/mysql demo-connection.c

$ gcc -o demo-connection demo-connection.o -L/usr/lib/mysql -lmysqlclient
$ ./demo-connection

Program output:

ch9-2

9.4 CRUD (Create, Read, Update and Delete) Operations

In this section, we try to develop simple CRUD operations using MySQL and C.

For testing, we create a database, mydatabase, and a table, called product. Run this SQL script into your MySQL server.

CREATE SCHEMA `mydatabase` ;

CREATE TABLE `mydatabase`.`product` (

`idproduct` INT NOT NULL AUTO_INCREMENT ,

`name` VARCHAR(45) NOT NULL ,

`price` FLOAT NOT NULL ,

`created` DATETIME NOT NULL ,

PRIMARY KEY (`idproduct`) );

9.4.1 Creating Data

We create data into MySQL. In this case, we create a ten data. Create a file, called demo-create-data.c and write this code.

#include <my_global.h>

#include <mysql.h>

#include <time.h>

#include <string.h>

#define CREATE_DATA "insert into product(name,price,created) values(?,?,?)"

int main(int argc, char **argv)

{

MYSQL *con = mysql_init(NULL);

if (con == NULL)

{

fprintf(stderr, "%s\n", mysql_error(con));

exit(1);

}

printf("connecting to mysql server...\r\n");

// change host, username, and password

if (mysql_real_connect(con, "localhost", "root", "password",

"mydatabase", 0, NULL, 0) == NULL)

{

printf("error: %s\r\n", mysql_error(con));

mysql_close(con);

exit(1);

}

printf("connected.\r\n");

printf("inserting 10 data...\r\n");

MYSQL_STMT *stmt;

stmt = mysql_stmt_init(con);

if (!stmt)

{

printf(" mysql_stmt_init(), out of memory\r\n");

exit(0);

}

if (mysql_stmt_prepare(stmt, CREATE_DATA, strlen(CREATE_DATA)))

{

printf("mysql_stmt_prepare(), INSERT failed\r\n");

printf("error: %s\r\n", mysql_stmt_error(stmt));

exit(0);

}

int i;

time_t now = time(NULL);

char name[10];

unsigned long str_length;

float price;

MYSQL_BIND bind[3];

MYSQL_TIME ts;

memset(bind, 0, sizeof(bind));

bind[0].buffer_type= MYSQL_TYPE_VAR_STRING;

bind[0].buffer= (char *)&name;

bind[0].is_null= 0;

bind[0].length= &str_length;

bind[1].buffer_type= MYSQL_TYPE_FLOAT;

bind[1].buffer= (char *)&price;

bind[1].is_null= 0;

bind[1].length= 0;

bind[2].buffer_type= MYSQL_TYPE_DATETIME;

bind[2].buffer= (char *)&ts;

bind[2].is_null= 0;

bind[2].length= 0;

// bind parameters

if (mysql_stmt_bind_param(stmt, bind))

{

printf("mysql_stmt_bind_param() failed\r\n");

printf("%s\r\n", mysql_stmt_error(stmt));

exit(0);

}

for(i=1;i<=10;i++)

{

// set values

sprintf(name,"product-%d",i);

str_length = strlen(name);

price = 0.23*i;

// time_t to MYSQL_TIME

struct tm *now_struct = gmtime(&now);

ts.year = now_struct->tm_year + 1900;

ts.month = now_struct->tm_mon + 1;

ts.day = now_struct->tm_mday;

ts.hour = now_struct->tm_hour;

ts.minute = now_struct->tm_min;

ts.second = now_struct->tm_sec;

printf("executing data %d...\r\n",i);

if (mysql_stmt_execute(stmt))

{

printf("mysql_stmt_execute(), 1 failed\r\n");

printf("%s\r\n", mysql_stmt_error(stmt));

exit(0);

}

}

printf("done.\r\n");

printf("closing connection...\r\n");

/* Close the statement */

if (mysql_stmt_close(stmt))

{

printf("failed while closing the statement\r\n");

printf("%s\r\n", mysql_stmt_error(stmt));

exit(0);

}

mysql_close(con);

printf("closed.\r\n");

exit(0);

}

Save this code.

Now you can compile and run this file.

$ gcc -c -I/usr/include/mysql demo-create-data.c

$ gcc -o demo-create-data demo-create-data.o -L/usr/lib/mysql -lmysqlclient
$ ./demo-create-data

Program output:

ch9-3

9.4.2 Reading Data

In this section, we try to read data from MySQL. For illustration, create a file, called demo-read-data.c and write this code.

#include <my_global.h>

#include <mysql.h>

int main(int argc, char **argv)

{

MYSQL *con = mysql_init(NULL);

if (con == NULL)

{

fprintf(stderr, "%s\n", mysql_error(con));

exit(1);

}

printf("connecting to mysql server...\r\n");

// change host, username, and password

if (mysql_real_connect(con, "localhost", "root", "password",

"mydatabase", 0, NULL, 0) == NULL)

{

printf("error: %s\r\n", mysql_error(con));

mysql_close(con);

exit(1);

}

printf("connected.\r\n");

printf("show data product\r\n");

if (mysql_query(con, "SELECT * FROM product"))

{

printf("error: %s\r\n", mysql_error(con));

mysql_close(con);

exit(1);

}

MYSQL_RES *result = mysql_store_result(con);

if (result == NULL)

{

printf("error: %s\r\n", mysql_error(con));

mysql_close(con);

exit(1);

}

int num_fields = mysql_num_fields(result);

MYSQL_ROW row;

int i;

while ((row = mysql_fetch_row(result)))

{

for(i = 0; i < num_fields; i++)

{

printf("%s\t", row[i] ? row[i] : "NULL");

}

printf("\r\n");

}

mysql_free_result(result);

printf("closing connection...\r\n");

mysql_close(con);

printf("closed.\r\n");

exit(0);

}

Save this code.

Now you can compile and run this file.

$ gcc -c -I/usr/include/mysql demo-read-data.c

$ gcc -o demo-read-data demo-read-data.o -L/usr/lib/mysql -lmysqlclient
$ ./demo-read-data

Program output:

ch9-4

9.4.3 Updating Data

In this section, we try to update data, for instance, we update data with id=5. Create a file, called demo-update-data.c and write this code.

#include <my_global.h>

#include <mysql.h>

#include <string.h>

#define UPDATE_DATA "update product set name = ?, price=? where idproduct=?"

int main(int argc, char **argv)

{

MYSQL *con = mysql_init(NULL);

if (con == NULL)

{

fprintf(stderr, "%s\n", mysql_error(con));

exit(1);

}

printf("connecting to mysql server...\r\n");

// change host, username, and password

if (mysql_real_connect(con, "localhost", "root", "password",

"mydatabase", 0, NULL, 0) == NULL)

{

printf("error: %s\r\n", mysql_error(con));

mysql_close(con);

exit(1);

}

printf("connected.\r\n");

MYSQL_STMT *stmt;

stmt = mysql_stmt_init(con);

if (!stmt)

{

printf(" mysql_stmt_init(), out of memory\r\n");

exit(0);

}

if (mysql_stmt_prepare(stmt, UPDATE_DATA, strlen(UPDATE_DATA)))

{

printf("mysql_stmt_prepare(), UPDATE failed\r\n");

printf("error: %s\r\n", mysql_stmt_error(stmt));

exit(0);

}

int i;

char name[10];

unsigned long str_length;

float price;

MYSQL_BIND bind[3];

int product_id;

memset(bind, 0, sizeof(bind));

bind[0].buffer_type= MYSQL_TYPE_VAR_STRING;

bind[0].buffer= (char *)&name;

bind[0].is_null= 0;

bind[0].length= &str_length;

bind[1].buffer_type= MYSQL_TYPE_FLOAT;

bind[1].buffer= (char *)&price;

bind[1].is_null= 0;

bind[1].length= 0;

bind[2].buffer_type= MYSQL_TYPE_LONG;

bind[2].buffer= (char *)&product_id;

bind[2].is_null= 0;

bind[2].length= 0;

// bind parameters

if (mysql_stmt_bind_param(stmt, bind))

{

printf("mysql_stmt_bind_param() failed\r\n");

printf("%s\r\n", mysql_stmt_error(stmt));

exit(0);

}

// set updated values

// change these values!!

sprintf(name,"product-updated");

str_length = strlen(name);

price = 10.33;

product_id = 5;

printf("updating data...\r\n");

if (mysql_stmt_execute(stmt))

{

printf("mysql_stmt_execute(), 1 failed\r\n");

printf("%s\r\n", mysql_stmt_error(stmt));

exit(0);

}

printf("done.\r\n");

printf("closing connection...\r\n");

/* Close the statement */

if (mysql_stmt_close(stmt))

{

printf("failed while closing the statement\r\n");

printf("%s\r\n", mysql_stmt_error(stmt));

exit(0);

}

mysql_close(con);

printf("closed.\r\n");

exit(0);

}

Save this code.

Now you can compile and run this file.

$ gcc -c -I/usr/include/mysql demo-update-data.c

$ gcc -o demo-update-data demo-update-data.o -L/usr/lib/mysql -lmysqlclient
$ ./demo-update-data

Program output:

ch9-5

9.4.4 Deleting Data

The last operation is to delete data, for instance, delete data with id=3. Create a file, called demo-delete-data.c and write this code.

#include <my_global.h>

#include <mysql.h>

#include <string.h>

#define DELETE_DATA "delete from product where idproduct=?"

int main(int argc, char **argv)

{

MYSQL *con = mysql_init(NULL);

if (con == NULL)

{

fprintf(stderr, "%s\n", mysql_error(con));

exit(1);

}

printf("connecting to mysql server...\r\n");

// change host, username, and password

if (mysql_real_connect(con, "localhost", "root", "password",

"mydatabase", 0, NULL, 0) == NULL)

{

printf("error: %s\r\n", mysql_error(con));

mysql_close(con);

exit(1);

}

printf("connected.\r\n");

MYSQL_STMT *stmt;

stmt = mysql_stmt_init(con);

if (!stmt)

{

printf(" mysql_stmt_init(), out of memory\r\n");

exit(0);

}

if (mysql_stmt_prepare(stmt, DELETE_DATA, strlen(DELETE_DATA)))

{

printf("mysql_stmt_prepare(), UPDATE failed\r\n");

printf("error: %s\r\n", mysql_stmt_error(stmt));

exit(0);

}

int i;

MYSQL_BIND bind[1];

int product_id;

memset(bind, 0, sizeof(bind));

bind[0].buffer_type= MYSQL_TYPE_LONG;

bind[0].buffer= (char *)&product_id;

bind[0].is_null= 0;

bind[0].length= 0;

// bind parameters

if (mysql_stmt_bind_param(stmt, bind))

{

printf("mysql_stmt_bind_param() failed\r\n");

printf("%s\r\n", mysql_stmt_error(stmt));

exit(0);

}

// select product id to be deleted

// change these values!!

product_id = 3;

printf("deleting data...\r\n");

if (mysql_stmt_execute(stmt))

{

printf("mysql_stmt_execute(), 1 failed\r\n");

printf("%s\r\n", mysql_stmt_error(stmt));

exit(0);

}

printf("done.\r\n");

printf("closing connection...\r\n");

/* Close the statement */

if (mysql_stmt_close(stmt))

{

printf("failed while closing the statement\r\n");

printf("%s\r\n", mysql_stmt_error(stmt));

exit(0);

}

mysql_close(con);

printf("closed.\r\n");

exit(0);

}

Save this code.

Now you can compile and run this file.

$ gcc -c -I/usr/include/mysql demo-delete-data.c

$ gcc -o demo-delete-data demo-delete-data.o -L/usr/lib/mysql -lmysqlclient
$ ./demo-delete-data

Program output:

ch9-6