Table of contents:

  Info
  Introduction
  The W3_mSQL language
    msql connect
    msql close
    msql database
    msql query
    msql free
    msql print
    msql print_rows
    msql if, else, fi
    msql fetch
    msql seek
    msql set
    msql setdefault
    msql convert
    msql translate
    variable $NUM_FIELDS
    variable $NUM_ROWS
  Examples
    Query and Insert
    Control
  Frequently asked questions (FAQ)
    FAQ - OS/2
  Author's Details
  Archive Location
  Mailing List

W3-mSQL 1.0.3 (OS/2 version)


Info

Gateway interface between WWW and mSQL
W3-mSQL 1.0.x for OS/2 2.x or Warp with TCP/IP
All programs included in this package are provided "as is", without
any warranty! Try them on your own risk.

This documentation is based on the HTML file w3-msql.htm and informations coming with the mailing list. It is based upon version 1.0 and has been adapted to the OS/2 port.

W3-mSQL has been developed as part of the Minerva Network Management Environment.

Copyright (c) 1993 - 1996 David J. Hughes (Hughes Technologies Pty Ltd)

Adaption to OS/2 made by Dirk Ohme

Introduction

W3-mSQL is an interface between the World-Wide Web (WWW) 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 specifuied page and "fill in the blanks" by interpreting and processing the embedded mSQL commands.

W3-mSQL supports the HTTP method GET in all versions, the method POST only in the OS/2 version of W3-mSQL.

Note:
'W3-mSQL.exe' is linked dynamically against mSQL.DLL

The W3_mSQL language

A W3_mSQL directive is embedded within an HTML page using the following syntax:

    <! msql command args>

The commands that are available are:

Note:
Commands marked with an asteriks (*) are undocumented features not mentioned in the original documentation.

There are further these internal variables available:

msql connect

  <! msql connect>
  <! msql connect [hostname]>
  <! msql connect "[hostname_with_domain]">

Connect to the mSQL database engine. An option hostname can be provided to indicate that the database is running on a remote machine. If the host name contains any dots ('.'), you have to place quotes ('"') around the hostname. On missing parameter hostname a connection to a database server on the local machine will be tried. For example:

  <! msql connect>
  <! msql connect localhost>
  <! msql connect "host.at.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.

Note:
Since version 1.0.3 of W3-mSQL and mSQL for OS/2 version 1.0.14b there is a user identification mechanism. Most WWW servers allow to protect HTML pages so only authorized person can access them. W3-mSQL now supports this technique.

On the CERN httpd you may protect a path (URL) with the following statements in your configuration file:

#
# start of configuration file
#
  ...
  Protection      internal        {     # name of the protection method
          Mask            all           # --> all IP addresses / hosts
          Passwordfile    int_passwd    # name of the username/password file
          Groupfile       int_group     # name of the username/groups file
  }                                     #
  Protect /int/*          internal      # path to be protected
  ...
  Exec    /int/doit/*     /www/cgi/*    # CGI path for authorized access
  Exec    /doit/*         /www/cgi/*    # CGI path for unauthorized access
  ...
  Pass    /int/*          /www/secret/* # document path for authorized access
  Pass    /*              /www/pages/*  # document path for unauthorized access
  ...
#
# end of configuration file
#



  http://localhost/page.htm             - unauthorized access
  http://localhost/int/page.htm         - authorized access with username
                                          and password checking

msql close

  <! 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

  <! 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 literaly swap back and forth between databases at any time.

msql query

  <! 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.

Note:
The example above uses the SELECT SQL keyword. If 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.

Note:
Access to empty handles result in an uncontrolled behavior of W3-mSQL. Therefor it is recomended to check the number of lines returned first before output (see variable $NUM_ROWS):

  <! msql if ($NUM_ROWS != 0)>
  ... data output ...
  <! fi>

msql free

  <! msql free QueryHandle>

Frees the QueryHandle and any data associated with the query. For example:

  <! msql free q1>

msql print

  <! msql print "format">

Print the contents of variables from the current row of the 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.

Note:
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 environment 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 different 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 first 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 numbered 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

  <! msql print_rows QueryHandle "format">

The print_rows directive allows the entire contents of a QueryHandle 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 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:

msql if, else, fi

  <! 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 of 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 operators, == != < <= > >=, as well as the C logical operators &amp.&amp. (logical AND) and || (logical OR). Parenthesis may be used 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)&amp.&amp.(($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 provide 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 != operators may be used to compare string values. <, <=, > and >= can onyl be used on numeric values. Naturally, == and != can be used on numeric data too.

msql fetch

  <! msql fetch QueryHandle>

Fetches the next row of data from the query handle and updates the current row and the data cursor Exampe:

  <! msql fetch q1>

msql seek

  <! msql seek QueryHandle Position>

Moves the data cursor for the specified QueryHandle 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 current 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>

msql set

Undocumented command due to original documentation!
  <! msql set Variable = Value>

Set an internal variable Variable to a given Value. Value can be either a string, another variable or a handle, but no combination of these.

  <! msql set $var1 = "something">

msql setdefault

Only available since W3-mSQL 1.0.3!
  <! msql setdefault Variable = Value>

Set an internal variable Variable to a given Value if the Variable isn't set previously. Value can be either a string, another variable or a handle, but no combination of these. This routine provides the ability to set default values for parameters.

  <! msql setdefault $var1 = "something">

msql convert

Only available since W3-mSQL 1.0.3!
  <! msql convert Variable>

Converts an internal variable Variable so it can be stored into a database without a problem. All special characters of Variable will be translated to escape sequences, i.e. ' will be converted to \'.

  <! msql convert $var1>

msql translate

Undocumented command due to original documentation!
  <! msql translate Variable "Char1" "Char2">

Translates all characters Char1 of the given variable Variable by the replacement character Char2.

variable $NUM_FIELDS

The internal variable NUM_FIELDSS is set at execution of a SELECT query and reflects the number of fields (columns) found by the selection (see msql query).

variable $NUM_ROWS

The internal variable NUM_ROWS is set at execution of a SELECT query and reflects the number of rows found by the selection (see msql query).

Examples

This section contains some simple examples showing how to use the W3-mSQL tool. In order to use the sample W3-mSQL page,

  1. you have to create a database called test:
      msqladm create test
    
  2. You then have to create a table called test and fill it with some test data:
      msql test < sample.src
    
    sample.src:
      create table test (
        user   char(20),
        age    int,
        phone  char(20)
      )\g
    
      insert into test values ('David J. Hughes', 0, '0412 644 078')\g
      insert into test values ('Dirk Ohme', 25, '07071 703-190')\g
    
      \q
    
  3. Put the sample pages sample.htm and chkbox.htm in your HTML document path.
  4. Copy W3-MSQL.EXE into your CGI directory.
  5. Access the sample page with your browser - be aware of the directory mapping the WWW server uses. If for instance the browser maps an URL path \doc\ to \pub\www\ and sample.htm is located in \pub\www, then you have to use an URL like this:
      http://Your.Machine/CGI-Path/w3-msql/doc/sample.htm
    

Query and Insert

The following shows the contents of sample.htm, a sample page showing access to a mSQL database:

<!---------------------------------------------------------------------------->
<! Simple Name/Age/Phone Sample                                               >
<!      shows how to display a database table and how to insert new data      >
<!                                                                            >
<! starting parameter:                                                        >
<!    http://localhost/doit/w3-msql/w3-msql/sample.htm                        >
<!                     ^^^^         ^^^^^^^                                   >
<!                  replace with your settings!                               >
<! note:                                                                      >
<!   You need a mSQL server running with a database 'test' and a database     >
<!   table 'test' with the entries name, age and phone.                       >
<!---------------------------------------------------------------------------->

<!-- set the default values  -->
<! msql setdefault $insert = "false">
<! msql setdefault $name   = "">
<! msql setdefault $age    = "">
<! msql setdefault $phone  = "">

<HTML>
  <HEADER>
    <TITLE>w3-mSQL sample</TITLE>
  </HEADER>
  <BODY>
    <CENTER>
      <H1>Page Title</H1>
    </CENTER>

<!-- make a connection to the local database server, using database 'test' -->
<! msql connect>
<! msql database test>

<!-- do insert  -->
<! msql if ($insert == "true")>
<!   msql convert $name>
<!   msql convert $phone>
<!   msql query "insert into test values('$name', $age, '$phone')" q0>
<!   msql free q0>
<! msql fi>

    <H2>Bambi's phone number</H2>
    <P>
      <BLOCKQUOTE>

<!-- start a SELECT, display the data and free the handle -->
<! msql query "select phone,age from test where user='Bambi'" q1>
<! msql print_rows q1 "$0 ... $1">
<! msql free q1>

      </BLOCKQUOTE>
    </P>
    <P>

<!-- start a second SELECT using an other handle -->
<! msql query "select user,age,phone from test order by user" q2>

      <TABLE BORDER WIDTH="100%">
        <TR>
        <TH>Name
        <TH>Age
        <TH>Phone

<!-- display the query data in a table structure -->
<!msql print_rows q2 "<TR><TD>$0\n<TH>$1\n<TD>$2\n" >

      </TABLE>

<!-- free handle -->
<! msql free q2>

<!-- third SELECT -->
<! msql query "select user from test order by user desc" q3>

    </P>
    <P>
      <H2>List of usernames</H2>
      <BR>
      <UL>

<!-- display results in an unordered list -->
<! msql print_rows q3 "<li>$0\n">

      </UL>

<!-- free handle -->
<! msql free q3>

<!-- close connection to database server -->
<! msql close >

    </P>

<!-- display input form -->
  <FORM ACTION="/doit/w3-msql/w3-msql/sample.htm">
        <INPUT TYPE=HIDDEN NAME="insert" VALUE="true">
    <BR>Name:
        <INPUT TYPE=TEXT NAME="name" VALUE="">
    <BR>Age:
        <INPUT TYPE=TEXT NAME="age" VALUE="">
    <BR>Phone:
        <INPUT TYPE=TEXT NAME="phone" VALUE="">
    <BR><INPUT TYPE=SUBMIT>
    <BR><INPUT TYPE=RESET>
  </FORM>

  </BODY>
</HTML>

<!-- end of file -->

Control

The following shows the contents of chkbox.htm, a sample page showing how to use control mechanisms within WWW:

<!--------------------------------------------------------------------------->
<! Checkbox example                                                          >
<! shows how to transfer boolean values from a checkbox for use with W3-mSQL >
<!                                                                           >
<! starting parameter:                                                       >
<!    http://localhost/doit/w3-msql/w3-msql/chkbox.htm?checkbox=false        >
<!                     ^^^^         ^^^^^^^                                  >
<!                  replace with your settings!                              >
<!                                                                           >
<! note:                                                                     >
<!    Does not need the mSQL server (no DB connection will be established)   >
<!--------------------------------------------------------------------------->

<! msql setdefault $checkbox = "false">

<HTML>
  <HEADER>
    <TITLE>w3-mSQL checkbox sample</TITLE>
  </HEADER>
  <BODY>
    <CENTER>
      <H1>Checkbox sample</H1>
    </CENTER>
    <P> Previous selection was:
<! msql if ($checkbox == "true") >
<B>TRUE</B>
<! msql else>
<B>FALSE</B>
<! msql fi>
    <FORM ACTION="/doit/w3-msql/w3-msql/chkbox.htm">
      <INPUT TYPE=CHECKBOX NAME="checkbox" VALUE="true"> TRUE?
      <BR>
      <INPUT TYPE=SUBMIT>
    </FORM>
    </P>
  </BODY>
</HTML>
<!--------------------------------------------------------------------------->

Frequently asked questions (FAQ)

The following sections contain questions and answers to various problems with bot Unix and OS/2 version of W3-mSQL. There are the following sections:

FAQ - OS/2

This section deals with special problems of the OS/2 version of W3-mSQL.

Q: On starting relshow, msqldump or w3-mSQL I get an error 'SYS1804: Can't find file MSQL'

A: OS/2 can't find the dynamic link library mSQL.DLL. Check, if there is mSQL.DLL in a path accessable via the LIB_PATH entry in your CONFIG.SYS. You may include the directory \lib\ or \bin\ in the LIB_PATH line.

Note:
On the OS/2 port of the CERN (httpd) WWW server, a good location is the server's binary directory, where httpd.exe is located. Therefore the LIB_PATH should contain '.' for the current directory.

Q: When I set a BACKGROUND image in a BODY statement, it won't be displayed with W3-mSQL

A: For the first moment it looks like a bug. But it isn't. When a page is loaded through W3-mSQL, the path from the URL is not the same, as if you would address the HTML page directly (without W3-mSQL). So make sure you use an absolute path when adressing images!

Change
    <BODY BACKGROUND="wall.gif" BGCOLOR="#000000">
to
    <BODY BACKGRUND="/path/wall.gif" BGCOLOR="#000000">
if 'path' is the path to the directory containing the image.

Q: I get a parsing error at '.' in the connect statement

A: If you use a host specifier containing a domain qualifier (i.e. something separated by dots '.'), put a quote '"' at both start and end of the host string. This new syntax has been introduced within W3-mSQL 1.0.2

Q: Is there a way I can run a w3-msql test off the command line?

A: There is the possibility executing W3-mSQL off the command line. Therefore it is necessary to set some environment variables accordingly the WWW server would do. The variables are:

To simulate a call http://localhost/doit/w3-msql/w3msql/sample.html which should be reloved to a path
Pass /w3msql/* /www/sql/*
you have to set the environment variables like this:
    set REQUEST_METHOD=GET
    set PATH_INFO="w3msql/sample.html"
    set PATH_TRANSLATED="/www/sql/sample.html"

Note:
OS/2 prevents using a '=' within a SET instruction, so that parameters could not be transfered via QUERY_STRING when operating from command line! To come across this limitation, write a small REXX script for setting the environment and starting W3-mSQL.

Q: How can I prevent a user from inserting more data in a (text) database column than specified?

A: The easiest way is to limit the entry field in your HTML form. Since HTML 2.0 there is the possibility to set a maximum length for a entry field::
    <INPUT TYPE=TEXT NAME=... MAXLENGTH=max_number_of_chars VALUE=...>

Author's Details

Mini SQL was written by:

  David J. Hughes
  Senior Network Programmer (and Ph. D. lunatic)
  Bond University
  Australia

  E-mail: bambi@Bond.edu.au
          http://Bond.edu.au/People/bambi.html
  Fax:    +61 75 951456

Mini SQL has been ported to OS/2 by:

  Dirk Ohme

  T&ue.bingen, Germany

  E-mail:  Dirk.Ohme@transtec.de
  Fidonet: 2:246/2001.9@fidonet

Archive Location

The primary source of information relating to Mini SQL is Hughes Technologies Web Site. It contains all current information and pointers to the software distribution, mailing list archives, and other important information. The Hughes Technologies Web Site is located at:

  http://Hughes.com.au/

The latest version of the OS/2 port can be found at :

  Host:   rzsco.fh-albsig.de (141.87.110.2)
  URL:    http://rzsco.fh-albsig.de/~ohme
  Files:  FILES/msql*.lsm (description)
          FILES/msql*.zip (Zip 2.0.1 archive, Source &amp. Binaries)
  Note:   OS/2 port

Mailing List

A mailing list is operated to provide a place for common users of mSQL to discuss the product. It is currently operated at Bunyip Information Systems in Canada (a long-time user of Mini SQL) and we thank them for their help and support. To subscribe to the mailing list, send an e-mail message containing the word "subscribe" to msql-list-request@Bunyip.com.

Once you are subscribed you can send a message to the entire list by addressing it to msql-list@Bunyip.com. Please note that there are usually between 600 and 1000 mSQL users subscribed to the mailing list at the time of writing so it is an excellent forum for asking general mSQL user questions.

If you have special questions, suggestions, etc. for the OS/2 port, please send your mail directly to Dirk.Ohme@transtec.de - Thank you!