How can I publish the contents of my DataPower 2 database on the web?
DataPower 2's powerful scripting language make it possible to combine a web 'template' page with the database contents to produce a web page (or pages) filled in with the data from the database.
This may sound complicated, but actually it's possible for a single script to interpret template web pages in such a way that you never actually need to alter the script, and can use the template web page to specify which fields are to go where, and which parts of the page are to be repeated to format the data into a table.
Essentially the script looks for special tags within the HTML and replaces them with fields from the current layout. It also repeats sections of the HTML for each body record, and can also repeat sections for each record of a subview, to produce even more powerful reporting.
Click here to download a Spark archive containing the Acorn example files.
Click here to download a zip archive (unpack using WinZip) containing the Windows example files.
Click here to download the script containing the ExportAsHTML subroutine (see below).
First, click here to download the script which you need to copy and paste into the global scripts for your database. To do that, edit the layout, choose File/Edit Scripts, copy the text from the script and paste it into the script window. [On an Acorn you can drag the text file into the editor window.]
Then, create a button (by creating a background frame, typing some text into it and giving it a slab border), select it and press Ctrl-T, then enter this script (or something similar):
You will need to create the web template file, possibly using an HTML editor to create the initial draft: but note that some editors can't handle the extra tags that you'll need to put in to indicate the various sections, so you may have to edit the final HTML by hand).
Wherever you want to substitute the value of a field into the HTML, put the name of the field (including the table name if it needs to be qualified) inside double-hash characters as follows:
for example, if you have a field called "Name", you would use:
Normally your web page will consist of a header section, followed by a table that will contain the database records, followed by a footer section.
In HTML, the syntax of a table is as follows:
<Table> <TR> <TD> First column </TD> <TD> Second column </TD> </TR> <TR> <TD> Row 1 col 1 </TD> <TD> Row 1 col 2 </TD> </TR> <TR> <TD> Row 2 col 1 </TD> <TD> Row 2 col 2 </TD> </TR> <TR> <TD> Row 3 col 1 </TD> <TD> Row 3 col 3 </TD> </TR> </Table>
So, <Table> and </Table> denote a table, <TR> and </TR> denote a row, and <TD> and </TD> indicate a cell within a row. Each of these is nested inside the other.
Normally you will want the top row of the table to contain the field names, followed by a separate row for each record.
To indicate the start and end of the "body" section (ie. that part of the HTML which is to be repeated for each record), the DataPower script looks for the following tags:
So, a simple table within a DataPower HTML template file would look like this:
<Table> <TR> <TD>Field1</TD> <TD>Field2</TD> </TR> <!----BodySection----> <TR> <TD>##Field1##</TD> <TD>##Field2##</TD> </TR> <!----/BodySection----> </Table>
Running the script will generate an output file that has the body section repeated as many times as there are records in the current table (or query), with the actual field values for each record substituted in place of the ##Field## bits.
Using an HTML editor (or doing it by hand), it's pretty easy to alter the background colour and text style to make the field headings stand out from the body section, and to alter the table border thickness etc. etc.
Where it really gets interesting is where you want to export data from a layout that contains one or more subviews. Due to the nature of the table mechanism in HTML, the HTML export script becomes extremely powerful, as you are allowed to nest tables within tables, etc.
Exporting the contents of a subview is similar to exporting body records: the start and end of the section is indicated by these tags:
The subview name is actually the name of the table it's attached to (it appears at the bottom-left of the subview).
You can either nest the subview body section within the normal body section, so that you get a single HTML page containing a whole set of tables (one per master record), or you can alter the script so that it generates a separate HTML page for each main record (with filenames that depend on some field within the main record).
The all-one-one template would look like this:
<!----BodySection----> <P>Record for ##Name##</P> <Table> <TR> <TD>Field1</TD> <TD>Field2</TD> </TR> <!----BodySection"SubViewName"----> <TR> <TD>##Field1##</TD> <TD>##Field2##</TD> </TR> <!----/BodySection"SubViewName"----> </Table> <!----/BodySection---->
To export as separate HTML pages, you'd use an approach like this:
On ClickLeft Dim r as recordset, ok as boolean, name as text r = CurrentRecset ok = r.MoveFirst While ok name = r.Field("Name").GetValue ExportAsHTML("MyWebTemplateName","WebFiles."+name+"/htm") ok = r.MoveNext EndWhile ok = r.MoveFirst // go back to the top
and the template file would look like this:
<P>Record for ##Name##</P> <Table> <TR> <TD>Field1 </TD> <TD>Field2 </TD> </TR> <!----BodySection"SubViewName"----> <TR> <TD>##Field1##</TD> <TD>##Field2##</TD> </TR> <!----/BodySection"SubViewName"----> </Table>
OK, so now you know how to export the contents of a DataPower layout to a single HTML file, or a set of files, using an HTML template to control the appearance of the output.
However, since DataPower 2 is a relational database manager, the likelihood is that you have quite a lot of separate tables containing interesting stuff that you'd like to export to the web and allow the user to automatically click on various links to go between the pages.
This is actually quite easy to achieve, since you can use formula fields to generate links from one page to another relatively easily.
Let's say you've exported a set of invoices from one layout, where each invoice is on a separate page, with an HTML table on each page containing the invoice items. The filename of each invoice's HTML page is given by its invoice number with .htm appended (or /htm on an Acorn).
Suppose you also want to export a set of pages, one per customer, each with a list of the invoices relating to that customer. The customer filenames are derived from the customer ID (a bit easier than using their name, as the name could contain all sorts of nasty characters).
Now you want to have links from each invoice to the customer's page, and from each customer to all the individual invoices for that customer (not to mention links from the invoice items to the products).
To do the links from the invoices to the customer pages, all you have to do is to add a formula field called 'CustomerLink' to the customers table:
This will result in something like this (on a typical record):
<A HREF="1234.htm">Fred Bloggs</A>
which, when substituted into your HTML template file (where the ##CustomerLink## sequence appears) will produce the desired link to the customer page.
To do the links from the customer's invoice list to the individual invoices, you need to add a formula field to the 'Invoice Items' table called 'InvoiceLink':
"<A HREF="""+Str$('Invoice Number')+".htm"">"+'Invoice Number'+"</A>"
which will result in something like this (on a typical record):
and, once again, you just need to put the sequence ##InvoiceLink## in your web template file in the appropriate place.
Other types of links are also possible: for example, you could put an intra-page anchor at the start of each body record (if you'd exported them all to a single page), and link to them in a similar way to the above.
It's not alway necessary to use a formula field for this - in the above example, where the invoice number is used for the filename as well, you could just put this in your HTML template file:
<A HREF="##Invoice Number##.htm">##Invoice Number##</A>
Back to DataPower 2 Documentation Page
Back to DataPower 2 Home Page