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.
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:
Explanation:
Example 2
URL:
Explanation:
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 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:
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
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:
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.
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.
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>
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.
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>
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.
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.
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:
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.
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">
This allows you to display version information about the HTML Toolkit. Syntax:
<EXECUTE TYPE="empvers" DBASE="testdb"> </EXECUTE>
<EXECUTE TYPE="INCLUDE">
filename
</EXECUTE>