Building Business Logic

SQLData Server Customization

 

Building Business Logic

Creating Web Applications

Introduction

SQLData Server provides both web services and database services. The web services enable HTML being delivered to user browser. It can be understood as user interface component in a multi-tier web application. The database services enable generic database operations, such as insert, delete and update, to be processed against your ODBC data sources. It is virtually the backend processor. The things that are missing are your application specific logic: How to control data presentations, how to preserve data integrity and consistency.

This document describes the processes of building powerful web applications using SQLData enterprise server.

Feature Highlights

SQLData Enterprise Server provides many ways for building your own business logic with virtually no programming necessary:

Table Logic

Business logic is categorized into two types: Table Logic and Field Logic. Table logic is associated with database tables and Field Logic is related to columns in database tables.

Table Logic includes procedures, triggers and reports, which operate on database tables. All table logic items are stored in a SQLData System table called TABLELOGIC. SQLData server retrieves logic items from the table and executes them at runtime. Because TABLELOGIC is a normal database table, it can be edited using the Record Editor and Record Browser shipped with the SQLData Enterprise Server.

The fields in the TABLELOGIC are described below:

 

Field Logic

Field Logic is logic that associated with a particular column in a database table. For example, you may want to display STATE in a customer table as dropdown list, or encrypt/decrypt a PASSWORD field. A field logic item defines how a database field is presented, converted and validated.

The Field Logic is very often used for creating HTML forms that link directly to your database tables. It allows you to control what to present, how to display, when to validate and where to find helps.

SQLData Enterprise/Desktop Servers shipped with a Logic Editor which can be used for defining the field logic very easily. The Logic Editor can be invoked on the Record Editor page where a record detail is displayed. You can create field logic by clicking on a small image with a hand and pen right after each field name.

Field logic items are stored in a database table, FIELDLOGIC, in the SQLData system database. The following is the definition and description of all fields in the FIELDLOGIC table.

 

Create Reports

Reports can be accessed directly on your desktop and can be executed with one button click. Reports are also displayed on both the Record Browser and Record Editor pages for quick execution.

SQLData Enterprise/Desktop Server shipped with a Report Editor which makes report management extremely easy. The following is a procedure to add your reports using a web browser:
    1. Click the MyReport link in your browser.
    2. Once you are in the Report Editor, click on the Add New button on the bottom of the page.
    3. Type in REPORT for LogicType and give a unique name as the LogicName. LogicName is the name of your report.
    4. Select an ODBC data source from the dropdown list.
    5. Type in a template file name if you want to display the result set in your own HTML file. Leave the TemplateFile field empty if you wish to use HTML files supplied by the system.
    6. Give a brief description to your report. The description will be displayed in the Report list.
    7. Type in your SQL SELECT statement in the SQLStatement field. This query will be executed when the report is requested.
    8. Put a "*" character in the Permission box if you want users of your report authenticated, otherwise leave it empty.
    9. Click the Add New button to save your report.

 

Build Procedures

A procedure is a group of SQL statements that can be executed as a whole by the server. Procedures are saved in the TableLogic table. Once defined, procedure can be referenced in HTML files as part of a URL or in HTTP requests as one of the parameters. For example, if MyProc is a defined procedure in the TableLogic table, you can use:

http://www.yourhost.com/record_browser.html?ProcedureName=MyProc

in your HTML file. MyProc will be executed and the result will be displayed in the record_browser.html file when user clicks on the link. Using procedures is a preferred way for accessing database content in HTML pages or forms. It separates user interface (HTML) from business logic. Procedures can be altered with no impact on the HTML files.

Procedures can be edited just as any other database records using tools provided by the SQLData server. To add a procedure:

Define Visual Presentations

With SQLData server, it is easy to change the way data is displayed. The server automatically generates HTML forms where users can add new records and update old ones. Data fields in the forms are text boxes by default. You can change the display type to radio buttons, check boxes, dropdown selections and others using the Logic Editor. The Logic Editor can be accessed from the Record Editor page where a database record is displayed. Clicking on the small image right after the field name will invoke the Logic Editor.

Using Radio Buttons

Radio buttons give use a limited number of choices and user can select one and only one from them. The actual values stored in database are often integers, but it could be other data types. To use radio button for a database field:
    1. Click on the small image after the field name when one of the data records is displayed in the Record Editor.
    2. Select RadioButton as the Visual Type on the Logic Editor Page.
    3. Enter available choices, separated by the '+' character, in the SelectString entry. The string will be displayed to user.
    4. Enter a list of values, separated by the '+' character, in the SelectValue entry. These are values to be stored in the database.
    5. Click the update button.

Using Check Boxes

Check boxes allows user to select multiple entries from a list of choices. The procedure of using check boxes is very similar to that of radio buttons except (1) The Visual Type should be CheckBox, (2) You should check the Selection box in the Conversion field. The server combines multiple values submitted into a single value before writing to the database.

The data type of the database field must be numerical for using check boxes.

 

Using Dropdown Lists

Dropdown list allows user to select one from many different choices. The procedure of using dropdown list is the same as using radio buttons except the Visual Type should be Dropdown.

 

Using Value Lists

A value list is a dropdown list with values come from a database field. The server uses an SQL select statement in the SELECTSTRING field to populate the list automatically.

The following is a procedure for defining a value list:
    1. Click on the small image after the field name when one of the data records is displayed in the Record Editor.
    2. Select ValueList as the Visual Type on the Logic Editor Page.
    3. Enter an SQL select statement as the SelectString. The server uses this query statement to fill the list. Use the DISTINCT keyword in your query statement to make sure there is no duplicates.
    4. Leave the SelectValue field empty. User selected value from the dropdown list will be sent to the server.
    5. Click the update button.

Using Value Maps

A value map is a dropdown list with values come from another database field. However, the values displayed in the list are different from the actual values for the field. For instance, suppose you have a Classes table with a department ID as a foreign key to the Departments table. So, you want to display the name of the departments in the Classes table but to save the department Id when user makes a selection. In other words, Value Maps allows you to store different values from what users have selected.

The procedure for using Value Maps is very similar to that of Value List except the following differences:
    1. The Visual Type should be ValueMap.
    2. There should be at least two fields in the SELECT clause of your query statement. The first field contains the actual values for the database, and the rest for displaying in the dropdown list. Using the example above, you may enter,
      select DeptID, DepartmentName from Departments
      as the query statement. The names of departments are displayed in the dropdown list. DeptID is hidden from users, but it is sent to the server when users select a corresponding name.

Logic Processing Pipeline (LPP)

There are many situations where a user request needs to be processed in several stages, and the stages are related to each other. To deal with such complicated situations, SQLData server offers a Logic Processing Pipeline. It allows you to define your business logic as stages and link them together to perform complicate tasks. For example, in an e-commerce system, the application server may need to process payments, deduct inventory, send order notification and display confirmation once an order is finalized. Each of the stages can be viewed as a processing unit and the status of one stage has influences to the subsequent stages.

There are several advantages of using Logic Processing Pipeline:
    1. Since the business logic is further divided into smaller unit. It is much easier to deal with complex business requirements.
    2. Stages can be shared by multiple pipelines and, thus reduce redundancy.
    3. Flow control is much more flexible than traditional method. As we will demonstrate later, processing status of current stage can be used to determine what to do next.
    4. Business logic is further extracted out of other part of the system design. Logic Processing Pipeline provides a central depository for not only business logic, but also control logic.

 

LPP Structure

Stages in LPP are stored in a database table called PIPELINE in the SQLDataSystem database. Each stage is a record in the table. The table is defined as follows:

Stage Control

Using the Logic Processing Pipeline, you can easily link multiple stages together to form a complete processing unit. The next stage to be executed is defined by OnSuccess, OnError and OnEmpty based on the current processing status.

Using LPP

LPP is an external component that resides in the dynamic linked library SHOPPER.DLL. The processor requires two parameters, the stage name and the group name in order to process a stage. It can be invoked using an URL:

http://www.yourhost.com/shopper.dll?Stage=MyStage&GroupName=MyGroup

 

Where MyStage and MyGroup is the name of your stage and group respectively. You may also post data to the processor using HTML forms similar to:

<form method="post" action=shopper.dll>

<input type=hidden name="Stage" value="MyStage">

<input type=hidden name="GroupName" value="MyGroup">

the rest of your form elements goes here…

</form>

Note that the stage and group are passed as hidden parameters in the form.

An Example

Our e-Bridge Server, an e-Commerce server, uses the Logic Processing Pipeline extensively. You can download the server at http://www.sqldata.com/download/ebrdige20.exe and examine the Pipeline table in the Merchant.MDB file under the DATA subdirectory. You may also using the SQLData Desktop server to display the pipeline structure under MyStore data source once the e-Bridge server is installed. The logic-processing pipeline contains many stages including order processing, inventory deduction, mail notification, shipping/handing and customer registration.

Dynamic SQL Statements

There are many situations in which the parameters of SQL statements are unknown or the search criteria for queries are specified at runtime. SQLData Server allows you to create SQL statement templates and construct SQL statement based on parameters submitted in user requests.

Dynamic SQL statements are SQL templates in which special tokens are used for representing information to be supplied at runtime. For instance, if you allow user to search customers by country and the name of the country is supplied using an HTML form, you may use a dynamic SQL:

select * from customer where country = '$COUNTRY'

where $COUNTRY is a variable that will be replaced by the actual value submitted by users.

Dynamic SQL can be used in procedures, reports and triggers. It can also be used in the SQLStatement parameter that passed to the SQLData server. For example, you may search customers by country using the following HTML form

<form method="get" action="record_browser.html">

<input type=hidden name="SQLStatement"

value=" select * from customer where country = '$COUNTRY'">

Country Name: <input type=text name="Country" size=16>

<input type=submit value="Search">

</form>

The dynamic SQL statement is hidden in the form. When users type in the name of a country and click the Search button, the server replaces $COUNTRY with the specified country name, and displays results in the record_browser.html file. You can build powerful reports using the technique.

Using data in HTTP requests

All data in HTTP requests can be referenced by prefixing a '$' character in the name of a value, including parameters in URLs and data in HTML forms. The following SQL statement using OrderId in the HTTP requests:

select * from orders where orderid=$OrderId

If you wish not to use OrderId when it is empty, you may use a dynamic SQL statement similar to :

select * from orders where $F(OrderId, =)

The $F token adds a condition in a SQL where clause using the specified field name (first parameter) and the operator (the second parameter). The condition is not added, however, if the OrderId field is an empty string or non-exist. The statement reduces to

select * from orders

in such a situation. The $F() token is very useful if you want to construct queries with multiple where clauses.

Two other toknes, which are used exclusively in the where-clause of a SQL statements, are $AND() and $OR(). Both of them add not only a condition in the where clause, but also a logic operator: either AND or OR. For example,

select * from orders where $F(OrderId, =) $AND(CustomerId, =)

is constructed as:

select * from order where Orderid=12 and CustomerId='GTERE'

if OrderId=12 and CustomerId="GTERE'. Similar to $F(), the values for the two token are optional. The statement reduce to

select * from order where CustomerId='GTERE'

if the OrderId string is empty.

 

Referencing Cookies

You may also use cookie values in the dynamic SQL statement. Suppose the server sets customer ID as cookie, you may use:

select * from customers where CustomerId='$GetCookie(CustomerId)'

The $GetCookie token requires the name of a cookie as its parameter. The token will be replaced by the value of the cookie.

 

Using environment settings

You can also using values defined in the server configuration file as parameters in the dynamic SQL statement. The token is

$S(SettingName)

where SettingName is the name of a variable defined in the server configuration file. It can also be the name of a session variable associated with a user (Session variable can be set easily using the SessionVariables field in the Logic Processing Pipeline).