Shiv Kumar's Tutorials - ISAPI Data Entry and Database Access

In the first article dealing with ISAPI applications, we talked about web applications as ISAPI or CGI applications, and built our first ISAPI application.

The application that we will build is a simple application that allows other web site owners to add a link to their web site to be accessed from our web site. Rather than having them send us an email and then we manually make the change in our database, we will build an application that will automate the process for us.

To create a data entry form, we could create an HTML file and place it in the web server's root folder. To display it, you would enter the URL to the HTML form, such as:

ServerName is the Computer Name of your PC or web server. DataEntry.htm is the physical name of the HTML file that resides in the root folder of your web server (C:\inetpub\wwwroot).

The other approach, the one we will use here, is to generate the HTML form from our application. Here, we will:

To ensure that your applications will reside in the scripts folder, you will want to compile directly to that folder. To do this, choose Project | Options from Delphi's main menu. In the dialog box presented, switch to the - Directories/Conditionals page, and in the "output directory" field, enter the path to the scripts folder, for example, C:\Inetpub\scripts. Then click the OK button. We do this so that our ISAPI DLL is generated in this folder rather than copy it over to this folder each time we re-compile.

To create an Action item, select the Web Data Module, and then from the object inspector's properties page, select "Actions" and click on the ellipses (or double click on the data module (not in the TreeView partition of the Datamodule (D5)) to active the Actions editor).

In the object inspector, fill in the PathInfo property as "/AddLinkPage". Set the "Name" property to "waAddLinkPage".

The object inspector and actions editor should look like that shown in Figure 1.

Figure1: Showing the Object Inspector and Actions Editor with the /AddLinkPage action item.

Now, choose the Events page of the object inspector and generate the OnAction event handler for this action item. In the event handler, type in the following code:

procedure TWebMod1.WebMod1waAddLinkPageAction(Sender: TObject;
  Request: TWebRequest; Response: TWebResponse; var Handled: Boolean);
begin
  Response.Content :=
    '<html>'   NewLine  
    '<head>'   NewLine  
    '	<title>Add a Link to your Web Site</title>'   NewLine  
    '</head>'   NewLine  
    '<body>'   NewLine  
    '<h2>Please Enter the Required information to be able to link your Site from here.</h2>'   NewLine  
    '<form action="'   Request.ScriptName   '/PostLinkInfo" method="post"'   NewLine  
    '  title="Enter the information required to Add a link to your Web Site.">'   NewLine  
    '<table>'   NewLine  
    '<tr>'   NewLine  
    '<td><font face="Tahoma">URL to your Web Site</font></td>'   NewLine  
    '<td><font face="Tahoma"><input type="text" name="txtLinkURL"'   NewLine  
    '  style="height: 22px; width: 274px" value="http://"></font></td></tr>'   NewLine  
    '<tr>'   NewLine  
    '<td><font face="Tahoma">Title to be Shown</font></td>'   NewLine  
    '<td><font face="Tahoma"><input type="text" name="txtTitle"'   NewLine  
    '  style="height: 22px; width: 274px"></font></td></tr>'   NewLine  
    '<tr>'   NewLine  
    '<td><font face="Tahoma">Description to be shown</font></td>'   NewLine  
    '<td><textarea name="txtDescription" style="height: 80px; width: 274px"></textarea></td></tr>'   NewLine  
    '<tr>'   NewLine  
    '<td><font face="Tahoma">Owner''s First Name</font></td>'   NewLine  
    '<td><font face="Tahoma"><input type="text" name="txtOwnerFirstName"'   NewLine  
    '  style="height: 22px; width: 274px"></font></td></tr>'   NewLine  
     '<tr>'   NewLine  
    '<td><font face="Tahoma">Owner''s Last Name</font></td>'   NewLine  
    '<td><font face="Tahoma"><input type="text" name="txtOwnerLastName"'   NewLine  
    '  style="height: 22px; width: 274px"></font></td></tr>'   NewLine  
    '<tr>'   NewLine  
    '<td><font face="Tahoma">Email Address</font></td>'   NewLine  
    '<td><font face="Tahoma"><input type="text" name="txtEmailAddress"'   NewLine  
    '  style="height: 22px; width: 274px"></font></td></tr>'   NewLine  
    '<tr>'   NewLine  
    '<td><font'   NewLine  
    '  face="Tahoma"><input type="submit" name="btnSubmit" value="Submit"></font></td>'   NewLine  
    '<td><font'   NewLine  
    '  face="Tahoma"><input type="reset" value="Reset"></font></td></tr>'   NewLine  
    '</table>'   NewLine  
    '</form>'   NewLine  
    '<P><h4>The server will attempt to validate the URL you are attempting to submit. If you get an error'   NewLine  
    ' due to an invalid URL. Please refresh the page before attempting to re-sumbit.'   NewLine  
    ' if everything goes fine, you should be re-directed to the Links Page.'   NewLine  
    ' You should be able to see the link you just added.'   NewLine  
    '<P>Thank you for taking the time to link your web site from here.</h4>'   NewLine  
    '</body>'   NewLine  
    '</html>';
end;

Note: You may want to use the variable "NewLine" in your web applications to make the generated HTML more readable. You may declare it as a constant in the interface section of the unit, as:

const
  NewLine = #13#10;

Instead of NewLine, you may prefer crlf (easier to type). Also, remember that we don't need to have a new line per se. We do this only to format the generated HTML. The browser will render the page in exactly the same way, with or without the new line. But if one was to see the source of the HTML, then it would all be in one line. This would make it awfully difficult to debug (the HTML) if things were to go wrong. I personally like to see well formatted HTML when I look at the source of an HTML page.

The Action tells the browser what to do when the "submit" button is pressed. In this case, we tell the browser to execute the action item with the PathInfo property set as /PostLinkInfo in our ISAPI. The Action evaluates to:

Since we have told the web server to direct the form's data to the /PostLinkInfo action of our application (AddLink.dll), we will need to create an action item with a PathInfo of "/PostLinkInfo". We'll do that later.

For now, let’s see what is happening in the OnAction event created above. What we're doing, is sending back (using the "Response" object's "Contents" property) the HTML required for a data entry HTML form. The Contents property is a string that is sent back via the web server to the requesting browser.

For this example, the fields we need our user (who is interested in adding a link to her site from our web site) to enter, are:

When you see this form, you will notice that the URL field already has the text - "http://". This is done at the time of generating the form, by entering "http://" in the "value" attribute as follows:

Notice also that each field on the HTML Form (recognized by the <INPUT> tag) has a unique name such as txtLinkURL. Later, we will use these names to extract the values for each of the required fields in order to post this information to a database.

So we have an action item in our ISAPI DLL that will generate a data entry form. To call this action, you will enter the following URL in the web browser’s address field:

Note that if you need to recompile your project for any reason as an ISAPI (DLL), you must stop and restart the WWW service, and then recompile your application. (This is not necessary for a CGI (EXE)). If you are using IIS, you could turn off - Cache ISAPI Applications checkbox in the Internet service manager options. Be sure to turn it on, on your production web server machine.

In order to release the lock on a DLL, PWS must be manually stopped using the command prompt. By default, Pws.exe resides in the folder C:\Windows\System\Inetsrv.

You can then restart PWS manually restarted from the Command Prompt by using the following command:

Once PWS is stopped, this will release the ISAPI DLL. Recompile the DLL, if necessary. Once PWS restarts, it will again lock the DLL when it gets instantiated by your browser.

At the run line, type: net stop iisadmin /y and then press Enter. This will stop all services while running the make on the DLL.

After recompiling your DLL, type: net start w3svc from the run line. Your services will be up again.

After you have compiled the ISAPI DLL, make sure it is present in your scripts folder. Type in the URL and you should see the form as shown in Figure 2.

Figure 2: Showing the dynamically generated Data Entry Screen in a browser.

If we set the Default property of this action to True, we could simply type in the URL

Now that we have an HTML form that will allow us to capture data, we need to "receive" this data, validate it and post it to our database.

We need to create an Action item that corresponds to the Form's action. The form's action is:

To do this, we need to create a new action item. Set the pathinfo property to - /PostLinkInfo and the Name property to waPostLinkInfo. Then we need to write an event handler for the OnAction event of this action item. But before that, we need our database in place.

For this example, we'll use an Access database. Access is fine for small ISAPI/CGI applications. But ideally you should use an RDBMS. The name of the table in the Access mdb is LINKS and the structure of the table is as shown in Figure 3.

Create an ODBC alias for this mdb file called WEBSITE_ODBC using the ODBC Manager. The ODBC DSN should be a System DSN since it will be used by the Webserver(IIS) and not by a logged-on user. This point is very important to note. On Windows NT, IIS uses IUSER_ as the user. This user has limited access to the system. Also, I suggest creating a folder called Database under the scripts folder and keeping all databases in this folder.

We can't have a login dialog box popping up in our ISAPI/CGI applications. We will never see this dialog box and therefore we'll never be able to log in to the database. When that happens, the application will seem to hang. It's not really hanging, but since you didn't login, you won't be able to use the application either. Setting the LogInPrompt property to False and setting the parameters as shown above, will allow our ISAPI/CGI application to log in to the database non-interactively.

Remember that an ISAPI DLL runs in the process space of the web server. Each time a request is made to the ISAPI DLL, a new thread is spawned (An instance of the web data module is created in this thread). To be able to connect to a database multiple times (like in a multi-threaded application) using the same instance we need to provide a unique session name for each connection. The TSession component handles that for us when we set the AutoSessionName property to true. Do not use a TSession component in your CGI applications. The web server creates a new instance of your CGI application for each request and so a unique session name is not required. Your data module should look like that shown in Figure 4.

Figure 4 shows the web data module with the data access components thus far.

Every action's event (OnAction) makes 2 objects available to you -- the Request object and the Response object. You can see above that we've used the Request object's ScriptName property.

The Request.ScriptName property evaluates to - /scripts/AddLink.DLL. It is good practice to not hard- code the script's name, but instead use the ScriptName property. Later if you change the name of the script or the scripts folder, you won’t have to modify your code.

Since we used the POST method of the form above, the data of that form will be made available in the ContentFields property of the Request object – "Request" since it comes to this event as a Request made by the browser via the web server. If we use the GET method in the HTML form, then the same information would be made available to us in the QueryFields property of the Request object.

To make our data validation easier, we will first create two custom Exception objects that can be raised if any part of the data is invalid, by putting the following code in the Type section:

type
  EInsufficientInfo = class(Exception);
  EInvalidURL = class(Exception);

The ContentFields and QueryFields properties are TStrings derivatives. The Form's data is made available to us in the form of name=value pairs. So the data entered in the URL field will look like

We can therefore use the Values method of the ContentFields object to get the Value for a given (Field) Name. The code in this action item's OnAction event will look like this:

procedure TWebMod1.WebMod1waPostLinkInfoAction(Sender: TObject;
  Request: TWebRequest; Response: TWebResponse; var Handled: Boolean);
begin
  { I've create a custom Exception object called - EInsufficientInfo that will
    be rasied if any part of the data is invalid. In this case invalid = null.
  }
  try
    { Validate Data Entered by the User }
    if Request.ContentFields.Values['txtLinkURL'] = '' then
      raise  EInsufficientInfo.Create('Please Enter the URL of your Web Site');
    if Request.ContentFields.Values['txtTitle'] = '' then
      raise  EInsufficientInfo.Create('Please Enter the Title you want Displayed for your Web Site');
    if Request.ContentFields.Values['txtDescription'] = '' then
      raise  EInsufficientInfo.Create('Please Enter the Description you want Displayed for your Web Site');
    if Request.ContentFields.Values['txtOwnerFirstName'] = '' then
      raise  EInsufficientInfo.Create('Please Enter Your First Name.');
    if Request.ContentFields.Values['txtOwnerLastName'] = '' then
      raise  EInsufficientInfo.Create('Please Enter Your Last Name');
    if Request.ContentFields.Values['txtEmailAddress'] = '' then
      raise  EInsufficientInfo.Create('Please Enter your contact Email Address');

    { In this particular case, we'd like to validate the URL that the user has
      entered as well. To do that, we'll use an HTTP component in the URLIsValid
      function. Warn the user if the URL is found to be invalid.
    }
    if not URLIsValid(Request.ContentFields.Values['txtLinkURL']) then
      raise EInvalidURL.Create('The URL you submitted is not a valid URL.'   #13#10  
        'Please make sure you type a valid URL before submitting');

    { If data is Valid and URL is Valid, post it to the database }
    Query1.Close;
    Query1.SQL.Clear;
    Query1.SQL.Add('INSERT INTO LINKS');
    Query1.SQL.Add('(LinkURL, Title, Description, DateAdded, OwnerFirstName, OwnerLastName, EmailAddress)');
    Query1.SQL.Add('Values(:LinkURL, :Title, :Description, :DateAdded, :OwnerFirstName, :OwnerLastName, :EmailAddress)');
    Query1.ParamByName('LinkURL').AsString := Request.ContentFields.Values['txtLinkURL'];
    Query1.ParamByName('Title').AsString := Request.ContentFields.Values['txtTitle'];
    Query1.ParamByName('Description').AsString := Request.ContentFields.Values['txtDescription'];
    Query1.ParamByName('DateAdded').AsDateTime := Now;
    Query1.ParamByName('OwnerFirstName').AsString := Request.ContentFields.Values['txtOwnerFirstName'];
    Query1.ParamByName('OwnerLastName').AsString := Request.ContentFields.Values['txtOwnerLastName'];
    Query1.ParamByName('EmailAddress').AsString := Request.ContentFields.Values['txtEmailAddress'];
    Query1.ExecSQL;
    { If every thing went fine, we want to show the user the links page, so he
      can see the link he just added
    }
    Response.SendRedirect(Request.ScriptName   '/ShowDelphiLinks');
  except
    on E : Exception do
      Response.Content := Format('<html><head><title>Error Posting Inforamtion</title>'  
        '</head><body><H1>%s</H1></body></html>',
        [E.Message]);
  end;
end;

If the data was posted successfully to the table then we want to show a list of links in our database. This line will redirect the browser to the action in our ISAPI DLL that can do that. We have yet to create that action and the code for it. We'll do that soon.

NMHTTP1 is a NetMaster's HTTP component. We use this component to try and retrieve the web site the user gave us. If the HTTP component can not find the URL it fires the OnInvalidHost event. In this event we set the URLValid variable to false, indicating that the URL was not found. If we find the URL, we set this variable to true.

Those using Indy (Indy aka Winshoes 8 can be downloaded from the Indy Web Site) can use the IdHTTP component for this function like this:

function TWebMod1.URLIsValid(URL : string) : Boolean;
begin
 try
   { If the URL is not found an exception will be rasied }
   IdHTTP1.Get(URL);
   Result := True;
 except
  Result := False;
 end;
end;

Before we go onto create the waShowDelphiLinks action item let's briefly discuss the difference between the GET method and the POST method. We have used the POST method here. If instead we used the GET method, the URL would change when you hit the submit button. The browser will add (to the path information) the information as parameters to send back to the server.

http://ServerName/scripts/AddLink.dll/PostLinkInfo?txtLinkURL=http://mywebsite.com&txtTitle=My%20Site%20Title&txtDescription=My%20Sites%20description

Your application accesses the data from the Request object’s "QueryFields" property rather than its "ContentFields" property, extracting the values from Request.QueryFields in the same manner as Request.ContentFields since both properties are TStrings derivatives.

The advantage of the GET method is that it does not require a form to send information back to your application. You can simply append the value you need to send, to the end of the URL. In the POST method, the information is captured from the form and sent as a separate stream. Of course you may find that the POST method is advantageous because the information does not appear in the address box of the browser. Additionally, the POST method can handle much greater volumes of information.

We can do this in two ways. The simpler way is to use the TQueryTableProducer component that comes with Delphi. The second way is to do the work manually. For now, we'll take the manual approach

To display the information, which in our example is links to other sites, we will create a new Action item and call it waShowDelphiLinks and set its PathInfo property to /ShowDelphiLinks.

Drop another TQuery component on the web data module. Set its DatabaseName property to DELPHILINKS and its SQL property to:

procedure TWebMod1.WebMod1waShowDelphiLinksAction(Sender: TObject;
  Request: TWebRequest; Response: TWebResponse; var Handled: Boolean);
begin
  with Query2 do
  begin
    Open;
    Response.Content :=
      '<html>'   NewLine  
      '  <head>'   NewLine  
      '    <title>Delphi Links People have added</title>'   NewLine  
      '  </head>'   NewLine  
      '<body>'   NewLine  
      { The Image is in a sub folder of your web server's root folder called "Images"
        for exmaple C:\inetpub\wwwroot\images\ }
      '<img src="/Images/DelphiLinksHead.gif">'   NewLine  
      '<a href="'   Request.ScriptName  
        '/AddLinkPage"><img alt="Add a link to your web site from here" border="0" src="'  
        '/Images/AddLink.gif"></a>'   NewLine  
      '<p/><hr/>'   NewLine  
      '<table>'   NewLine;

    while not Eof do
    begin
      Response.Content := Response.Content   Format('<tr>%s<td><table><tr><td><a href="%s"><b>%s</b></A></td>%s'  
      '<td><b>Posted By:</b>%s</td>'  
      '%s<td><b>Date Posted:</b>%s</td></tr></table>%s<b>Description:</b><br>%s%s</td>%s</tr><tr><td><hr/></td></tr>',
        [NewLine,
         FieldByName('LinkURL').AsString,
         FieldByName('Title').AsString,
         NewLine,
         FieldByName('OwnerFirstName').AsString   ' '   FieldByName('OwnerLastName').AsString,
         NewLine,
         FieldByName('DateAdded').AsString,
         NewLine,
         FieldByName('Description').AsString,
         NewLine,
         NewLine]);
      Next;
    end; { while not Eof }
    Close;
    Response.Content := Response.Content  
      '</table>'   NewLine  
      '</body>'   NewLine  
      '</html>';
  end;
end;

      '<img src="/Images/DelphiLinksHead.gif">'   NewLine  
      '<a href="'   Request.ScriptName  
        '/AddLinkPage"><img alt="Add a link to your web site from here" border="0" src="'  
        '/Images/AddLink.gif"></a>'   NewLine

We've referenced 2 images here. These images should be in a sub folder of your web server's root folder. The name of the sub folder should be Images. You can call this folder anything you like but be sure to change the code above to match your folder's name.

It is a good idea to keep images in a separate folder, rather than in the root folder. It's easier to manage your web sites that way. The second image is hyper-linked. This link will direct the user to our Data Entry screen.

Also notice the line with the Format procedure. This line of code generates a string that looks something like this:

In the browser this will be rendered as words - The Delphi Stop, in bold typeface as hyper-linked text. When one clicks on the hyperlink, the browser will direct you to the URL specified in the HREF attribute of the anchor <A> tag.

To see this page, the URL will be http://ServerName/scripts/AddLink.dll/ShowDelphiLinks.

Since we'd like our users to hit this page by default we'll set the Default property of this action item to True. This will automatically set all other action item's Default property to False. There can be one and only one default action in an ISAPI/CGI application.

If you have data in your table, then the generated HTML page should look like that shown in Figure 5.

There you have it -- an ISAPI/CGI that allows data entry into a database and then displays that information in an HTML page.

ISAPI Data Entry and Database Access

Comments have been Disabled for this post

Menus