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
- Install Visual Studio 2013 (As code along with article is developed in Visual Studio 2013)
- 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:
- 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.
- 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:
- C/C++ -> General -> Additional Include Directories - C:\Program Files\MySQL\MySQL Server 5.6\include
- Linker -> General -> Additional Library Directories - C:\Program Files\MySQL\MySQL Server 5.6\include
- 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.
bool CDatabaseManipulation::Connect(const TCHAR *ptchHost,
const TCHAR *ptchUserID,
const TCHAR *ptchDatabase,
const TCHAR *ptchPassword)
{
bool bConnectionSuccess = false;
if(IsConnected())
{
m_objLogger.log(_T("Connection has already been established."));
bConnectionSuccess = false;
}
if(mysql_init(&mysqlInitial) == NULL)
{
m_objLogger.log(_T("Failed to initiate MySQL connection"));
bConnectionSuccess = false;
}
mysqlConnection = mysql_real_connect(&mysqlInitial,
(const char*)ptchHost,
(const char*)ptchUserID,
(const char*)ptchPassword,
(const char*)ptchDatabase, 0, 0, 0);
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)
{
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
bool CDatabaseManipulation::ImportDatabase(const TCHAR *ptchHost,
const TCHAR *ptchUserID,
const TCHAR *ptchPassword,
const TCHAR *ptchDatabaseNameToImport,
const TCHAR *ptchImportDatabaseFile)
{
bool bImportDBSuccess = false;
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);
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;
}
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"));
CExecutablePathInfo objExecutablePathInfo;
LPTSTR lptstrExecutableDirectory = new TCHAR[1024];
objExecutablePathInfo.GetExecutableDirectory(lptstrExecutableDirectory, 1024);
_tcscat_s(lptstrExecutableDirectory, MAX_PATH, _T("\\TempDatabaseManipulationImport.bat"));
WriteVectorInFile(vecToWriteInFile, lptstrExecutableDirectory);
vecToWriteInFile.clear();
vecToWriteInFile.shrink_to_fit();
TCHAR strSystemDirPath[MAX_PATH] = _T("");
GetSystemDirectory(strSystemDirPath, sizeof(strSystemDirPath) / sizeof(_TCHAR));
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 };
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((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.
void CDatabaseManipulation::RetrieveDatabasesListInMySqlServer(vector<TCHAR*> &vecMySqlDatabasesList)
{
MYSQL_RES *myqlResult = mysql_list_dbs(mysqlConnection, _T("%") );
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
bool CDatabaseManipulation::ExportDatabase(const TCHAR *ptchHost,
const TCHAR *ptchUserID,
const TCHAR *ptchPassword,
const TCHAR *ptchDatabaseNameToExport,
const TCHAR *ptchExportDatabaseFileWithPath)
{
bool bExportDBSuccess = false;
if(!IsConnected())
{
m_objLogger.log(_T("MySql server is not connected."));
bExportDBSuccess = false;
}
else
{
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;
}
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"));
CExecutablePathInfo objExecutablePathInfo;
LPTSTR lptstrExecutableDirectory = new TCHAR[1024];
objExecutablePathInfo.GetExecutableDirectory(lptstrExecutableDirectory, 1024);
_tcscat_s(lptstrExecutableDirectory, MAX_PATH, _T("\\TempDatabaseManipulationExport.bat"));
WriteVectorInFile(vecToWriteInFile, lptstrExecutableDirectory);
vecToWriteInFile.clear(); vecToWriteInFile.shrink_to_fit();
TCHAR strSystemDirPath[MAX_PATH] = _T("");
GetSystemDirectory(strSystemDirPath, sizeof(strSystemDirPath) / sizeof(_TCHAR));
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 }; 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((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:
- First connect to MySQL server using "Connect to MySql Server Instance" section.
- Import database using "Import Database" section.
- 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
- Remember to change your Project Settings to point to the MySQL include files and the MySQL libs.
- 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.