Display your data on the Web

by Dr. Peter Wayne

With a little ingenuity you can publish your Alpha Five tables in a web-compatible format.

Alpha Five version 5 will allow you to generate reports in HTML, but in the meantime you may still have a need to put your data up in a web-compatible format. There are at least 3 good reasons for wanting to do so:

You may want to display your data on the internet
You may want to send your table to someone who doesn't have Alpha Five, or maybe not even a Windows PC. Any computer user with a browser can view your data if it's in standard HTML format. You can send your table to an iMac owner or to the user of a Linux or Unix system.
HTML format is only ASCII characters without any binary characters, so the table can be changed, if necessary, with only a text editor.

Crash course in HTML

If you've been in a cave the last few years, HTML, or HyperText Markup Language, is the basic text formatting language for the Web browsers. Markup languages are nearly as old as Gutenberg, and in the simplest conceptualization are nothing more than printer's marks to guide typesetting. HTML is a markup language in which most formatting commands are given in pairs. For example, the command to display text in boldface type is given by enclosing the selected text in a <b>-</b> pair, e.g., <b>in HTML, this text would be in boldface</b>.

We're going to use a subset of HTML commands. Every HTML document begins with an <HTML>-</HTML> outermost containing pair. Within the outer container there are 2 sections, the HEAD and BODY. We are going to use the HTML table-formatting markers to display a browse of our records. The table-formatting markers we will use are:

TABLE - to define a table
THEAD - to define the header portion of a table
TBODY - to define the body of a table
TR - to define a table row
TH - to define a table cell formatted as a header cell, that is, in emphasized text with horizontal centering
TD - to define a regular table cell, that is, normal text with left alignment
CAPTION - to define a table caption

We will also use a few alignment specifications so that numeric fields will be right-aligned and other fields left-aligned, and a few table parameters to set a border and cell spacing. Remember, markers occur in pairs. Alignment specifications are not paired, as you will see in the scripts.

Pseudocode

As is often the case in these articles, it's a good idea to sketch out in English, or pseudocode, the logic of the script. What we'll do is:

select a table
open an external text file with an .html extension
write the initial HTML code to the file to define the HEAD section and begin the BODY
begin to write a TABLE to the file
write the field names as the THEAD row
begin the TBODY section
read each record and write each one as a separate row. Format each cell depending on the data type of the cell
write the terminating markers for TBODY, TABLE, BODY, and HTML
close the file

First version

And here's the first version of the code:

''XBasic
dir_put(a5.get_path())
tblname=ui_get_file("Enter name of table","table(*.dbf)","","X")
if tblname="" then
	end 
end if
tblname=left(tblname,len(tblname)-4)  ' strip off .dbf extension   
if file.exists(tblname+".html") then
	response=ui_msg_box("Output file exists","Erase it?",\
	UI_ATTENTION_SYMBOL+UI_YES_NO+UI_FIRST_BUTTON_DEFAULT)
	if response=UI_NO_SELECTED then
		end
	else
		file.remove(tblname+".html") 
	end if
end if
t=table.open(tblname)
fp=file.create(tblname+".html",file_rw_exclusive)
fp.write_line("<HTML>")
fp.write_line("<TITLE>")
fp.write_line(tblname)
fp.write_line("</TITLE>")
fp.write_line("<BODY>")
fp.write_line("<TABLE BORDER=1 CELLPADDING=1 CELLSPACING=1>")
fp.write_line("<CAPTION ALIGN=\"TOP\">")
fp.write_line(tblname)
fp.write_line("</CAPTION>")
fp.write_line("<THEAD><TR>")
nfields=t.fields_get()
for i=1 to nfields
	fp.write_line("<TH>"+t.field_get(i).name_get()+"</TH>") 
next
fp.write_line("</TR></THEAD>")
fp.write_line("<TBODY>")
t.fetch_first()  ' first record in table
while .not. t.fetch_eof() 
	fp.write_line("<TR>")
	for i=1 to nfields
		ftype=t.field_get(i).type_get()
		f=t.field_get(i)
		select
			case f.is_blank()=.t.
				dataval="<TD>&nbsp;"
			case ftype="C"
				dataval="<TD>"+f.value_get()
			case ftype="L"
				dataval="<TD>"+if(f.value_get()=.t.,"T","F")
			case ftype="N"
				dataval="<TD ALIGN=\"RIGHT\">"+\
				alltrim(str(f.value_get(),\
				f.width_get(),f.dec_get()))
			case ftype="M"
				dataval="<TD>[MEMO]"
			case ftype="D"
				dataval="<TD>"+dtoc(f.value_get())
			case else
				dataval="<TD>[unknown data type]"
		end select 
		fp.write_line(dataval+"</TD>")
	next
	fp.write_line("</TR>")
	t.fetch_next()
end while
fp.write_line("</TBODY>")
fp.write_line("</TABLE>")
fp.write_line("</BODY>")
fp.write_line("</HTML>")
t.close()
fp.close()

Script 1. Write an Alpha table to an HTML table.

Notice that if the field is blank we output a special HTML code of & which translates to "non-printing blank space."

I ran the above script on the Invoice_items.dbf in the /Samples/Invoice/Invoice.adb table provided with Alpha Five. The results are here.

Improved version

Script 1 outputs the results of an entire table. What if you want to display only part of the table, showing the results of the latest query or active index? Here we have to use a trick to get the last index used by the table. We can do that in interactive mode by using the table.reset() method to reset the interactive session's current table to the table of interest. The revised script is almost the same as Script 1:

''XBasic
dir_put(a5.get_path())
tblname=ui_get_file("Enter name of table","table(*.dbf)","","X")
if tblname="" then
	end 
end if
tblname=left(tblname,len(tblname)-4)  ' strip off .dbf extension   
if file.exists(tblname+".html") then
	response=ui_msg_box("Output file exists","Erase it?",\
	UI_ATTENTION_SYMBOL+UI_YES_NO+UI_FIRST_BUTTON_DEFAULT)
	if response=UI_NO_SELECTED then
		end
	else
		file.remove(tblname+".html") 
	end if
end if
't=table.open(tblname)
t=table.reset(tblname)  
fp=file.create(tblname+".html",file_rw_exclusive)
fp.write_line("<HTML>")
fp.write_line("<TITLE>")
fp.write_line(tblname)
fp.write_line("</TITLE>")
fp.write_line("<BODY>")
fp.write_line("<TABLE BORDER=1 CELLPADDING=1 CELLSPACING=1>")
fp.write_line("<CAPTION ALIGN=\"TOP\">")
fp.write_line(tblname)
fp.write_line("</CAPTION>")
fp.write_line("<THEAD><TR>")
nfields=t.fields_get()
for i=1 to nfields
	fp.write_line("<TH>"+t.field_get(i).name_get()+"</TH>") 
next
fp.write_line("</TR></THEAD>")
fp.write_line("<TBODY>")
t.fetch_first()  ' first record in table
while .not. t.fetch_eof() 
	fp.write_line("<TR>")
	for i=1 to nfields
		ftype=t.field_get(i).type_get()
		f=t.field_get(i)
		select
			case f.is_blank()=.t.
				dataval="<TD>&nbsp;"
			case ftype="C"
				dataval="<TD>"+f.value_get()
			case ftype="L"
				dataval="<TD>"+if(f.value_get()=.t.,"T","F")
			case ftype="N"
				dataval="<TD ALIGN=\"RIGHT\">"+\
				alltrim(str(f.value_get(),\
				f.width_get(),f.dec_get()))
			case ftype="M"
				dataval="<TD>[MEMO]"
			case ftype="D"
				dataval="<TD>"+dtoc(f.value_get())
			case else
				dataval="<TD>[unknown data type]"
		end select 
		fp.write_line(dataval+"</TD>")
	next
	fp.write_line("</TR>")
	t.fetch_next()
end while
fp.write_line("</TBODY>")
fp.write_line("</TABLE>")
fp.write_line("</BODY>")
fp.write_line("</HTML>")
' t.close()
fp.close()

Script 2. Here we use table.reset() instead of table.open() in the final Publish to web script.

I tested Script 2 - Publish to web by querying customer.dbf for all customers whose lastname>"P". I also ordered the results in lastname order, and then, still in interactive mode, I went to the Control Panel and ran the Publish to web script. You can see the results here.

And now, to make it really easy...

We'd like to make the Publish to web script available to our end-users on a form. Unfortunately, the table.reset() method is only available through interactive mode, or at least, that's what the Alpha Five documentation says. But a close reading of the specifications states that table.reset() method changes the current table for a session. Aha! We can't change the current table for a form, but we can launch a "form-less" session with 2 Xbasic methods, script_spawn() and script_schedule(). Both of these methods should begin a new Alpha Five session.

Now, I haven't been able to get script_spawn() to work at all, but script_schedule() runs like a charm. All you need do to publish a table to HTML format from any Alpha Five form is to create a button with this code:

script_schedule("Publish to web",totime(toseconds(time())+60,2,0),2))

This one line button script will schedule a new interactive session 60 seconds in the future (I had to choose 60 seconds to guarantee that it would be a new minute - the script_schedule() timer only ticks off minutes, not seconds.) One minute after the button is pressed, the ui_get_file() dialog will appear, asking for the name of the table to place into HTML.

We've only begun to explore the ways in which you can format data for the Web. Since the Publish to web script produces pure HTML, you can modify it to add explanatory text, to change the color, to add pictures, sound, or whatever your web-publishing instincts lead you to do. Or you can run the script as is. You're only limited by your energy and imagination!

4/28/99

Don't forget, we need your feedback to make this site better!

Return to home