Accessing Database Using ISAPI And ADO

In this article we will see how to access database using ISAPI Extension DLL and ADO. You can run this DLL on HTTP server having Microsoft's Internet Information Server.

This DLL sends a page containing records of 'courses.mdb' file to the browser on receiving a command from the user to show the records. The 'courses.mdb' file has a table 'registration' having fields, userid, name, address and email all of which are strings.

Create the ISAPI extension DLL by selecting 'File | New | ISAPI Extension Wizard'. Enter the project name as 'dbaseserver' and click on 'Next'. In the Step 1 of the wizard accept the default selections and click on 'Finish'. You will see a class CDbaseserverExtension derived from CHttpServer added to the project.

Since we are going to use ADO to access the database we must import the type library of ADO components. For this, add the following statements in the 'StdAfx.h' file.

#include <atlbase.h>

#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF","adoEOF")

Since ADO is a COM based technology we must load the COM library to use the COM specific functions. To load the COM library we have called CoInitialize( ) function in the GetExtensionVersion( ) function. The GetExtensionVersion( ) function stands added to the CDbaseserverExtension class. This function is shown below:

BOOL CDbaseserverExtension::GetExtensionVersion ( HSE_VERSION_INFO* pVer )

{

// AppWizard added code

CoInitialize ( NULL ) ;

return TRUE ;

}

The GetExtensionVersion( ) function gets called by the server when it first loads the extension DLL. This function reports description and version information about your DLL to the server. You may also use this function to initialize the variables used by the DLL. Returning FALSE from GetExtensionVersion( ) function will abort the loading of your DLL.

If the client executes only DLL without giving any command to the server we would display a default message in the browser indicating the usage of the DLL. When no command is associated with the DLL a function Default( ) gets called. The Default( ) function also is added to the class by default. Add the following code in the CHttpserverExtension::Default( ) function.

void CDbaseserverExtension::Default ( CHttpServerContext* pCtxt )

{

StartContent ( pCtxt ) ;

WriteTitle ( pCtxt ) ;

*pCtxt << _T ( "To view the records copy dbaseserver.dll and courses.mdb in any folder of the http server" ) ;

*pCtxt << "<p>" ;

*pCtxt << _T ( "The folder must have Execute rights" ) ;

*pCtxt << "<p>" ;

*pCtxt << _T ( "The courses.mdb file must have a table 'registration' with fields 'userid', 'name', 'address' and 'email' " ) ;

EndContent ( pCtxt ) ;

}

The default message that we would display in the browser should be in the HTML form. The job of creating a HTML file is done by the CHttpServer::StartContent( ) function. The StartContent( ) function inserts the <Body> and <HTML> tags into an HTML stream to be returned to the client. The WriteTitle( ) function inserts a title string in the browser's window. We can display our title in the window by overriding the virtual CHttpServer::GetTitle( ) function. The CHttpServerContext class has an overloaded << operator using which we can put the contents in the HTML file being created. We have displayed the correct usage of the extension server DLL as the default message. The EndContent( ) function marks an end of the HTML file by inserting </Body> and </HTML> tags in the HTML file.

Add following macro in the parse map in the 'dbaseserver.cpp' file.

ON_PARSE_COMMAND ( printrecords, CHttpserverExtension,ITS_EMPTY )

The macro ON_PARSE_COMMAND defines a command (in our case printrecords) to be sent by the client to the server. ITS_EMPTY flag specifies that the command will not take any parameters.

Add the function printrecords( ) to the CDbaseserverExtension class as its member function and add the code in it as shown below:

void CDbaseserverExtension::printrecords ( CHttpServerContext *pcontext )

{

char buffer [MAX_PATH] ;

DWORD size = MAX_PATH ;

StartContent ( pcontext ) ;

WriteTitle ( pcontext ) ;

pcontext -> GetServerVariable ( "URL", buffer, &size ) ;

size = MAX_PATH ;

pcontext -> ServerSupportFunction ( HSE_REQ_MAP_URL_TO_PATH, buffer, &size, 0 ) ;

CString filepath ( buffer ) ;

int index = filepath.ReverseFind ( '\\' ) ;

filepath = filepath.Left ( index ) ;

filepath += "\\" ;

CString content ;

filepath += "courses.mdb" ;

HRESULT hr ;

hr = m_pconnection.CreateInstance ( __uuidof ( Connection ) ) ;

CString dsource ;

dsource.Format ( "Provider=Microsoft.Jet.OLEDB.3.51;DataSource=%s", filepath ) ;

USES_CONVERSION ;

BSTR dsourcebstr = ::SysAllocString ( A2W ( dsource ) ) ;

hr = m_pconnection -> Open ( dsourcebstr, "", "", adOpenUnspecified ) ;

if ( FAILED ( hr ) )

*pcontext << "Cannot establish ADO connection" ;

_RecordsetPtr recset ;

CString query ;

CComVariant v ( 0L ) ;

query.Format ( "SELECT * FROM registration" ) ;

recset = m_pconnection -> Execute ( query.operator LPCTSTR( ), &v, adOptionUnspecified ) ;

if ( recset == NULL )

{

*pcontext << "Cannot Execute query" ;

return ;

}

CComVariant uidvar, namevar, addvar, emailvar ;

CString uid, name, add, email, resultstr ;

while ( !recset -> adoEOF )

{

resultstr = "" ;

uidvar = recset -> GetCollect ( L"userid" ) ;

uid = uidvar.bstrVal ;

*pcontext << uid << "  &nbsp" ;

namevar = recset -> GetCollect ( L"name" ) ;

name = namevar.bstrVal ;

*pcontext << name << "  &nbsp" ;

addvar = recset -> GetCollect ( L"address" ) ;

add = addvar.bstrVal ;

*pcontext << add << "  &nbsp" ;

emailvar = recset -> GetCollect ( L"email" ) ;

email = emailvar.bstrVal ;

*pcontext << email ;

*pcontext << "<p>" ;

recset -> MoveNext( ) ;

}

recset -> Close( ) ;

m_pconnection -> Close( ) ;

EndContent ( pcontext ) ;

}

Whenever we add a member function to the CHttpServer derived class the first parameter of the function should be a pointer to CHttpServerContext class. Since the function printrecords( ) shown last week does not take any other parameters the function will have a pointer to CHttpServerContext class as the only parameter.

In this function, firstly we have retrieved the URL passed by the user to the HTTP server by calling CHttpServerContext::GetServerVariable( ) function. The first parameter passed to this function specifies that the URL entered by the user (for example, '/data/dbaseserver.dll') should get stored in the buffer passed to this function as the second parameter. The third parameter specifies the size of the buffer. Next, we have converted the URL stored in the array buffer into the local path of the DLL (for example, 'D:\data\dbaseserver.dll') by calling CHttpServerContext::ServerSupportFunction( ). The first parameter of this function specifies the action to be taken on the buffer. Second and third parameters are the buffer containing URL and its size respectively. The last parameter specifies that the return value should get terminated by "\r\n". The return value is stored in the buffer passed as the second parameter. Next, we have stored the local path of the DLL in a CString object. We have extracted the path of the DLL by using member functions of CString class and concatenated the data source name, 'courses.mdb', to this path.

Next, we have instantiated a Connection object by calling CreateInstance( ) function. Add m_pconneciton as a private data member of the _ConnectionPtr class to the CDatabaseserverExtension class. We have then created a string containing the database provider name and the data source name to be passed as the first parameter to the Connection::Open( ) function. The 'Provider=' part of the string specifies the name of the OLE DB provider whereas 'Data Source=' part of the query specifies the data source to be accessed. Since the Open( ) function accepts a BSTR variable as the first parameter we must convert the CString object to the BSTR. For this, we have used the ATL string conversion macro A2W. To be able to use the conversion macros we must write the statement USES_CONVERSION before using the macros We have called the ::SysAllocString( ) function to allocate memory for the BSTR variable and to copy the string in it. We have called the Open( ) function to open the connection with the data source.

Next, we have created an SQL statement to select all the records of the table 'registration' from the data source. This query is then executed by calling Connection::Execute( ) function. This function returns a Recordset pointer using which we can move through the data base. We have retrieved the values of each field of a record by calling Recordset::GetCollect( ) function. We have passed the field name to the GetCollect( ) function. This function returns a CComVariant object. We have retrieved the value stored in the CComVariant object and stored in the CString object. This value is then displayed by using a pointer pcontext of the CHttpServerContext class. We have moved to the next record by calling Recordset::MoveNext( ) function.

We must unload the COM library when our extension DLL is unloaded. We have done this by calling CoUninitialize( ) function in the TerminateExtension( ) function. The TerminateExtension( ) function is also exported by the extension DLL. This function is called whenever the server unloads the extension DLL. The TerminateExtension( ) function is given below:

BOOL CDbaseserverExtension::TerminateExtension ( DWORD dwFlags )

{

// AppWizard added code

CoUninitialize( ) ;

return TRUE ;

}

Now you can build the DLL by pressing F7.

How to use the DLL

Create a directory 'data' under your http server by using Microsoft's Internet Information Service (IIS). Remember to grant Execute rights to this folder. Copy the 'dbaseserver.dll' and the 'course.mdb' file to this directory. Type the following URL in the browser:

/data/dbaseserver.dll?printrecords

You will see the records displayed in the browser as shown in the following figure.

Download