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

About the Author
Graduated with a degree in Electronics and Telecommunications from the Technical University of Iasi in 1986. However, in 1994 he again met his first love from high school: computer science. He began programming at a company specializing in financial software. Since 2002, he's been a project manager for WAM (work area management) applications used in medical laboratories. During that time, he's dealt with many programming languages, beginning with old Fortran. Actually, he programs in C++ using Visual Studio, MFC, Windows API, and COM.