Database Display

heitml's database components are quite versatile. Some applications can be created using a single database component. Database components can also be configured to be part of a complex application. heitml features three basic components for database display. dbdisplay to display a set of database tuples, and dbscroller to perform a query and to display the results in a scrollable fashion. dbcontainer is used to display a single database record.

dbdisplay and dbscroller are rather similar, so most of the things we describe here apply to both of them. The tryout Web site contains various examples of dbdisplays and dbscrollers . It also contains a step by step instruction, how to put a dbdisplay on a page . Here we give some background information.

Context

When a heitml page containing a dbdisplay component is called, the database is queried and the result is shown using the formatting options specified.

Sometimes it's necessary to supply data to the query (e.g. a keyword must be supplied to a search engine before it does any work). To allow this kind of operation, both components can be triggered by Messages. For example a search or query form can be used for this purpose.

Layout Mechanisms

dbdisplay and dbscroller automatically put a dbformat or dbscrollformat component on the page. These format components are responsible for the layout. You can modify the formatting options (assuming you are using RADpage to edit your pages) by clicking on the highlighted component handles.

There are two possible ways to layout the database results, automatic formatting and user-defined formatting. Automatic formatting displays the database results in the form of a table. You can adjust various parameters of the table and the fields (i.e. select which fields to display, and their order). If this is not flexible enough, user-defined formatting gives you full flexibility see Repeaters.

Selecting Fields

Normally all fields of the relation(s) are selected and displayed. By selecting the dbdisplay or dbscroller component handle (assuming RADpage is active) and then clicking on fields, you are presented with a form that allows you to select which fields you want to display. After selecting these fields, the table is shown with component handles in the headline of the table. You can use these handles to edit properties of each field (e.g. the layout used for automatic formatting). You can also reorder fields by moving the field components.

Automatic Layout

The automatic layout is a table showing all selected fields. You can edit some attributes of the underlying HTML table by editing the dbformat or dbscrollformat tags. By clicking on field markers in the table headline (this works only if you have selected some fields) you can edit layout parameters for each field. You can give a title (used in the table headline), font properties (size, bold, italic, color), properties of the table cell (alignment, nowrap, width, bgcolor), and formatting options for the field content.

Querying the Database using Constraints

Initially all records of a database relation are shown, restricted only by the Number property that specifies the maximum number of records to be read from the database. In fact, this number has a very low default value of 3 in order to simplify the component editing in the first place.

You can add constraint components to your query to further restrict the set of database records shown. Every constraint added further restricts the resulting data set.

To add a constraint: first select the dbdisplay or dbscroller component handle, and click on Constraints. This shows you a variety of possible constraint components. Here we are concerned with the dbconstrain component - all others are handled in ....

dbconstrain requests that a field has a certain content. You can select the field, then give a value and specify a relational operator. The "=" operator requests the field content to exactly match the value. Using "contains" the field must contain the value as a substring. Other operators like "<", ">", ... etc are possible, as well as the SQL "like" operator.

Finally, place the dbconstrain component on the page by clicking on the first handle of the dbdisplay or dbscroller component. The set of database records displayed is then restricted accordingly. Afterwards you can edit and delete the dbconstrain component just as any other component.

Summary

At this point you should be able to display database data on a Web page, by using a dbdisplay or dbscroller component. Many more applications are possible by using them as generic components (i.e. combining them with other components).

Reference

Main Components

Component:dbdisplayDetails
Displays the result of a database query. Initially all records of a certain Relation are selected. The maximal number of records to be displayed is given in the Number parameter. It is set to 3 initially which is fine for debugging.

By clicking on constrain you can add constraints on the records selected. By clicking on fields you can select what fields should be selected. Using Join Relations you can create queries on multiple database relations.

Property	Kind	Description
Relation	Relation Name	Database Relation to be shown.
Number	Integer	Maximal number of records.
Showconstraints	Checkbox	Show Constraints (for debugging).
Showsql	Checkbox	Show SQL query (for debugging).

Component:dbformatDetails
Specifies the formatting of the dbdisplay results. There are two fundamental different ways to do that: automatic and user controlled. Using the show parameter you can switch the automatic formatting off and on.

Automatic formatting displays all fields in a table. You can specify some of the most common layout parameters of the HTML table. All selected fields are shown, but you can specify what fields to select from the database in the dbdisplay component. Make sure to display at least one database record to edit the display format.

To use User-defined formatting insert a repeatable layout into the dbformat. Then the repeatable layout is repeated for each selected database record. Then insert dfields into the repeatable layout to display various database fields.

Property	Kind	Description
Show	yes,dbg,no	Show the table yes,no,or only during debugging
All	Checkbox	Show all fields (regardless of field definitions)
Showtable	Checkbox	Show Result Table
Bgcolor	String(60)	Parameters of underlying table
Border	Integer
Cellspacing	Integer
Cellpadding	Integer

Component:dbscrollerDetails
Displays the result of a database query. Initially all records of a certain Relation are selected. The maximal number of records display is given in the Number Parameter. It is set to 3 initially which is fine for debugging.

By clicking on constrain you can add constraints on the records selected. By clicking on fields you can select what fields should be selected. Using Join Relations you can create queries on multiple database relations.

Property	Kind	Description
Oid	Id
Relation	Relation Name	Database Relation to be shown
Number	Integer	Maximal number of records
Autoread	Checkbox	Automatically execute a query
Showconstraints	Checkbox	Show Constraints for debugging
Showsql	Checkbox	Show SQL query (for debugging)
Msgallowed	String(60)	Accepted messages. The enabled functions can be activated from the browser.

Table Column Components used in automatic layout

Component:dbselectfieldDetails
Selects a database field from the database. If you use automatic formatting in dbformat you can also select formatting options.

Property	Kind	Description
Name	String(20)	Field Name
Alias	String(20)	Rename field name in heitml (in case of name conflicts)
Format	String(60)
Width	Integer %	Formatting and width
Title	String(20)	Table headline of field
Size	String(60)	Font parameters
Color	String(60)
Bold	Checkbox
Italics	Checkbox
Bgcolor	String(60)	Table field parameters
Align	Left, Middle, Right
Valign	Top, Middle, Bottom
Nowrap	Checkbox
Hidden	Checkbox

Component:dbselectlinkDetails
Link to another component to handle the current record. dbselectlink must be used inside the automatic format of a dbdisplay or dbscroller. It adds a new column containing an HTML-link. When the link is clicked the component specified by receiver on the page specified by href gets a message. Typically this is a dbform or dbcontainer to display the record or another dbdisplay or dbscroller.

Property	Kind	Description
Href	String(60)	Destination URL
Action	String(60)	Action to be performed by the receiver
Receiver	String(60)	Destination Component
Key	String(20)	Key field used to identify the record. Key can also be a comma separated list of key field names. Field names can be preceded by 'targetname=' id the receiver uses different key field names.
Title	String(20)	Title of the Link column
Width	Integer %	Width of the link column
Bgcolor	String(60)	Table field parameters
Align	Left, Middle, Right
Valign	Top, Middle, Bottom
Nowrap	Checkbox
Urlpar	String(20)	Parameters appended to the URL. (The ? part of the URL excluding the #).

Component:dbtextcolumnDetails
Inserts a column into the dbdisplay or dbscroller auto format table.

Property	Kind	Description
Title	String(20)	Title of the Link column
Width	Integer %	Width of the link column
Bgcolor	String(60)	Table field parameters
Align	Left, Middle, Right
Valign	Top, Middle, Bottom
Nowrap	Checkbox
Nocompedit	Checkbox	Switch off markers inside

Constraints

Component:dbconstrainDetails
This constraint compares a database field with a given constant.

Property	Kind	Description
Field	String(20)	Field to be constraint.
Op	String(60)	Comparison operator.
Value	String(20)	Value to compare with.

Component:dbqueryfieldDetails
This constraint compares a database field with a value entered into the query form.

Property	Kind	Description
Field	String(20)	Field to be constraint
Op	String(60)	Comparison operator
Fname	String(20)	Fieldname in Queryform

Component:dbDistinctDetails
This option removes duplicate rows from the result set.

Component:dbsqlDetails
Adds an arbitrary SQL search constraint to the query.

Property	Kind	Description
Select	String	Add text to select clause
Relations	String	Add text to from clause
Order	String	Add text at end, i.e. an order clause

Component:dbjoinDetails
Add (Join) another relation to the query.

Property	Kind	Description
Relation	Relation Name	Relation to join
Shortname	String(15)	Alias name