Today I want to give an example on how to access and retrieve data from a relational database , in this case Sybase SQL Anywhere, using MFC.
Yes MFC. MFC is an early attempt by Microsfot on creating a Object Oriented API for Windows development. Basically MFC is wrapper API for the underlaying Windows API.
For this example I am using Tools For Men (AKA Visual Studio 2010) and C++.
The high level steps are:
1. Establishing connection to the database. For this I will be using OLE DB
2. Call a store procedure.
Establishing a connection to a database using OLE DB.
For opening a connection to our SQL Anywhere a CDatasource,CSession and initialisation string is needed:CDataSource _db;
CSession session
LPTSTR connString = _T("Provider=MSDASQL.1;Persist Security Info=False;User ID=admin;Extended Properties=\"DSN=hoteldb;UID=admin;\"");
Since OLE DB is made of set of interfaces that implement COM a HRESULT variable is needed to keep track of the result of our operation.
HRESULT hr;
hr = _db.OpenFromInitializationString(connString);
if(FAILED(hr)){
AfxMessageBox(_T("It can't connect to database"),0,0);
}
hr = session.Open(_db);
if(FAILED(hr)){
AfxMessageBox(_T("it can't open database session"),0,0);
}
Once that we have succesfull open a session we can use this instance to access our database.
Call a Sybase SQL Anywhere store procedure
In this example my recordset is bound to the data source via my CAccessor class CCount. For an explanation of what a CAccesors are and their role in OLE DB you can gohere
When opening the recordset I am passing a store procedure, togehter with session. Store procedures are an elegant way of executing SQL operations on databases.
CCommand<CAccessor<CCount>> recordset;
hr = recordset.Open(session,storeProcCall );
if(FAILED(hr)){
AfxMessageBox(_T("Can't open table"),0,0);
}
while(recordset.MoveNext() == S_OK){
passes= recordset.m_count;
}
After getting the result of the record set is a good idea to close the session and recordset object.
CloseSession();
recordset.Close();
Then I can pass or return my result to a calling method:
return passes;
The code for the accessor type is very simple:
Class CCount{
public:
int m_count;
//input
TCHAR m_From[50];
TCHAR m_To[50];
TCHAR m_PassCode[50];
//Parameter Accessor
BEGIN_PARAM_MAP(CCount)
SET_PARAM_TYPE(DBPARAMIO_INPUT)
COLUMN_ENTRY(1, m_From)
SET_PARAM_TYPE(DBPARAMIO_INPUT)
COLUMN_ENTRY(2, m_To)
SET_PARAM_TYPE(DBPARAMIO_INPUT)
COLUMN_ENTRY(3, m_PassCode)
END_PARAM_MAP()
BEGIN_COLUMN_MAP(CCount)
COLUMN_ENTRY(1,m_count)
END_COLUMN_MAP()
};
I found CString MFC class very handy when building the store procedure parameters:
CString storeProcCall ("CALL admin.GuestPassDetails(");
storeProcCall.Append(from);
storeProcCall.Append(_T(","));
storeProcCall.Append(to);
storeProcCall.Append(_T(","));
storeProcCall.Append(passesCode);
storeProcCall.Append(_T(") "));
This CString is passed to the recordset as I mentioned previoulsy:
hr = recordset.Open(session,storeProcCall );
See you next time.
