# CHAPTER 4: ehsql:writer

## 4.1 Introduction

`ehsql:writer` is a tool which allows direct SQL statements (operations on a database) to be executed and displayed within an HTML template. The `<QUERY>` tag is used for SQL `SELECT` statements, and the `<EXECUTE>` markup tag is used for all other SQL statements, and some other specialized commands as well. `<QUERY>` and `<EXECUTE>` tags can appear anywhere HTML tags can appear.

It is permissible for `<QUERY>` and `<EXECUTE>` tags to be nested inside `<QUERY>` tags. For instance:

       <QUERY DBASE="registry"
        SQL="SELECT name FROM members WHERE age <= 18">
            <EXECUTE TYPE="SQL" DBASE="children">
                 INSERT INTO newsletter(list) VALUES (%%name%%)
            </EXECUTE>
       </QUERY>

This EHTML fragment will select all `members` of `age` less than or equal to 18 from the `registry` database, and insert them into the `list` attribute of the `newsletter` table in the `children` database. The syntax of the `<QUERY>` and `<EXECUTE>` tags is described below.

## 4.2 Invoking ehsql:writer

`ehsql:writer` is the default tool in the HTML Toolkit. It is invoked on the URL line of a browser by giving the ScriptAlias of the CGI directory containing the Toolkit followed by `ehsql.cgi` or `ehlink.cgi`. The information after the Toolkit name is called a virtual path; and indicates the location and name of the text file to be processed by the Toolkit. This is the location of the file prepended by `DOCUMENT_ROOT`/`EH_DOC_ROOT`.

- Example

  URL:

    `http://workstn1:8285/My-bin/ehsql.cgi/docs/test.ehtml`

  Explanation:

    Host Name: `workstn1`
    HTTP Server Port Number: 8285
    Script Alias: `/My-bin/`
    Toolkit CGI Program Called: `ehsql.cgi`
    User's EHTML File: `DocumentRoot/EH_DOC_ROOT/docs/test.ehtml`

- Example 2

  URL:

    `/My-bin/ehlink.cgi/test2.ehtml`

  Explanation:

    Hostname: last host visited
    HTTP Server Port Number: last port number visited
    CGI ScriptAlias Name: `/My-bin/`
    Toolkit CGI Program Called: `ehlink.cgi`
    User's EHTML File: `DocumentRoot/EH_DOC_ROOT/test2.ehtml`
    Toolkit Tool Invoked: `writer`

  ## 4.3 Query Database

  The syntax for querying the database from an HTML page is:

         <QUERY
          DBASE="datasource_name"
          SQL="select_statement"
          [COUNT="some_number" TEXT="some_HTML_string"]
          [START="some_HTML_string" END="some_HTML_string"]>

          ...

          [<NUMRECORD operator some_number
          [START="some_HTML_string" END="some_HTML_string"]
          [NOQUERY]
          >]

         ...

         </QUERY>

  Notes:
  - The tag may be commented out with the HTML comment syntax `<!-- .... -->` (for example, `<!--/QUERY-->`) if the markup will interfere with your HTML editor. This won't affect its action at all.
  - Tag names are case insensitive but database attribute names are case sensitive.

  The `DBASE=" ... "` of the `<QUERY>` start tag is used to specify which database the server must access. The datasource it references should be quoted, and must be defined in an `empress_config_exml.ini` file. See Using data source file section under Connection to the Database for more information about setting up these files.

  The `SQL=" ... "` phrases the actual query. An SQL `SELECT` statement follows the equal sign, and it must be double-quoted. If quoted strings inside the `SELECT` statement are required, use single-quotes for them. For example:

         SQL="SELECT name, department FROM employees
              WHERE title = 'engineer'"

  Everything between the start and end `<QUERY>` tags will serve as a template for the display of the information received. The template will be repeated for every record returned, and each attribute value specified in the `SELECT` statement will be available by enclosing the attribute name in double percent signs. A simple example follows.

  Suppose we have a data source called `db1`, which contains a table `table1` with the attributes `attr1` and `attr2`, both integer types. Further suppose that the table has three records: `(1,3),(1,4),(2,7)`, where the bracketed values correspond to records made up of pairs of `attr1` and `attr2`. We wish to display the results of a query of this table on the Internet. Thus, we create an HTML file `query_results.ehtml`, in our web server's DocumentRoot:

         <html>
         <h1>Printout of 'table1':</h1>
         <table>
            <tr><th>attr1<th>attr2
            <QUERY DBASE="db1" SQL="SELECT attr1, attr2 FROM table1">
               <tr><td>%%attr1%%<td>%%attr2%%
            </QUERY>
         </table>
         </html>

  In this file we will organize the information into an HTML table (`<table>`). The first HTML table row (`<tr>`) contains two column headers - `attr1` and `attr2`. Then, still within the `<table>` tag, the query is defined. It lists the data source as `db1` and gives a very simple `SELECT` statement. The statement just says to retrieve all the records from `table1` , and display the attributes `attr1` and `attr2` The next line after the query is the template. It can contain any HTML tags, plus attribute names delimited with double percents. This line is never displayed literally by the browser, but is evaluated for each record returned by the query, with the attribute values being substituted for the names. The `SELECT` statement will first return the record `(1, 3)`. A table row will be created underneath the original row. Its first data element in the first column will be the value of `attr1`, which is `1`. The value of `attr2` - `3` is specified to go into the second column. This second row will have the numbers `1` and `3` in the boxes of the HTML table when it displays on the browser. Then the template will be evaluated again for the second row and hence generate a table row which says `(1, 4)`. The template will be processed again for the last row which is `(2, 7)`. Thus the HTML document above will display as:

    `Printout of 'table1'`

    `attr1` | `attr2`
    --- | ---
    `1` | `3`
    `1` | `4`
    `2` | `7`

  The `COUNT=" ... "` and `TEXT=" ... "` tags allow another formatting possibility. The text string specified by `TEXT` is inserted after every group of records specified by `COUNT`. This is useful for neatly displaying in matrix format values from a single attribute. For instance:

         <QUERY
          DBASE="db1"
          SQL="SELECT votes FROM poll"
          COUNT="10" TEXT="<p>">
             %%votes%%
         </QUERY>

  If the `votes` attribute of the `poll` table contains `Y` and `N` values, then this query will display the entire list ten per line.

         YYYNNYNYYY
         YNYYYNYYYY
         YYYYNYYNNN
         YYNYY

  This technique can also be useful when generating an HTML table to do the same thing with data that doesn't line up so nicely.

  The `<NUMRECORDS ...>` tag can appear as many times as you want different layouts in the template section (between the `<QUERY>` and `</QUERY>` tags). It defines the start of a special section of HTML, which ends when either the `</QUERY>` tag or another `<NUMRECORDS=...>` tag is found. The HTML in that section will only be displayed if the number of records returned by the query matches the operator and number given in the tag. The HTML text that comes before the first `<NUMRECORDS=...>` tag will be displayed if the number of records returned is different from any of the values of the `NUMRECORDS` tags. The `START` and `END` arguments are explicitly required for each individual `NUMRECORDS` tag; the `START` and `END` arguments in the `QUERY` tag itself are only used for the default layout.

  The `NOQUERY` option of the `NUMRECORDS` tag is used when you do not want the text layout to be repeated for each record in the database.

  For instance:

         <html>
         <QUERY
          DBASE="db2"
          SQL="SELECT name, address FROM employees WHERE emp_num=??num??">

               The name and address of employee &&num&& is <p>
               %%name%%, %%address%%,<p>

            <NUMRECORDS=0>
               There was no record found with employee number &&num&&<p>

            <NUMRECORDS > 1 NOQUERY >
            Error - more than one employee found matching employee number
                    &&num&&.  Inform the Database Administrator. <p>

         </QUERY>
         </html>

  In this example we assume that a previous HTML form existed in which a user typed in a number into a text box called `num`, and posted it through `ehsql:writer` to this document. Thus the value will be available as the name of the form field enclosed in double question marks. The `WHERE` clause of the `SELECT` statement queries the database for an employee number of this value. One would expect an employee number to be unique, so we expect one record returned. This would use the default layout. If a non-existent employee number is entered, no records will be returned and the `` section will be displayed instead. If more than one record is returned because of some database inconsistency, then the number of records returned will match `NUMRECORD > 1`, and the third layout will be used. The `NOQUERY` argument will ensure that the error text is only printed once.

  ## 4.4 Execute Tag and It's Types

  The `<EXECUTE>` tag is used when the SQL statement is not `SELECT`, and also when other specialized features are to be executed. These include such things as editing an `empress_config_exml.ini` file or running a user-specified executable. Within the `<EXECUTE>` start tag, a `TYPE=...` is declared with the type of execute statement desired.

  The options available for TYPE are:

  - SQL
  - COMMIT
  - Content-Type
  - EDIT
  - eh_info
  - eh_info_edit
  - empvers
  - SCRIPT
  - SYSTEM
  - TRANSACTION
  - writer
  - INCLUDE
  - NONE
  We will consider each available type in turn.

  ### 4.4.1 SQL

  The default type for the `<EXECUTE>` tag is `SQL` for executing an SQL statement. It has the following syntax:

         <EXECUTE TYPE="SQL" DBASE="datasource_name">
            sql_statement
         </EXECUTE>

  If you want to place more than one SQL statement between the start and end `<EXECUTE>` tags they must be separated by `<NEXT>`.

         <EXECUTE TYPE="SQL" DBASE="datasource_name">
            sql_statement
         <NEXT>
            sql_statement
         <NEXT>
            sql_statement
         </EXECUTE>

  Any SQL statement except `SELECT` statement and transaction statement may go there.

  All the data editing statements (like `INSERT`, `UPDATE`, `DELETE`) and data-definition statements (like `ALTER TABLE`, `CREATE TABLE` ... ) can be issued from within an `<EXECUTE TYPE="SQL">` block. No HTML output will be produced unless there is an error.

  In the example below, we have a fill-out form called `form.html` posted through `ehsql:writer` to an `.ehtml` file called `insert.ehtml` which inserts the data on the form into the database.

  The following is the script for `form.html`:

         <html>
         <h1> Insert Form for Daily Weather Table </h1>
         <form method=POST
          action="/My-bin/ehsql.cgi/docs/insert.ehtml">
            Temperature<input name="temp"><p>
            Precipitation<input name="precip"<p>
            Cloud Cover<input name="cloud"><p>
            <input type=submit value="insert record into database">
         </form>
         </html>

  The following is the script for `insert.ehtml`:

         <html>
         <EXECUTE TYPE="SQL" DBASE="climate">
            INSERT INTO weather(temperature, precip_type, cloud_cover)
               VALUES(&&temp&&,"&&precip&&",&&cloud&&)
         </EXECUTE>
         <h2>Record inserted into weather table</h2>
         </html>

  The Empress RDBMS syntax for `DISPLAY` doesn't work with the Toolkit, but a simplified syntax is provided to get information about your database:

         <EXECUTE  TYPE="SQL" DBASE="datasource_name">
            DISPLAY DB
         <NEXT>
            DISPLAY TABLE table_name
         <NEXT>
            DISPLAY INDEX table_name
         <NEXT>
            DISPLAY PRIVILEGE table_name
         </EXECUTE>

  Each of the `DISPLAY` statements which take a table name will display information specific to that table. The output from `DISPLAY` statement is fixed, there is no way to reformat it. If other formats are desired, the `DISPLAY` functionality must be simulated by `SELECT` statements on the Empress system tables.

  ### 4.4.2 TRANSACTION

  The `TYPE="TRANSACTION"` is used for managing transactions across multiple EHTML pages. It is used for session-based `ehlink.cgi` only to start work, commit work, rollback work, or print the status of the transaction. It has the following syntax:

         <EXECUTE TYPE="TRANSACTION" DBASE="datasource_name">
            START  | COMMIT | ROLLBACK | STATUS
         </EXECUTE>

  In a session-based application using `ehlink.cgi` the developer may wish to span a transaction across more than one EHTML page, and to allow the user to decide to finally roll back or to commit. For example, if the net-user is to input a variety of pieces of information about themselves over a number of pages, and needs the option of submitting everything or cancelling.

  The `START` keyword starts the transaction. The `COMMIT` keyword makes permanent all changes to a database made during the current transaction and to end the current transaction. The `ROLLBACK` keyword ends the transaction and erases the changes made to the database during the current transaction. The `STATUS` keyword will be replaced at run-time by the word `ON` or `OFF` in the application, indicating whether a transaction has been turned on with `TYPE="TRANSACTION"` or not.

  ### 4.4.3 COMMIT

  In both `ehlink.cgi` and `ehsql.cgi`, each page is automatically treated as an SQL transaction if it contains an `<EXECUTE>` tag or uses the tool `update`. If an error occurs, that page will be rolled back. The `TYPE="COMMIT"` is used for committing update commands part way through a single HTML page. The syntax is:

         <EXECUTE TYPE="COMMIT" DBASE="datasource_name">
         </EXECUTE>

  ### 4.4.4 EDIT

  The `TYPE="EDIT"` preformatted table insert, update or delete. It has the following syntax:

         <EXECUTE TYPE="EDIT"
            DBASE="datasource_name"
            TABLE="table_name"
            RECORD=internal_index_number
            DELCHECK="path_for_delete_prompt">
         </EXECUTE>

  This type takes a datasource, a table, a record number, and the name of an optional HTML file to prompt the user prior to any deleting action. Nothing can go between the start and end tags of the `EXECUTE` block, and there is no way to alter the format. The form produced generates input fields for all the attributes in the table specified. There is no need to know in advance what attributes exist, and it will work on any table. Users will also have the option of uploading files in the case of `BULK` and `TEXT` data.

  The `internal_index_number` is always available from any `<QUERY>` or `ehsql.cgi?select` form by referencing `%%eh-internal-index%%`, regardless of whether it appeared in the attribute list of the `SELECT` statement or not. Typically, an application might provide a form which allows the user to specify a particular record based on some criteria, and then pass the `eh-internal-index` attribute to `EXECUTE TYPE="EDIT"` for easy editing.

  ### 4.4.5 WRITER

  The `TYPE="writer"` calls `ehsql:writer` from within an `EXECUTE` tag of a document already called through `ehsql:writer`. It has the following syntax:

         <EXECUTE TYPE="writer">
           file_name
         </EXECUTE>

  The area between the start and end tags may only contain a URL of an `.ehtml` file. It is not necessary to preface this with a URL of `ehsql.cgi`, or to put any arguments on the URL line.

  The `TYPE="eh_writer"` is primarily useful when some standard section of `.ehtml` is used again and again in different `.ehtml` pages. Use `TYPE="writer"` to piece these sections together into specific documents, without the need to write the `.ehtml` lines over again. As an example, assume a standard query of a database table is often displayed at the top of the various forms of a net application. The query could be put in one file called `query.ehtml`:

         <h1>User List:</h1>
         <QUERY DBASE="clients" SQL="SELECT last_name FROM usernames">
           %%last_name%%<p>
         </QUERY>

  This `query.ehtml` can then be called from other document, for example:

         <html>

         <h1>Form 23:  If your name is not included in the following list, please
                       fill out the following:</h1>
         <hr>

         <EXECUTE TYPE="writer">
           query.ehtml
         </EXECUTE>

         <EXECUTE TYPE="writer">
           order_form.ehtml
         </EXECUTE>

         <EXECUTE TYPE="writer">
           creditcard_no.ehtml
         </EXECUTE>

         </html>

  ### 4.4.6 EH_INFO

  The `TYPE="eh_info"` displays the contents of the `empress_config_exml.ini` file in use. It has the following syntax:

         <EXECUTE TYPE="eh_info">
           any_HTML
         </EXECUTE>

  `any_HTML` may include the following variables:

         %%logical_db%%
         %%physical_db%%
         %%server%%
         %%unique_server%%
         %%port%%
         %%Index%%

  Much like a `<QUERY>` on a database table, `TYPE="eh_info"` scans the `empress_config_exml.ini` file and makes the results available in the above special variables. Each variable refers to one part of a typical section of a data source file. The correlation is as follows:

         %%logical_db%%    for logical datasource name
         %%physical_db%%   for the physical database name (path)
         %%server%%        for the host name
         %%unique_server   for the host name
         %%port%%          for the host network port number
         %%Index%%         the index number of the databases

  Just like a `<QUERY>` the HTML between the start and end `<EXECUTE>` tag serves as a template, to be repeated for every section existing in the `empress_config_exml.ini` file used by the client.

  `%%unique_server%%` is special in that it doesn't refer to any part of a data source file, but, if present, limits the output to one template iteration per server in the `empress_config_exml.ini` file. This is useful for generating a list of available hosts, without the databases associated with them.

  ### 4.4.7 EH_INFO_EDIT

  The `TYPE="eh_info_edit"` is used for editing the information in the `empress_config_exml.ini` file. It has the following syntax:

         <EXECUTE
          TYPE="eh_info_edit"
          DBASE="datasource_name"
          SERVER="host_name"
          PORT="port_number">
            __KEY_ADD__ | __KEY_UPDATE__ | __KEY_DELETE__
            physical_database_name
         </EXECUTE>

  The `eh_info_edit` alters the `empress_config_exml.ini` file. The data source name is provided by the `DBASE="datasource_name"` clause. The host name is provided by the `SERVER="host_name"` clause. The physical name of the database is placed between the start and end tags of the `<EXECUTE>` block, preceded by the desired action, one of add, update or delete.

  If the database resides in the Empress Connectivity Server working directory, there is no need to prepend the full path of the database; otherwise a full path name of the database must be specified.

  By combining `TYPE="eh_info"` and `TYPE="eh_info_edit"`, a net application can allow users to build, edit, and view datasource files - important for managing and creating databases.

  ### 4.4.8 SCRIPT

  This allows the toolkit to pass text blocks to scripting language for logic processing. It has the following syntax:

         <EXECUTE
          TYPE="SCRIPT"
          [NAME="scripting_language"]
          [FLAGS="options"]>
            text_to_be_processed
         </EXECUTE>

  The defaults `NAME` and `FLAG` for the scripting language is `perl` and `-e`.

  The `TYPE="SCRIPT"` option should work with any scripting language that can take its input as a command line option: the `FLAGS` option should be set to instruct the script that it will receive its input as a command line option. For example:

         <EXECUTE
          TYPE="SCRIPT"
          NAME="sh"
          FLAGS="-c">
            find . -type f -newer NEW -print
         </EXECUTE>

  In order for the Toolkit to locate the scripting language you should either:

  1. Ensure that the path to the language is in the `PATH` environment of the HTTP server running the system.
  2. Include the path with the name, for example, `NAME="/usr/bin/sh"`

  ### 4.4.9 SYSTEM

  The `TYPE="SYSTEM"` is used for executing any program on the HTTP server machine. It has the following syntax:

         <EXECUTE
          TYPE="SYSTEM" NAME="third_party_program" [WAIT]>
            [command_line_parameters]
         </EXECUTE>

  Any executable program can be executed by specifying the path to that program as a `TYPE="SYSTEM" and NAME="third_party_program"`. If the program produces standard-out output, this will be displayed by the browser. Command-line parameters can be placed between the start and end tags of the `<EXECUTE>` block.

  Since `ehsql:writer` doesn't have any control over third-party programs, the `WAIT` clause can be added to insist that `writer` waits for the third party program to end before continuing. This is only useful if the results of the called program are needed to be displayed on the browser. For example:

         <html>
         <EXECUTE TYPE="SYSTEM" NAME="ls" WAIT>
            -a
         </EXECUTE>
         </html>

  If this file is given to `ehsql.cgi`, it will produce the results of the UNIX `ls` command on the browser. The listing in this case will be of the directory where `ehlink.cgi` or `ehsql.cgi` was run.

  Care should be taken not to compromise the security of your system by allowing net users to run potentially dangerous or invasive commands.

  ### 4.4.10 CONTENT-TYPE

  By default the Toolkit transmits a mime type of Text/HTML to the requesting HTTP Server. It will transmit other mime types depending on the files extension. However, the mime types of individual files can be set directly by adding the `TYPE="Content-Type"` into the file itself. This tag can be located anywhere in the file. The syntax is:

          <EXECUTE TYPE="Content-Type" NAME="mime type">

  eg

          <EXECUTE TYPE="Content-Type" NAME="text/plain">

  ### 4.4.11 EMPVERS

  This allows you to display version information about the HTML Toolkit. Syntax:

         <EXECUTE TYPE="empvers" DBASE="testdb">
         </EXECUTE>

  ### 4.4.12 INCLUDE

  Using INCLUDE allows you to include the files that are in the directory `$EMPRESSPATH/hypmedia/resources/include`. The current files in that directory are JavaScripts `required.js` and `datatype.js`. Syntax:

          <EXECUTE TYPE="INCLUDE">
              filename
          </EXECUTE>