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.

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.

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

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

msql close

  <! msql close>

  <! msql database DBName>

Choose the database that you wish to access from your queries, for example :

  <! msql database test

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

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

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

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

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:

• REQUEST_METHOD - specifes the HTTP method, must be set to GET if the parameters are stored in QUERY_STRING.
• QUERY_STRING - used at HTTP method GET to store the parameters after a '?' in the URL. Example:
  http://localhost/doit/w3-msql/path/file.html?var1=A&amp.var2=B
  would be stored as
  var1=A&amp.var2=B
• PATH_INFO - path extension, as specified in the URL.
  http://localhost/doit/w3-msql/path/file.html
  would be translated by the WWW server to
  /path/file.html
• PATH_TRANSLATED - full path/file specification, resolved normally by the WWW server. If the WWW server (CERN, NCSA, etc.) has a setting
  Pass /* /www/pages/*
  the URL
  http://localhost/doit/w3-msql/path/file.html
  would be resolved to
  /www/pages/path/file.html

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?

Mini SQL was written by:

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

  E-mail: [email protected]
          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:  [email protected]
  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 [email protected].

Once you are subscribed you can send a message to the entire list by addressing it to [email protected]. 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 [email protected] - Thank you!