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

28 thoughts on “mysql – connection example”

  3. 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. 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

  6. 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.

  9. 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!

  12. 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. 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

  15. 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. Hi Pankaj

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

    Regards
    Genux

  17. 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. 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. 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.

  20. Hi there!

    Thank you so much for putting this up!
    I was stuck, trying to connect to my MySQL database with the Oracle provided C++ connector. No compiling issues for my code, no install problems… but my program was simply crashing without error message at the connection line (driver->connect(etc…)). In my despair, I started looking for alternative ways to connect but couldn’t find anything clean and easy until I found this post of yours!

    I worked perfectly. I hope there are no restrictions from connecting this way… I’m starting to wonder what this C++connector is actually good for….

    Regards,
    R.R.

  21. Hi, I have one question. Is it possible to protect the data related to struct connection_details the username and password, that can not be seen when the compiled file open in GEdit. How to protect that information, whether it is possible to be done and how?
    Maybe this question is not for this post but I was not able to found a way to protect these values, if they happen to someone steals application and later use this information for unauthorized access to the database.

  22. Hi Askynp, I would say that you could encrypt the login details. But of course if you are able to decrypt them then so will someone else, what is application for ? and also to what level do you wan to keep the data secure ?

  23. I am hoping you can help genux. This is an attempt at coding after a long hiatus (last coding was under dos with borland c)…. I copied your file and modified it with the details to connect to my sql server. I am running MinGW under win 8. (no cygwin). I go to compile it and get this lovely little message …..

    C:\startrader>g++ objects.cpp -o objects.exe
    objects.cpp: In function ‘int main()’:
    objects.cpp:47:17: warning: deprecated conversion from string constant to ‘char*
    ‘ [-Wwrite-strings]
    mysqlD.server = “localhost”; // where the mysql database is
    ^
    objects.cpp:48:15: warning: deprecated conversion from string constant to ‘char*
    ‘ [-Wwrite-strings]
    mysqlD.user = “root”; // the root user of mysql
    ^
    objects.cpp:49:19: warning: deprecated conversion from string constant to ‘char*
    ‘ [-Wwrite-strings]
    mysqlD.password = “misty1”; // the password of the root user in mysql
    ^
    objects.cpp:50:19: warning: deprecated conversion from string constant to ‘char*
    ‘ [-Wwrite-strings]
    mysqlD.database = “startrader_objects”; // the databse to pick
    ^
    objects.cpp:56:48: warning: deprecated conversion from string constant to ‘char*
    ‘ [-Wwrite-strings]
    res = mysql_perform_query(conn, “show tables”);
    ^
    C:\Users\Steven\AppData\Local\Temp\ccwAv3oN.o:objects.cpp:(.text+0xf): undefined
    reference to `mysql_init@4′
    C:\Users\Steven\AppData\Local\Temp\ccwAv3oN.o:objects.cpp:(.text+0x54): undefine
    d reference to `mysql_real_connect@32′
    C:\Users\Steven\AppData\Local\Temp\ccwAv3oN.o:objects.cpp:(.text+0x6b): undefine
    d reference to `mysql_error@4′
    C:\Users\Steven\AppData\Local\Temp\ccwAv3oN.o:objects.cpp:(.text+0xaa): undefine
    d reference to `mysql_query@8′
    C:\Users\Steven\AppData\Local\Temp\ccwAv3oN.o:objects.cpp:(.text+0xc1): undefine
    d reference to `mysql_error@4′
    C:\Users\Steven\AppData\Local\Temp\ccwAv3oN.o:objects.cpp:(.text+0xeb): undefine
    d reference to `mysql_use_result@4′
    C:\Users\Steven\AppData\Local\Temp\ccwAv3oN.o:objects.cpp:(.text+0x181): undefin
    ed reference to `mysql_fetch_row@4′
    C:\Users\Steven\AppData\Local\Temp\ccwAv3oN.o:objects.cpp:(.text+0x19d): undefin
    ed reference to `mysql_free_result@4′
    C:\Users\Steven\AppData\Local\Temp\ccwAv3oN.o:objects.cpp:(.text+0x1ab): undefin
    ed reference to `mysql_close@4′
    f:/mingw/bin/../lib/gcc/mingw32/4.8.1/../../../../mingw32/bin/ld.exe: C:\Users\S
    teven\AppData\Local\Temp\ccwAv3oN.o: bad reloc address 0x20 in section `.eh_fram
    e’
    f:/mingw/bin/../lib/gcc/mingw32/4.8.1/../../../../mingw32/bin/ld.exe: final link
    failed: Invalid operation
    collect2.exe: error: ld returned 1 exit status

    Any help you can provide would be greatly appreciated… Thank You!

  24. Hi Qaldyari,

    Sorry for the late reply, but it looks like

    a). I would need to update the code so that char* are converted correctly.
    b). I think that you are missing the mysql libraries ?

    Not sure if that helps.
    Genux

  25. Brilliant explanation, however, i have included the mysql.h from the include file within the MYSQL program files, but i can’t find the .lib file which i need for the code to run. I am using visual studio 2013 c++. Where abouts is it? Or can you send me it please?

    Thank you 🙂

  26. Thanks very much.

    The mysql.lib file is the library from MYSQL, I compiled the example on a linux machine but on a windows setup the library would probably be installed within the MySQL directory ? (c:\program files\mysql\lib …. sort of thing. )

  27. Yeah i can only see the “libmysql.lib” and “mysqlclient.lib” however, the first doesn’t work which is what i would have thought would be the correct one. Are you able to send me the correct lib file or point me in the right direction where to download it from
    ?

Leave a Reply

Your email address will not be published. Required fields are marked *