Using WebSnap to output SQL Server database data

July 2, 2001
Update August 8, 2001: download WebSnap PDF from Borland

Delphi 6 Enterprise
SQL Server 7.0 or 2000

If you are using Delphi 6 Enterprise, the new web application development infrastructure WebSnap (sometimes incorrectly written "Web Snap") is certainly of interest to you. WebSnap extends the old Web Broker architecture with additional components and scripting support. Read on to learn how to write your fist database-aware WebSnap application.

There is already lots of material about WebSnap on the web. For instance, John Kaster's introduction at the Community Site is very worth reading (there are many others as well). However, here at WhirlWater.com we've found that many of these demos try to do too much for a beginner; the purpose of this article is to keep (simple) things simple.

To start developing a WebSnap application, first open up the New Items dialog box (using File | New | Other�) and navigate to the WebSnap tabsheet. Here, click the WebSnap Application icon and click OK. Alternatively, you could first right-click Delphi's Toolbar, and activate the by-default-hidden Internet toolbar (on the right). Clicking the leftmost button on this toolbar does the same as using the dialog box.

Either way you choose to do, you will see a dialog box like the following. Our purpose is to create a WebSnap application that lists the Products table of the Northwind example database in SQL Server, so choose the settings as follows:

The Server Type (topmost setting) should be set to "Web App Debugger executable". You will shortly learn what this means.

Adding components

Once you have clicked OK in the previously shown New WebSnap Application dialog box, you should see a web module and an empty in front of you in the IDE. The form is included so that you can see more easily when your application is running and additionally close it if needed. The web module should contain many colorful components, but you need to add two more.

Firstly, add a TDataSetAdapter component from the WebSnap component palette page to the web module.

Then, add a TADOQuery component from the ADO palette page to the form. The web module should now look something like this:

You need to connect the TDataSetAdapter and TADOQuery components together. Do this by clicking the TDataSetAdapter component and go to the Object Inspector. Select ADOQuery1 as the value of the DataSet property. Note how a small plus sign appears on the left of the property name. Click it, and you will see properties of the query component (this is one of the new features in Delphi 6).

Navigate to the ConnectionString property, and click the small ellipsis ("�") button. Select the SQL Server OLE DB driver and click Next. You should see a dialog box a page like the following. Fill it as shown on the picture, except be sure to change the server name to something meaningful to your setup (EMERALD is the name of the WhirlWater.com database server).

Once done, click the Test Connection button to see if everything is OK, and finally click OK to close the dialog box. The ConnectionString property should now say something like this:

Provider=SQLOLEDB.1;Persist Security Info=False;
User ID=sa;Initial Catalog=Northwind;Data Source=EMERALD

Back in the Object Inspector, go to the SQL property, and enter the following statement:

SELECT * FROM products

Collapse the in-line properties of the query by clicking the minus sign on the DataSet property, and click the ellipsis button next to the Data property. A new window should open. Right-click the Fields tree root, and choose the Add All Fields command from the popup menu. The window should now look like this:

The purpose of an adapter component is to provide an object that can be manipulated in a script, sometimes called a web script.

Writing a custom script

Now, it is time to see what a script can do for you. Go to the Code Editor, and click on the tabs to select the web module .PAS file. You should see a definition of a TMainPage class, inherited from the TWebAppPageModule class.

Take a close look at the bottom of the Code Editor window. You should see a set of tabs, in addition to those you've accustomed to on the top of the editor window. Click the second bottom tab which reads something like "Unit2.html" or "ProductListAPM.html" (APM stands for "Application Page Module") if you have already downloaded the example application.

When you click the minimal tab, the Code Editor displays a HTML page with script code inside <% and %> tags. These tags are for separating script code from normal HTML code. You should see many code lines which probably don't make sense at first. Instead, replace all the code you see with the following:

<HTML>
<HEAD>
<TITLE><%= Application.Title %></TITLE>
</HEAD>
<BODY>
<H1><%= Application.Title %></H1>

<H2>Products</H2>

<TABLE BORDER="1">
  <TR>
    <TD WIDTH="50"><B>Row</B></TD>
    <TD><B>Product Name</B></TD>
  </TR><%
var Row = 1;
var Recs = new Enumerator(DataSetAdapter1.Records);
while (!Recs.atEnd()) {
  %>
  <TR>
    <TD WIDTH="50"><%= Row %></TD>
    <TD><%= DataSetAdapter1.ProductName.DisplayText %></TD>
  </TR><%
    Recs.moveNext();
    Row = Row+1;
}
%>
</TABLE>

</BODY>
</HTML>

Here, the first script code line says:

<%= Application.Title %>

This means that between the TITLE HTML tags you want to display the title of the application. Application is a built-in class, of which there unfortunately isn't too much documentation available. However, Borland has just started to publish some documentation at the Community site.

To select the application title, modify the ApplicationTitle property of the TApplicationAdapter component on your web module. Next, the script code starts to do something more serious. First, it initializes a Row variable, and then a Recs parameter. It uses the DataSetAdapter1 component that you dropped on the web module to create an enumerator for all rows of the connected dataset (your ADO query). Then, it enters a simple loop until the end of the result set is hit. This is tested with the atEnd method of the enumerator.

Note that all web script code is case sensitive, so it is not the same to say Application.Title or APPLICATION.TITLE as you've used to doing in Delphi. This is JScript. VBScript is also supported, see for example the ScriptEngine property of the TPageProducer component.

Testing the application

Have going through all this trouble isn't worth it if you cannot see your application running. Firstly, since we are developing a Web App Debugger application (remember the very first dialog box!), you need to run once your application by pressing F9 in the IDE. This registers your application as a COM server. Note that it is a good idea to save your application before running it, since this allows you to change at least the project name to something more meaningful than Project3 or whatever.

The Web App Debugger is a very simple HTTP server that is specially built to ease debugging and development of WebSnap applications. To start it, choose the Tools | Web App Debugger menu command in the IDE. This should start the Web App Debugger executable:

Click the Start button, and the URL activates on the main form. Click it, and the debugger will open your browser on a special "launch" page. You should see your project listed. Click it, and then click GO. If everything goes right, entries should be added to your Web App Debugger's Log page, and your browser should display the data on the SQL Server Northwind database table Products:

Cool, ain't it?

If it didn't go right, make sure you have saved, compiled and run your WebSnap application from the IDE at least once. You don't need to run the application from the IDE while experimenting with your browser as long as Web App Debugger is running. Also, it is interesting to note that you don't have to recompile your application if you want to change the script. Simply edit it in Delphi, save, and refresh the page in the browser!

Download the example code

Download usingwebsnaptooutputsqldata.zip (433 kB) which contains the sample ProductList WebSnap application developed in this article. Please note that the sample application will require Delphi 6 Enterprise as well as a database server. By default, Microsoft SQL Server 2000 is used.

* * *

Need consulting help to develop your WebSnap applications? Let us know!