Another way to Import and Export MySQL database using C++

Introduction

First of all, I want to thank Mr. Imagiro who gave valuable comments to my last article. Their comments forced me to work on import and export database from/to MySQL server without using batch files. In this article I tried to develop functionality for import and export database which supports both "Unicode" as well as "MBCS" character set. This article shows you how to import or export database to/from MySQL server without connection to MySQL server even without using MySQL C API. 

Background

This is a C programming tutorial for the MySQL Server to import and export of database. It covers import and export of MySQL without using the C API provided by MySQL.

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. The development of MySQL begun in 1994 by a Swedish company MySQL AB. Sun Microsystems acquired MySQL AB in 2008. Sun was bought by Oracle in 2010. So today, Oracle corporation is the owner of the MySQL database. MySQL is developed in C/C++. Except of the C/C++, APIs exist for PHP, Python, Java, C#, Eiffel, Ruby, Tcl or Perl.

Prerequisites

1. Install Visual Studio 2013 (As code along with article is developed in Visual Studio 2013)
2. Install MySQL Server on your local machine 

Settings

Before going further details in writing database code we need to configure our Visual Studio for MySQL using following settings:

1. First of all copy "libmysql.dll" from location where MySQL is installed, on my PC it is situated at location, "C:\Program Files\MySQL\MySQL Server 5.6\lib". And then paste it at location where  your application's exe/dll will get created.
2. Now it is time to configure Visual Studio to use MySQL C APIs: For that access project properties of your project and update the following settings accordingly:
   1. C/C++ -> General -> Additional Include Directories - C:\Program Files\MySQL\MySQL Server 5.6\include
   2. Linker -> General -> Additional Library Directories - C:\Program Files\MySQL\MySQL Server 5.6\include
   3. Linker -> Input -> Additional Dependencies - "C:\Program Files\MySQL\MySQL Server 5.6\lib\libmysql.lib"

Note: Please do needful changes according to your PC settings where MySQL server is installed.

Using the code

I have created a seperate class (CDatabaseManipulation) which deals only with  import database to MySQL server and export database from MySQL server. The class also contains connection to MySQL which is only used in retrieving database list from MySQL server which ease our job while exporting database from MySQL server. For details you need to go through application code along with this article. So it is upto the user of this class whether he is interested in retrieving database list from MySQL server or not. 

To use this class in your application, you first need to update your Visual Studio settings as explained in "Settings" section. Then you need to include, "DatabaseManipulation.h" file where you want to use class's functionality. Then create object of the class and access functionality provided by "CDatabaseManipulation" class.

Import database to MySQL server: To import database user need to provide following information:

1. Server Host name
2. User name
3. Password
4. Database name to import
5. Database import file (with .sql extension) with its location

Here we can achieve our task of importing database using following 2 ways:
        1) Using CreateProcess API
        2) Using ShellExecute API

It is upto user to use functionality according to their need.

Note: For function called from ImportDatabase function please refer  to the class in the application code along with this article

Hide   Shrink    Copy Code

bool CDatabaseManipulation::ImportDatabase(/*[IN]*/const TCHAR *ptchHost,
                                           /*[IN]*/const TCHAR *ptchUserID, 
                                           /*[IN]*/const TCHAR *ptchPassword, 
                                           /*[IN]*/const TCHAR *ptchDatabaseNameToImport, 
                                           /*[IN]*/const TCHAR *ptchImportDatabaseFileWithPath)
/* ===================================================================================================
NAME OF FUNCTION:   CDatabaseManipulation::ImportDatabase
CREDIT:             Satish Jagtap
PURPOSE:            This function is used to import MySQL database.
Special Note:       Here we can achieve our task of importing database using following 2 ways:
                    1) Using CreateProcess API
                    2) Using ShellExecute API
                    User of this class can modify this function according to his/her need.
                    Means user can remove other section            
PARAMETERS:         [IN]  1) const TCHAR *ptchHost: Server host name
                    [IN]  2) const TCHAR *ptchUserID: User of MySQL server.
                    [IN]  3) const TCHAR *ptchPassword: Password  of MySQL server.
                    [IN]  4) const TCHAR *ptchDatabaseNameToImport: Database to import.
                    [IN]  5) constTCHAR *ptchImportDatabaseFileWithPath: Database file to import.
RETURN VALUE:       Returns true on success.
CALLS TO:           1) SearchForFilePath
CALLED FROM:        None
Added date:         31 March, 2015
Updated date:
=====================================================================================================*/
{
    bool bImportDBSuccess                = false;
    TCHAR strProgramFilePath[MAX_PATH]    = { 0 };

    //Retreive program file path
    if(!SHGetSpecialFolderPath(0, strProgramFilePath, CSIDL_PROGRAM_FILES, FALSE)) 
    {
        LPTSTR lpstrError = new TCHAR[1024];

        _stprintf_s(lpstrError, 1024, _T("Unable to retrieve program file path (%d)\n"), 
                                      GetLastError());
        m_objLogger.log(lpstrError);

        delete[] lpstrError;

        bImportDBSuccess = false;
    }
    else
    {
        TCHAR *strReturnSQLFilePath = new TCHAR[MAX_PATH];
        TCHAR *strImportCommand = new TCHAR[MAX_PATH];

        _tcscpy_s(strReturnSQLFilePath, MAX_PATH, _T(""));
        SearchForFilePath(strProgramFilePath, _T("mysql.exe"), strReturnSQLFilePath);

        _tcscat_s(strReturnSQLFilePath, MAX_PATH, _T("\\mysql.exe"));

        //Populate command to import database
        _tcscpy_s(strImportCommand, MAX_PATH, _T("-u"));
        _tcscat_s(strImportCommand, MAX_PATH, ptchUserID);
        _tcscat_s(strImportCommand, MAX_PATH, _T(" -p"));
        _tcscat_s(strImportCommand, MAX_PATH, ptchPassword);
        _tcscat_s(strImportCommand, MAX_PATH, _T(" -h "));
        _tcscat_s(strImportCommand, MAX_PATH, ptchHost);
        _tcscat_s(strImportCommand, MAX_PATH, _T(" -e "));
        _tcscat_s(strImportCommand, MAX_PATH, _T(" \"drop database if exists "));
        _tcscat_s(strImportCommand, MAX_PATH, ptchDatabaseNameToImport);
        _tcscat_s(strImportCommand, MAX_PATH, _T("; "));
        _tcscat_s(strImportCommand, MAX_PATH, _T(" create database "));
        _tcscat_s(strImportCommand, MAX_PATH, ptchDatabaseNameToImport);
        _tcscat_s(strImportCommand, MAX_PATH, _T("; "));
        _tcscat_s(strImportCommand, MAX_PATH, _T("use "));
        _tcscat_s(strImportCommand, MAX_PATH, ptchDatabaseNameToImport);
        _tcscat_s(strImportCommand, MAX_PATH, _T(";\\. "));
        _tcscat_s(strImportCommand, MAX_PATH, ptchImportDatabaseFileWithPath);
        _tcscat_s(strImportCommand, MAX_PATH, _T("\""));

        /***************************************************************************************
        Here we can achieve our task of importing database using following 2 ways:
        1) Using CreateProcess API
        2) Using ShellExecute API
        Note: I have just commented "Achieve import database process using CreateProcess API"
              section
        ***************************************************************************************/
    #pragma region "Achieve import database process using CreateProcess API"
        //LPTSTR strImportDatabaseCommandLine = new TCHAR[1024];

        //_stprintf_s(strImportDatabaseCommandLine, 1024, _T("%s %s"), strReturnSQLFilePath, 
        //                                                             strImportCommand);

        //STARTUPINFO si = { 0 }; // alternative way to zero array
        //si.cb = sizeof(si);
        //PROCESS_INFORMATION pi = { 0 };

        //if (!CreateProcess(NULL,
        //                    strImportDatabaseCommandLine,
        //                    NULL,
        //                    NULL,
        //                    FALSE,
        //                    0,
        //                    NULL,
        //                    NULL,
        //                    &si,
        //                    &pi)
        //  )
        //{
        //    LPTSTR lpstrError = new TCHAR[1024];

        //    _stprintf_s(lpstrError, 1024, _T("CreateProcess failed (%d)\n"), GetLastError());
        //    m_objLogger.log(lpstrError);

        //    delete[] lpstrError;

        //    bImportDBSuccess = false;
        //}
        //else
        //{
        //    bImportDBSuccess = true;
        //}

        //WaitForSingleObject(pi.hProcess, INFINITE);
        //CloseHandle(pi.hProcess);
        //CloseHandle(pi.hThread);

        //delete [] strImportDatabaseCommandLine;
        //strImportDatabaseCommandLine = NULL;
    #pragma endregion

    #pragma region "Achieve import database process using ShellExecute API"
        UINT nReturnErrorCode = (UINT)ShellExecute(NULL, 
                                                    _T("open"),
                                                    strReturnSQLFilePath, //mysql.exe file path
                                                    strImportCommand, //command to import database 
                                                                      //into MySQL server
                                                    NULL, 
                                                    SW_HIDE); //hides command window

        if(nReturnErrorCode <= 32)
        {
            LPTSTR lpstrError = new TCHAR[1024];

            _stprintf_s(lpstrError, 1024, _T("ShellExecute failed (%d)\n"), GetLastError());
            m_objLogger.log(lpstrError);

            delete[] lpstrError;

            bImportDBSuccess = false;
        }
        else
        {
            bImportDBSuccess = true;
        }
    #pragma endregion

        delete [] strReturnSQLFilePath;
        strReturnSQLFilePath = NULL;
        delete [] strImportCommand;
        strImportCommand = NULL;
    }

    return bImportDBSuccess;
}

Retrieve databases list from currently running MySqlServer: To export database from MySQL server we need to know databases currently available in currently running MySQL server. This section retrieves database list from MySQL server. Here I have created two versions of this function one used with Unicode character set and another with MBCS.

Hide   Shrink    Copy Code

#pragma region Retrieve databases list from currently running MySqlServer

#ifdef UNICODE

void CDatabaseManipulation::RetrieveDatabasesListInMySqlServer(vector<wchar_t*> &vecMySqlDatabasesList)
/* ===================================================================================================
NAME OF FUNCTION:   CDatabaseManipulation::RetrieveDatabasesListInMySqlServer
CREDIT:             Satish Jagtap
PURPOSE:            This function is used to receives databases list in currently running MySql 
                    server instance
PARAMETERS:         1) vector<wchar_t*> &vecMySqlDatabasesList - Receives databases list in currently 
                                                                 running MySql server instance
RETURN VALUE:       None
CALLS TO:           None
CALLED FROM:        None
Added date:         27 March, 2015
Updated date:
====================================================================================================*/
{
    char strTemp[MAX_PATH]        = { 0 };
    size_t nTempLen                = 0;
    
    wcstombs_s(&nTempLen, strTemp, _T("%"), wcslen(_T("%")) + 1);
    MYSQL_RES *myqlResult = mysql_list_dbs(mysqlConnection, strTemp /* fetch all */);
    
    if (!myqlResult) 
    {
        LPTSTR lptstrError = new TCHAR[1024];

        _stprintf_s(lptstrError, 1024, _T("Couldn't get db list: %s"), GetError());
        m_objLogger.log(lptstrError);

        delete [] lptstrError;
    }
    else 
    {
        MYSQL_ROW mysqlRow;
        
        while(mysqlRow = mysql_fetch_row(myqlResult)) 
        {
            size_t nLen                    = 0;
            int nLenInfoSchema            = strlen(mysqlRow[0]) + 1;
            wchar_t wcstrRow[MAX_PATH]    = { 0 };

            mbstowcs_s(&nLen, wcstrRow, mysqlRow[0], nLenInfoSchema);

            if((_tcscmp(wcstrRow, _T("information_schema"))) && 
               (_tcscmp(wcstrRow, _T("performance_schema"))) && 
               (_tcscmp(wcstrRow, _T("mysql"))))
            {
                size_t nTempLen                = 0;
                wchar_t strRow[MAX_PATH]    = { 0 };

                strMySQLResultRow = new wchar_t[MAX_PATH];

                mbstowcs_s(&nTempLen, strRow, mysqlRow[0], strlen(mysqlRow[0]) + 1);
                _tcscpy_s(strMySQLResultRow, _tcslen(strRow) + 1, strRow);

                vecMySqlDatabasesList.push_back(strMySQLResultRow);
            }
        }
    }
}
#else
void CDatabaseManipulation::RetrieveDatabasesListInMySqlServer(vector<CHAR*> &vecMySqlDatabasesList)
/* ===================================================================================================
NAME OF FUNCTION:   CDatabaseManipulation::RetrieveDatabasesListInMySqlServer
CREDIT:             Satish Jagtap
PURPOSE:            This function is used to receives databases list in currently running MySql 
                    server instance
PARAMETERS:         1) vector<CHAR*> &vecMySqlDatabasesList - Receives databases list in currently 
                                                              running MySql server instance
RETURN VALUE:       None
CALLS TO:           None
CALLED FROM:        None
Added date:         21 March, 2015
Updated date:
=====================================================================================================*/
{
    MYSQL_RES *myqlResult = mysql_list_dbs(mysqlConnection, _T("%") /* fetch all */);

    if (!myqlResult) 
    {
        LPTSTR lptstrError = new TCHAR[1024];

        _stprintf_s(lptstrError, 1024, _T("Couldn't get db list: %s"), GetError());
        m_objLogger.log(lptstrError);

        delete [] lptstrError;
    }
    else 
    {
        MYSQL_ROW mysqlRow;

        while(mysqlRow = mysql_fetch_row(myqlResult)) 
        {
            if((_tcscmp(mysqlRow[0], _T("information_schema"))) && (_tcscmp(mysqlRow[0], _T("performance_schema"))) && (_tcscmp(mysqlRow[0], _T("mysql"))))
            {
                vecMySqlDatabasesList.push_back(mysqlRow[0]);
            }
        }
    }
}
#endif
#pragma endregion

Export database to MySQL server: To export database user need to provide following information:

1. User name
2. Password
3. Database name to export
4. Database export file (with .sql extension) with its location

Here we can achieve our task of exporting database using following 2 ways:
        1) Using CreateProcess API
        2) Using ShellExecute API

It is upto user to use functionality according to their need.

Note: For function called from ExportDatabase function please refer  to the class in the application code along with this article.

Hide   Shrink    Copy Code

bool CDatabaseManipulation::ExportDatabase(/*[IN]*/const TCHAR *ptchUserID, 
                                           /*[IN]*/const TCHAR *ptchPassword, 
                                           /*[IN]*/const TCHAR *ptchDatabaseNameToExport, 
                                           /*[IN]*/const TCHAR *ptchExportDatabaseFileWithPath)
/* ===================================================================================================
NAME OF FUNCTION:   CDatabaseManipulation::ExportDatabase
CREDIT:             Satish Jagtap
PURPOSE:            This function is used to export MySQL database.
Special Note:       Here we can achieve our task of exporting database using following 2 ways:
                    1) Using CreateProcess API
                    2) Using ShellExecute API
                    User of this class can modify this function according to his/her need.
                    Means user can remove other section    
PARAMETERS:         [IN]  1) const TCHAR *ptchUserID: User of MySQL server.
                    [IN]  2) const TCHAR *ptchPassword: Password  of MySQL server.
                    [IN]  3) const TCHAR *ptchDatabaseNameToExport: Database to export.
                    [IN]  4) constTCHAR *ptchExportDatabaseFileWithPath: Database file to export.
RETURN VALUE:       Returns true on success.
CALLS TO:           1) SearchForFilePath
CALLED FROM:        None
Added date:         31 March, 2015
Updated date:
====================================================================================================*/
{
    bool bExportDBSuccess                = false;
    TCHAR strProgramFilePath[MAX_PATH]    = { 0 };

    //Retreive program file path
    if(!SHGetSpecialFolderPath(0, strProgramFilePath, CSIDL_PROGRAM_FILES, FALSE)) 
    {
        LPTSTR lpstrError = new TCHAR[1024];

        _stprintf_s(lpstrError, 1024, _T("CreateProcess failed (%d)\n"), GetLastError());
        m_objLogger.log(lpstrError);

        delete[] lpstrError;

        bExportDBSuccess = false;
    }
    else
    {
        TCHAR *strReturnSQLFilePath = new TCHAR[MAX_PATH];
        TCHAR *strExportCommand        = new TCHAR[MAX_PATH];

        _tcscpy_s(strReturnSQLFilePath, MAX_PATH, _T(""));
        SearchForFilePath(strProgramFilePath, _T("mysqldump.exe"), strReturnSQLFilePath);
        _tcscat_s(strReturnSQLFilePath, MAX_PATH, _T("\\mysqldump.exe"));

        //Populate command to export database
        _tcscpy_s(strExportCommand, MAX_PATH, _T(" --user="));
        _tcscat_s(strExportCommand, MAX_PATH, ptchUserID);
        _tcscat_s(strExportCommand, MAX_PATH, _T(" --password="));
        _tcscat_s(strExportCommand, MAX_PATH, ptchPassword);
        _tcscat_s(strExportCommand, MAX_PATH, _T(" "));
        _tcscat_s(strExportCommand, MAX_PATH, ptchDatabaseNameToExport);
        _tcscat_s(strExportCommand, MAX_PATH, _T(" -r "));
        _tcscat_s(strExportCommand, MAX_PATH, ptchExportDatabaseFileWithPath);

        /***************************************************************************************
        Here we can achieve our task of exporting database using following 2 ways:
        1) Using CreateProcess API
        2) Using ShellExecute API
        Note: I have just commented "Achieve export database process using CreateProcess API"
              section
        ***************************************************************************************/
    #pragma region "Achieve export database process using CreateProcess API"
        //LPTSTR strExportDatabaseCommandLine = new TCHAR[1024];

        //_stprintf_s(strExportDatabaseCommandLine, 1024, _T("%s %s"), strReturnSQLFilePath, 
        //                                                             strExportCommand);

        //STARTUPINFO si = { 0 }; // alternative way to zero array
        //si.cb = sizeof(si);
        //PROCESS_INFORMATION pi = { 0 };

        //if (!CreateProcess(NULL,
        //                    strExportDatabaseCommandLine,
        //                    NULL,
        //                    NULL,
        //                    FALSE,
        //                    0,
        //                    NULL,
        //                    NULL,
        //                    &si,
        //                    &pi)
        //  )
        //{
        //    LPTSTR lpstrError = new TCHAR[1024];

        //    _stprintf_s(lpstrError, 1024, _T("CreateProcess failed (%d)\n"), GetLastError());
        //    m_objLogger.log(lpstrError);

        //    delete[] lpstrError;

        //    bExportDBSuccess = false;
        //}
        //else
        //{
        //    bExportDBSuccess = true;
        //}

        //WaitForSingleObject(pi.hProcess, INFINITE);
        //CloseHandle(pi.hProcess);
        //CloseHandle(pi.hThread);

        //delete [] strExportDatabaseCommandLine;
        //strExportDatabaseCommandLine = NULL;
    #pragma endregion

    #pragma region "Achieve export database process using ShellExecute API"
        UINT nReturnErrorCode = (UINT)ShellExecute(NULL, 
                                                    _T("open"), 
                                                    strReturnSQLFilePath, //mysqldump.exe file path
                                                    strExportCommand, //command to import 
                                                                      //database into MySQL server
                                                    _T(""), 
                                                    SW_HIDE); //hides command window

        if(nReturnErrorCode <= 32)
        {
            LPTSTR lpstrError = new TCHAR[1024];

            _stprintf_s(lpstrError, 1024, _T("ShellExecute failed (%d)\n"), GetLastError());
            m_objLogger.log(lpstrError);

            delete[] lpstrError;

            bExportDBSuccess = false;
        }
        else
        {
            bExportDBSuccess = true;
        }
    #pragma endregion

        delete [] strReturnSQLFilePath;
        strReturnSQLFilePath = NULL;
        delete [] strExportCommand;
        strExportCommand = NULL;
    }

    return bExportDBSuccess;
}

Application code along with this article is whole source code developed using MFC dialog based application. Application code in this article is much more different from my last article. 
Steps to use application:

1. First enter required details in " Enter MySQL server details" section.
2. Import database using "Import Database" section.
3. To export database first retrieve database list using "Retrieve Database List" button. Application comes with editable combo box ("Select Database") where user manually enter database name which he/she wants to export by skipping "Retrieve Database List" functionality. Then user can able to export database by providing further details in "Export Database" section.

Points of Interest

As in my last article "Import and Export MySQL databases using C++" I have created batch files to import and export database. But in this article I have skipped that part and provided different functionality to import and export database to/from MySQL server. Also I have created this source code to work with Unicode character set as well as multibyte character set.

Remarks

1. Remember to change your Project Settings to point to the MySQL include files and the MySQL libs.
2. Copy "libmysql.dll" from location where MySQL is installed, on my PC it is situated at location, "C:\Program Files\MySQL\MySQL Server 5.6\lib". And then paste it at location where  your application's exe/dll will get created.

Import and Export MySQL databases using C++

Introduction

This article shows you how to connect to a MySQL Server. It will also show you how you can import database to MySQL server and how you can export database from MySQL server using MySQL C API. 

Background

This is a C programming tutorial for the MySQL Server and import and export of database. It covers import and export of MySQL with the C API.

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. The development of MySQL begun in 1994 by a Swedish company MySQL AB. Sun Microsystems acquired MySQL AB in 2008. Sun was bought by Oracle in 2010. So today, Oracle corporation is the owner of the MySQL database. MySQL is developed in C/C++. Except of the C/C++, APIs exist for PHP, Python, Java, C#, Eiffel, Ruby, Tcl or Perl.

Prerequisites

1. Install Visual Studio 2013 (As code along with article is developed in Visual Studio 2013)
2. Install MySQL Server on your local machine 

Settings

Before going further details in writing database code using MySQL C APIs we need to configure our Visual Studio for MySQL using following settings:

1. First of all copy "libmysql.dll" from location where MySQL is installed, on my PC it is situated at location, "C:\Program Files\MySQL\MySQL Server 5.6\lib". And then paste it at location where  your application's exe/dll will get created.
2. Now it is time to configure Visual Studio to use MySQL C APIs: For that access project properties of your project and update the following settings accordingly:
   1. C/C++ -> General -> Additional Include Directories - C:\Program Files\MySQL\MySQL Server 5.6\include
   2. Linker -> General -> Additional Library Directories - C:\Program Files\MySQL\MySQL Server 5.6\include
   3. Linker -> Input -> Additional Dependencies - "C:\Program Files\MySQL\MySQL Server 5.6\lib\libmysql.lib"

Note: Please do needful changes according to your PC settings where MySQL server is installed.

Using the code

I have created a seperate class (CDatabaseManipulation) which deals with MySQL server connection, import database to MySQL server and export database from MySQL server. The class also contains some other functionalities to manipulate MySQL databases.

Connecting to MySQL server: To connect to the MySQL server user need to supply Server host name, user name and password. Database need to pass as blank. 

Hide   Shrink    Copy Code

bool CDatabaseManipulation::Connect(/*[IN]*/const TCHAR *ptchHost,
                                    /*[IN]*/const TCHAR *ptchUserID,
                                    /*[IN]*/const TCHAR *ptchDatabase,
                                    /*[IN]*/const TCHAR *ptchPassword)
/* =====================================================================================================
NAME OF FUNCTION:    CDatabaseManipulation::Connect
CREDIT:              Satish Jagtap
PURPOSE:             This function is used to connect MYSQL database.
PARAMETERS:          [IN] 1) TCHAR *ptchHost: Parameter passed as ptchHost may be either a host name or
                             an IP address. If host is NULL or the string "localhost", a connection to
                             the local host is assumed.
                             For Windows, the client connects using a shared-memory connection,if the
                             server has shared-memory connections enabled. Otherwise, TCP/IP is used.
                     [IN] 2) TCHAR *ptchUserID: Parameter passed as ptchUserID contains the user's
                             MySQL login ID.
                     [IN] 3) TCHAR *ptchDatabase: Parameter passed as ptchDatabase is the database
                             name. If ptchDatabase is not NULL, the connection sets the default
                             database to this value.
                     [IN] 4) TCHAR *ptchPassword: Parameter passed as ptchPassword contains the 
                             password for user.
RETURN VALUE:        None
CALLS TO:            None
CALLED FROM:         None
Added date:          12 March, 2015
Updated date:         
======================================================================================================*/
{
    bool bConnectionSuccess = false;

    if(IsConnected()) 
    {
        m_objLogger.log(_T("Connection has already been established."));
        bConnectionSuccess = false;
    }

    //Allocates or initializes a MYSQL object suitable for mysql_real_connect()
    //returns an initialized MYSQL* handle. 
    //Returns NULL if there was insufficient memory to allocate a new object.
    if(mysql_init(&mysqlInitial) == NULL)
    {
        m_objLogger.log(_T("Failed to initiate MySQL connection"));
        bConnectionSuccess = false;
    }
    //Establishes a connection to a database server. 
    //Returns a MYSQL * handle or NULL if an error occurred.
    mysqlConnection = mysql_real_connect(&mysqlInitial, 
                                          (const char*)ptchHost, 
                                          (const char*)ptchUserID, 
                                          (const char*)ptchPassword, 
                                          (const char*)ptchDatabase, 0, 0, 0);
 
    // Check if connection succeeded.
    if( mysqlConnection == NULL )
    {
        LPTSTR lptstrError = new TCHAR[1024];

        _stprintf_s(lptstrError, 1024, _T("%s %s"), 
                    _T("Couldn't connect to MySQL database server! Error: "), 
                    mysql_error(mysqlConnection));
        m_objLogger.log(lptstrError);

        delete [] lptstrError;

        bConnectionSuccess = false;
    }
    else
    {
        m_objLogger.log(_T("Connect success."), _T("INFO")) ;
        bConnectionSuccess = true;
    }

    if(!IsConnected()) 
    {
        m_objLogger.log(GetError());
        bConnectionSuccess = false;
    }

    return bConnectionSuccess;
}

bool CDatabaseManipulation::CloseConnection(void) 
/* =====================================================================================================
NAME OF FUNCTION:    CDatabaseManipulation::CloseConnection
CREDIT:              Satish Jagtap
PURPOSE:             This function is used to close database connection if exist.
                     It should be non-NULL for MySQL.
PARAMETERS:          None
RETURN VALUE:        Returns connection status of database.
CALLS TO:            1) IsConnected()
CALLED FROM:         2) Destructor i.e. from ~CDatabaseManipulation()
Added date:          12 March, 2015
Updated date:         
======================================================================================================*/
{
    bool bSuccessCloseConnection = false;

    if(IsConnected()) 
    {
        mysql_close(mysqlConnection);
        mysqlConnection = (MYSQL *)NULL;

        bSuccessCloseConnection = true;
    }
    else
    {
        bSuccessCloseConnection = false;
    }

    return bSuccessCloseConnection;
}

Import database to MySQL server: To import database user need to connect to MySQL server. Along with other details user need to pass database name and import file (with .sql extension) location to ImportDatabase function.

Note: For function called from ImportDatabase function please refer  to the class in the application code along with this article

Hide   Shrink    Copy Code

bool CDatabaseManipulation::ImportDatabase(/*[IN]*/const TCHAR *ptchHost, 
                                           /*[IN]*/const TCHAR *ptchUserID, 
                                           /*[IN]*/const TCHAR *ptchPassword, 
                                           /*[IN]*/const TCHAR *ptchDatabaseNameToImport, 
                                           /*[IN]*/const TCHAR *ptchImportDatabaseFile)
/* =====================================================================================================
NAME OF FUNCTION:   CDatabaseManipulation::ImportDatabase
CREDIT:             Satish Jagtap
PURPOSE:            This function is used to import database using import file into MySql database.
                    This function create vector of strings containing commands to import database.
                    This function then creates batch file.
                    This function then writes vector of commands into batch file.
                    This function then execute batch file using cmd.exe.
                    At the end after import of database, function removes batch file.
PARAMETERS:         [IN] 1) TCHAR *ptchHost: Host or server name to connect and import database.
                    [IN] 2) TCHAR *ptchUserID: User name to connect and import database.
                    [IN] 3) TCHAR *ptchPassword: Password to connect and import database.
                    [IN] 4) TCHAR *ptchDatabaseNameToImport: MySql database name to import.
                    [IN] 5) TCHAR *ptchImportDatabaseFile: Database file to import into MySql database.
RETURN VALUE:       None
CALLS TO:           1) WriteVectorInFile
                    2) GetExecutablePath
CALLED FROM:        None
Added date:         17 March, 2015
Updated date:
======================================================================================================*/
{
    bool bImportDBSuccess = false;

    //Database connection
    //Connect(ptchHost, ptchUserID, _T(""), ptchPassword);
    if(!IsConnected()) 
    {
        m_objLogger.log(_T("MySql server is not connected."));
        bImportDBSuccess = false;
    }
    else
    {
        TCHAR *strCreateDatabaseCommand = new TCHAR[MAX_PATH];
        _tcscpy_s(strCreateDatabaseCommand, MAX_PATH, _T("CREATE DATABASE "));
        _tcscat_s(strCreateDatabaseCommand, MAX_PATH, ptchDatabaseNameToImport);
        mysql_query(mysqlConnection, (const char*)strCreateDatabaseCommand);

        //Creating batch file data to execute
        TCHAR strProgramFilePath[MAX_PATH];
        SHGetSpecialFolderPath(0, strProgramFilePath, CSIDL_PROGRAM_FILES, FALSE);

        TCHAR * strReturnSQLFilePath = new TCHAR[MAX_PATH];
        _tcscpy_s(strReturnSQLFilePath, MAX_PATH, _T(""));

        SearchForFilePath(strProgramFilePath, _T("mysql.exe"), strReturnSQLFilePath);

        if(!_tcscmp(strReturnSQLFilePath, _T("")))
        {
            return false;
        }

        //populate vector with import database command to write into batch file for import database
        vector<TCHAR *> vecToWriteInFile;
        vecToWriteInFile.push_back(_T("@echooff"));
        vecToWriteInFile.push_back(_T("set MainDir = %CD%"));
        vecToWriteInFile.push_back(_T("CD %MainDir%"));

        TCHAR strSQLDrive[3];
        strSQLDrive[0] = strReturnSQLFilePath[0];
        strSQLDrive[1] = strReturnSQLFilePath[1];
        strSQLDrive[2] = _T('\0');
        vecToWriteInFile.push_back(strSQLDrive);

        TCHAR * strTempPath = new TCHAR[MAX_PATH];
        _tcscpy_s(strTempPath, MAX_PATH, _T("CD "));
        _tcscat_s(strTempPath, MAX_PATH, strReturnSQLFilePath);
        vecToWriteInFile.push_back(strTempPath);

        TCHAR strImportCommand[1024];
        _tcscpy_s(strImportCommand, MAX_PATH, _T("mysql --user="));
        _tcscat_s(strImportCommand, ptchUserID);
        _tcscat_s(strImportCommand, _T(" --password="));
        _tcscat_s(strImportCommand, ptchPassword);
        _tcscat_s(strImportCommand, _T(" -D"));
        _tcscat_s(strImportCommand, ptchDatabaseNameToImport);
        _tcscat_s(strImportCommand, _T(" < \""));
        _tcscat_s(strImportCommand, ptchImportDatabaseFile);
        _tcscat_s(strImportCommand, _T("\""));
        vecToWriteInFile.push_back(strImportCommand);
        vecToWriteInFile.push_back(_T("exit"));

        //Create temporary import batch file
        CExecutablePathInfo objExecutablePathInfo;
        LPTSTR lptstrExecutableDirectory = new TCHAR[1024];
        objExecutablePathInfo.GetExecutableDirectory(lptstrExecutableDirectory, 1024);
        _tcscat_s(lptstrExecutableDirectory, MAX_PATH, _T("\\TempDatabaseManipulationImport.bat"));

        //Write into temporary created import batch file
        WriteVectorInFile(vecToWriteInFile, lptstrExecutableDirectory);

        vecToWriteInFile.clear(); //clears the vector
        vecToWriteInFile.shrink_to_fit(); //It requests the removal of unused capacity of vector

        TCHAR strSystemDirPath[MAX_PATH] = _T("");
        GetSystemDirectory(strSystemDirPath, sizeof(strSystemDirPath) / sizeof(_TCHAR));

        // path to cmd.exe, path to batch file, plus some space for quotes, spaces, etc.
        TCHAR strCommandLine[2 * MAX_PATH + 16] = _T("");

        _sntprintf_s(strCommandLine, sizeof(strCommandLine) / sizeof(_TCHAR), 
                     _T("\"%s\\cmd.exe\" /C \"%s\""), strSystemDirPath, lptstrExecutableDirectory);

        delete[] strTempPath;
        strTempPath = NULL;
        delete[] strReturnSQLFilePath;
        strReturnSQLFilePath = NULL;

        STARTUPINFO si = { 0 }; // alternative way to zero array
        si.cb = sizeof(si);
        PROCESS_INFORMATION pi = { 0 };

        if (!CreateProcess(NULL,
                            strCommandLine,
                            NULL,
                            NULL,
                            FALSE,
                            0,
                            NULL,
                            NULL,
                            &si,
                            &pi)
            )
        {
            LPTSTR lpstrError = new TCHAR[1024];

            _stprintf_s(lpstrError, 1024, _T("CreateProcess failed (%d)\n"), GetLastError());
                        m_objLogger.log(lpstrError);

            delete[] lpstrError;

            bImportDBSuccess = false;
        }
        else
        {
            bImportDBSuccess = true;
        }

        WaitForSingleObject(pi.hProcess, INFINITE);
        CloseHandle(pi.hProcess);
        CloseHandle(pi.hThread);

        //Remove batch file
        remove((const char*)lptstrExecutableDirectory);

        delete[] lptstrExecutableDirectory;
        lptstrExecutableDirectory = NULL;
    }

    return bImportDBSuccess;
}

Retrieve databases list from currently running MySqlServer: To export database from MySQL server we need to know databases currently available in currently running MySQL server. This section retrieves database list from MySQL server.

Hide   Shrink    Copy Code

void CDatabaseManipulation::RetrieveDatabasesListInMySqlServer(vector<TCHAR*> &vecMySqlDatabasesList)
/* =====================================================================================================
NAME OF FUNCTION:   CDatabaseManipulation::RetrieveDatabasesListInMySqlServer
CREDIT:             Satish Jagtap
PURPOSE:            This function is used to receives databases list in currently running MySql server 
                    instance
PARAMETERS:         1) vector<TCHAR*> &vecMySqlDatabasesList - Receives databases list in currently 
                                                               running MySql server instance
RETURN VALUE:       None
CALLS TO:           None
CALLED FROM:        None
Added date:         12 March, 2015
Updated date:
======================================================================================================*/
{
    MYSQL_RES *myqlResult = mysql_list_dbs(mysqlConnection, _T("%") /* fetch all */);

    if (!myqlResult) 
    {
        LPTSTR lptstrError = new TCHAR[1024];

        _stprintf_s(lptstrError, 1024, _T("Couldn't get db list: %s"), GetError());
        m_objLogger.log(lptstrError);

        delete [] lptstrError;
    }
    else 
    {
        MYSQL_ROW mysqlRow;

        while(mysqlRow = mysql_fetch_row(myqlResult)) 
        {
            if((_tcscmp(mysqlRow[0], "information_schema")) && 
               (_tcscmp(mysqlRow[0], "performance_schema")) && 
               (_tcscmp(mysqlRow[0], "mysql")))
            {
                vecMySqlDatabasesList.push_back(mysqlRow[0]);
            }
        }
    }
}

Export database from MySQL server: To export database user need to connect to MySQL server. Along with other details user need to pass database name and export file (with .sql extension) location to ExportDatabase function.

Note: For function called from ExportDatabase function please refer  to the class in the application code along with this article

Hide   Shrink    Copy Code

bool CDatabaseManipulation::ExportDatabase(/*[IN]*/const TCHAR *ptchHost,
                                           /*[IN]*/const TCHAR *ptchUserID, 
                                           /*[IN]*/const TCHAR *ptchPassword, 
                                           /*[IN]*/const TCHAR *ptchDatabaseNameToExport, 
                                           /*[IN]*/const TCHAR *ptchExportDatabaseFileWithPath)
/* ======================================================================================================
NAME OF FUNCTION:   CDatabaseManipulation::ExportDatabase
CREDIT:             Satish Jagtap
PURPOSE:            This function is used to export database to the specified path with specified file 
                    name.
PARAMETERS:         [IN] 1) TCHAR *ptchHost: Host or server name to connect and import database.
                    [IN] 2) TCHAR *ptchUserID: User name to connect and import database.
                    [IN] 3) TCHAR *ptchPassword: Password to connect and import database.
                    [IN] 4) TCHAR *ptchDatabaseNameToExport: MySql database name to export.
                    [IN] 5) TCHAR *ptchExportDatabaseFileWithPath: Database filename with path to 
                            export MySql database.
RETURN VALUE:       Returns true on success.
CALLS TO:           1) WriteVectorInFile
                    2) GetExecutablePath
CALLED FROM:        None
Added date:         17 March, 2015
Updated date:
=======================================================================================================*/
{
    bool bExportDBSuccess = false;

    //Database connection
    //Connect(ptchHost, ptchUserID, ptchDatabaseNameToExport, ptchPassword);
    if(!IsConnected()) 
    {
        m_objLogger.log(_T("MySql server is not connected."));
        bExportDBSuccess = false;
    }
    else
    {
        //Creating batch file data to execute
        TCHAR strProgramFilePath[MAX_PATH];
        SHGetSpecialFolderPath(0, strProgramFilePath, CSIDL_PROGRAM_FILES, FALSE);

        TCHAR * strReturnSQLFilePath = new TCHAR[MAX_PATH];
        _tcscpy_s(strReturnSQLFilePath, MAX_PATH, _T(""));

        SearchForFilePath(strProgramFilePath, _T("mysqldump.exe"), strReturnSQLFilePath);

        if(!_tcscmp(strReturnSQLFilePath, _T("")))
        {
            return false;
        }

        //populate vector with export database command to write into batch file for export database
        vector<TCHAR *> vecToWriteInFile;
        vecToWriteInFile.push_back(_T("@echooff"));
        vecToWriteInFile.push_back(_T("set MainDir = %CD%"));
        vecToWriteInFile.push_back(_T("CD %MainDir%"));

        TCHAR strSQLDrive[3];
        strSQLDrive[0] = strReturnSQLFilePath[0];
        strSQLDrive[1] = strReturnSQLFilePath[1];
        strSQLDrive[2] = _T('\0');
        vecToWriteInFile.push_back(strSQLDrive);

        TCHAR * strTempPath = new TCHAR[MAX_PATH];
        _tcscpy_s(strTempPath, MAX_PATH, _T("CD "));
        _tcscat_s(strTempPath, MAX_PATH, strReturnSQLFilePath);
        vecToWriteInFile.push_back(strTempPath);

        TCHAR strImportCommand[1024];
        _tcscpy_s(strImportCommand, MAX_PATH, _T("mysqldump --user="));
        _tcscat_s(strImportCommand, ptchUserID);
        _tcscat_s(strImportCommand, _T(" --password="));
        _tcscat_s(strImportCommand, ptchPassword);
        _tcscat_s(strImportCommand, _T(" --result-file="));
        _tcscat_s(strImportCommand, _T("\""));
        _tcscat_s(strImportCommand, ptchExportDatabaseFileWithPath);
        _tcscat_s(strImportCommand, _T("\""));
        _tcscat_s(strImportCommand, _T(" "));
        _tcscat_s(strImportCommand, ptchDatabaseNameToExport);
        vecToWriteInFile.push_back(strImportCommand);
        vecToWriteInFile.push_back(_T("exit"));

        //Create temporary import batch file
        CExecutablePathInfo objExecutablePathInfo;
        LPTSTR lptstrExecutableDirectory = new TCHAR[1024];
        objExecutablePathInfo.GetExecutableDirectory(lptstrExecutableDirectory, 1024);
        _tcscat_s(lptstrExecutableDirectory, MAX_PATH, _T("\\TempDatabaseManipulationExport.bat"));

        //Write into temporary created import batch file
        WriteVectorInFile(vecToWriteInFile, lptstrExecutableDirectory);
    
        vecToWriteInFile.clear(); //clears the vector
        vecToWriteInFile.shrink_to_fit(); //It requests the removal of unused capacity of vector

        TCHAR strSystemDirPath[MAX_PATH] = _T("");
        GetSystemDirectory(strSystemDirPath, sizeof(strSystemDirPath) / sizeof(_TCHAR));

        // path to cmd.exe, path to batch file, plus some space for quotes, spaces, etc.
        TCHAR strCommandLine[2 * MAX_PATH + 16] = _T("");

        _sntprintf_s(strCommandLine, sizeof(strCommandLine) / sizeof(_TCHAR), 
                     _T("\"%s\\cmd.exe\" /C \"%s\""), strSystemDirPath, lptstrExecutableDirectory);

        delete [] strTempPath;
        strTempPath = NULL;
        delete [] strReturnSQLFilePath;
        strReturnSQLFilePath = NULL;

        STARTUPINFO si = { 0 }; // alternative way to zero array
        si.cb = sizeof(si);
        PROCESS_INFORMATION pi = { 0 };

        if (!CreateProcess(NULL,
                            strCommandLine,
                            NULL,
                            NULL,
                            FALSE,
                            0,
                            NULL,
                            NULL,
                            &si,
                            &pi)
            )
        {
            LPTSTR lpstrError = new TCHAR[1024];

            _stprintf_s(lpstrError, 1024, _T("CreateProcess failed (%d)\n"), GetLastError());
            m_objLogger.log(lpstrError);

            delete [] lpstrError;

            bExportDBSuccess = false;
        }
        else
        {
            bExportDBSuccess = true;
        }

        WaitForSingleObject(pi.hProcess, INFINITE);
        CloseHandle(pi.hProcess);
        CloseHandle(pi.hThread);

        //Remove batch file
        remove((const char*)lptstrExecutableDirectory);

        delete [] lptstrExecutableDirectory;
        lptstrExecutableDirectory = NULL;
    }

    return bExportDBSuccess;
}

Application code along with this article is whole source code developed using MFC dialog based application. Here is the application window will look like:

Steps to use application:

1. First connect to MySQL server using "Connect to MySql Server Instance" section.
2. Import database using "Import Database" section.
3. Export database using "Export Database" section.

Points of Interest

To import and export database, I have created respective(for import/export database) batch files and run those batch files on respective events. I have deleted those batch files after completion of event (import/export).

Remarks

1. Remember to change your Project Settings to point to the MySQL include files and the MySQL libs.
2. Copy "libmysql.dll" from location where MySQL is installed, on my PC it is situated at location, "C:\Program Files\MySQL\MySQL Server 5.6\lib". And then paste it at location where  your application's exe/dll will get created.