Building a DSN less connection for OLE DB

Needed to hook a Windows application to an MS-Access database . The windows application would insert records into the tables when the end user choose one of the application's menu option.

I thought that it would be good idea to free up the end user from having to create the databases DSN using the ODBC data source administrator control panel. A DSN less connection string look like this

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\myapplication\\mycooldb.accdb"

The only down size of this is that I would have to hard code this somewhere on the source code.

Wait! Since the database will be distributed with the application what about if  'Data Source"' property gets built dynamically.

To achieve this it's need to retrieve the directory of the running application , append to this the database name and make sure that the Provider property is included.

CString myDatabasePath;

CString connString (_T("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = "));

//this get the file path of the application ,chop  off the executable name and
// add the database name

GetModuleFileName(NULL,myDatabasePath.GetBufferSetLength(MAX_PATH),MAX_PATH);

myDatabasePath.Truncate(targetDir.ReverseFind('\\') + 1) ;

myDatabasePath +=        "mycooldb.accdb";

// add the database path (i.e C\myapplication\\mycooldb.accdb) to the connection string

connString += myDatabasePath

//need to do this since  OLE DB is used

BSTR bstrConnString = connString.AllocSysString();

hr = _db.OpenFromInitializationString(bstrConnString);

//free up string used

targetDir.ReleaseBuffer(MAX_PATH);

SysFreeString(bstrConnString);

Where is my ODBC driver gone?

Hello Boys and Girls,
Just finish installing some database drivers for SQLAnywhere 5.0 in my Windows 7.
I have the Professional version of Windows 7.
After the installation of the drivers I wanted to create an ODBC data source.

To my surprise when I went to the Control Panel->All Control Panel Items->Administrative Tools->Data Sources (ODBC)
and tried to add a new datasource the recently SQLAnywhere driver was not there.
The only drivers listed on the ODBC data source administrator were: SQL Server and SQL Server native client.
No trace of any drive for SQLAnywhere.

After spending 10 or 15 minutes with my friend Google I found one interesting fact:
There are two ODBC data source administrator executable (odbcad32.ex) in my Windows 7:
One is located on C:\Windows\System32 and the other is located in C:\Windows\SysWOW64

To manage a data source that connects to 64 bits driver, under a windows 7 64 bits, the odbcad32.exe located
on C:\Windows\System32
To manage a data source that connects to a 32 bit driver, under a 64 bits platform, the odbcad32.exe located on
C:\Windows\SysWOW64 should be used.

Snce I want to create a data source for the SQLAnywhere 5.0 driver which is a 32 bits I should use the odbcad32.exe located on, C:\Windows\SysWOW64
once I double click on it I was presented with the familiar ODBC data source administrator and then I clicked on "Add" I could see the SQLAnywhere 5.0
driver.

Problem solved.

 

MFC data access using OLE DB

Hello Boys and Girls,

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 go
here

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.