Introduction

Welcome to the Studio JS 201 tutorial. (JS stands for JavaScript)

The Studio JS 201 tutorial is the first in a series of tutorials which take you step by step through writing an Omnis Studio ultra-thin web application. An ultra-thin web application does not use the Omnis Web Client plug-in. Users can interact with your Omnis Studio application using FireFox, Internet Explorer, or Safari... no plug-ins required! Functionality on the client side is provided using JavaScript. Page decoration (fonts, colors, borders, etc.) control on the client side is provided by CSS (Cascading Style Sheets).

I wrote my first Omnis Studio web app in 2003 for the Wingham Cemetery. I used the Omnis Web Client Plug-in. Initially the app seemed good, but I kept running into problems with glitches with the Omnis web client plug-ins between different versions of browsers. Also, each time the client upgraded or changed computers they had to go through the Omnis web client installation. This proved to be too difficult for low level computer users.

In 2005 I started tinkering around with PHP and JavaScript. I discovered that you can do some pretty amazing and powerful stuff with PHP and JavaScript! I started thinking about writing web apps using PHP & JavaScript... dropping Omnis Studio altogether. But I had numerous Omnis Studio applications running on LANs with runtime clients. I was also very familiar and productive with Omnis Studio. Writing and debugging code in Omnis Studio is a cake walk, compared to JavaScript. One character out of place in JavaScript and nothing runs.

The best thing in my situation was to make use of the code I'd already written in Omnis Studio and extend my applications to the web using the Omnis Web App Server, ultra-thin client, JavaScript, and CSS.

I rewrote the Wingham Cemetery app in 2006 using the Omnis Web App Server, ultra-thin client, JavaScript, and CSS. The results were very encouraging. Performance was excellent. The customer could access, update, and insert cemetery records from any computer, anywhere, anytime. No plug-ins, no delays. They loved it, and so did I.

Overview

There are several components to the ultra-thin client application you will be building in this tutorial.

1. The Omnis Studio library which contains the remote task classes.
2. Omnis Web App Server is the instance of Omnis Studio listening for messages from the CGI or Script. If it receives a request it instantiates the specified remote task and sends it a $construct message with the HTTP request parameters in a row variable.
3. The web server which receives standard HTTP requests. Port 80 is the default port for HTTP requests.
4. The CGI or Script which handles communication between the web browser and the Omnis Web App Server.
5. The user's web browser which displays web pages sent to it from the web server.
6. JavaScript which adds functionality to the web page and sends requests to the web server CGI or script.

The following communication takes place in the demo we will be building.

1. Using a web browser the user requests a static web page from the web server, searchcountries.htm
2. The webserver responds with the HTML which the web browser displays to the user.
3. The user fills in a search value and clicks the Search button on the web page.
4. The Search button calls a JavaScript function which checks to make sure a search value has been entered, and then sends the request to the web server CGI or script.
5. The request is processed and forwarded to the Omnis Web App Server which instantiates the specified remote task in the specified library and sends a $construct message with the HTTP parameters in a row variable.
6. The remote task processes the request building the HTML for a new page with the search results. The HMTL page is returned to the Omnis Web App Server which passes it back to the CGI or Script which passes it back to the web browser.
7. The web browser displays the HTML to the user.

Here is the finished product of what we are going to build in this tutorial.

Getting Started

The Studio JS 200 series tutorial assumes you have completed the Studio 100 series tutorials.

Completing the Studio 100 series tutorials is not mandatory, though it is highly recommended. If you are an experienced Omnis Studio developer you will be able to whip through the Studio 100 series tutorials quickly. If you are fairly new to Omnis Studio do not tackle the Studio JS 200 series tutorials before completing the Studio 100 series tutorials!

The first thing you need to do is get the localhost web server running on your computer, and the Omnis Web App Server working on your computer. Refer to your Omnis Studio documentation and to StudioTips -> Internet -> Web Server for information on getting this working.

Please do not email me with questions on how to get your localhost web server and the Omnis Web App Server working on your computer. Send your questions to the Omnis Dev mailing list or Omnis Tech Support. There are just too many variations of installations and possible problems for me to help you out. (The simplest solution I know of is to use Mac OS X.)

The next thing you need to do is download and install FireFox, if you don't already have it. At the time of writing this, FireFox is the best tool for testing and debugging JavaScript.

Once you have the Omnis Web App Server working on your local computer and FireFox installed you are ready to proceed with this tutorial.

We will begin with the Studio 100 series Contacts library. It already has schema classes, query classes, table classes, and a database with records.

ContactsWeb Library

To make sure that we are all starting from the same place, download the StudioJS201 zip file from studiotips.net.

Create Ping Remote Task

One of the first remote tasks I create in my web apps is an rtPing remote task. The rtPing remote class responds with a simple HTML page whenever it receives a message. This allows me to test communication from a web browser through the Omnis Web App Server to a remote task in my Omnis Studio library.

1. F2 Browser > Select ContactsWeb library.
2. Click New Class > Remote Task. Name the remote task rtPing.
3. Add a parameter, pParams, Row data type to the $construct method of the rtPing remote task.
4. Add the following code to the $construct method of rtPing.
   
   

   ; Copy the the params row parameter to a local variable.
   Calculate ParamsRow as pParams
   
   ; Write a message to the trace log.
   Calculate Mssg as con("Web App Ping received at ",jst(#D,'D:y-M-D H:N.S')," - ",$clib().$name,"/",$cmethod().$name)
   Send to trace log {[Mssg]}
   
   ; Return a ping message page.
   Do method retPingReplyHTMLContents Returns HTML
   Do method addHTTPContentHeader (HTML)
   Quit method HTML

   
   
   
   

   If you have StudioTips you can copy any of the code snips by clicking the Sample Code button which takes you to the actual code then copy and paste the code to your own method.

5. Add a retPingReplyHTMLContents method to rtPing with the following code:
   
   

   Begin text block
   Text: <html> (Carriage return,Linefeed)
   Text: <head> (Carriage return,Linefeed)
   Text: <title>Omnis Studio Web App Ping</title> (Carriage return,Linefeed)
   Text: </head> (Carriage return,Linefeed)
   Text: <body> (Carriage return,Linefeed)
   Text: <h3>Omnis Studio Web App Ping</h3> (Carriage return,Linefeed)
   Text: <p>Ping test communication successful!</p> (Carriage return,Linefeed)
   Text: <p>$clib().$name = [$clib().$name]</p> (Carriage return,Linefeed)
   Text: <p>$cclass().$name = [$cclass().$name]</p> (Carriage return,Linefeed)
   Text: <p>$ctask().$name = [$ctask().$name]</p> (Carriage return,Linefeed)
   Text: <p>$prefs.$serverport = [$prefs.$serverport]</p> (Carriage return,Linefeed)
   Text: </body> (Carriage return,Linefeed)
   Text: </html> (Carriage return,Linefeed)
   End text block
   Get text block HTML
   Quit method HTML

6. Add an addHTTPContentHeader method to rtPing
7. Add a parameter, pfHTML, Field reference type to the method.
8. Add the following code to the method.
   
   

   Calculate ContentLength as len(pfHTML)
   
   ; HTTP Content type header.
   Begin text block
   Text: Content-type: text/html (Carriage return,Linefeed)
   Text: Content-length: [ContentLength] (Carriage return,Linefeed)
   Text: (Carriage return,Linefeed)
   End text block
   Get text block HTTPHeaderText
   
   Calculate pfHTML as con(HTTPHeaderText,pfHTML)
   
   Quit method kTrue

Test Ping Remote Task

Open a web browser and enter the URL which applies to your localhost web server:

• Mac OS X - http://localhost/cgi-bin/nph-omniscgi?OmnisServer=5912&OmnisLibrary=ContactsWeb&OmnisClass=rtPing
• WinXP IIS - http://localhost/Script/omnisapi?OmnisServer=5912&OmnisLibrary=ContactsWeb&OmnisClass=rtPing

All going well the rtPing remote task will receive your request and respond to you with a new web page. If you don't get a response try putting a breakpoint in the $construct method of rtPing. If you do not hit the breakpoint when requesting the above specified URL in your web brower, there is likely a problem with your Omnis Web App Server setup.

You must get the rtPing remote task response working before proceeding any further.

HTTP Request URL Syntax

Take a moment to study the HTTP request URL which we sent to the web server.

http://localhost/cgi-bin/nph-omniscgi?OmnisServer=5912&OmnisLibrary=ContactsWeb&OmnisClass=rtPing

The URL breaks down into 2 parts:

1. The URL address - the first part up to the ? character.
2. The parameters - the part after the ? character.

The syntax of each parameter is simple. ParamName=Value

Each parameter is separated from the next by the & ampersand character. You can include up to 400 parameters in the URL which you send to the Omnis Web App Server.

The only required parameters are:

1. OmnisServer - the port which the Omnis Web App Server is listening on.
2. OmnisLibrary - the library containing the remote task we wish to instantiate.
3. OmnisClass - the name of the remote task we wish to instantiate.

The Omnis Web App Server parses the parameters and put them into a row variable which it passes as the first parameter to the $construct method of the specified remote task. The ParamName becomes the column name, and the Value is copied to the row. Each column is a kCharacter data type.

Once you know the syntax the Omnis Web App Server it really isn't that complicated. Understanding the syntax of the HTTP request URL is important because we will be using JavaScript to generate our own HTTP requests later on in the tutorial.

HTML Tools

Rather than put all of our code in methods of each remote task, we will put the general methods in an object class which we can call from different remote tasks. This may seem a bit complicated at first, but you will see the benefits later on as we add more remote tasks.

1. F2 Browser > select ContactsWeb library > New Class > Object Class.
2. Name the object class, oHTMLTools

Return HTML Template

We will need a method that returns an HTML page template.

1. Add a public method, $retHTMLPageTemplate to oHTMLTools.
2. Add the following code to the method:

Begin text block
Text: <html> (Carriage return,Linefeed)
Text: <head> (Carriage return,Linefeed)
Text: <title>##TITLE##</title> (Carriage return,Linefeed)
Text: </head> (Carriage return,Linefeed)
Text: <body> (Carriage return,Linefeed)
Text: (Carriage return,Linefeed)

Text: (Carriage return,Linefeed)

Text: <p>##BODY##</p> (Carriage return,Linefeed)

Text: (Carriage return,Linefeed)
Text: </body> (Carriage return,Linefeed)
Text: </html> (Carriage return,Linefeed)
End text block

Get text block HTML

Quit method HTML

##TITLE## and ##BODY## are placeholders which can be replaced by the sender using the replaceall() function. You will see this later on in the tutorial.

This is a very basic HTML page. For your real world application you would need to include numerous additional sections and tags.

Convert List to HTML Table

We will need a method than converts an Omnis Studio list into an HTML table.

1. Add a public method, $convertListToHTMLTable, to the oHTMLTools.
   
   
   

   If you have StudioTips you can copy any of the code snips by clicking the Sample Code button which takes you to the actual code, then copy and paste the code to your own method.

2. Add the following parameters to the method:
1. pfList - field reference
2. pbInclSelectCheckBoxes - booleann default kTrue
3. pCSVColNames - character
4. pCSVColHeadings - character
5. pPrimayKeyColName - character
3. Add the following class variables:
   
   1. kTableCloseTag - character - initial value '</table>'
   2. kTableDivCloseTag - character - init value '</td>'
   3. kTableDivOpenTag - character - init value '<td>'
   4. kTableOpenTag - character - initial value '<table>'
   5. kTableRowCloseTag - character - init value '</tr>'
   6. kTableRowOpenTag - character - init value '<tr>'

4. Add the following code to the method:
   

Calculate List as pfList

; Open the table
Calculate HTML as kTableOpenTag

; Open the table header group.
Calculate HTML as con(HTML,kCr,'<thead>')

Calculate HTML as con(HTML,kCr,kTableRowOpenTag)

; Add a checkbox column for selecting rows.
If pbInclSelectCheckBoxes
   Calculate HTML as con(HTML,kCr,'<th>','','</th>')
End If

; Add the header row columns.
Calculate String as pCSVColHeadings
While len(String)
   
   Calculate Text as strtok('String',',')
   Calculate HTML as con(HTML,kCr,'<th>',Text,'</th>')
   
End While

; Close the table header row
Calculate HTML as con(HTML,kCr,kTableRowCloseTag)

; Close the table header group.
Calculate HTML as con(HTML,kCr,'</thead>')

; Open the table body group.
Calculate HTML as con(HTML,kCr,'<tbody>')

; Add the list rows and columns.
For List.$line from 1 to List.$linecount step 1
   
   ; Open the row.
   Calculate HTML as con(HTML,kCr,kTableRowOpenTag)
   
   ; Add a checkbox column for selecting rows. The primary key is stored in the checkbox id attribute.
   If pbInclSelectCheckBoxes
      If List.0.$selected()
         Calculate SelectCheckBox as con('<input type="checkbox" name="selected" id="',List.[pPrimaryKeyColName],'" value="pkey" checked="true" />')
      Else
         ; Do not include a 'checked=' attribute. checked="false" or "0" still caused it to be checked.
         Calculate SelectCheckBox as con('<input type="checkbox" name="selected" id="',List.[pPrimaryKeyColName],'" value="pkey" />')
      End If
      Calculate HTML as con(HTML,kCr,kTableDivOpenTag,SelectCheckBox,kTableDivCloseTag)
   End If
   
   ; Add the columns.
   Calculate String as pCSVColNames
   While len(String)
      
      Calculate ColName as strtok('String',',')
      Calculate HTML as con(HTML,kCr,kTableDivOpenTag,List.[ColName],kTableDivCloseTag)
      
   End While
   
   ; Close the list row
   Calculate HTML as con(HTML,kCr,kTableRowCloseTag)
   
End For

; Close the table body group.
Calculate HTML as con(HTML,kCr,'</tbody>')

; Open the table footer group.
Calculate HTML as con(HTML,kCr,'<tfoot>')


; Close the table footer group.
Calculate HTML as con(HTML,kCr,'</tfoot>')

; Close the table.
Calculate HTML as con(HTML,kCr,kTableCloseTag)

Quit method HTML

Add HTTP Content Header

We will need a method than adds the Content-type and Content-length header to the HTML.

1. Add a public method, $addHTTPContentHeader, to oHTMLTools.
2. Add the following code to the method. (You can copy this code from the rtPing class which you created earlier.)

Calculate ContentLength as len(pfHTML)

; HTTP Content type header.
Begin text block
Text: Content-type: text/html (Carriage return,Linefeed)
Text: Content-length: [ContentLength] (Carriage return,Linefeed)
Text: (Carriage return,Linefeed)
End text block
Get text block HTTPHeaderText

Calculate pfHTML as con(HTTPHeaderText,pfHTML)

Quit method kTrue

Countries List

We are now ready to tackle creating a remote task that will fetch records from the database and return it as an HTML page.

The simplest table in our database is the Country table. We will begin with creating a remote task that will return a web page listing all of the countries in our database.

Create the remote task.

1. F2 Browser > select ContactsWeb library > New Class > Remote Task.
2. Name the remote task, rtCountryList.

Task Variables

The first problem we are going to run into is that the instance of rtCountryList opened by the Omnis Web App Server is not in scope with the Startup_Task instance. They live in two different worlds.

The Startup_Task has two task variables that we would like to access from our remote task instance:

1. dbsessionobj - The session which was opened with the database on startup.
2. errhndlr - The error handler which was initialized on startup.

Being able to access those Startup_Task variables from our remote task would save waste time opening a new session and initializing another error handler each time a remote task request came in.

To accomplish this we need to add a method to the Startup_Task.

1. Add a $getTaskVarRef method to the Startup_Task.
2. Add a Character parameter, pTaskVarName, to the method.
3. Add a Field reference parameter, pfrVar, to the method.
4. Add the following code to the method:

Set reference pfrVar to [pTaskVarName].$ref
Quit method kTrue

The $getTaskVarRef method sets a reference to the specified task variable.

To minimize code in the $construct method of rtCountryList we will use a private method to set the remote task task variables.

1. Add the following task variables to rtCountryList.
1. dbsessionobj - Item reference variable
2. errhndlr - Item reference variable
2. Add a private method, setTaskVars to rtCountryList.

3. Add the following code to the setTaskVars method.
   

Do $itasks.[$clib().$name].$getTaskVarRef('dbsessionobj',$ctask.dbsessionobj) Returns FlagOK
If FlagOK
   Do $itasks.[$clib().$name].$getTaskVarRef('errhndlr',errhndlr) Returns FlagOK
End If
Quit method FlagOK

$construct Method

We are finally ready to write the code in the $construct method of our rtCountryList remote task.

1. Add an Object type instance variable, ioHTMLTools, to the rtCountryList class.
2. Point ioHTMLTools to the oHTMLTools object class.
3. Add a Row type parameter, pParamsRow to the $construct method. (Do not attempt to make this a field reference variable! I discovered the hard way that Omnis Studio doesn't like that.)
4. Add the following code to the $construct method.
   
   

   ; Set the task variables.
   Do method setTaskVars Returns FlagOK
   If FlagOK
      
      ; Define a list variable using the 'tCountry' table class.
      Do List.$definefromsqlclass('tCountry')
      
      ; Set the session object in the list variable so that the SQL statements will be issued to that session's database.
      Do List.$sessionobject.$assign(dbsessionobj)
      
      ; Get all the records in the table.
      Do List.$getAllRecords() Returns FlagOK
      If FlagOK
         
         ; Prepare HTML table to return to the web browser.
         Calculate bInclCheckboxes as kTrue
         Calculate CSVColHeadings as 'Country Name'
         Calculate CSVColNames as 'CountryName'
         Calculate PKeyColName as List.$:PrimaryKeyColName
         Do ioHTMLTools.$convertListToHTMLTable(List,bInclCheckboxes,CSVColNames,CSVColHeadings,PKeyColName) Returns TableHTML
         
         ; Get an HTML page template.
         Do ioHTMLTools.$retHTMLPageTemplate() Returns HTML
         
         ; Replace the placeholders with content.
         Calculate HTML as replaceall(HTML,'##TITLE##','Countries')
         Calculate HTML as replaceall(HTML,'##BODY##',TableHTML)
         
         ; Add the HTTP content header.
         Do ioHTMLTools.$addHTTPContentHeader(HTML) Returns FlagOK
         
      End If
   End If
   
   If not(FlagOK)
      
      Breakpoint
      
   End If
   
   Quit method HTML

Test Countries Remote Task

We are ready to test the rtCountryList remote task.

Open a web browser and enter the URL which applies to your localhost web server:

• Mac OS X - http://localhost/cgi-bin/nph-omniscgi?OmnisServer=5912&OmnisLibrary=ContactsWeb&OmnisClass=rtCountryList
• WinXP IIS - http://localhost/cgi-bin/nph-omniscgi?OmnisServer=5912&OmnisLibrary=ContactsWeb&OmnisClass=rtCountryList

All going well the rtCountryList remote task will receive your request and respond to you with a new web page listing the countries in the database.

Web Error Handling

We put a Breakpoint near the end of the $construct method if FlagOK was false. Breakpoints, OK, Yes/No, No/Yes messages, and any modal prompts are a big no, no, with web related code. The reason is that the prompt will show up on the server and no response will be sent to the web browser. The Omnis Web App Server will be stuck and unable to responds to any further requests from other web browsers. You get the picture. Someone will have to go to the server, click the OK button on the dialog window before any new requests can be processed. Not a good thing!

Error Handler Method

The error handler has a $promptonceLastError method which opens a modal prompt. We need to add a method which passes back the last error message text, rather than opening a prompt.

1. F2 Browser > ContactsWeb > double-click oErrorHandler
2. Add a new method, $getonceLastError
3. Add the following parameters to the method
1. pfRetMssg - Field reference - Pass back the last error message.
2. pfRetMethod - Field reference - Pass back the method which logged the last error.
4. Add the following code to the method. (You can copy the code from $promptonceLastError and then modify it.)
   

; Only return the error message values the first time this method is called.
If iErrorsList.prompted=kFalse
   
   ; Set the prompted flag to true to avoid multiple prompts of the same error message.
   Calculate iErrorsList.prompted as kTrue
   
   ; Pass the error message text and method name to the sender.
   Calculate pfRetMssg as iErrorsList.errormssg
   Calculate pfRetMethod as con(iErrorsList.libname,'/',iErrorsList.classname,'/',iErrorsList.methodname)
   
End If
Quit method kTrue

HTML Tools Method

We will add a $retHTMLLastError method to oHTMLTools. This method will get the last error from the error handler, and then format it into an HTML page that is ready to return to the web browser via the Omnis Web App Server.

1. Add a method, $retLastErrorHTML, to oHTMLTools.
2. Add the following code to the method.

; Get the last error message logged with the error handler.
Do errhndlr.$getonceLastError(Mssg,Method) Returns FlagOK
If len(Mssg)=0
   Calculate Mssg as "An error occured, but was not logged to the error handler. Please notify the webmaster."
End If

Calculate Title as 'Error'

; Prepare the HTML for the body section of the web page.
Begin text block
Text: <h3>[Title]</h3> (Carriage return,Linefeed)
Text: (Carriage return,Linefeed)
Text: [Mssg] (Carriage return,Linefeed)
End text block
Get text block Body

; Prepare the HTML for a web page that displays the error.
Do $cinst.$retHTMLPageTemplate Returns HTML

Calculate HTML as replaceall(HTML,'##TITLE##',Title)
Calculate HTML as replaceall(HTML,'##BODY##',Body)

Do $cinst.$addHTTPContentHeader(HTML)

Quit method HTML

Fix Remote Task $construct

We can now replace the Breakpoint in the $construct method of rtCountryList with a call to oHTMLTools.

1. Go to the Breakpoint near the end of the $construct method of rtCountryList.
2. Replace it with a $retLastErrorHTML message to oHTMLTools. The end of the $construct method should look like this:
   
   

   If not(FlagOK)
      
      ; An error occurred. Get the last error as an HTML page.
      Do ioHTMLTools.$retLastErrorHTML() Returns HTML
      
   End If
   
   Quit method HTML

Search Countries Web Page

So far, we have been sending our HTTP requests to the web server by manually typing the URL in the web browser. Obviously that isn't a very user friendly way to send requests to the Omnis Web App Server.

We are ready to create a static web page that uses JavaScript to send a request to the Omnis Web App Server when the user clicks the Search button.

This tutorial is not intended to teach you everything you need to know about HTML and JavaScript. If you decide to develop an Omnis Studio ultra-thin web application you will need to purchase a couple of books on JavaScript, HTML, and CSS. Two books which I recommend are:

1. JavaScript Bible by Goodman, Morrison
2. HTML, XHTML, and CSS Bible by Pfaffenberger, Schafer, White, Karow.

You can buy these books online from Amazon.

You will need to use a text editor to create and edit your web pages, JavaScripts, and CSS.

If you are on the MacOS X platform get BBEdit if you don't already have it. It is the programmer's best friend for editing HTML, JavaScript, CSS, PHP, etc.

Create Search Page

1. Using a text editor create an HTML file with the following content. (You can copy and paste the HTML from here.)
   
   

   <html>
   <head>
   <title>Countries</title>
   </head>
   <body>
   <h3>Countries</h3>
   
   <p>Enter the first letter of a country and click the Search button</p>
   
   <form name="SearchForm" method="POST" action="http://localhost/cgi-bin/nph-omniscgi"> <!-- start SearchForm -->
   
   <input type="text" name="SearchValue" size="1"/>
   
   <input type="submit" value="Search" title="Click to search for the records." />
   
   <input type="hidden" name="OmnisServer" value="5912" />
   <input type="hidden" name="OmnisLibrary" value="ContactsWeb" />
   <input type="hidden" name="OmnisClass" value="rtCountryList" />
   
   </form> <!-- end SearchForm -->
   
   </body>
   </html>

2. Replace cgi-bin/nph-omniscgi with the appropriate cgi or script as applicable to your local host.
3. Save the text file as searchcountries.htm to your local host web server directory.
4. Open the FireFox web browser.
5. Load the web page. http://localhost/searchcountries.htm
6. Click the Search button. All going well the list of all the countries in the database will be returned to you.

Add $getWhere to tBase

We need to modify our Omnis Studio application to handle searching for records which begin with a certain value. The LIKE operator in SQL is used to search for records which begin with, contain, or end with, a certain value.

We need to add a $getSelect method to tBase to make it easy for use to pass in a WHERE clause and get back the records from the database.

1. Add the method, $getSelect, to tBase.
2. Add a Character type parameter pWhereText to the method.
3. Copy the code from $getAllRecords to the $getSelect method.
4. Modify the $getSelect method code so that it reads as follows:

; Prepare the ORDER BY text.
If len(pOrderBySQL)
   Calculate OrderBy as pOrderBySQL
Else
   Calculate OrderBy as $cinst.$:DefaultOrderBy
End If

Calculate SQLText as pWhereText
If pos("WHERE ",$cinst.$extraquerytext)
   Calculate SQLText as replace(SQLText,"WHERE ","AND ")
End If

; Prepare the SQL text to exclude the empty zero(0) primary key record.
Calculate ColName as $cinst.$:PrimaryKeyColName
If len(ColName)=0
   Calculate FlagOK as kFalse
Else
   
   If pos("WHERE ",$cinst.$extraquerytext)|len(SQLText)
      Calculate SQLText as con(SQLText," AND ",ColName," <> 0")
   Else
      Calculate SQLText as con("WHERE ",ColName," <> 0")
   End If
   
   Calculate SQLText as con(SQLText,' ',OrderBy)
   
   ; Select all the records in the table.
   Do $cinst.$select(SQLText) Returns FlagOK
   If not(FlagOK)
      Calculate Mssg as con("Flag false after $cinst.$select(",SQLText,") for the $sqlclassname ",$cinst.$sqlclassname,".")
      Do errhndlr.$logError($cmethod,Mssg)
   Else
      
      ; Fetch all the records in the table.
      Do $cinst.$fetch(kFetchAll) Returns FetchStatus
      If not(FetchStatus)
         Calculate FlagOK as kFalse
         Calculate Mssg as con("Flag false after $cinst.$fetch(kFetchAll) for the $sqlclassname ",$cinst.$sqlclassname,".")
         Do errhndlr.$logError($cmethod,Mssg)
      Else
         
         ; Set the current line to the first line.
         Do $cinst.$line.$assign(1)
         
      End If
   End If
End If
Quit method FlagOK

Add LIKE to Remote Task

We need to modify the $construct method of the rtCountryList remote task to get countries which begin with the search value entered in the web form by the user. We will prepare a WHERE clause which uses the LIKE operator and call the $getWhere method of the table class.

1. Go to the $construct method of the rtCountryList remote form.
2. Add a local variable, Value, character data type.
3. Add a local variable, SQLText, character data type.
4. Replace the $getAllRecords section of the method so that the method reads as follows:

; Set the task variables.
Do method setTaskVars Returns FlagOK
If FlagOK
   
   ; Use the upper case value of the first character they entered.
   Calculate SearchValue as upp(mid(pParams.SearchValue,1,1))
   
   ; Define a list variable using the 'tCountry' table class.
   Do List.$definefromsqlclass('tCountry')
   
   ; Set the session object in the list variable so that the SQL statements will be issued to that session's database.
   Do List.$sessionobject.$assign(dbsessionobj)
   
   ; Prepare the WHERE clause.
   Calculate SQLText as con("WHERE CountryName LIKE '",SearchValue,"%'")
   
   ; Select and fetch the records beginning with the search value.
   Do List.$getSelect(SQLText) Returns FlagOK
   If FlagOK
      
      ; Prepare HTML table to return to the web browser.
      Calculate bInclCheckboxes as kTrue
      Calculate CSVColHeadings as 'Country Name'
      Calculate CSVColNames as 'CountryName'
      Calculate PKeyColName as List.$:PrimaryKeyColName
      Do ioHTMLTools.$convertListToHTMLTable(List,bInclCheckboxes,CSVColNames,CSVColHeadings,PKeyColName) Returns TableHTML
      
      ; Get an HTML page template.
      Do ioHTMLTools.$retHTMLPageTemplate() Returns HTML
      
      ; Replace the placeholders with content.
      Calculate HTML as replaceall(HTML,'##TITLE##','Countries')
      Calculate HTML as replaceall(HTML,'##BODY##',TableHTML)
      
      ; Add the HTTP content header.
      Do ioHTMLTools.$addHTTPContentHeader(HTML) Returns FlagOK
      
   End If
End If

If not(FlagOK)
   
   ; An error occurred. Get the last error as an HTML page.
   Do ioHTMLTools.$retLastErrorHTML() Returns HTML
   
End If

Quit method HTML

Test Search LIKE

Test searching for a country that begins with a letter.