An Overview

• MySQL Client library: It is a native C API library distributed with MySQL and implemented in thelibmysqlclient 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.

C/C++ and MySQL

1. Initialize Connection Handle Structure

1. MYSQL *mysql_init(MYSQL *);

2. Make the Connection

1. MYSQL mysql_real_connect(
2. MYSQL connection,
3. const char *host,
4. const char *username,
5. const char *password,
6. const char *database_name,
7. unsigned int port,
8. const char *unix_socket_name,
9. unsigned int flags
10. );

3. Execute SQL Statements

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

4. Functions to Use for Data Retrieval

1. MYSQL_RES *mysql_use_result(MYSQL *connection);
2.  
3. MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

5. Error Handling

1. unsigned int mysql_errno(MYSQL *connection);
2.  
3. char *mysql_error(MYSQL *connection);

6. Close the Connection

1. void mysql_close(MYSQL *connection);

An Example: Transaction Processing System

Figure 1: Transaction Processing System

Configuration and Settings the in 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

Figure 2: Project settings

1. #ifndef BANKACCOUNT_H
2. #define BANKACCOUNT_H
3.  
4. #include <string>
5. using std::string;
6.  
7. class BankAccount
8. {
9. public:
10. static const int MAX_SIZE = 30;
11.  
12. BankAccount(int = 0, string = "",
13. string = "", double = 0.0);
14. ~BankAccount();
15.  
16. void setAccountNumber(int);
17. void setLastName(string);
18. void setFirstName(string);
19. void setBalance(double);
20.  
21. int getAccountNumber() const;
22. string getFirstName() const;
23. string getLastName() const;
24. double getBalance() const;
25.  
26. private:
27. int accountNumber;
28. char firstName[MAX_SIZE];
29. char lastName[MAX_SIZE];
30. double balance;
31. };
32.  
33. #endif // BANKACCOUNT_H

Listing 1: BankAccount.h

1. #include "BankAccount.h"
2.  
3. #include <string>
4. #include <cstring>
5.  
6. using std::string;
7.  
8. BankAccount::BankAccount(int accno, string fname,
9. string lname, double bal)
10. {
11. setAccountNumber(accno);
12. setFirstName(fname);
13. setLastName(lname);
14. setBalance(bal);
15. }
16.  
17. void BankAccount::setAccountNumber(int accno)
18. {
19. accountNumber = accno;
20. }
21.  
22. void BankAccount::setLastName(string lname)
23. {
24. const char* ln = lname.data();
25. int len = lname.size();
26. len = (len < MAX_SIZE ? len : MAX_SIZE - 1);
27. strncpy(lastName, ln, len);
28. lastName[len] = '\0';
29. }
30.  
31. void BankAccount::setFirstName(string fname)
32. {
33. const char* fn = fname.data();
34. int len = fname.size();
35. len = (len < MAX_SIZE ? len : MAX_SIZE - 1);
36. strncpy(firstName, fn, len);
37. firstName[len] = '\0';
38. }
39.  
40. void BankAccount::setBalance(double bal)
41. {
42. balance = bal;
43. }
44.  
45. int BankAccount::getAccountNumber() const
46. {
47. return accountNumber;
48. }
49.  
50. string BankAccount::getFirstName() const
51. {
52. return firstName;
53. }
54.  
55. string BankAccount::getLastName() const
56. {
57. return lastName;
58. }
59.  
60. double BankAccount::getBalance() const
61. {
62. return balance;
63. }
64.  
65. BankAccount::~BankAccount()
66. {
67. }

Listing 2: BankAccount.cpp

1. #ifndef BANKTRANSACTION_H
2. #define BANKTRANSACTION_H
3.  
4. #include <mysql.h>
5. #include <string>
6.  
7. class BankAccount;
8.  
9. using namespace std;
10. class BankTransaction
11. {
12. public:
13. BankTransaction(const string = "localhost",
14. const string = "",
15. const string = "", const string = "");
16. ~BankTransaction();
17. void createAccount(BankAccount*);
18. void closeAccount(int);
19. void deposit(int, double);
20. void withdraw(int, double);
21. BankAccount* getAccount(int);
22. void printAllAccounts();
23. void message(string);
24.  
25. private:
26. MYSQL* db_conn;
27. };
28.  
29. #endif // BANKTRANSACTION_H

Listing 3: BankTransaction.h

1. #include <cstdlib>
2. #include <sstream>
3. #include <iostream>
4. #include <iomanip>
5.  
6. #include "BankTransaction.h"
7. #include "BankAccount.h"
8.  
9. BankTransaction::BankTransaction(const string HOST,
10. const string USER, const string PASSWORD,
11. const string DATABASE)
12. {
13. db_conn = mysql_init(NULL);
14. if(!db_conn)
15. message("MySQL initialization failed! ");
16. db_conn = mysql_real_connect(db_conn, HOST.c_str(),
17. USER.c_str(), PASSWORD.c_str(), DATABASE.c_str(), 0,
18. NULL, 0);
19. if(!db_conn)
20. message("Connection Error! ");
21. }
22.  
23. BankTransaction::~BankTransaction()
24. {
25. mysql_close(db_conn);
26. }
27.  
28. BankAccount* BankTransaction::getAccount(int acno)
29. {
30. BankAccount* b = NULL;
31. MYSQL_RES* rset;
32. MYSQL_ROW row;
33. stringstream sql;
34. sql << "SELECT * FROM bank_account WHERE acc_no="
35. << acno;
36.  
37. if(!mysql_query(db_conn, sql.str().c_str())) {
38. b = new BankAccount();
39. rset = mysql_use_result(db_conn);
40. row = mysql_fetch_row(rset);
41. b->setAccountNumber(atoi(row[0]));
42. b->setFirstName(row[1]);
43. b->setLastName(row[2]);
44. b->setBalance(atof(row[3]));
45. }
46. mysql_free_result(rset);
47. return b;
48. }
49.  
50. void BankTransaction::withdraw(int acno, double amount)
51. {
52. BankAccount* b = getAccount(acno);
53. if(b != NULL) {
54. if(b->getBalance() < amount)
55. message("Cannot withdraw. Try lower amount.");
56. else {
57. b->setBalance(b->getBalance() - amount);
58. stringstream sql;
59. sql << "UPDATE bank_account SET balance="
60. << b->getBalance()
61. << " WHERE acc_no=" << acno;
62. if(!mysql_query(db_conn, sql.str().c_str())) {
63. message("Cash withdraw successful.
64. Balance updated.");
65. } else {
66. message("Cash deposit unsuccessful!
67. Update failed");
68. }
69. }
70. }
71. }
72.  
73. void BankTransaction::deposit(int acno, double amount)
74. {
75. stringstream sql;
76. sql << "UPDATE bank_account SET balance=balance+" << amount
77. << " WHERE acc_no=" << acno;
78. if(!mysql_query(db_conn, sql.str().c_str())) {
79. message("Cash deposit successful. Balance updated.");
80. } else {
81. message("Cash deposit unsuccessful! Update failed");
82. }
83. }
84.  
85. void BankTransaction::createAccount(BankAccount* ba)
86. {
87. stringstream ss;
88. ss << "INSERT INTO bank_account(acc_no, fname, lname,
89. balance)"
90. << "values (" << ba->getAccountNumber() << ", '"
91. << ba->getFirstName() + "','"
92. << ba->getLastName() << "',"
93. << ba->getBalance() << ")";
94. if(mysql_query(db_conn, ss.str().c_str()))
95. message("Failed to create account! ");
96. else
97. message("Account creation successful.");
98. }
99.  
100. void BankTransaction::closeAccount(int acno)
101. {
102. stringstream ss;
103. ss << "DELETE FROM bank_account WHERE acc_no="
104. << acno;
105. if(mysql_query(db_conn, ss.str().c_str()))
106. message("Failed to close account! ");
107. else
108. message("Account close successful.");
109. }
110.  
111. void BankTransaction::message(string msg)
112. {
113. cout << msg << endl;
114. }
115. void BankTransaction::printAllAccounts()
116. {
117. MYSQL_RES* rset;
118. MYSQL_ROW rows;
119. string sql = "SELECT * FROM bank_account";
120. if(mysql_query(db_conn, sql.c_str())) {
121. message("Error printing all accounts! ");
122. return;
123. }
124.  
125. rset = mysql_use_result(db_conn);
126.  
127. cout << left << setw(10) << setfill('-') << left << '+'
128. << setw(21) << setfill('-') << left << '+'
129. << setw(21)
130. << setfill('-') << left << '+' << setw(21)
131. << setfill('-')
132. << '+' << '+' << endl;
133. cout << setfill(' ') << '|' << left << setw(9)
134. << "Account"
135. << setfill(' ') << '|' << setw(20) << "First Name"
136. << setfill(' ') << '|' << setw(20) << "Last Name"
137. << setfill(' ') << '|' << right << setw(20)
138. << "Balance" << '|' << endl;
139.  
140. cout << left << setw(10) << setfill('-') << left
141. << '+' << setw(21) << setfill('-') << left << '+'
142. << setw(21)
143. << setfill('-') << left << '+' << setw(21) << setfill('-')
144. << '+' << '+' << endl;
145. if(rset) {
146. while((rows = mysql_fetch_row(rset))) {
147. cout << setfill(' ') << '|' << left << setw(9) << rows[0]
148. << setfill(' ') << '|' << setw(20) << rows[1]
149. << setfill(' ') << '|' << setw(20) << rows[2]
150. << setfill(' ') << '|' << right << setw(20)
151. << rows[3] << '|' << endl;
152. }
153. cout << left << setw(10) << setfill('-') << left
154. << '+' << setw(21) << setfill('-') << left << '+'
155. << setw(21)
156. << setfill('-') << left << '+' << setw(21)
157. << setfill('-')
158. << '+' << '+' << endl;
159. }
160. mysql_free_result(rset);
161. }

Listing 4: BankTransaction.cpp

1. #include <iostream>
2. #include <sstream>
3. #include <iomanip>
4.  
5. #include <cstdlib>
6. #include <mysql.h>
7.  
8. #include "BankAccount.h"
9. #include "BankTransaction.h"
10.  
11. using namespace std;
12.  
13. enum Options { PRINT = 1, NEW, WITHDRAW, DEPOSIT,
14. CLOSE, END };
15.  
16. int mainMenu()
17. {
18. cout << "\nMenu Options" << endl
19. << "1 - Print All Account"
20. << endl << "2 - Open New Account" << endl
21. << "3 - Withdraw" << endl << "4 - Deposit"
22. << endl << "5 - Close Account" << endl
23. << "6 - End Transaction" << endl;
24. int ch;
25. cin >> ch;
26. return ch;
27. }
28.  
29. int main(int argc, char** argv)
30. {
31. BankTransaction* bt =
32. new BankTransaction("localhost", "root",
33. "passwd123", "mybank");
34.  
35. int choice;
36. int acno;
37. string fname, lname;
38. double bal;
39.  
40. while(1) {
41. choice = mainMenu();
42. if(choice == END)
43. break;
44. switch(choice) {
45. case PRINT:
46. bt->printAllAccounts();
47. break;
48. case NEW:
49. cout << "\nEnter account no, first name,
50. last name, balance: "
51. << endl << "? ";
52. cin >> acno;
53. cin >> fname;
54. cin >> lname;
55. cin >> bal;
56. if(acno < 1) {
57. cout << "Invalid account number." << endl;
58. break;
59. }
60. bt->createAccount(new BankAccount(acno, fname, lname,
61. bal));
62. break;
63. case WITHDRAW:
64. cout << "\nEnter account no, amount to withdraw "
65. << endl << "? ";
66. cin >> acno;
67. cin >> bal;
68. if(bal < 0) {
69. cout << "Invalid amount." << endl;
70. break;
71. }
72. bt->withdraw(acno, bal);
73. break;
74. case DEPOSIT:
75. cout << "\nEnter account no, amount to deposit "
76. << endl << "? ";
77. cin >> acno;
78. cin >> bal;
79. if(bal < 0) {
80. cout << "Invalid amount." << endl;
81. break;
82. }
83. bt->deposit(acno, bal);
84. break;
85. case CLOSE:
86. cout << "\nEnter account no to close account "
87. << endl << "? ";
88. cin >> acno;
89. bt->closeAccount(acno);
90. break;
91. default:
92. cerr << "Invalid choice!" << endl;
93. break;
94. }
95. }
96. return 0;
97. }

Listing 5: main.cpp

Build and Execute Project

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.

News:

1 UCanCode Advance E-XD++ CAD Drawing and Printing Solution Source Code Solution for C/C++, .NET V2023 is released!

2 UCanCode Advance E-XD++ HMI & SCADA Source Code Solution for C/C++, .NET V2023 is released!