WEBBASE INTERFACE AND ODBC CONCEPTS

More Information About WebBase

http://www.webbase.com/

Peter Thorsen, Sales Representative
1-800-888-8670
E-mail: Peter.Thorsen@expertelligence.com

An Example of WebBase Programming

 < FORM METHOD="get" ACTION="http://www.webbase.com/docs/webbase/examples/getname.htf"> 
Please enter the name to search for:  < INPUT NAME="name" SIZE=15 VALUE="Denny"> 
 < INPUT TYPE=SUBMIT value="Enter">  < /FORM> 

If you click the ENTER button, this will send a "GET" request to the server that looks like:

http://www.WebBase.com/docs/webbase/examples/getname.htf?name=Denny

 < HTML> ;
 < HEAD> ;
 { sql to answers source 'Primary' user 'Administrator' password 'Secret' max 25  } 
SELECT * FROM Examples WHERE Name LIKE '% { name } %'
 { /sql } 

 { if 0 answers size = } 
 < TITLE> ; I'm sorry!  < /TITLE> ; 
 < /HEAD> ;
 < BODY> ;
 < H2> ; Search results for:  { %search% }   < /H2> ;
 < HR SIZE=8> ;
 < H2> ; I'm sorry, I could not find any records that match in the database. < /H2> ;
 { else } 
 < TITLE>  WebBase Demo Results  < /TITLE> ; 
 < /HEAD> ;
 < BODY> ;
 < H2> ; Search results for:  { %search% }   < /H2> ;
 { forRow aRow on answers }  < HR> ; < PRE> ;
       Name:  < B> ; { Name }  < /B> ;
    Company:  < B> ; { Company }  < /B> ;
       City:  < B> ; { City }  < /B> ;
      State:  < B> ; { State }  < /B> ;
   Zip Code:  < B> ; { Zip }  < /B> ;
         Ph:  < B> ; { Phone }  < /B> ;
 < /PRE> ; { /forRow } 
 { /if } 
 < P> ;
 < /BODY> ; 
 < /HTML> ;  

The WebBase macro language is intermixed with the HTML language. To avoid confusion these WebBase macros and tags are enclosed in curly braces. WebBase will dynamically interpret these macros and tags to generate the presentation.

WebBase automatically creates local variables for each of the command line arguments sent by the browser. Any WebBase variable names specified in the htf file within curly braces, e.g., {name}, are automatically replaced by the value of the variable.

The first WebBase macro encountered is an sql macro. The 'source' tells WebBase where data are to be found. WebBase then replaces { name } with 'Denny' and performs the SQL SELECT as directed from the 'Examples' table of the 'Primary' ODBC source. This will result in zero to N records (max of 25) returned to variable { answers } .

The { if 0 answers size = } tests the size of the answers variable and returns the HTML immediately following the { if } if the test is true (zero answers) and returns the HTML immediately following the { else } if one or more answers exist. The expression { if 0 answers size = } is in reverse Polish notation.

In the { else } clause we loop on the answers with a { forRow aRow on answers } with the variable aRow taking on each of the returned results in turn. The fields from the returned record, { Name } , { Company } , etc. will be substituted where requested.

A More Complex Example

How the WebBase Program Works

The form Default.htf is the main menu. The following WebBase tags and macros are used: (For detailed discussion of macros, read Chapter 8 of WebBase User's Guide)

Comment Tags let the programmer create comments within the Web pages that will be invisible to the web browser. There are two types of comments: the {comment} and closing tag, which will NOT limit the comment, and the {! ... !} commment. You cannot put WebBase tags inside the {! ... !} comment.

{insert filename} lets you take a file from the server and place its contents within the current WebBase file. This is very much like the #include filename in C and C++.

{BackColor} is a user defined variable. It is defined in the file default.hti. It contains the color to be used as background. Since it is defined in defaults.hti you only need to change it in there and any page that calls defaults.hti and uses BackColor will change.

{LinkColor} is similar to BackColor, but this is the link color.

{VLinkColor} is similar to BackColor, but this is the visited link color.

The Default.htf form looks like this:

{insert 'defaults.hti'}	{! insert is like the include in C and C++ !}	

 < HTML> 

 < TITLE> Student Information System < /TITLE> 

{! The variables BackColor, LinkColor, and VLinkColor are defined in defaults.hti !}
 < BODY BGCOLOR={BackColor} LINK={LinkColor} VLINK={VLinkColor}> 

 < CENTER>  < H1> Student Information System < /H1>  < /CENTER> 

 < HR SIZE=2>  < !---------------------------------------------------------> 

 < h2> Select one of the queries: < /h2> 
 < OL> 
   < LI>  < A HREF="Exercise5.htf?Item=A"> List the name of students who have
    completed any course module. < /A> 
   < LI>  < A HREF="Exercise5.htf?Item=B"> List the name of students who have
    completed course module A1. < /A> 
   < LI>  < A HREF="Exercise5.htf?Item=C"> List the name of students who have
    completed at least two course modules. < /A> 
   < LI>  < A HREF="Exercise5.htf?Item=D"> List the name of students who have
   who have completed any course module during the period 1/1/90 to 6/30/90. < /A> 
 < /OL> 

{insert 'Footer.hti'}  {! Inserts The generic footer for all pages !}

 < /BODY> 
 < /HTML> 

The database retrieval and presentation are done in the WebBase form exercise5.htf. Again we will first explain the WebBase macros and tags used in this form:

{case 'variable'} is the Select clause for WebBase. Takes a variable and matches to one of the values. If {match 'value'} do what follows. {otherwise} if nothing matches then do otherwise. {/case} is end of the case macro.

{set 'variable' 'expression'} is the assignment operation in WebBase. The expression must be in RPN Reverse Polish Notation. This will also create variables if they aren't already created.

{if 'expression'} is the if clause for WebBase. The expression must be in reverse polish notation. {/if} ends the if macro.

{sql [to 'recSet'] source 'dataSourceName' [[user 'userid'] [password 'password']] SQLSstatement is the way to embedded sql into your WebBase pages. The 'recSet' is required for any queries that return records. {/sql} ends the SQL macro.

{forRow 'rec' on 'recSet'} is the for loop in WebBase. It works with the 'recSet' for the size of the 'recSet'. Inside the loop any variables in a record can be directly accessed. {/forRow} ends the loop.

{Item} is a variable defined by the search string in the URL. The format for the search string is http:// < host> / < file> ? < var> = < value> .

{SQLStatement} is the SELECT statement to be used in the query.

The form exercise5.htf looks like this:

{insert 'defaults.hti'}	{! insert is like the include in C and C++ !}

 < HTML> 

 < TITLE> Student Information System < /TITLE> 

{! The variables BackColor, LinkColor, and VLinkColor are defined in defaults.hti !}
 < BODY BGCOLOR={BackColor} LINK={LinkColor} VLINK={VLinkColor}> 

 < CENTER>  < H1> Student Information System < /H1> 

   < HR SIZE=2>  < !---------------------------------------------------------> 
		 {! Note: how the variable was passed...   Through the command line !}
    {case Item}  {! Determines which Item was selected and queries on that item !}
      {match 'A'}
         < H3> List the name of students who have completed any course module < /H3> 
        {set SQLStatement 'SELECT DISTINCT NAME FROM Student WHERE COURSE_MOD is Not null'}
      {match 'B'}
         < H3> List the name of students who have completed course module A1 < /H3> 
        {set SQLStatement 'SELECT DISTINCT NAME FROM Student WHERE COURSE_MOD =''A1'''}
      {match 'C'}
         < H3> List the name of students who have completed at least two course modules < /H3> 
        {set SQLStatement 'SELECT DISTINCT NAME FROM Student AS A WHERE 2  < = (SELECT COUNT(*) FROM Student B Where A.SSN=B.SSN)'}
      {match 'D'}
         < H3> List the name of students who have completed any course module during the period 1/1/90 to 6/30/90 < /H3> 
        {set SQLStatement 'SELECT DISTINCT NAME FROM Student WHERE COMP_DATE> =#1/1/90# AND COMP_DATE < =#6/30/90# AND COURSE_MOD is not Null'}
      {otherwise}	{! If the page was opened without using the menu page !}
         < H3> Nothing was selected from the menu. < /H2> 
        {set SQLStatement ''}
    {/case}

   < HR SIZE=2>  < !---------------------------------------------------------> 

  {if SQLStatement '' = not}   {! If the SQLStatement is not '' Then !}
    {sql to recs source 'Student'}	{! The sql- Record Set variable "recs"      !}
      {SQLStatement}  			{!          The Data Source 'Student'       !}
    {/sql}				{!          The SQL Statement "SQLStatement !}

     < TABLE BORDER=1>  < TR>  < TH> Student`s Name < /TH>  < /TR> 
    {forRow curRec on recs}		{! Output each row of the record set !}
       < TR>  < TD> {NAME} < /TD>  < /TR> 
    {/forRow}
     < /TABLE> 
  {/if}
   < BR> 
   < A HREF="default.htf"> Try again. < /A>  < BR> 
 < /CENTER>  
{insert 'Footer.hti'}  {! Inserts The generic footer for all pages !}

 < /BODY> 
 < /HTML> 

In the form Defaults.hti, {%output%} is a Global Variable that tells WebBase whether it should be outputting data to the browser. The form looks like this:

{set %output% false}	{! %output% is used to display information to the browser !}
{set BackColor "#FFFFFF"}  {! These are user defined variables "Global Like" !}
{set LinkColor "#8C0029"}
{set VLinkColor "#8C0029"}
{set %output% true}

Finally, the form Footer.hti generates a common footnote. No WebBase Macros or variables used or defined in this form. The form looks like this.

 < P> 
 < HR>  < !----------------------------------------------------------------> 
 < CENTER>  < FONT SIZE="-1"> 
	Copyright © 1997 ksi.edu All rights reserved.
 < /FONT>  < /CENTER> 
 < HR>  < !----------------------------------------------------------------> 
 < TABLE BORDER=0 WIDTH=100%> 
   < TR>  < TD> 
    Questions can be addressed to the Webmaster -- 
     < I>  < A HREF="mailto:ksiwww@ksi.edu"> ksiwww@ksi.edu < /A>  < /I> 
   < /TD>  < TD ALIGN=RIGHT> 
    Last modified:  < I> 10/29/1997 < /I> 
   < /TD>  < /TR> 
 < /TABLE> 

Open Database Connectivity

The ODBC driver is the interface program via which the end-user has access to the database. Microsoft provides a set of ODBC drivers on the web site. The sql macro within the WebBase form is used to generate a database query. WebBase translates this query into an ODBC function call and pass it to the ODBC driver. The driver accepts the ODBC request, translates it into internal format recognizable by the specific database system (e.g., ODBC to Microsoft Access), manages the communications with the database itself, and provides the results back to WebBase. WebBase in turn completes the processing of the results which are made available in the WebBase form for subsequent use.

The advantage of this approach is that it allows WebBase to support any ODBC compliant database without the knowledge about the specifics of a particular DBMS. Therefore it is possible to retrieve information from a Microsoft Access database and use it to generate a new record in an Oracle database. In the future, if the Oracle database is replaced by another DBMS, no change in the WebBase forms are required.

In order for WebBase to access a database, an ODBC source for that database must be created. An ODBC source assigns a name and optionally a username and password to a particular database file. This ODBC source name, username and password are then used by WebBase to access the database. A data source must be created for EACH database that you will be accessing using WebBase.

Programming Tips