, heitml Tags to access the database

heitml Tags to access the database

Querying the Database: The dbquery Tag

<dbquery queryname> heitml    SQL query statement
   <dbrow> heitml             row section, text for every row
   <dbtop> heitml             top section, text before first row
   <dbfoot> heitml            foot section, text after last row
   <dbempty> heitml           empty section, in case of empty result

Purpose: Query the database.

The SQL query statement and all sections are processed in HTML mode. All heitml tags can be used to generate the SQL query statement and every section. The sections are optional.

The heitml code after the dbquery tag must evaluate to a valid SQL query statement.

The SQL statement is executed and the heitml code after the dbrow tag (row section) is processed repeatedly for every row selected. For example, the row section may contain the ? tag to include the content of a database field within the page, ordinary HTML code or other heitml tags, including nested queries or update statements.

An arbitrary queryname can be chosen which must be distinct from the names of local variables. The fields of the selected rows are accessed by writing queryname.fieldname (e.g. if the query was named q, then <? q.firstname> prints the firstname field in the database). In fact, queryname denotes a heitml object that contains the current row. The object can be read as any other object but it can not be written with some databases systems, so it is recommended that the object is always used read-only.

The empty section after the dbempty tag is processed if the result of the SQL query statement is empty. Otherwise top and foot sections after the dbtop and dbfoot tags are processed once, before the first row and after the last row, respectively. The advantage of the top and foot section over writing a table head and table foot before and after the dbquery tag is that in case of an empty result table only the text in the empty section is displayed.

Configuration: For the dbquery to work properly, you have to setup the heitml configuration options dbname, dbhost, dbport (UNIX) and datasource (Windows). If your database system requires authentication, an appropriate account must be assigned to the user and passwd configuration options.

The maximum number of rows returned by a dbquery is limited by the write-only Server Variable SrvMax which defaults to 200. You might want to change this value by assigning an appropriate value to SrvMax before the dbquery.

For nested dbquery's, the maximum nest level is limited by the write-only Server Variable SrvMaxNest which defaults to 5. You might want to change this value by assigning an appropriate value to SrvMaxNest before the dbquery.

Accessing Anonymous Relations

<dbquery queryname> heitml SQL query statement
   <dbhead> heitml         head section, headline for a column
   <dbbody> heitml         body section, text between head & body
   <dbrow> heitml          row section, text before every row
   <dbcolumn> heitml       column section, text for every column
   <dbrowend> heitml       rowend section, text after every row

This form of the dbquery Tag can be used to display a table whose field names are not known a priori. It first shows a headline with one entry for every field. This entry is specified after dbhead. It can use the special Server Variable SrvFname to access the name of the current field. Afterwards a separator, dbbody, is printed. Note that you can leave out the headline by leaving out both the dbhead and dbbody parts.

All records in the table are then shown. For each record, first the text in dbrow, then for every field the text in dbcolumn and finally the text in dbrowend is shown. Inside dbcolumn, the special Server Variable SrvField can be used to access the field content and SrvFname can be used to access the field name.

dbtop and dbfoot can be used as in the usual dbquery Tag. It is also possible to give a name to the current object and access specific fields, all as documented with the normal database access.

Modifying the Database: The dbupdate Tag

Format: <dbupdate> heitml </dbupdate>

Purpose: Modify the database.

The heitml code in the body of the dbupdate tag is processed in HTML mode and must evaluate to a legal SQL update, delete or insert statement. With appropriate database systems, many other SQL statements are allowed, including create table etc.

In general, you would insert field values into the update statement using the "Q" format of the ? Tag. If your database system supports host variables, and if you want to store large text, you should insert the field value with the "Qh" format of the ? Tag.

Configuration: The heitml configuration option write must be switched on to issue update statements to the database.

See Also: ? Tag.


Transactions are a crucial feature of database systems to ensure consistent operation in case of errors and in case of multiple simultaneous access. heitml supports transactions if the database system does and if they are not disabled (e.g. by the autocommit or isolation configuration parameters).

Note: Transactions cost performance but without transactions you may risk your data consistency. You do not need transactions if you have a read only application. If there is just one update per page things might work, if you have multiple updates per page be aware only part of them might become effective. Also funny things can happen sporadically when multiple updates happen at the same time.

heitml automatically opens a transaction at the first database statement and closes the transaction (issues a commit) after the page has been successfully generated and been sent to the browser. However in case of an error, e.g. a program error or a lost connection, the transaction is cancelled (by issuing rollback). This means that none of the changes issued actually take effect.

As long as the database supports serializable transactions (which it should according to the SQL standard) and you did not switch them off using the isolation parameter, you do not need to worry about multiple updates at the same time. We strictly recommend to use serializable transactions for all but read-only or trivial applications. If you do not, your program must use explicit locking to prepare for two pages being called simultaneously.

Note: If your application is not save for multiple shared updates and your database does not guarantee for serializable transactions, it is very likely that you notice just nothing during testing. However in heavy production usage your data might get inconsistent.

Selecting the Database: The dbdatabase Tag

Format: <dbdatabase> heitml </dbdatabase>

Purpose: The dbdatabase Tag allows you to switch to another database, if the heitml configuration option dbname is set to *. This is used when you want to access an SQL Table in a database other than the one that is currently open.

Anything you type between the opening and closing dbdatabase tags is processed in HTML mode and must evaluate to the name of a database.

Configuration: If the configuration option dbname is not set to *, the dbdatabase tag will have no effect.

Note: This Tag is not implemented on the Windows version. Moreover, the dbdatabase Tag is Case Sensitive, which means heitml will not recognize it if you use capital letters (e.g. <DBDATABASE>).

This page was dynamically generated by the web application development tool RADpage of H.E.I.
© 1996-2017 H.E.I. All Rights Reserved.

Component Guide
  Language Guide
  Language Ref.
    General Design
    Lexical Structure
    heitml Tags
      ? Tag
      assign Tag
      break Tag
      callenv Tag
      dbdatabase Tag
      dbquery Tag
      dbupdate Tag
      def Tag
      defautoclose Tag
      defbody Tag
      defclass Tag
      defcontpara Tag
      defenv Tag
      defhtml Tag
      definpara Tag
      defpara Tag
      defremovecdata Tag
      deftranspara Tag
      defwysiwyg Tag
      for Tag
      forin Tag
      forsep Tag
      if Tag
      elsif Tag
      else Tag
      include Tag
      includeonce Tag
      inherit Tag
      let Tag
      mail Tag
      return Tag
      shell Tag
      while Tag
      writefile Tag
      \ Tag
    heitml Functions
    Advanced Functions
    Database Access
    Global Variables
    Form Fields
    Server Variables
    heitml Syntax
  Component Ref.
  Class Library
  User Components
  Tryout Form
  New Features
  heitml 1
User Guide
Discussion Group