Discover ISAPI: ADO Data Access from ISAPI



Click here for a larger image.

Environment: ISAPI

Introduction

This article presents a way to build HTML pages in ISAPI, using OLE DB database access. The sample database is an Access database.

Functionality

When you open the page in a client browser (WriteLayout.dll in the URL), the default page is opened:



Click here for a larger image.

Here are two links, WritePageDB2Web and WritePageDemo. Both links load the same HTML page.

The first link loads the page using dynamic data from an Access database and pieces of data from an HTML resource. The second link loads the page using a HTML string resource. The advantages are clear: The entire HTML page will be loaded faster because the only dynamic data will be coming from the Database using the CAdoDatabase and CAdoRecordset classes. Large HTML parts of the page are hard-coded in resource strings and will be loaded only on the first call.

Developing the pieces of HTML code inside of a Visual Studio project is very easy. The HTML string resources from a C++ project will be linked exactly to the correspondent external files. If you will work with Macromedia's or InterDev's own HTML files, just recompile the C++ project and the changes will be updated!

To link the external HTML files to HTML resources strings follow these steps:



Click here for a larger image.

Open the Resource tab of the project. Right-click inside this tab and choose Import, as in the picture.



Click here for a larger image.

After the previous step, will see the Import Resource control dialog. Choose an appropriate directory for your project directory where you work on the HTML files. In this example, we have the C++ project under the /MFC/WriteForm and the HTML hard-coded files under /MFC/Web/HTML.



Click here for a larger image.

After this step, we will obtain a new tag in our resources, called HTML.

Under that tag, you will see the newly added HTML file with a resource ID such as IDR_HTML1. Right-click on IDR_HTML1, click Properties, and then change the ID to your custom name (as in the picture). It is very important to change the Properties, the FileName location, and check the ExternalFile (see the picture). In that way, your resource inside the C project will be linked exactly to the external file ("..\web\HTML\Form.html").

You now have the HTML file in your C++ project! To see it, just double-click the imported resource.



Click here for a larger image.

Double-click the resource in the right panel to open the HTML file with syntax coloring. Right-click on the right panel to obtain an option with browser preview of that resource HTML file.

Using the HTML Resources in Your Project

How do you use these string resources in your Visual Studio project? Just use the LoadLongResource private function:

BOOL CWriteLayoutExtension::LoadLongResource(CString& str,
                                             UINT nID)
{
    HRSRC       hRes;
    HINSTANCE   hInst   = AfxGetResourceHandle();
    BOOL        bResult = FALSE;

    //if you want a standard HTML type
    hRes = FindResource(hInst, MAKEINTRESOURCE(nID), RT_HTML);
    if (hRes == NULL)
        ISAPITRACE1("Error: Resource %d could not be
                     found\r\n", nID);
    else
    {   
        DWORD dwSize = SizeofResource(hInst, hRes);
        if (dwSize == 0)
        {   str.Empty();
            bResult = TRUE;
        }
        else
        {   
            LPTSTR  pszStorage = str.GetBufferSetLength(dwSize);
            HGLOBAL hGlob      = LoadResource(hInst, hRes);
            if (hGlob != NULL)
            {
                LPVOID lpData = LockResource(hGlob);
                if (lpData != NULL)
                {    
                    memcpy(pszStorage, lpData, dwSize);
                    bResult = TRUE;
                }
                FreeResource(hGlob);
            }
        }    
    }
    return bResult;
}

Coding

The WritePageDB2Web method builds the entire page. The HTML page is constructed from four pieces of hard-coded HTML data intercorrelated with dynamic string data.

The hard-coded strings are loaded from resources using the LoadLongResource private function.

// first piece of hard-coded page
LoadLongResource(strPagePart1, IDR_HTML_PAGE_PART1);

The dynamic HTML string parts are loaded from the database using customized functions.

//The output page will be:
*pCtxt << strPagePart1 + GetArticlesFeature +
       // some HTML built from DB
          strPagePart2 + GetArticlesTopRated +
       // some HTML built from DB
          strPagePart3 + GetArticlesLast10 +
       // some HTML built from DB
          strPagePart4.
// (will have three fixed parts and two parts dynamically,
// from DB)

Outputting the mix of hardcoded and dynamic HTML to the page is done as follows:

    //1, fixed part:
    *pCtxt << strPagePart1;     // output hardcoded HTML part 1
    
    //2, dynamic part:          // GetArticlesFeature
    if ( ! GetArticlesFeature(AdoDB, &bstrOutput, &bstrError) )
    {
        *pCtxt << bstrError;    // if it's an error,
                                // display it
        return;
    }
    *pCtxt << bstrOutput;       // write part 1

The dynamic data is built-in private functions using the CAdoRecordset and CAdoDatabase classes. The connection string is hosted in the CWriteLayoutExtension() constructor string. I chose an Access database for database support. It is possible to use a direct connection string:

m_bstrConnectionString =
         L"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
           C:\\Projects\\DB\\Db2Isapi.mdb";

or a file connection string (double-click the udl file to link it to the Access DB):

m_bstrConnectionString        = L"File Name=C:\\DataLinks
                              \\ConnectionArticleDB2Isapi.udl";

The database is accessed very easily:

//the sql stmt string
_bstr_t  bstrSTMT(L"SELECT * from tblArticles where type=1");

MACRO_BEGIN                //here begins the code from the macro

_bstr_t  bstrValue_link, bstrValue_title,
bstrValue_author, bstrValue_headline;

while ( !AdoRS->IsEof() )             //if we have records
{
    AdoRS->GetFieldValue("link",      &bstrValue_link );
    AdoRS->GetFieldValue("title",     &bstrValue_title );
    AdoRS->GetFieldValue("author",    &bstrValue_author );
    AdoRS->GetFieldValue("headline",  &bstrValue_headline );
            
    wsprintf(wcOut,     "TR"\
         "TD width=100%% FONT class=links size=1
                         A href=%s %s/A BR "\
         "by B%s/B BR"\
         "FONT color=black%s/FONT"\
         "/TD"\
         "/TR",
         (LPCTSTR)bstrValue_link, (LPCTSTR)bstrValue_title,
         (LPCTSTR)bstrValue_author, (LPCTSTR)bstrValue_headline
           );

    AdoRS->MoveNext();
    *bstrOutput = (LPCTSTR)wcOut;    //the output here
}

MACRO_END                            //here ends the code from
                                     //the macro

To Install

  • Copy the folder Web under your Web site.
  • Provide "Scripts and Executables" Execute Permission to the Web application. This will allow the IIS Web server to execute the WriteLayout.dll.

Downloads

Download ISAPI source files - 260 Kb


Comments

  • Error on CreateInstance: REGDB_E_CLASSNOTREG

    Posted by Legacy on 02/17/2004 12:00am

    Originally posted by: Madlee

    When i try to use ADO in ISAPI, I meet the question.
    
    

    The following code:

    #import "c:\program files\common files\system\ado\msado15.dll" no_namespace rename ("EOF", "adoEOF")

    _ConnectionPtr pConnection;
    pConnection.CreateInstance(__uuidof(Connection));

    It works good at console and/or Windows program. But when i use it in an ISAPI program, The CreateInstance will return REGDB_E_CLASSNOTREG. What it mean? How can i solve it.

    My environments:
    Windows XP + IIS
    Visual Studio.net 2003

    3x.
    :)

    Reply
  • Excellent !!!

    Posted by Legacy on 12/17/2002 12:00am

    Originally posted by: sunil

    Which settings makes the bottom part(HTML table) of SamplePart4.htm appear to the right while retrieving info from Database.

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

Top White Papers and Webcasts

  • Live Event Date: July 30, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT You may already know about some of the benefits of Bluemix, IBM's open platform for developing and deploying mobile and web applications. Check out this upcoming eSeminar that focuses on building an Android application using the MobileData service, with a walk-through of the real process and workflow used to build and link the MobileData service within your application. Join IBM's subject matter experts as they show you the way to build a base …

  • 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 …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds