heitml Tags to access the database

Querying the Database: The dbquery Tag

Format: <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 </dbquery>

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

Format: <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 </dbquery>

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

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

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).

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.

Selecting the Database: The dbdatabase Tag

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.