|heitml Tags to access the database|
<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
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
If your database system requires authentication, an appropriate account
must be assigned to the
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 </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.
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
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
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.|
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>).|