MySQLWrapp: MFC-Extension Library for MySQL

Introduction

This article presents a collection of MFC-Extension classes that use the MySQL API. They are implemented in an MFC Extension DLL—MySQLWrapp.dll. The main goal of these classes is to optimize query and fetch time, handle errors, easily use query results, work with BLOB fields, and last but not least, reduce the code size of the applications that use them.

The main arguments for using these classes are:

  • They can be easily integrated in an MFC-based application.
  • They are very fast. For example, fetch operations are about 5 to 7 times faster than using ADO and 70 to 100 times faster than using an MFC CRecordset class.
  • They are simple and easy to use/understand for everybody accommodated with MFC programming.
  • They support BLOB fields handling.

Reference

Hierarchy Chart


CSQLException

A CSQLException object represents a MySQL-related exception condition.
CSQLException objects are constructed and thrown in CSQLConnection and CSQLResult member functions. You can access these objects within the scope of a CATCH expression.


CSQLBase

CSQLBase is the base class for CSQLConnection and CSQLResult. It keeps a connection handle and implements methods for throwing exceptions common to CSQLConnection and CSQLResult classes.


CSQLConnection

A CSQLConnection object represents a connection to a MySQL database. To use CSQLConnection, construct a CSQLConnection object and call its Open member function. Then, you can construct a CSQLResult object to get and fetch result sets. Also, CSQLConnection implements methods for SQL statements that do not return results (such as INSERT and UPDATE) and "single row" queries that do not require the explicit construction of a CSQLResult in the target source code.

CSQLConnection::Open

void Open(LPCTSTR pszHost, LPCTSTR pszDatabase, LPCTSTR pszUser,
     LPCTSTR pszPassword, UINT nPort = 3306);
throw(CSQLException);

Opens the database connection.

Parameters

  • pszHost—Host name
  • pszDatabase—Database name
  • pszUser—User name
  • pszPassword—User password
  • nPort—Port number; default 3306

Example

TRY
{
   // NOTE: In all examples m_conn is an object of the
   // CSQLConnection type.
   m_conn.Open(_T("localhost"), _T("salaries"), _T("root"),
               _T("root"));
}
CATCH(CSQLException, e)
{
   e->ReportError();
}
END_CATCH

CSQLConnection::Close

void Close();

Call this member function if you want to disconnect from database. Note that Close is also called in the CSQLConnection destructor.

CSQLConnection::IsOpen

bool IsOpen();

Returns true if database connection is open; otherwise false.

CSQLConnection::ExecuteSQL

void ExecuteSQL(LPCTSTR pszSQL);
throw(CSQLException);

Executes SQL statements that do not require a result-set.

Parameters

  • pszSQL—SQL statement.

Example

CString strSQL = _T("INSERT INTO cities(Name, CountryId)
                    VALUES('Houston',235)");
TRY
{
   m_conn.ExecuteSQL(strSQL);
}
CATCH(CSQLException, e)
{
   e->ReportError();
}
END_CATCH

CSQLConnection::ExecuteSQLV

void ExecuteSQLV(LPCTSTR pszFormat, ...);
throw(CSQLException);

Similar to ExecuteSQL, but the SQL statement is built according to the format specification in pszFormat and the variable list of arguments.

Parameters

  • pszFormat—Format string.
  • ...—Variable list of arguments

Example

int nId = 5;
TRY
{
   m_conn.ExecuteSQLV(_T("UPDATE cats SET BirthDate='2002-10-14'
                          WHERE Id=%d"), nId);
}
CATCH(CSQLException, e)
{
   e->ReportError();
}
END_CATCH

CSQLConnection::GetOneRow

bool GetOneRow(LPCTSTR pszSelect, SQLValueArray& arrValues);
throw(CSQLException);

Gets a single row from a result set.

Parameters

  • pszSelect—SELECT statement.
  • arrValues—Array of CSQLValue objects.

Example

CString strSelect = _T("SELECT Name FROM cats WHERE Id=2");
TRY
{
   SQLValueArray arrValues;
   bool bFound = m_conn.GetOneRowV(arrValues, strSelect);
   if(true == bFound)
   {
      CString strName = (LPCTSTR)arrValues.GetAt(0);
   }
}
CATCH(CSQLException, e)
{
   e->ReportError();
}
END_CATCH

CSQLConnection::GetOneRowV

bool GetOneRowV(SQLValueArray& arrValues, LPCTSTR pszFormat, ... );
throw(CSQLException);

Similar to GetOneRow, but the SQL statement is built according to the format specification in pszFormat and the variable list of arguments.

Parameters

  • arrValues—array of CSQLValue objects.
  • pszFormat—Format string.
  • ...—Variable list of arguments

Example

int nID = 5;
enum e_Fields {Name, BirthDate};
TRY
{
   SQLValueArray arrValues;
   bool bFound = m_conn.GetOneRowV(arrValues,
                                   _T("SELECT Name, BirthDate ")
                                   _T("FROM cats WHERE Id=%d"), nID);
   if(true == bFound)
   {
      CString strName = (LPCTSTR)arrValues.GetAt(Name);
      CString strDate = arrValues.GetAt(BirthDate).GetFormattedDate();
   }
}
CATCH(CSQLException, e)
{
   e->ReportError();
}
END_CATCH

CSQLConnection::UpdateBLOBField

void UpdateBLOBField(const CSQLValue& value, LPCTSTR pszTable,
     LPCTSTR pszField, LPCTSTR pszWhere = NULL); 
throw(CSQLException);

Updates a BLOB field.

Parameters

  • value—Value for updating the BLOB field
  • pszTable—Table name
  • pszField—Table field name
  • pszWhere—WHERE condition

Example

LPBYTE pBuffer = NULL;
TRY
{
   CFile file(_T("c:\\test.jpg"), CFile::modeRead);
   DWORD dwLength = file.GetLength();
   pBuffer = new BYTE[dwLength];
   file.Read(pBuffer, dwLength);

   CSQLValue value(pBuffer, dwLength);
   m_conn.UpdateBLOBField(value, _T("cats"), _T("Image"), _T("Id=2"));
}
CATCH_ALL(e)
{
   e->ReportError();
}
END_CATCH_ALL

if(NULL != pBuffer)
{
   delete []pBuffer;
}

CSQLConnection::UpdateBLOBFieldV

void UpdateBLOBFieldV(const CSQLValue& value, LPCTSTR pszTable,
     LPCTSTR pszField, LPCTSTR pszWhereFormat, ...);
throw(CSQLException);

Similar to UpdateBLOBField, but the WHERE condition is built according to the format specification in pszWhereFormat and the variable list of arguments.

Parameters

  • value—Value for updating the BLOB field
  • pszTable—Table name
  • pszField—Table field name
  • pszWhereFormat—WHERE condition format string
  • ...—Variable list of arguments

CSQLConnection::operator HCONNECT

operator HCONNECT() const;

Returns a connection handle. It is useful when instantiating a CSQLResult object that requires a HCONNECT argument.


CSQLResult

A CSQLResult object performs SQL queries and fetches the result set rows. After instantiating a CSQLResult object, you must call Query or QueryV method before fetching rows.

CSQLResult::CSQLResult

CSQLResult(HCONNECT hConnect);

Constructs a CSQLResult object. Because CSQLConnection has a HCONNECT operator, you can pass a CSQLConnection type directly.

Parameters

  • hConnect—The connection handle

CSQLResult::Query

void Query(LPCTSTR pszQuery);
throw(CSQLException);

Executes the SQL query and initiates the result set.

Parameters

  • pszQuery—SQL SELECT statement

Example

enum e_Fields {Name, BirthDate};
TRY
{
   CSQLResult res(m_conn);
   res.Query(_T("SELECT Name, BirthDate FROM cats"));
   SQLValueArray arrValues;
   while(res.Fetch(arrValues))
   {
      CString strName = (LPCTSTR)arrValues.GetAt(Name);
      CString strDate = arrValues.GetAt(BirthDate).GetFormattedDate();
   }
}
CATCH(CSQLException, e)
{
   e->ReportError();
}
END_CATCH

CSQLResult::QueryV

void QueryV(LPCTSTR pszFormat,...);
throw(CSQLException);

Similar to Query, but the SQL statement is built according to the format specification in pszFormat and the variable list of arguments.

Parameters

  • pszFormat—Format string
  • ...—Variable list of arguments

CSQLResult::Fetch

bool Fetch(SQLValueArray& arrValues, bool bFetchBLOB = true);
throw(CSQLException);

Retrieves the next row of the result set. If bFetchBLOB flag is false, the BLOB fields will be ignored. Returns true if more rows are in the result set. See the example at CSQLResult::Query.

Parameters

  • arrValues—Array of CSQLValue objects
  • bFetchBLOB—Fetch BLOB flag; default true

CSQLResult::EndFetch

void EndFetch();

Frees the result set. Note that EndFetch is also called by CSQLResult destructor and inside the Fetch function when there are no more rows in the result set.

CSQLResult::GetFieldsCount

UINT GetFieldsCount() const;
throw(CSQLException);

Gets the fields number in the result set.

CSQLResult::GetFields

void GetFields(SQLFieldArray& arrFields);
throw(CSQLException);

Gets the fields definition of a result set.

Parameters

  • arrFields—Array of CSQLField objects

Example

TRY
{
   CSQLResult res(m_conn);
   res.Query(_T("SELECT * FROM countries"));

   SQLFieldArray arrFields;
   res.GetFields(arrFields);

   const int nFieldsCount = arrFields.GetSize();
   for(int nIndex = 0; nIndex < nFieldsCount; nIndex++)
   {
      const CSQLField& field = m_arrFields.ElementAt(nIndex);
      CString strFieldName = field.GetName();
      bool bIsPrimaryKey   = field.IsPrimaryKey();
      bool bIsBLOB         = field.IsBLOB();
   }
}
CATCH(CSQLException, e)
{
   e->ReportError();
}
END_CATCH

CSQLValue

Encapsulates a field value from a result set.

CSQLValue::CSQLValue

CSQLValue();
CSQLValue(LPBYTE pData, UINT nLength,
          SQLType::e_Type eType = SQLType::eBLOB);
CSQLValue(const CSQLValue& rValue);

Constructs a CSQLValue object.

Parameters

  • pData—Pointer to a data buffer
  • nLength—Data buffer length
  • eType—Value type; default BLOB
  • rValue—An existing CSQLValue object to be copied into this CSQLValue object

CSQLValue::operator =

CSQLValue& operator=(const CSQLValue& rValue);

Assignment operator

Parameters

  • rValue—An existing CSQLValue object to be copied into this CSQLValue object

Cast operators

operator LPBYTE() const;
operator LPCTSTR() const;
operator char() const;
operator short() const;
operator int() const;
operator long() const;
operator __int64() const;
operator float() const;
operator double() const;

Performs conversion to different types. The CSQLValue object must have the appropriate type; otherwise, it asserts.

Date and time formatting functions

CString GetFormattedDate() const;
CString GetFormattedTime() const;
CString GetFormattedDatetime() const;
CString GetFormattedTimestamp() const;

Returns a formatted date/time string according to the current locale ID (m_lcid member).

CSQLValue::GetDefaultLCID

static LCID GetDefaultLCID();

Gets the current locale ID.

CSQLValue::SetDefaultLCID

static void SetDefaultLCID(LCID lcid);

Sets the current locale ID (used in date/time format functions). Note that the initial locale ID is the user locale ID.

CSQLValue::GetLength

UINT GetLength() const;

Returns the data buffer length.

CSQLValue::GetType

SQLType::e_Type GetType() const

Returns the value type.

CSQLValue::IsNull

bool IsNull() const;

Returns true if the value is NULL; false otherwise.

CSQLValue::IsBLOB

bool IsBLOB() const;

Returns true if the value is of BLOB type; false otherwise.


CSQLField

Encapsulates the definition of one column (field) of a result set.

CSQLField::CSQLField

CSQLField();
CSQLField(HFIELD hField);
CSQLField(const CSQLField& rField);

Constructs a CSQLField object.

Parameters

  • hField—A field handle
  • rField—An existing CSQLField object to be copied into this CSQLField object

CSQLField::operator =

CSQLField& operator=(const CSQLField& rField);

Assignment operator

Parameters

  • rField—An existing CSQLField object to be copied into this CSQLField object

Column definition functions

LPCTSTR GetName() const;           // Name of column
LPCTSTR GetOriginalName() const;   // Original column name,
                                   // if an alias
LPCTSTR GetTable() const;          // Table of column if column
                                   // was a field
LPCTSTR GetOriginalTable() const;  // Org table name, if table was
                                   // an alias
LPCTSTR GetDatabase() const;       // Database for table
SQLType::e_Type GetType() const;   // Type of field. See SQLTypes.h
                                   // for types

bool IsPrimaryKey() const;         // Field is part of a primary key
bool IsBLOB() const;               // Field is BLOB
bool IsNotNull() const;            // Field can't be NULL 
bool IsUniqueKey() const;          // Field is part of a unique key
bool IsMultipleKey() const;        // Field is part of a key
bool IsUnsigned() const;           // Field is unsigned
bool IsZeroFill() const;           // Field is zerofill
bool IsBinary() const;             // Field is binary

MySQLWrapp: MFC-Extension Library for MySQL

Using MySQLWrapp in Your Application

  • Build MySQLWrapp.dll.
  • Include in your application "MySQLWrapp.h".
  • In "Project settings/Lib/Object library modules" field add (full path and) file name of MySQLWrapp.lib, e.g. "c:\MyProject\lib/MySQLWrapp.lib". You can also use pragma comment in your source code, for example
    #pragma comment(lib,_T("\\MyProject\\lib/MySQLWrapp.lib"))
  • Don't forget to include <AfxTempl.h> in StdAfx.h, because MySQLWrapp uses CArray MFC template class.
  • Be sure that MySQLWrapp.dll and libmysql.dll are in the same folder with your application executable file.

Demo Application

[2_screenshot.gif]

Brief description

The demo application is a simple UI client (DemoUIClient.exe).
Wrapper classes described in this article are implemented in SQLWrapp.dll.
MySQL C API functions are exported from libmysql.dll which is shipped with MySQL install kit. Please copy libmysql.dll into the application folder.

Left pane tree contains:

  • connection info, i.e. user and host in the root;
  • database names;
  • table names for each database.

Right pane displays:
  • if a database is selected - information about its tables (SHOW TABLE STATUS);
  • if a table is selected - table contents (SELECT * FROM table);
  • the result of a query entered by the user (see SQL/Query menu item).

For BLOB fields "<blob>" string is displayed in the list.
If click in a <blob> field and it contains a image (BITMAP, GIF, or JPEG) this is displayed in a picture tool window.
To update a <blob> field from a file right-click on it.

Demo Database

To install the demo database you can use mysql.exe command-line tool:
  • download and unzip test_db.sql script file.
  • launch mysql.exe from command line.
  • use source command to execute the SQL script file.
Example
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql --user=root --password=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.7-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> source c:\test_db.sql

Final notes

  • Here you can download MySQL Database Server & standard clients. Because there are more versions available, I recommend the last stable release (non-beta).
  • MySQLWrapp was tested on MySQL versions 4.0.24, 4.1.11, and 5.0.7-beta.
  • libmysql.dll, libmysql.lib, and mysql header files are shipped with MySQL Database Server & standard clients.
  • The demo application is only for testing purpose and designed to be very simple. It has its limitations, and its not a full featured MySQL UI client application.



About the Author

Ovidiu Cucu

Graduated at "Gh. Asachi" Technical University - Iasi, Romania. Programming in C++ using Microsoft technologies since 1995. Microsoft MVP awardee since 2006. Moderator and article reviewer at Codeguru.com, the number one developer site. Co-founder of Codexpert.ro, a website dedicated to Romanian C++ developers.

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Protecting business operations means shifting the priorities around availability from disaster recovery to business continuity. Enterprises are shifting their focus from recovery from a disaster to preventing the disaster in the first place. With this change in mindset, disaster recovery is no longer the first line of defense; the organizations with a smarter business continuity practice are less impacted when disasters strike. This SmartSelect will provide insight to help guide your enterprise toward better …

  • Live Event Date: August 19, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT You deployed your app with the Bluemix PaaS and it's gaining some serious traction, so it's time to make some tweaks. Did you design your application in a way that it can scale in the cloud? Were you even thinking about the cloud when you built the app? If not, chances are your app is going to break. Check out this upcoming eSeminar to learn various techniques for designing applications that will scale successfully in Bluemix, for the …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds