Today's Question:  What does your personal desk look like?        GIVE A SHOUT

mysql – connection example

  codingfriends.com        2011-10-29 00:42:10       4,721        0    

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

MYSQL  C++  CONNECTION  DATABASE  EXAMPLE 

Share on Facebook  Share on Twitter  Share on Weibo  Share on Reddit 

  RELATED


  0 COMMENT


No comment for this article.