How To Connect C++ To Mysql

have a look at the following functions. you can work out some wrapper functions like this yourself if you want to. I have used these functions in a recent project at work.

mysqlFunctions.h

/*
MySQL header file
written with c++, using Code blocks
author: Jos de Jong, Nov 2008

Requirements:
  the program needs libmysql.dll in the same directory as the executable to be able to run

Project settings when using CodeBlocks:
  - mysql server must be installed on your computer (complete, including include directory).
	This is just to be able to compile the program
  - libmysql.dll must be in the same directory as your program
  - Project settings: In project built options:
	- Project -> Built options -> Search Directories -> Compiler -> add "C:\Program Files\MySQL\MySQL Server 5.0\include"
	- Project -> Built options -> Linker settings -> add "C:\Program Files\MySQL\MySQL Server 5.0\lib\opt\libmysql.lib"

Project settings when using DevCpp:
  - install the libmysql devpack, "libmysql-5.0.5-1sid.DevPak"
  - startup Devcpp and create a console project.
  - libmysql.dll must be in the same directory as your program
  - mysql must be installed on your computer (complete, including include directory). This is just to be able to compile the program
  - in the project options you have to add
	directories -> include directories -> add "C:\Program Files\MySQL\MySQL Server 5.0\include"
										  or add "C:\Program Files\Dev-Cpp\include\"
										  (in short: the directory where the file mysql.h is located)
	parameters -> Linker -> add "-lmysql"

Example usage:
  ___________________________________________
  string server = "192.168.0.40";
  string username = "myusername";
  string password = "mypassword";
  string database = "mydatabase";
  sql = "SELECT * FROM mytable WHERE id=33;";
  string sqlResult;
  int resultFields = 0;
  int resultRows = 0;

  const int errorlen = 255;
  char cError[errorlen] = "";

  success = mysql_quick_execute(server.c_str(),
								username.c_str(),
								password.c_str(),
								database.c_str(),
								sql.c_str(),
								sqlResult,
								resultRows,
								resultFields,
								cError,
								errorlen);

  if (success)
  {
	printf("MySQL query result: %i rows, %i fields.\n%s\n",
			resultRows, resultFields, sqlResult.c_str());
  }
  else
  {
	printf("MySQL query result: Error: %s\n", cError);
  }
  ___________________________________________

more documentation:
  http://devpaks.org/details.php?devpak=105
  http://www.linuxfocus.org/Nederlands/September2003/article304.shtml
  http://www.geocities.com/jahan.geo/mysql_c_by_example.html
  http://www.cplusplus.com/

*/


#ifndef MYSQL_FUNCTIONS_H
#define MYSQL_FUNCTIONS_H


#include "windows.h"			  // for socket and COM etc.
#include <mysql.h>
#include <string>
#include <vector>
#include <cstring>

#include "GeneralFunctions.h"

using namespace std;



bool mysql_quick_execute(const char *server, const char *username, const char *password, const char *database,
						 const char *query, char *error, const int errorlen);

bool mysql_quick_execute(const char *server, const char *username, const char *password, const char *database,
						 const char *query, int& affectedRows, char *error, const int errorlen);

bool mysql_quick_execute(const char *server, const char *username, const char *password, const char *database,
						 const char *query, string& result,  int& resultRows, int& resultFields,
						 char *error, const int errorlen);

MYSQL* mysql_connect(const char server[], const char username[], const char password[], const char database[]);

void mysql_print_result(MYSQL *mysql);
string mysql_print_result(MYSQL *mysql, int& numRows, int& numFields);


/**
to close the connection, just use:
	mysql_close(MYSQL *mysql)
*/


/**
to get an error description use:
	char *error = mysql_error(mysql)
*/


#endif

mysqlFunctions.cpp

#include "mysqlFunctions.h"

/**
 * this function connects to mysql, executes a query that does not return result and closes the connection again
 * @param server	   the server, for example "quookerserver"
 * @param username	 the username under which you want to login on the MySQL server
 * @param password	 the accompanying password of the user
 * @param database	 the database to which you want to connect
 * @param query		pointer to a string with a MySQL query that does not return a result
 * @param error		pointer to a char array where an error message can possibly be filled in.
 * @param errorlen	 the lenght of the provided char array error
 * @return			 true when the function was executed succesful, else false and then there is an error message in error
 */
bool mysql_quick_execute(const char *server, const char *username, const char *password, const char *database,
						 const char *query, char *error, const int errorlen)
{
  int affectedRows = 0;
  bool success = mysql_quick_execute(server, username, password, database,
									 query, affectedRows, error, errorlen);
  return success;
}


/**
 * this function connects to mysql, executes a query that does not return result and closes the connection again
 * @param server	   the server, for example "quookerserver"
 * @param username	 the username under which you want to login on the MySQL server
 * @param password	 the accompanying password of the user
 * @param database	 the database to which you want to connect
 * @param query		pointer to a string with a MySQL query that does not return a result
 * @param affectedRows returns the number of affected rows. Can be useful for update queries.
 * @param error		pointer to a char array where an error message can possibly be filled in.
 * @param errorlen	 the lenght of the provided char array error
 * @return			 true when the function was executed succesful, else false and then there is an error message in error
 */
bool mysql_quick_execute(const char *server, const char *username, const char *password, const char *database,
						 const char *query, int& affectedRows, char *error, const int errorlen)
{
  MYSQL *mysql = 0;
  bool success = false;

  // initialize mysql
  mysql = mysql_init((MYSQL*) 0);

  // connect to mysql server
  if(mysql_real_connect(mysql, server, username, password, database, 0, NULL, 0))
  {
	// connection is made

	// execute the query
	if (!mysql_real_query(mysql, query, (unsigned int) strlen(query)))
	{
	  affectedRows = mysql_affected_rows(mysql);
	  success = true;
	}
	else
	{
	  // an error occured while executing query
	  strncpy (error, mysql_error(mysql), errorlen - 1);
	  error[errorlen - 1] = '\0';
	  success = false;
	}

	// close the mysql connection
	mysql_close(mysql);
  }
  else
  {
	// an error occured while trying to connect to the MySQL server
	strncpy (error, mysql_error(mysql), errorlen - 1);
	error[errorlen - 1] = '\0';
	success = false;
  }

  return success;
}


/**
 * this function connects to mysql, executes a query, and closes the connection again.
 * It returns a string containing all results. The fields are separated with a comma ",",
 * and the rows with a semicolon ";"
 * @param server	   the server, for example "quookerserver"
 * @param username	 the username under which you want to login on the MySQL server
 * @param password	 the accompanying password of the user
 * @param database	 the database to which you want to connect
 * @param query		pointer to a string with a MySQL query that does not return a result
 * @param result	   a string where the result of the query will be stored (multiple fields and rows)
 * @param resultRows   the number of rows of the result
 * @param resultFields the number of fields (columns) of the result
 * @param error		pointer to a char array where an error message can possibly be filled in.
 * @param errorlen	 the lenght of the provided char array error
 * @return			 true when the function was executed succesful, else false and then there is an error message in error
 */
bool mysql_quick_execute(const char *server, const char *username, const char *password, const char *database,
						 const char *query, string& result, int& resultRows, int& resultFields,
						 char *error, const int errorlen)
{
  MYSQL *mysql = 0;
  bool success = false;

  result.clear();

  // initialize mysql
  mysql = mysql_init((MYSQL*) 0);

  // connect to mysql server
  if(mysql_real_connect(mysql, server, username, password, database, 0, NULL, 0))
  {
	// connection is made

	// execute the query
	if (mysql_real_query(mysql, query, (unsigned int) strlen(query)) == 0)
	{
	  result = mysql_print_result(mysql, resultRows, resultFields);
	  success = true;
	}
	else
	{
	  // an error occured while executing query
	  strncpy (error, mysql_error(mysql), errorlen - 1);
	  error[errorlen - 1] = '\0';
	  success = false;
	}

	// close the mysql connection
	mysql_close(mysql);
  }
  else
  {
	// an error occured while trying to connect to the MySQL server
	strncpy (error, mysql_error(mysql), errorlen - 1);
	error[errorlen - 1] = '\0';
	success = false;
  }

  return success;
}



/**
 * connects to mysql database
 * @param server	   the server, for example "quookerserver"
 * @param username	 the username under which you want to login on the MySQL server
 * @param password	 the accompanying password of the user
 * @param database	 the database to which you want to connect
 * @return			 a pointer to the created mysql connection, and Null when the connection failed
 *					 if there was an error, the error can be requested with the function char* errorMsg = mysql_error(mysql)
 */
MYSQL* mysql_connect(const char server[], const char username[], const char password[], const char database[])
{
  MYSQL *mysql = 0;

  // initialize mysql
  mysql = mysql_init((MYSQL*) 0);

  // connect to mysql server
  mysql_real_connect(mysql, server, username, password, database, 0, NULL, 0);

  return mysql;
}



/**
 * print the results of an executed query
 * @param mysql		a pointer to the (open) mysql connection
 */
void mysql_print_result(MYSQL *mysql)
{
  MYSQL_RES *res;
  MYSQL_FIELD *field;
  MYSQL_ROW row;
  int t = 0;
  //int r = 0;
  int num_fields = 0;
  int num_rows = 0;

  // display the content of the query
  printf("\n");
  res = mysql_use_result(mysql);
  if (res != 0)
  {
	num_fields = mysql_num_fields(res);
	num_rows = mysql_num_rows(res);  // seems not to work
	printf("table with %i fields and %i records\n\n", num_fields, num_rows);
	while ((field = mysql_fetch_field(res)))
	{
	  printf("%s ", field->name);
	}
	printf("\n");

	while ((row = mysql_fetch_row(res)))
	{
	  for(t=0; t<num_fields; t++)
	  {
		if (row[t] != 0)
		{
		  printf("%s ", row[t]);
		}
		else
		{
		  printf(" ");
		}
	  }
	  printf("\n");
	}

	mysql_free_result(res);
  }
}


/**
 * returns a string with the results of an executed query
 * @param mysql	 a pointer to the (open) mysql connection
 *				  where a result is ready to be read
 * @return		  a string with the result
 */
string mysql_print_result(MYSQL *mysql, int& numRows, int& numFields)
{
  string result = "";

  try
  {

	MYSQL_RES *res;
	//MYSQL_FIELD *field;
	MYSQL_ROW row;
	int t = 0;

	// display the content of the query
	res = mysql_use_result(mysql);
	if (res != 0)
	{
	  numFields = mysql_num_fields(res);
	  //numRows = mysql_num_rows(res);  // seems not to work

	  numRows = 0;
	  while ((row = mysql_fetch_row(res)))
	  {
		for(t=0; t<numFields; t++)
		{
		  string row_t;
		  if (row[t] != 0)
		  {
			row_t = row[t];
		  }
		  else
		  {
			row_t = "";
		  }

		  // replace ''' with '\''
		  unsigned int i = 0;
		  const string match = "'";
		  while ((i = row_t.find(match)) != string::npos) {row_t.replace(i, match.length(), "\\'");}

		  result += "'" + string(row_t);
		  if (t < numFields - 1)
		  {
			result += "',";
		  }
		  else
		  {
			result += "'";
		  }
		}
		result += ";";
		numRows ++;
	  }

	  mysql_free_result(res);
	}
  }
  catch (...)
  {
	// todo: error handling
	//result = "Error: An unkown error occurred.";
  }

  return result;
}

example usage:

string server = "192.168.0.40";
string username = "myusername";
string password = "mypassword";
string database = "mydatabase";
sql = "SELECT * FROM mytable WHERE id=33;";
string sqlResult;
int resultFields = 0;
int resultRows = 0;

const int errorlen = 255;
char cError[errorlen] = "";

success = mysql_quick_execute(server.c_str(),
							  username.c_str(),
							  password.c_str(),
							  database.c_str(),
							  sql.c_str(),
							  sqlResult,
							  resultRows,
							  resultFields,
							  cError,
							  errorlen);

if (success)
{
  printf("MySQL query result: %i rows, %i fields.\n%s\n",
		  resultRows, resultFields, sqlResult.c_str());
}
else
{
  printf("MySQL query result: Error: %s\n", cError);
}

5 Replies - 46089 Views - Last Post: 03 March 2009 - 07:05 AM Rate Topic:

#1 goodmuyis

How to connect C++ to Mysql

Replies To: How to connect C++ to Mysql

#2 gabehabe

Re: How to connect C++ to Mysql

#3 joske

Re: How to connect C++ to Mysql

#4 goodmuyis

Re: How to connect C++ to Mysql

#5 joske

Re: How to connect C++ to Mysql

#6 goodmuyis

Re: How to connect C++ to Mysql

General Discussion

Programming Help

Web Development

Follow & Share

Give It A Try!

C++ Tutorials

Reference Sheets

C++ Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing