Database Programming with C/C++

Creating a database application in C/C++ is a daunting task, especially for a novice programmer. Although the actually code is quite simple, it is the configuration issues such as importing right library, drivers to use, how to access them, and so forth, that make it an uphill battle. If you think you need to sharpen those C/C#/C++ skills, visit the TechRepublic Academy today. Modern high-level languages are pretty straightforward in these matters. They are quite simple and convenient with an all-in-one-place library with very few configuration troubles. One of the interesting aspects of using a high-level language is that you can almost start coding with even a little understanding of the underlying principles. C/C++, on the other hand, is a different breed. It is not easy to tread even a step superficially. This makes it more intriguing, challenging, that which tests your skills with every code you write. But, once you overcome the preliminary hurdles, there is nothing like C/C++. And the database programming? It’s quite fun, actually. Let’s get a first ride with the database code in C/C++ in this article.

How Do You Access Databases In C/C++?

There are many practical ways to access a database in C/C++. Except for ODBC; its APIs are not standard. Most database vendors provide a native client library to access a database. Client libraries are vendor specific; that means that the API provided by one is different from another although the underlying principles are the same. MySQL, for example, has its own client library and the API supplied by is are quite different from the API supplied by the client library of PostgreSQL. If you stick to one vendor-specific database, say MySQL, the driver options for database programming with C/C++ are:

  • MySQL Client library: It is a native C API library distributed with MySQL and implemented in the libmysqlclient library. If you have MySQL Server installed, most probably the client API library is already installed. (Otherwise, it may be downloaded with the command, something like sudo apt-get install libmysqlclient<version> libmysqlclient<version>-dev in Ubuntu/Mint.)
  • MySQL C/C++ Connector: It is an innovation in its prime to make database connectivity simple and convenient. The API is based partially on the JDBC4.0 API standard and perhaps will be the standard way to access a database as it matures. There is a separate connector for C and as well as for C++. While using the C++ connector, no C functions calls are required.
  • ODBC (Open Database Connectivity): Developed by Microsoft in the 90’s, it provides a vendor-neutral API to a access database system. Most database vendors provide at least an ODBC driver apart from its native support. ODBC is basically a driver model that contains logic to convert a standard set of commands into calls understood by the underlying system. It stands between the application and the database system to reciprocate the exchange of calls/responses among them. In recent years, due to the advent of thin client computing, the requirement of virtualization offered by ODBC has been reduced. Most Web development platforms work in layers with HTML as the intermediary between the user and the application. And, in most cases, the underlying database layers have a direct link with the target database. This made native library more preferable over ODBC. Due to these reasons, the development of ODBC has slacked down in the recent years.

How Do You Connect MySQL and C/C++?

Let’s try out a database application using a very basic, low-level MySQL client C API library. The database access routine more or less involves the following steps:

1. Initialize Connection Handle Structure

MYSQL *mysql_init(MYSQL *);

2. Make the Connection

MYSQL mysql_real_connect(
   MYSQL connection,
   const char *host,
   const char *username,
   const char *password,
   const char *database_name,
   unsigned int port,
   const char *unix_socket_name,
   unsigned int flags
);

3. Execute SQL Statements

int mysql_query(MYSQL *connection, const char *query);

4. Functions to Use for Data Retrieval

MYSQL_RES *mysql_use_result(MYSQL *connection);

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

5. Error Handling

unsigned int mysql_errno(MYSQL *connection);

char *mysql_error(MYSQL *connection);

6. Close the Connection

void mysql_close(MYSQL *connection);

There are many other functions, but these are the functions we shall use when creating the application down the line. Consult the MySQl C API manuals for more details on these and other APIs. To sum up, you’ll basically need at least the following software.

Application Name Source Details
Database MySQL 5 MySQL Database Server will be our back-end database
Database API MySQL client library Provides native driver and library: libmysqlclient as an interface between the application and the database. Make sure that the client API library is installed. Once installed, the header files and the library are generally found in /usr/include/mysql and /usr/lib/mysql, respectively; otherwise, make sure of the correct path in your system.
Compiler g++ GNU C++ compiler
IDE CodeLite 9.1.8 It is not absolute necessary to use an IDE, but it is convenient to use one. There are many IDEs available in Linux for C/C++ programming. CodeLite seemed (to me) modern,  simple, and intuitive. Also, it creates the make file automatically. You may choose any other, even simple gedit, vi, or any other simple text editor such as nano is also fine. In such a case. consult the appropriate manual for the configuration and settings and how to create the make file if you resort to do everything manually.

An Example: Transaction Processing System

This is a very simple and rudimentary implementation of a transaction processing system. The code could have been written in a C style (without classes) because the MySQL API functions calls are in C format. But, to get a feel of object-oriented database programming with C++, classes are used. In many cases, we had to force its parameters to go with the essence of C++ by casting and converting occasionally. The application processing can imagined as shown in Figure 1.

DB1
Figure 1: Transaction Processing System

Configuration and Settings in the IDE: CodeLite

Make sure the following configurations are set in the Global Settings of Project Settings.

Additional Include Paths = .;/usr/include/mysql

Options = -lmysqlclient

Library Path = .;/usr/lib/mysql

DB2
Figure 2: Project settings

#ifndef BANKACCOUNT_H
#define BANKACCOUNT_H

#include <string>
using std::string;

class BankAccount
{
public:
   static const int MAX_SIZE = 30;

   BankAccount(int = 0, string = "",
      string = "", double = 0.0);
   ~BankAccount();

   void setAccountNumber(int);
   void setLastName(string);
   void setFirstName(string);
   void setBalance(double);

   int getAccountNumber() const;
   string getFirstName() const;
   string getLastName() const;
   double getBalance() const;

private:
   int accountNumber;
   char firstName[MAX_SIZE];
   char lastName[MAX_SIZE];
   double balance;
};

#endif   // BANKACCOUNT_H

Listing 1: BankAccount.h

#include "BankAccount.h"

#include <string>
#include <cstring>

using std::string;

BankAccount::BankAccount(int accno, string fname,
   string lname, double bal)
{
   setAccountNumber(accno);
   setFirstName(fname);
   setLastName(lname);
   setBalance(bal);
}

void BankAccount::setAccountNumber(int accno)
{
   accountNumber = accno;
}

void BankAccount::setLastName(string lname)
{
   const char* ln = lname.data();
   int len = lname.size();
   len = (len < MAX_SIZE ? len : MAX_SIZE - 1);
   strncpy(lastName, ln, len);
   lastName[len] = '\0';
}

void BankAccount::setFirstName(string fname)
{
   const char* fn = fname.data();
   int len = fname.size();
   len = (len < MAX_SIZE ? len : MAX_SIZE - 1);
   strncpy(firstName, fn, len);
   firstName[len] = '\0';
}

void BankAccount::setBalance(double bal)
{
   balance = bal;
}

int BankAccount::getAccountNumber() const
{
   return accountNumber;
}

string BankAccount::getFirstName() const
{
   return firstName;
}

string BankAccount::getLastName() const
{
   return lastName;
}

double BankAccount::getBalance() const
{
   return balance;
}

BankAccount::~BankAccount()
{
}

Listing 2: BankAccount.cpp

#ifndef BANKTRANSACTION_H
#define BANKTRANSACTION_H

#include <mysql.h>
#include <string>

class BankAccount;

using namespace std;
class BankTransaction
{
public:
   BankTransaction(const string = "localhost",
      const string = "",
   const string = "", const string = "");
   ~BankTransaction();
   void createAccount(BankAccount*);
   void closeAccount(int);
   void deposit(int, double);
   void withdraw(int, double);
   BankAccount* getAccount(int);
   void printAllAccounts();
   void message(string);

private:
   MYSQL* db_conn;
};

#endif   // BANKTRANSACTION_H

Listing 3: BankTransaction.h

#include <cstdlib>
#include <sstream>
#include <iostream>
#include <iomanip>

#include "BankTransaction.h"
#include "BankAccount.h"

BankTransaction::BankTransaction(const string HOST,
   const string USER, const string PASSWORD,
   const string DATABASE)
{
   db_conn = mysql_init(NULL);
   if(!db_conn)
      message("MySQL initialization failed! ");
   db_conn = mysql_real_connect(db_conn, HOST.c_str(),
      USER.c_str(), PASSWORD.c_str(), DATABASE.c_str(), 0,
         NULL, 0);
   if(!db_conn)
      message("Connection Error! ");
}

BankTransaction::~BankTransaction()
{
   mysql_close(db_conn);
}

BankAccount* BankTransaction::getAccount(int acno)
{
   BankAccount* b = NULL;
   MYSQL_RES* rset;
   MYSQL_ROW row;
   stringstream sql;
   sql << "SELECT * FROM bank_account WHERE acc_no="
      << acno;

   if(!mysql_query(db_conn, sql.str().c_str())) {
      b = new BankAccount();
      rset = mysql_use_result(db_conn);
      row = mysql_fetch_row(rset);
      b->setAccountNumber(atoi(row[0]));
      b->setFirstName(row[1]);
      b->setLastName(row[2]);
      b->setBalance(atof(row[3]));
   }
   mysql_free_result(rset);
   return b;
}

void BankTransaction::withdraw(int acno, double amount)
{
   BankAccount* b = getAccount(acno);
   if(b != NULL) {
      if(b->getBalance() < amount)
         message("Cannot withdraw. Try lower amount.");
      else {
         b->setBalance(b->getBalance() - amount);
         stringstream sql;
         sql << "UPDATE bank_account SET balance="
            << b->getBalance()
            << " WHERE acc_no=" << acno;
         if(!mysql_query(db_conn, sql.str().c_str())) {
            message("Cash withdraw successful.
               Balance updated.");
         } else {
            message("Cash deposit unsuccessful!
               Update failed");
         }
      }
   }
}

void BankTransaction::deposit(int acno, double amount)
{
   stringstream sql;
   sql << "UPDATE bank_account SET balance=balance+" << amount
      << " WHERE acc_no=" << acno;
   if(!mysql_query(db_conn, sql.str().c_str())) {
      message("Cash deposit successful. Balance updated.");
   } else {
      message("Cash deposit unsuccessful! Update failed");
   }
}

void BankTransaction::createAccount(BankAccount* ba)
{
   stringstream ss;
   ss << "INSERT INTO bank_account(acc_no, fname, lname,
         balance)"
      << "values (" << ba->getAccountNumber() << ", '"
         << ba->getFirstName() + "','"
         << ba->getLastName() << "',"
         << ba->getBalance() << ")";
   if(mysql_query(db_conn, ss.str().c_str()))
      message("Failed to create account! ");
   else
      message("Account creation successful.");
}

void BankTransaction::closeAccount(int acno)
{
   stringstream ss;
   ss << "DELETE FROM bank_account WHERE acc_no="
      << acno;
   if(mysql_query(db_conn, ss.str().c_str()))
      message("Failed to close account! ");
   else
      message("Account close successful.");
}

void BankTransaction::message(string msg)
{
   cout << msg << endl;
}
void BankTransaction::printAllAccounts()
{
   MYSQL_RES* rset;
   MYSQL_ROW rows;
   string sql = "SELECT * FROM bank_account";
   if(mysql_query(db_conn, sql.c_str())) {
      message("Error printing all accounts! ");
      return;
   }

   rset = mysql_use_result(db_conn);

   cout << left << setw(10) << setfill('-') << left << '+'
        << setw(21) << setfill('-') << left << '+'
        << setw(21)
        << setfill('-') << left << '+' << setw(21)
        << setfill('-')
        << '+' << '+' << endl;
   cout << setfill(' ') << '|' << left << setw(9)
        << "Account"
        << setfill(' ') << '|' << setw(20) << "First Name"
        << setfill(' ') << '|' << setw(20) << "Last Name"
        << setfill(' ') << '|' << right << setw(20)
        << "Balance" << '|' << endl;

   cout << left << setw(10) << setfill('-') << left
       << '+' << setw(21) << setfill('-') << left << '+'
       << setw(21)
       << setfill('-') << left << '+' << setw(21) << setfill('-')
       << '+' << '+' << endl;
   if(rset) {
      while((rows = mysql_fetch_row(rset))) {
         cout << setfill(' ') << '|' << left << setw(9) << rows[0]
              << setfill(' ') << '|' << setw(20) << rows[1]
              << setfill(' ') << '|' << setw(20) << rows[2]
              << setfill(' ') << '|' << right << setw(20)
              << rows[3] << '|' << endl;
      }
      cout << left << setw(10) << setfill('-') << left
           << '+' << setw(21) << setfill('-') << left << '+'
           << setw(21)
           << setfill('-') << left << '+' << setw(21)
           << setfill('-')
           << '+' << '+' << endl;
   }
   mysql_free_result(rset);
}

Listing 4: BankTransaction.cpp

#include <iostream>
#include <sstream>
#include <iomanip>

#include <cstdlib>
#include <mysql.h>

#include "BankAccount.h"
#include "BankTransaction.h"

using namespace std;

enum Options { PRINT = 1, NEW, WITHDRAW, DEPOSIT,
               CLOSE, END };

int mainMenu()
{
   cout << "\nMenu Options" << endl
        << "1 - Print All Account"
        << endl << "2 - Open New Account" << endl
        << "3 - Withdraw" << endl << "4 - Deposit"
        << endl << "5 - Close Account" << endl
        << "6 - End Transaction" << endl;
   int ch;
   cin >> ch;
   return ch;
}

int main(int argc, char** argv)
{
   BankTransaction* bt =
      new BankTransaction("localhost", "root",
                          "passwd123", "mybank");

   int choice;
   int acno;
   string fname, lname;
   double bal;

   while(1) {
      choice = mainMenu();
      if(choice == END)
         break;
      switch(choice) {
      case PRINT:
         bt->printAllAccounts();
         break;
      case NEW:
         cout << "\nEnter account no, first name,
                             last name, balance: "
              << endl << "? ";
         cin >> acno;
         cin >> fname;
         cin >> lname;
         cin >> bal;
         if(acno < 1) {
            cout << "Invalid account number." << endl;
            break;
         }
         bt->createAccount(new BankAccount(acno, fname, lname,
                                           bal));
         break;
      case WITHDRAW:
         cout << "\nEnter account no, amount to withdraw "
              << endl << "? ";
         cin >> acno;
         cin >> bal;
         if(bal < 0) {
            cout << "Invalid amount." << endl;
            break;
         }
         bt->withdraw(acno, bal);
         break;
      case DEPOSIT:
         cout << "\nEnter account no, amount to deposit "
              << endl << "? ";
         cin >> acno;
         cin >> bal;
         if(bal < 0) {
            cout << "Invalid amount." << endl;
            break;
         }
         bt->deposit(acno, bal);
         break;
     case CLOSE:
         cout << "\nEnter account no to close account "
              << endl << "? ";
         cin >> acno;
         bt->closeAccount(acno);
         break;
      default:
         cerr << "Invalid choice!" << endl;
         break;
      }
   }
   return 0;
}

Listing 5: main.cpp

Build and Execute Project

DB3
Figure 3: The completed project

Conclusion

Many of the checks and validation are unimplemented to keep things as simple as possible. Only the absolute minimum number of functions are used from the libmysqlclient API library. The minimal CRUD operations are implemented so that it can be used as the basis for further improvement.

Manoj Debnath
Manoj Debnath
A teacher(Professor), actively involved in publishing, research and programming for almost two decades. Authored several articles for reputed sites like CodeGuru, Developer, DevX, Database Journal etc. Some of his research interest lies in the area of programming languages, database, compiler, web/enterprise development etc.

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read