mysql – connection example

Mysql is a database, and to gain access to the data within C++ you will need to be able to “talk” to the database via queries (just like on the mysql command line interface e.g. select * from tablename), the connection process is very similar to the command line interface you will need to supply connection details as in hostname (localhost normally), username, password, database to use and also there are other details that you can pass e.g port number more information can be gained from the MYSQL API pages

To start with I created a struct that will hold the username, host etc details.

struct connection_details
{
    char *server;
    char *user;
    char *password;
    char *database;
};

In essence to connect to a database I have created a function that will connect and return a MYSQL pointer to the new connection using the structure above connection_details.

MYSQL* mysql_connection_setup(struct connection_details mysql_details)
{
     // first of all create a mysql instance and initialize the variables within
    MYSQL *connection = mysql_init(NULL);
 
    // connect to the database with the details attached.
    if (!mysql_real_connect(connection,mysql_details.server, mysql_details.user, mysql_details.password, mysql_details.database, 0, NULL, 0)) {
      printf("Conection error : %s\n", mysql_error(connection));
      exit(1);
    }
    return connection;
}

That is it, you are connected now, now you can perform some sql queries, once again I have created a function to accomplish this and it returns a MYSQL_RES (mysql result pointer)

MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)
{
   // send the query to the database
   if (mysql_query(connection, sql_query))
   {
      printf("MySQL query error : %s\n", mysql_error(connection));
      exit(1);
   }
 
   return mysql_use_result(connection);
}

since you are now connected and also preformed a query, lets say the query was

SHOW TABLES;

You can traverse the results with a while loop in C++ and use the mysql_fetch_row function from within the mysql library set where a row is a type of MYSQL_ROW.

MYSQL_ROW row;
  while ((row = mysql_fetch_row(res)) !=NULL)
      printf("%s\n", row[0]);

Here is the full code that will display all of the tables within the mysql database database, you will need to alter the username and password for the mysql access details.

#include <mysql.h>
#include <stdio.h>
#include <stdlib.h>
 
// just going to input the general details and not the port numbers
struct connection_details
{
    char *server;
    char *user;
    char *password;
    char *database;
};
 
MYSQL* mysql_connection_setup(struct connection_details mysql_details)
{
     // first of all create a mysql instance and initialize the variables within
    MYSQL *connection = mysql_init(NULL);
 
    // connect to the database with the details attached.
    if (!mysql_real_connect(connection,mysql_details.server, mysql_details.user, mysql_details.password, mysql_details.database, 0, NULL, 0)) {
      printf("Conection error : %s\n", mysql_error(connection));
      exit(1);
    }
    return connection;
}
 
MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)
{
   // send the query to the database
   if (mysql_query(connection, sql_query))
   {
      printf("MySQL query error : %s\n", mysql_error(connection));
      exit(1);
   }
 
   return mysql_use_result(connection);
}
 
int main()
{
  MYSQL *conn;		// the connection
  MYSQL_RES *res;	// the results
  MYSQL_ROW row;	// the results row (line by line)
 
  struct connection_details mysqlD;
  mysqlD.server = "localhost";  // where the mysql database is
  mysqlD.user = "mysqlusername";		// the root user of mysql	
  mysqlD.password = "mysqlpassword"; // the password of the root user in mysql
  mysqlD.database = "mysql";	// the databse to pick
 
  // connect to the mysql database
  conn = mysql_connection_setup(mysqlD);
 
  // assign the results return to the MYSQL_RES pointer
  res = mysql_perform_query(conn, "show tables");
 
  printf("MySQL Tables in mysql database:\n");
  while ((row = mysql_fetch_row(res)) !=NULL)
      printf("%s\n", row[0]);
 
  /* clean up the database result set */
  mysql_free_result(res);
  /* clean up the database link */
  mysql_close(conn);
 
  return 0;
}

To compile up this program you will need to link to the mysql libraries and headers that are used within the program, e.g. mysql.h at the top of the program. To gain access to these, there is a nice mysql_config (you may need to install it via your package manager system if you do not have it already).

Here are my outputs of what is required on the command line for the g++ compiler

mysql_config --cflags
-I/usr/include/mysql  -DBIG_JOINS=1  -fno-strict-aliasing   -DUNIV_LINUX
mysql_config --libs
-Wl,-Bsymbolic-functions -rdynamic -L/usr/lib/mysql -lmysqlclient

These are the g++/c++ switches that tell the compiler where the libraries (-L) are and the headers (-I) and to insert into the g++/c++ compiler line within linux you can do something like

g++ -o mysqlconnect $(mysql_config --cflags) mysqlconnect.cpp $(mysql_config --libs)

Where the mysqlconnect.cpp is the c++ file of the above program and the output would be

./mysqlconnect
MySQL Tables in mysql database:
columns_priv
db
event
func
general_log
help_category
help_keyword
help_relation
help_topic
host
ndb_binlog_index
plugin
proc
procs_priv
servers
slow_log
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user

Tags: ,

19 Responses to “mysql – connection example”

  1. rajnish baflipara says:

    pls give information foe how create a mysql.h….?

  2. genux says:

    Hi Rajnish..

    I believe for windows you can obtain the mysql.h file from http://dev.mysql.com/downloads/connector/cpp/

    HTH

  3. Nigilan says:

    i hav mysql.h. and

    mysql_config –cflags
    -I/usr/include/mysql -DBIG_JOINS=1 -fno-strict-aliasing -DUNIV_LINUX
    mysql_config –libs
    -Wl,-Bsymbolic-functions -rdynamic -L/usr/lib/mysql -lmysqlclient

    can i execute this in terminal and run above program willl it run?

  4. genux says:

    Hi Nigilan

    Yeah, you need to run that on the command line (terminal) which will show you where the libraries are for mysql, e.g. mysql.o files which inturn you need to pass into the compile stage of the above program so that knows where the libraries are. HTH

  5. chinna says:

    hi
    i want to know how to learn c

  6. genux says:

    There is some good books out there, or courses. I have done some c programming on this website if you want to take a look around ? on the right hand side menu.

  7. MANI says:

    hi how can i connect c++ with database.. can u give me a sample project…

  8. genux says:

    Have you tried saving the code above ?

  9. tbny says:

    ahh soo great! so clear! and so concise! This is exactly what I needed to get me started. I’ve already started expanding on your code. Thanks for the great tut!

  10. kenneth says:

    I have problem with your codes sir!
    it does not compile is it working??

  11. genux says:

    Hi Kenneth

    What OS are you trying to compile on ?

    Regards
    Genux

  12. Jams says:

    hi…how can i connect mysql on linux from windows by using C++?
    and where should include the mysql.h and libs for different platform?

  13. Mark says:

    When I try to execute your code, I get an error of “VARARGS not implemented for this compiler” that comes from vadefs.h file.

    I am running Windows XP and ANSI C

  14. genux says:

    I would say that you just need to point the connection string to where the linux server is.

  15. pankaj says:

    when i connect mysql on linux from windows by using C++ in netbeans ide , the given problem occured

    build/Debug/Cygwin_4.x-Windows/main.o: In function `main':
    /cygdrive/c/Users/PINKY/Documents/NetBeansProjects/mysqlc/main.cpp:24: undefined reference to `_mysql_init’
    /cygdrive/c/Users/PINKY/Documents/NetBeansProjects/mysqlc/main.cpp:28: undefined reference to `_mysql_real_connect’
    /cygdrive/c/Users/PINKY/Documents/NetBeansProjects/mysqlc/main.cpp:29: undefined reference to `_mysql_error’
    /cygdrive/c/Users/PINKY/Documents/NetBeansProjects/mysqlc/main.cpp:34: undefined reference to `_mysql_query’
    /cygdrive/c/Users/PINKY/Documents/NetBeansProjects/mysqlc/main.cpp:35: undefined reference to `_mysql_error’
    /cygdrive/c/Users/PINKY/Documents/NetBeansProjects/mysqlc/main.cpp:39: undefined reference to `_mysql_use_result’
    /cygdrive/c/Users/PINKY/Documents/NetBeansProjects/mysqlc/main.cpp:43: undefined reference to `_mysql_fetch_row’
    /cygdrive/c/Users/PINKY/Documents/NetBeansProjects/mysqlc/main.cpp:47: undefined reference to `_mysql_free_result’
    /cygdrive/c/Users/PINKY/Documents/NetBeansProjects/mysqlc/main.cpp:48: undefined reference to `_mysql_close’

    how can i fix it. please help me!

  16. genux says:

    Hi Pankaj

    To me it looks like you are missing the library references for the mysql library ? have you included it.

    Regards
    Genux

  17. Gary Rubin (HopWorks) says:

    Thank you for the contribution! I got what I needed from this to successfully do as you did, list my tables in a database.

    I have a compiling error though that I cannot seem to solve. It’s a warning actually and my code still works, but it is nagging me.

    libz.so.1, needed by /home/hop/rpi_usr/lib/arm-linux-gnueabihf/libmysqlclient.so, not found (try using -rpath or -rpath-link)

    I actually copied libz.so.1 and a bunch of other libraries to the same folder as libmysqlclient.so but the compile still throws this warning. Any advice would be greatly appreciated.

    Again, thanks for the contribution!!

  18. genux says:

    Hi Gary

    The libz is an compression library (from memory) and should not stop the compiling / execution as you say.

    But it is the execution of the program that will cause that error ? do you have that library within the directory ?

    HTH
    Ian

  19. Gary Rubin (HopWorks) says:

    Yes, the library is in the same folder, but the links look different than they do on the Pi. I posted quite a bit more info on stackoverflow.
    http://stackoverflow.com/questions/22340854/libz-so-1-not-found-compiler-warning-mysql-c

    Also, I ran into a problem where MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query) threw an error, and when EXIT -1 was encountered, it exited my program and locked up my putty terminal. I am assuming this isn’t the way I want to exit my program but not sure how to approach it. This of course is my fault and just showed me that I still have quite a bit to learn about C++ programming in Linux. Thank you again for the contribution and the quick reply.

Leave a Reply