w3-msql is an interface between the World-Wide Web and mSQL. It is a mechanism that can be used to greatly simplify the use of a Mini SQL database behind a web server. Using w3-msql, you can embed SQL queries within your pages and have the results generated on the fly. The w3-msql program is used as a CGI script that your w3-msql enhanced pages are passed through. It should be referenced as
- /cgi-bin/w3-msql/Path/To/Your/Page.html
where /Path/To/Your/Page.html is a w3-msql enhanced html file within your WWW document tree. w3-msql will process the specified page and "fill in the blanks" by interpreting and processing the embedded mSQL commands.
A w3-msql directive is embedded within an html page using the following syntax :-
- <! msql command args >
The commands that are available are :-
<! msql connect [host] >
- Connect to the mSQL database engine. An option hostname can be provided to indicate that the database is running on a remote machine, for example :-
- <! msql connect www.some.domain >
Unlike the C programming language API for mSQL, you can only have one connection to a database server from within your w3-msql page. You can access multiple databases from the one connection by using the database directive outlined below.
<! msql close >
- Close the currently open mSQL database connection. You should call the close directive when you are finished with the database from within your page. Once you have closed the connection, you could open a new connection to another database server if your page requires data held in mSQL servers on different machines.
<! msql database DBName >
- Choose the database that you wish to access from your queries, for example :-
- <! msql database test >
If you need to access data from multiple database managed by the same mSQL server, you can simply issue the database directive again to select a new database to use. There is no limit to the number of times you can call the database directive in a single page so you can literally swap back and forth between databases at any time.
<! msql query "query text" QueryHandle >
- Submit a query to the database. The query text is submitted to the database and any returned data is stored in the QueryHandle. You use the QueryHandle to access the data later in your page. For example :-
- <! msql query "select name from users" q1 >
Once the query has been processed, the first row of the returned data is fetched and stored in the query handle. The row of data currently stored in the query handle is called the current row. The fetch and seek directives are provided by w3-msql to allow you to use other data rows as the current row. Another term used in association with the current row is the position of the data cursor. The data cursor is a logical pointer that indicates which row of the result table is the current row. The seek directive can be used to move the location of the data cursor and hence, change the current row.
The example above uses the SELECT SQL keyword. It should be noted however that any SQL query, including INSERT, UPDATE and DELETE can be used within the query text submitted via the query directive. Naturally, if anything other than SELECT is used, there will be no returned data available to the page. Use of query operators other than SELECT allows you to write HTML pages that actually modify the contents of the database. A simple example of this is to insert the contents of an HTML form into the database.
<! msql free QueryHandle>
- Frees the QueryHandle and any data associated with the query. For example :-
- <! msql free q1 >
<! msql print "format" >
- Print the contents of variables from the current row of a query handle, the environment or from data passed to the page from a GET or POST (such as from a form). The format string is similar to a printf() format string in that escape characters such as \n and \t are understood. The contents of variables are accessed by embedding the variables within the format string (like Perl, ESL, or Shell scripts) rather than by using references to variables such as %s etc. in C.
The order of priority for variables is internal w3-msql variables followed by environment variables. That is, when a variable is accessed, w3-msql first looks for the variable in the w3-msql symbol table and if it can't find it, it then looks for an enviornment variable by that name. The first variable it finds that matches the name specified is used.
To simplify the processing of forms (and to enable data to be passed between pages) W3-mSQL loads all data passed to it in the URL into it's symbol table. If you have a form entry such as < INPUT NAME="user" > then when you click the submit button, your browser will generate a URL like the following
- http://Your.Machine/Path/To/File.html?user=bambi
The ?user=bambi on the end of the URL reflects the name and contents of your form fields. W3-mSQL will see these values and load them into the symbol table so you can access them as variables in your page. If you referenced the variable $user in this example it would evaluate to bambi.
As the example above indicates, variables are referenced using a $ sign. This is the case for internal variables and environment variables. Accessing the contents of the current row from a query handle uses a difference format. Firstly, you have to indicate which query handle contains the information and secondly you have to indicate which field from the current row you want. The format used is
- @Handle . FieldOffset
That is, you use a @ for database variables (not the $ sign used for internal and environment variables), followed by the name of the query handle, followed by a '.', followed by the numerical index of the desired field in the row. Fields are number from left to right starting at 0. To illustrate this further, if the following query :-
- <! msql query "select name,age from people" result>
was submitted, @result.0 would correspond to the name field and @result.1 would correspond to the age field. You can reference any number of fields and other variables in a single format string. For example
- < ! msql print "Hello @result.0, your path is $PATH" >
<! msql print_rows QueryHandle "format" >
- The print_rows directive allows the entire contents of a Query Handle to be processed in one operation. The format specified is applied to each row of the remaining result data from the Query Handle, that is, all data from the position of the data cursor to the end of the result data is extracted and formatted. If the data cursor has been moved from the first row of data by using either fetch or seek directives, only the remaining data will be displayed. Naturally, the seek directive can be used to return the data cursor to the initial row of the result data before calling the print_rows directive.
This facility can be used to easily create lists, tables and select menus from the contents of a query. An example of each is given below :-
Table Creation
<! msql query "select name, address from staff" result>
<TABLE>
<TH>Name <TH> Address <TR>
<! msql print_rows result "<TD> @result.0 <TD> @result.1 <TR>"
</TABLE>
List Creation
<! msql query "select name, address from staff" result>
<UL>
<! msql print_rows result "<LI> @result.0 \n"
</UL>
Select Menu Creation
<! msql query "select name, address from staff" result>
<SELECT NAME=menu>
<! msql print_rows result "<OPTION> @result.0 \n"
</SELECT>
<! msql if ( condition ) >
<! msql else >
<! msql fi >- w3-msql provides an if-then-else construct for conditional inclusion of sections of an HTML page. If the condition evaluates to TRUE, the segment of the page between the IF and the ELSE or FI is processed. This may be normal HTML text or further w3-msql definitions. To enable complex pages to be created, w3-msql supports IF clauses nested to any level.
The structure of the condition statement is based on the syntax used by conditions in C. It supports the usual comparison operator, == != < <= > >= , as well as the C logical operators && and ||. Parenthesis may be using within the condition to group sections of the expression to control the evaluation. Parenthesis can be nested to any level. For example
- < ! msql if ( ($age < 50) && (($name == "fred") || ($name == "joe))) >
The IF directive will try to interpret the data and variables within the condition in the manner you intend. For example, if you provided a condition such as
- < ! msql if ( $age == 50 ) >
w3-msql would cast the value of $age to an integer value if possible because all w3-msql variables are text variables. If the variable in question does not contain a numeric string, the condition will abort and an appropriate error will be displayed. It should be noted that only the == and != operator may be used to compare string values. < <= > and >= can only be used on numeric values. Naturally, == and != can be used on numeric data too.
<! msql fetch QueryHandle>
- Fetches the next row of data from the query handle and updates the current row and the data cursor. example :-
- <! msql fetch q1 >
<! msql seek QueryHandle Position>
- Moves the data cursor for the specified query handle to the given position. Position 0 is the first row in the result data. If the value of position is negative, it will be replaced by 0. If the position is beyond the end of the table, the data cursor will be left pointing at the end of the table. The currnet row is replaced by the row of data located at the specified position. For example, to move to the 12th row of data returned in the query handle q1, you would call
- <! msql seek q1 12>