This document describes the HotSQL database gateway product. It assumes that the reader has a basic understanding of the Hypertext Markup Language (HTML) and at least some familiarity with databases, SQL, or ODBC.
HotSQL is not a standalone database engine. It requires a working ODBC-capable database management system. Nearly all available Windows DBMS systems have ODBC drivers available for them. Microsoft even supplies ODBC drivers for desktop applications like MS Excel and for flat text files. At the present time, HotSQL has been tested with ODBC drivers for MS Access, MS SQL Server, and delimited text files. However, any other standard ODBC driver should work as well.
HotSQL also requires a CGI-capable Web server. Popular web servers for Windows NT include various Netscape servers, Microsoft Internet Information Server and O'Reilly WebSite. All of these have been tested successfully with HotSQL.
HotSQL is distributed in a single ZIP file. This archive contains the following files:
HotSQL requires a Windows NT or 95 compatible Web server. These include such products as Microsoft IIS, Netscape Communications and Commerce Servers and O'Reilly WebSite. Any Web server that supports the Common Gateway Interface should be OK.
To install the HotSQL engine, simply copy the HOTSQL.EXE file into your Web server's cgi-bin directory. See your web server's documentation for more about this directory and how to configure it.
That's it! You are ready to start using HotSQL.
ODBC is an acronym for the Open Database Connectivity API, the industry standard database access API. Many database vendors have provided support for this specification within their products. HotSQL takes advantage of ODBC to allow you to access any data source that has an ODBC driver available. See the documentation for your database product to learn more about what type of ODBC support it has.
You can configure an ODBC data source by using Microsoft's ODBC Administrator, supplied with Windows 95 and Windows NT operating systems. This program allows you to set up data sources, install ODBC drivers, and configure various options. Each individual ODBC driver has a help file which explains the options available.
IMPORTANT NOTE FOR NT USERS:
When configuring an ODBC data source for use with HotSQL under Windows NT, you may need to configure it as a "System" data source. A system data source allows any process on the local machine to connect to it. Because of the Windows NT security model, any CGI program launched from a service running as LocalSystem will be unable to connect to a "normal" ODBC data source.
Another option for some Web servers is to configure the Web server service to run as an actual user instead of as LocalSystem (no user). This gives programs launched through the CGI interface the same rights as the user specified for the service.
HotSQL provides a simple interface to access database systems. It essentially supports standard SQL statemtents embedded in HTML, along with a few useful macros for application functionality. This section explains some basics of the structure of a HotSQL application.
A typical HotSQL application might be a form that allows some data entry for a query on one page, then queries a database and displays the results in a table. This would be represented in HotSQL in the following way:
We have two HTML files, Page1.htm and Page2.htm. Page1 is a simple form that accepts a name from a user. Page2 displays the results of a database lookup of this name. We will create a subdirectory under our web server's root document directory called "sample". The pages will both be stored in this directory. Assume also that we have configured our server's executable directory as "cgi-bin".
Our data source in this example is called "NAMEDB". This was configured by using the ODBC Administrator program (available in the Control Panel). We added a data source, selected a driver, and configured it to refer to a particular database. The userid and password required to connect to the database are "userid" and "password" respectively.
The pages for this example appear below:
<!-- PAGE1.HTM - Accept some data for a query --> <html> <body> <form method=post action="/cgi-bin/hotsql.exe/sample/page2.htm"> <input type=text name="Name"> <input type=submit > </form> </body> </html>
<!-- PAGE2.HTM - Display result of a database lookup -->
<html>
<body>
<!-- Connect to data source and lookup the name -->
<!-- hsql connect NAMEDB, userid, password -->
<!-- hsql select NAME, DEPT, PHONE_NUM from TABLE1 where NAME = '$Name'
-->
<!-- Display results of the query -->
<!-- hsql fetch -->
<!-- hsql print "Name = $1 <br>" -->
<!-- hsql print "Department = $2 <br>" -->
<!-- hsql print "Phone number = $3 <br>" -->
</body>
</html>
The HotSQL engine captures the input from the first page and stores this as internal variables. These variables can be accessed in another HTML page (page2.htm in our example) by simply using a macro of the form: $variable.
Page2 has HotSQL commands embedded in comments. This is the standard form for inserting HotSQL directives into an HTML document. This lets you load the page into any browser or HTML editor without any confusion. In general, all HotSQL directives are of the form:
<!-- hsql [directive] -->
In the example above, the command "connect NAMEDB, userid, password" is a HotSQL directive that establishes a connection to an ODBC data source called NAMEDB. Immediately following is a normal SQL select statement, which will query a table called TABLE1. Note that to embed SQL statements, the syntax is simply the hsql keyword, followed by appropriate SQL construct. Finally, there is a macro in the SQL statement that refers to the variable 'Name'. The value of the user variable 'Name' will be substitued by HotSQL before performing the database query. Note that the value of 'Name' was set by input from Page1.htm.
Finally, there are several HotSQL print directives that will cause the results of the search to be inserted into the HTML page. Note the use of the macros of the form $n which allow the display of the current row of the result set. A result set is the data returned from a database query. In our example, the result set should consist of only one row and 3 columns, NAME, DEPT, and PHONE_NUM. Thus, the reference to $1 refers to the first result column, in this case, to the NAME column. Note that the directive "fetch" causes the first row of the result set returned by the query to populate the HotSQL result variables. Each time "fetch" is issued, the next row of data will populate the result variables.
This section desribes all of the basic commands available through HotSQL Version 1.2. This guide assumes familiarity with Structured Query Lanaguage (SQL) and basic database concepts.
To connect to an ODBC data source in HotSQL, the directive is:
The datasource parameter refers to the ODBC data source. The userid and password fields can be blankl. Your data source may have access restrictions for which these parameters are needed. Note that data source refers to the name given in the ODBC administrator, not the database name.
A successful connection will initialize the HotSQL engine for data access. Failure will simply cause subsequent operations to fail. An error message will be displayed in the HTML output to help you diagnose the problem.
You can use variables as the parameters to this command, for example, the input from a login form. See the Interacting with forms section for more information.
After establishing a succesful connection, any number of queries can be performed. The syntax is standard SQL syntax, invoked by embedding the SQL within a HotSQL comment. For example:
<!-- hsql select * from TABLE1 where NAME = 'Dave' -->
The above statement causes HotSQL to invoke an SQL query and to get an implicit handle to the result set. This implicit handle will be used in subsequent fetches or result variable references as described in the following sections.
exec <statement>
The exec directive can also be used to execute a SQL statement. Simply follow the directive with a valid SQL statement. This is useful if your database has commands that conflict with HotSQL, such as an "if" clause. For example:
<!-- hsql exec select * from TABLE1 where NAME = 'Dave' -->
Note that any SQL statement that the ODBC data source supports can be used by HotSQL. This includes INSERT, UPDATE, and DELETE statements, as well as stored procedures. See your ODBC driver's documentation for database-specific syntax.
You can use variable references anywhere in your SQL statement, including variables passed from a form or URL, or results of a previous query.
Example:
<!-- hsql select cust_name, cust_id from customers where cust_name
= '$name' -->
<!-- hsql fetch -->
<!-- hsql select product_name, description, quantity from orders where
cust_id = '$2' -->
The above example uses the results of a search in a customer table to match all orders by that customer's id. Note that a "fetch" is required before using the variable $2 in the second query.
fetch
The fetch directive can be used to get the next row of result data from a query. The individual column data for the current row is then stored by HotSQL and can be accessed by referencing the variables $1, $2, etc. ( from 1 to the number of available columns in the result set). Typically, however, you might wish to use the list directive to display the results of a query. This directive is described in the next section. Note that a fetch is required after an SQL query in order to populate the result variables with the first row of result data. When using the list command, no fetch is required since list implicitly fetches rows.
Once a query has been performed, you can use the resulting data to generate HTML output. This is accomplished by two HotSQL directives print and list.
The print directive allows you to output any text or HTML, along with result column data or user variables. As described earlier, data from a submitted user form is captured by the HotSQL engine. Similarly, the result of a query can be accessed row by row by using the $n directive. To display the current row of data, then, one might issue the directive:
<!-- hsql print "Current row = $1, $2, $3" -->
This would insert the expanded values of the current result row into the HTML page so that the user might see:
Current row = Dave, Marketing, 555-1234
The list directive works much like the print directive described above. The key difference is that it iterates through each row in the result set, outputting the format string for each row. For example, if an SQL query produces a set of rows, the list directive can be used to print out a table as follows:
... <!-- hsql select name, dept, phone from AddressList --> <TABLE BORDER> <tr><th> Name </th><th> Department </th><th> Phone </th></tr> <!-- hsql list "<tr><td>$1 </td><td>$2 </td><td>$3 </td></tr>" --> </TABLE> ...
This prints out a table that might look like:
Name | Department | Phone |
---|---|---|
Dave | Marketing | 555-1234 |
John | Sales | 555-9876 |
Martha | Advertising | 555-0001 |
The list command can also be used with a numeric argument that indicates the maximum number of rows to process. This allows you to limit the size of a result set. The following HTML is the same as for the above table, but will limit the output to the first 100 rows (or less).
... <TABLE BORDER> <tr><th> Name </th><th> Department </th><th> Phone </th></tr> <!-- hsql list 100 "<tr><td>$1 </td><td>$2 </td><td>$3 </td></tr>" --> </TABLE> ...
To use HTML forms with HotSQL, the form should use the POST method, and should have an action of the form:
http://host/cgi-bin/hotsql.exe/Destination.htm
This will call the HotSQL engine when the user submits the form. HotSQL reads all data submitted by the form and stores them for use in processing the destination page. The destination page can contain embedded macros that reference these variables. These variables can appear in any HotSQL directive. They are always of the form $variable, where variable is the case-sensitive name of the form input variable.
This feature can be used to do database update and inserts based on user input from forms. It can also be used to specifiy where clauses of SQL SELECT statements.
HotSQL can also retrieve variable data passed in a URL's query argument. For example, consider the following HotSQL directive:
<a href='/cgi-bin/hotsql.exe/mydoc.htm?Name=Joe'>Link</a>
When this link is clicked, HotSQL will read the variable Name from the URL and store it for use in the document 'mydoc.htm'. To pass non-constant values, you can use a HotSQL print directive to output an anchor tag as follows:
<!--hsql print "<a href='/cgi-bin/hotsql.exe/mydoc.htm?Name=$Name'>Link</a>" -->
This can be used for such things as "drill-down" functionality,
where the results of one query produce a list of hypertext links that can
then be clicked on to run a second query using a key obtained from the
first.
HotSQL also support the use of conditional expressions. These allow HTML to be generated based on the values of user variables from HTML forms and CGI environment variables. The syntax for a conditional expression is:
if <var> operator [value]
<statement list>
else
<statement list>
endif
where var is a variable that was passed by a user (see the Interacting
with forms section)
operator is one of: { =, !=, <, >, <=, >= }
value is a string ("xxxx"), number (nnn) or a variable.
Conditions can be nested up to 100 levels deep.
Examples:
<!-- hsql if $name = "Dave" --> Hello Dave, this is a message for you!<br> <!-- hsql endif -->
The above simply displays a message if the user variable (from a form or URL) is equal to "Dave".
<!-- hsql if $number > 1 --> You have more than one thing. <!-- hsql endif
The above displays the text if the form input field "number" is greater than 1.
<!-- hsql select name from guestbook where name = '$userid' --> <!-- hsql fetch --> <!-- hsql if $1 != "" --> <!-- hsql insert into guestbook values($userid, $address, $email) --> <!-- hsql else --> You are already registered as a guest!<BR> <!-- hsql endif -->
The above is used to check for the existence of a guest in the database before adding his information.
<!-- hsql if $password != $confirm --> Your password does not match your confirmation. Retype it please! <!-- hsql else Everything is ok. <!-- hsql endif -->
The above example compares two form input values to validate the creation of a user account and password. It will display an error message if the two values do not match, otherwise a success message will be displayed.
Whenever a request to the HotSQL engine is processed and HTML output in returned to the browser, HotSQL automatically disconnects from the database. In additon, if you wish to use more than one data source within the same page, a second connect directive will automatically cause the connection to the first datasource to close.
This release of HotSQL has the following limitations: