The Unofficial mSQL FAQ

$Revision: 1.6 $

$Date: 1995/11/23 04:32:34 $

                                    Preamble

This FAQ is posted to the mSQL mailing list twice a month - on or around the
1st and 16th of the month. This posting will occur even if the contents of the
FAQ have not changed since the last posting. Additional postings may occur from
time to time, when necessary patches are announced or when a new release of
mSQL is made available for example.

A text copy of this FAQ can be obtained via anonymous ftp from:

    ftp://bond.edu.au/pub/Minerva/msql/faq.txt

or an HTML copy can be obtained via anonymous ftp from:

    ftp://bond.edu.au/pub/Minerva/msql/faq.html

It may take a couple of days for the new versions of the FAQ to be moved into
the mSQL area. If you're desperate for the latest FAQ and you missed it on the
mSQL mailing list, try looking for:

    ftp://bond.edu.au/pub/Minerva/msql/Incoming/faq.txt

        or

    ftp://bond.edu.au/pub/Minerva/msql/Incoming/faq.html

This FAQ is maintained by Peter Samuel .

While every attempt is made to ensure that the information contained in this
FAQ is accurate, no guarantees of accuracy can or will be made.

Third party applications mentioned in this FAQ may not be compatible with the
current release of mSQL - by necessity their development will lag that of mSQL.
If you have any questions concerning their status please contact the mSQL
mailing list or the author of the application in question.

New questions in the FAQ are marked with (=). Questions that have been modified
since the last release of the FAQ are marked with (-).

NOTE: BEFORE POSTING A QUESTION TO THE mSQL MAILING LIST, PLEASE READ THE
SECTION "How do I post a question to the mSQL mailing list".

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

                                    Contents

   * General Information
       1.  What is mSQL?
       2.  What platforms will it run on?
       3.  Is it free?
       4.  Where can I get it?
       5.  (-) What is the latest version?
       6.  When will the next version be released?
       7.  Why is David called Bambi?
       8.  Why is this an unofficial FAQ?
       9.  How is this FAQ prepared?

   * Help and Support
       1.  (-) Are there any recommended patches to the latest version?
       2.  Is online documentation available?
       3.  Is there a mailing list for mSQL?
       4.  How do I post a question to the mSQL mailing list? (PLEASE READ
          THIS!)
       5.  I've found a bug. How can I tell if it's been fixed yet?
       6.  How do I report a bug in mSQL?
       7.  How do I contribute a patch to mSQL?
       8.  How do I contribute code to the mSQL community?

   * Features and Limitations
       1.  What support programs are bundled with mSQL?
       2.  What datatypes are supported?
       3.  What SQL commands are supported?
       4.  Does mSQL support keys?
       5.  What functions does the mSQL API provide?
       6.  Are views or virtual tables supported?
       7.  Does mSQL support table aliasing?
       8.  Are column constraints supported?
       9.  Are stored procedures supported?
      10.  Are access privileges supported?
      11.  Does mSQL support BLOBs?
      12.  Are the transaction commands, BEGIN, COMMIT, and ROLLBACK supported?
      13.  What are the limits on table and field names?
      14.  What other limits can be modified?
      15.  How much data can mSQL address?
      16.  Are there any limitations in the way mSQL handles logical
          expressions?
      17.  How does mSQL return values?
      18.  How does SELECT return rows?
      19.  Can mSQL nest tables?
      20.  What storage overheads does mSQL have?
      21.  Does msqld allocate more RAM to itself as new databases are added?
      22.  Does performance degrade as the number of databases increase?

   * Installation Problems
       1.  Under Irix (SGI) I get problems relating to my username
       2.  On OSF/1 or HP-UX I have trouble starting msqld at boot time
       3.  Should I use cc or gcc when building mSQL on my Dec Alpha running
          OSF/1?
       4.  Does mSQL work with Linux mmap?
       5.  (-) Does mSQL work with Solaris 2.3 mmap?
       6.  I'm having trouble compiling MsqlPerl-1.03 with mSQL under HP-UX
       7.  How can I install mSQL on a SCO Unix system?

   * Runtime Problems
       1.  msqladmin will not let me create a database
       2.  When I start msqld it complains about an ACL file
       3.  When I start msqld it complains about a PID file
       4.  I've just installed the latest version of mSQL and now my own
          applications won't work!
       5.  Access control doesn't work with my setuid applications
       6.  Why do I see an "Address already in use" error message when I
          attempt to start msqld?

   * How do I ....?
       1.  How do I embed single quotes in a field?
       2.  What other characters need special treatment?
       3.  How do I handle null fields?
       4.  How do I perform case insensitive matches?
       5.  How do I add a column to an existing table?
       6.  When should I call msqlConnect() in a parent/child situation?
       7.  Can I use mSQL reserved words as field or table names?
       8.  How do I find the maximum or minimum value in a table?
       9.  How can I determine the structure of a database?
      10.  What happens when the mSQL server goes down between requests?

   *  Contributed Code and Third Party Applications

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

                              General Information

What is mSQL?

mSQL is mini SQL, a light weight database engine developed by David J. Hughes
 at Bond University, Australia. It has been designed to
provide fast access to stored data with low memory requirements. As its name
implies mSQL offers a subset of SQL as its query interface. Although it only
supports a subset of SQL, everything it supports is in accordance with the ANSI
SQL specification.

-------------------------------------------------------------------------------

What platforms will it run on?

mSQL has been developed under Sun OS 4.1.1 but has been tested under Solaris
2.3, Ultrix 4.3, Linux, and OSF/1 (cc not gcc). That said, it should "autoconf"
and build on most BSD derived systems, SVR4 based systems or POSIX O/S's (that
should cover most of them). It has been reported that it works out-of-the-box
on HP-UX, NeXT, SCO, Sequent, Cray, Tandem, *BSD and a few others.

-------------------------------------------------------------------------------

Is it free?

David Hughes writes:

"mSQL has been released in the past under terms known as 'conscience-ware', the
basic concept of which was that companies that used the software could
contribute a small amount to the continued development of the software without
any strict rules being placed upon such 'donations'. Although the concept
sounds fair, it failed badly with only 3 contributions being made from over
3,600 copies of mSQL-1.0.5 that were ftp'ed from my machine alone. Over 1,000
of those copies went to commercial organisations and I receive many questions a
day from companies using mSQL behind their WWW servers etc who are looking for
free support.

In an attempt to balance this out and allow me to devote some time to mSQL
(rather than other pursuits that I do to generate an income), mSQL is now
shareware. I still believe in free software over the Internet and cooperation
in research so the new license is designed not to hurt Universities, research
groups and other people that _should_ have free access to software. Commercial
organisations that are using this so that they don't have to buy an Oracle or
an Ingres will now have to buy mSQL (at a minute fraction of the cost of one of
the commercial offerings).

Please read the doc/License file to see if you are required to register your
copy. An invoice is included in both Postscript and ASCII format to ease the
generation of payments."

As of release 1.0.10, the cost of mSQL is:

    Commercial Installation - AUD $225

    Private Installation - AUD $65

Exchange rates vary wildly, but at the time of preparing this FAQ, the
Australian dollar was trading at about 0.75 US dollars. This information is
provided as an indication only. You MUST check your local exchange rates before
preparing to purchase mSQL.

-------------------------------------------------------------------------------

Where can I get it?

mSQL can be obtained via anonymous ftp from Bond University, Australia. The
latest version can be found in the directory:

    ftp://bond.edu.au/pub/Minerva/msql/

-------------------------------------------------------------------------------

What is the latest version?

At the time of compiling this FAQ, the latest released version was 1.0.10. The
much talked about version 2 was still being developed.

It is available via anonymous ftp from:

    ftp://bond.edu.au/pub/Minerva/msql/msql-1.0.10.tar.gz
    (198076 bytes)

-------------------------------------------------------------------------------

When will the next version be released?

David has suspended all development work on mSQL version 1.x and is
concentrating his efforts on version 2. However, he will continue to provide
any necessary bug fixes for the current release of mSQL.

mSQL development is NOT David's primary role. He does this work in whatever
spare time he has available so PLEASE don't pester him with requests about when
we can expect version 2. Take the zen approach and just let it happen :)

-------------------------------------------------------------------------------

Why is David called Bambi?

Bambi is a nickname that David acquired quite a long time ago. He claims there
are no derogatory implications associated with the name. If you'd like to find
out more send him some mail at .

-------------------------------------------------------------------------------

Why is this an unofficial FAQ?

This is an unofficial FAQ because it hasn't been officially sanctioned by the
author of mSQL - David Hughes. Someday this may become an official FAQ.

-------------------------------------------------------------------------------

How is this FAQ prepared?

The FAQ is written in HTML and proofread using version 1.12 of the Netscape
Navigator running under Solaris 2.4. The HTML syntax is checked using version
1.011 of Neil Bowers'  weblint package. Text versions of the
FAQ are created by saving the document as a text file from within the Netscape
Navigator.

(See http://www.khoros.unm.edu/staff/neilb/weblint.html for more details on
weblint).

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

                                Help and Support

Are there any recommended patches to the latest version?

There are no official patches to any release of mSQL. If modifications need to
be made, a new version of mSQL will be released.

The above notwithstanding, there is one unofficial recommended patch that
should be applied to mSQL version 1.0.10.

This patch, contributed by Rasmus Lerdorf  fixes a problem where
the mSQL database server will not notice that a client has died.

Rasmus writes:

    A second change is actually a bug fix in my opinion. If you have a
    look at the writePkt() function in net.c you will notice that it is
    a void function. The fact that writing a packet to a client socket
    is never checked for errors means that if a client goes away for
    whatever reason, msqld will continue sending the data to an invalid
    file descriptor. This is especially noticeable in www environments
    where people are likely to hit the Stop button or their back
    buttons in their browsers on any screen which takes a little while
    to load. If enough people do this, msqld is sitting around spinning
    its wheels feeding data to bogus file descriptors. The fix here is
    obvious and simple. Make writePkt() return an error status and
    check it. If the receiving socket has disappeared, stop sending
    data.

*** ./src/msql/net.c    Thu Nov 23 14:51:39 1995
--- ./src/msql/net.c.orig       Tue Oct 17 11:24:03 1995
***************
*** 66,72 ****

! int writePkt(fd)
        int     fd;
  {
        u_char  *cp;
--- 66,72 ----

! void writePkt(fd)
        int     fd;
  {
        u_char  *cp;
***************
*** 84,95 ****
                numBytes = write(fd,packetBuf + offset, remain);
                if (numBytes == -1)
                {
!                       return(-1);
                }
                offset += numBytes;
                remain -= numBytes;
        }
-       return(0);
  }

--- 84,94 ----
                numBytes = write(fd,packetBuf + offset, remain);
                if (numBytes == -1)
                {
!                       return;
                }
                offset += numBytes;
                remain -= numBytes;
        }
  }

*** src/msql/msql_priv.h        Thu Nov 23 14:59:07 1995
--- src/msql/msql_priv.h.orig   Thu Nov 23 14:58:58 1995
***************
*** 298,304 ****

! int writePkt();
  int readPkt();

--- 298,304 ----

! void writePkt();
  int readPkt();

This is a reverse patch. If it is applied in the traditional fashion, patch
will ask the question:

    Reversed (or previously applied) patch detected!  Assume -R? [y]

This is intentional. This (hopefully) ensures that the user will have to think
a little before applying the patch.

To avoid conflicts with mismatched whitespace, apply this patch using the "-l"
option of patch.

-------------------------------------------------------------------------------

Is online documentation available?

A set of WWW pages for mSQL, w3-msql, ESL and Minerva are now available in the
Hughes Technologies area of Web Publishing Australia. The URL is

    http://AusWeb.com.au/computer/Hughes/

-------------------------------------------------------------------------------

Is there a mailing list for mSQL?

Yes.

To subscribe to the list, send the word "subscribe" in the body of your message
to msql-list-request@bunyip.com.

To unsubscribe from the list send the word "unsubscribe" in the body of your
message to msql-list-request@bunyip.com.

Postings should be addressed to msql-list@bunyip.com.

Archives of the mailing list are available via anonymous ftp from:

    ftp://ftp.bunyip.com/pub/mailing-lists/msql-list.archive/

Each month's archive is stored in a file:

    msql-list.archive.YYMM

where YYMM represents the year and month. So the archive for October 1995 would
be in the file:

    msql-list.archive.9510

These files are also available from the majordomo mailing list server at
bunyip.com. To receive a list of the archive files available as well as the
majordomo help file send a message to majordomo@bunyip.com with the text:

    index msql-list
    help
    END

in the body of the message.

To reach a human for help send a note to:

    owner-msql-list@bunyip.com

or

    listmaster@bunyip.com

The mailing list discusses issues that arise from the use of mSQL and w3-msql
(both products developed by David Hughes). Often discussions on contributed
software arise but it is probably best to take these discussions off line and
summarise solutions back to the list.

Traffic on the list is moderate. There were approximately 2400 postings between
February 1 1995 and October 18 1995 which gives an average of around 280
messages per month. (If you think this is high, try subscribing to the
firewalls list - this has an average of around 1000 postings per month!)

-------------------------------------------------------------------------------

How do I post a question to the mSQL mailing list?

Before you post a question to the mSQL mailing list, read the manual that came
with the mSQL distribution. Also, ask yourself the following question:

    Is there any way I can find the answer to this question myself?

If you can figure out a way to simply find the answer, then it will probably be
quicker than asking the list. If you think your answer would be helpful to
others then post a summary to the mailing list.

Postings should be addressed to msql-list@bunyip.com.

IF YOU POST A QUESTION TO THE LIST ASKING FOR HELP, YOU MUST INCLUDE THE
FOLLOWING INFORMATION!

  1. The operating system you are using - for example Solaris 2.4 or Linux
     1.3.1. On Unix like systems, this can usually be obtained by running the
     command:

         uname -a

  2. The processor type - for example sparc, Intel 386, HP PA-RISC, mc68020.
     Often the same operating system may run on different processors.

  3. The output of the command:

         msqladmin version

  4. Which, if any, unofficial patches you have applied to the version of mSQL
     you're using.

Failure to include these details makes it almost impossible to pinpoint the
cause of your problem.

-------------------------------------------------------------------------------

I've found a bug. How can I tell if it's been fixed yet?

The status of the development is reflected in the HISTORY file. An on-line copy
of the HISTORY file is kept on the Hughes Technology web pages. This file is
updated as each modification is made to the sources. Any bugs that have been
fixed in the code since it was last release will be mentioned in the on-line
release history information. You can access this file on the web as:

    http://AusWeb.com.au/computer/Hughes/msql/history.htm


-------------------------------------------------------------------------------

How do I report a bug in mSQL?

The first thing to remember is that you should NOT mail David Hughes (aka
Bambi) directly with your questions. This just slows the process down. Any
questions you have or bug reports you wish to make should be sent to the mSQL
mailing list.

If you want to report a bug, send a report to the mSQL mailing list at
msql-list@bunyip.com. Please include the following:

   * the operating system and version - for example Solaris 2.4 or HP-UX 9.1
   * the processor you are using - for example sparc or Intel 486
   * include the output of "msqladmin version"
   * which, if any, unofficial patches you have applied to the version of mSQL
     you're using
   * include a dump of a test database using msqldump
   * include an mSQL script that will show the bug

You should also ensure that you are running the latest (stable) version of mSQL
before mailing the list as David fixes bugs in every version.

-------------------------------------------------------------------------------

How do I contribute a patch to mSQL?

Following this procedure will standardise the manner in which unofficial mSQL
patches are distributed to the mSQL user community.

  1. Ensure that you are patching the latest version of mSQL.

  2. Ensure that you have applied any necessary unofficial recommended patches
     to the latest version.

  3. Make your changes to the "src" hierarchy of mSQL. For example, if you need
     to patch the file:

         msqld.c

     Save the original file as follows:

         cp ./src/msql/msqld.c ./src/msql/msqld.c.orig

     Make your changes to the file:

         ./src/msql/msqld.c

  4. Test, double test and triple test your patch to make sure it REALLY fixes
     a problem.

  5. Generate a difference listing suitable for use by Larry Wall's patch
     program:

         diff -c  ./src/msql/msqld.c ./src/msql/msqld.c.orig

  6. Post this difference listing and an explanation of the patch to the mSQL
     mailing list.

If at all possible, try to avoid posting 'monster' patches. If your patch
modifies a number of separate aspects of mSQL, split each of these into
different patches. This makes it easier for people to apply feature specific
patches to mSQL while avoiding unwanted feature patches.

-------------------------------------------------------------------------------

How do I contribute code to the mSQL community?

Contributed code can be uploaded to the ftp server at Bond University
Australia. Place your code in the directory:

    ftp://bond.edu.au/pub/Minerva/msql/Incoming

then notify David at  and he will place it in the mSQL
contributions directory:

    ftp://bond.edu.au/pub/Minerva/msql/Contrib

You may like to discuss your proposed code with others on the mSQL mailing
list. The subscribers to this list may be able to help you with improvements or
modifications to your code or advise you of work already available in your
area.

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

                            Features and Limitations

What support programs are bundled with mSQL?

mSQL is bundled with the following programs:

           msqld     the mSQL database server.

         msqladmin   handles administrative details such as creation
                     and deletion of databases, server shutdown etc.

            msql     the mSQL monitor. It provides a user interface
                     for making SQL queries on databases.

                     Dumps a database in ASCII format. The dump takes
          msqldump   the form of SQL commands so that the entire
                     database can be re-created from the dump file.

          relshow    The mSQL schema viewer. Shows table details
                     for a given database.

For more details see the documentation that comes with mSQL.

-------------------------------------------------------------------------------

What datatypes are supported?

mSQL supports the following datatypes:

    char
    int         (4 bytes)
    real        (8 bytes)

The internal storage for types int and real is held at 4 bytes and 8 bytes
respectively regardless of the system architecture you're using. So even on 64
bit Crays a real will be 8 bytes.

For more details see the documentation that comes with mSQL.

mSQL version 2 will support more data types.

-------------------------------------------------------------------------------

What SQL commands are supported?

mSQL supports the following SQL commands:

        CREATE TABLE table_name (
            col_name col_type [ not null | primary key ]
            [, col_name col_type [ not null | primary key ] ]**
        )

        DROP TABLE table_name

        INSERT INTO table_name [ ( column [ , column ]** ) ]
            VALUES (value [, value]** )

        DELETE FROM table_name
            WHERE column OPERATOR value
                [ AND | OR column OPERATOR value ]**

                OPERATOR can be <, >, =, <=, >=, <>, or LIKE

        SELECT [table.]column [ , [table.]column ]**
            FROM table [ = alias] [ , table [ = alias] ]**
            [ WHERE [table.]column OPERATOR VALUE
                [ AND | OR [table.]column OPERATOR VALUE]** ]
            [ ORDER BY [table.]column [DESC] [, [table.]column [DESC] ]

            OPERATOR can be <, >, =, <=, >=, <>, or LIKE
            VALUE can be a literal value or a column name

        UPDATE table_name SET column=value [ , column=value ]**
        WHERE column OPERATOR value
            [ AND | OR column OPERATOR value ]**

            OPERATOR can be <, >, =, <=, >=, <>, or LIKE

For more details see the documentation that comes with mSQL.

mSQL version 2 will support more SQL commands.

-------------------------------------------------------------------------------

Does mSQL support keys?

Yes. Any single field of a table can be designated as the primary key. These
keys are, by definition, unique. In its current implementation, mSQL does not
support multiple keys within a table.

For more details see the documentation that comes with mSQL.

-------------------------------------------------------------------------------

What functions does the mSQL API provide?

The mSQL API provides the following functions:

            int         msqlConnect(char *host);

            int         msqlSelectDB(int sock, char *dbname);

            int         msqlQuery(int sock, char *query);

            m_result *  msqlStoreResult();

            void        msqlFreeResult(m_result *result);

            m_row       msqlFetchRow(m_result *result);

            void        msqlDataSeek(m_result *result, int pos);

            int         msqlNumRows(m_result *result);

            m_field *   msqlFetchField(m_result *result);

            void        msqlFieldSeek(m_result *result, int pos);

            int         msqlNumFields(m_result *result);

            m_result *  msqlListDBs(int sock);

            m_result *  msqlListTables(int sock);

            m_result *  msqlListFields(int sock, char *tableName);

            int         msqlClose(int sock);

For more details see the documentation that comes with mSQL.

-------------------------------------------------------------------------------

Are views or virtual tables supported?

No.

This feature may be included in mSQL version 2.

-------------------------------------------------------------------------------

Does mSQL support table aliasing?

Yes.

As an example, consider this method to find the list of grandparents from a
child/parent tuple:

    SELECT t1.parent, t2.child from parent_data=t1,parent_data=t2
        WHERE t1.child=t2.parent

mSQL also supports the SQL standard method of table aliasing which uses either
a space character or the keyword AS instead of an = character. So the above
example can also be written as either:

    SELECT t1.parent, t2.child from parent_data t1,parent_data t2
        WHERE t1.child=t2.parent

or

    SELECT t1.parent, t2.child from parent_data AS t1,parent_data AS t2
        WHERE t1.child=t2.parent

-------------------------------------------------------------------------------

Are column constraints supported?

No.

-------------------------------------------------------------------------------

Are stored procedures supported?

No - that's getting beyond the scope of Mini SQL (see Oracle :)

-------------------------------------------------------------------------------

Are access privileges supported?

Yes.

mSQL has an access control file which allows the database administrator to
control access on a user and host level.

For more details see the documentation that comes with mSQL.

mSQL does not support access control from within SQL commands.

-------------------------------------------------------------------------------

Does mSQL support BLOBs?

BLOBs are Binary Large OBjects. Typical examples would be large blocks of text,
graphics or audio data.

The current release of mSQL has NO direct support for BLOBs. However, you can
always store the path name of a file that points to the BLOB in one of the
fields of your table. Then your application can deal with the file name
appropriately.

If you're dealing with large blocks of text, you may also wish to consider this
approach from Pascal Forget :

    Another possible hack would be to have the "block_text"
    record contain a pointer to a "text_lines" table. This
    table would contain a linked list of text lines like this:

        CREATE TABLE text_lines (
            line_id     int primary key,
            string      char(80),
            next_line   int
        )

Later versions of mSQL may have support for BLOBs.

-------------------------------------------------------------------------------

Are the transaction commands, BEGIN, COMMIT, and ROLLBACK supported?

No, and they will probably never be supported (once again that's beyond the
scope of mSQL).

The mSQL server handles requests serially - that is only one user's request is
handled at a time. Therefore there is no danger of a user reading from a table
that is currently being updated by another user.

However, there is the possibility that the same read operations may straddle
another user's write operation so that different data will be returned from
each read.

mSQL version 2 will provide client initiated locking.

-------------------------------------------------------------------------------

What are the limits on table and field names?

A table or field name MUST begin with an alphabetic character. The remainder of
the name may be any of the following 63 characters:

   *  A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
   *  a b c d e f g h i j k l m n o p q r s t u v w x y z
   *  0 1 2 3 4 5 6 7 8 9
   *  _ (that's an underbar NOT a dash)

The maximum name length is set to 20 characters. That's for a table or a field
in a table. This allows for 813621925049196536663393538834956800 possible
naming combinations using the characters listed above. For the mathematically
inclined that's:

           19
         -----
         \
          \        i
   52 *   /      63   =   813621925049196536663393538834956800
         /
         -----
         i = 0

Though in practise, many of these combinations will probably remain unused.

While not recommended, the default maximum name length value of 20 can be
changed by editing the mSQL source code. However, if you change it AFTER you
have already created ANY databases, the old databases will be unreadable. To
avoid this problem, follow this procedure:

  1. Dump your old databases to ASCII files using msqldump.

  2. Drop your old databases using msqladmin.

  3. Shutdown the mSQL database server msqld using msqladmin.

  4. Edit the mSQL source and modify

         ./src/msql/msql_priv.h

     Change the line reading

         #define NAME_LEN    20              /* Field/table name length */

     to suit your needs.

  5. Recompile and install the modified mSQL.

  6. Start the new mSQL database server msqld.

  7. Create new databases using msqladmin.

  8. Repopulate your databases using msql and the ASCII dumps from step 1.

-------------------------------------------------------------------------------

What other limits can be modified?

The file

    ./src/msql/msql_priv.h

contains the definitions of the internal mSQL limits:

    #define MAX_FIELDS  75              /* Max fields per query */
    #define MAX_CON     24              /* Max connections */
    #define BUF_SIZE    (256*1024)      /* Read buf size if no mmap() */
    #define NAME_LEN    20              /* Field/table name length */
    #define PKT_LEN     (32*1024)       /* Max size of client/server packet */
    #define CACHE_SIZE  8               /* Size of table cache */

If you want to increase them you can just edit this file and recompile. Don't
change MAX_CON or CACHE_SIZE without understanding why these limits are set
(maximum file descriptors per process etc).

Changing any of these parameters will almost certainly make any existing
databases unreadable. To avoid this problem, follow this procedure:

  1. Dump your old databases to ASCII files using msqldump.

  2. Drop your old databases using msqladmin.

  3. Shutdown the mSQL database server msqld using msqladmin.

  4. Edit the mSQL source and modify

         ./src/msql/msql_priv.h

     changing the definitions to suit your needs.

  5. Recompile and install the modified mSQL.

  6. Start the new mSQL database server msqld.

  7. Create new databases using msqladmin.

  8. Repopulate your databases using msql and the ASCII dumps from step 1.

-------------------------------------------------------------------------------

How much data can mSQL address?

mSQL can theoretically address tables with a maximum size of 4 gigabytes. In
practise you'll probably run up against operating system limitations well
before this theoretical limit.

-------------------------------------------------------------------------------

Are there any limitations in the way mSQL handles logical expressions?

Yes.

Consider the SQL query:

    SELECT something from somewhere WHERE
        name='jan' or country='italy' and sex='female' or title='ms'

Under the current release of mSQL, the parser will scan the condition from left
to right. So in this example the condition reads:

    ((name='jan' or country='italy') and sex='female') or title='ms'

The current release of mSQL does NOT support parentheses in logical
expressions, so there is NO way to change this parsing.

Future versions of mSQL may support user defined levels of associativity.

-------------------------------------------------------------------------------

How does mSQL return values?

mSQL returns all values as ASCII strings. If, for example, you are expecting an
integer result you may have to do some internal conversions depending on the
language you're using.

In C, for example, see the manual pages on atoi().

-------------------------------------------------------------------------------

How does SELECT return rows?

On Tue, 4 Jul 1995, Karri-Pekka Laakso wrote:

    Does SELECT return the rows always in order 'first inserted first',
    if there is no ORDER statement given, and the rows are selected
    from one table only, and there has been no DELETEs on that table?
    It seems be so, but is it guaranteed?

David Hughes replied:

    This is guaranteed. The only time the rows will be returned in
    another order is if you have deleted a row and it's then filled by
    a later insert.

-------------------------------------------------------------------------------

Can mSQL nest tables?

On Tue, 25 Jul 1995, Jerome Stoller wrote:

    I am new at mSQL, and have a beginner question: Is it possible to
    create a table "normally", and to have the fields of one
    of the column being[sic] another table?

David Hughes replied:

    You can't nest tables in mSQL (don't think you can in ANSI SQL
    either). What you can do is to use a common value as a key to join
    the contents of two tables (eg. a part number or a user ID).

-------------------------------------------------------------------------------

What storage overheads does mSQL have?

mSQL stores each database in its own directory under the 'msqldb' directory of
wherever you specified mSQL should be installed. For example if you specified
that mSQL should be installed in:

    /usr/local/Minerva/

then the databases will be created in the directory:

    /usr/local/Minerva/msqldb/

Note that this can be overridden by specifying the MSQL_HOME environment
variable when starting msqld.

Each table in the database is stored as a number of files:

   * dbname/table.dat - table data. This file contains the data for all the
     rows in the table.

     For each field in a table, mSQL will also store an additional flag byte.
     mSQL also stores an additional flag byte for each row of the table.

     Consider the following table:

         CREATE TABLE test (
             f0      char(13),
             f1      int,
             f2      real,
             f3      real,
             f4      real,
             f5      real,
             f6      int
         )

     Storage space for each row of this table would be:

         (13 * char) + (2 * int) + (4 * double) + (7 * fields) + (1 * rows)
             =  (13 * 1) + (2 * 4) + (4 * 8) + (7 * 1) + 1
             =  61 bytes

     So if this table had 1000 records, it would occupy 61000 bytes of disk
     space. (In reality it may occupy slightly more real disk space because of
     the way the underlying file system behaves. This is operating system
     specific and not really an issue to worry about. If you do an 'ls -l' on
     the file it will show 61000 bytes).

   * dbname/table.key - table keys. This file will only exist if the table has
     a primary key. It contains the key pointers into the table data file.

     The size of this file will be the size of the key plus one flag byte times
     the number of rows in the table. In the above example, if the table was
     defined as:

         CREATE TABLE test (
             f0      char(13) primary key,
             f1      int,
             f2      real,
             f3      real,
             f4      real,
             f5      real,
             f6      int
         )

     and the table had 1000 rows, the size of the data file would still be
     61000 bytes and the size of the key file would be:

         ((13 * char) + 1) * 1000
             =  ((13 * 1) + 1) * 1000
             =  14 * 1000
             =  14000 bytes

   * dbname/table.def - table definition. This file contains the table
     structure definition.

     Each field in the table has a 64 byte definition. Using the example above,
     the table has 7 fields so the size of the definition file will be:

         7 * 64  =  448 bytes

   * dbname/table.stk - table stack. This file keeps track of the holes or
     empty rows in the table.

     For every hole in the table, this file will contain a 4 byte integer
     indicating the row number of the hole. It is accessed like a stack. When a
     row is deleted, it's index is appended to the file. When an insert is
     done, the last 4 bytes are "popped" off the file and the file is truncated
     back 4 bytes.

     If the table contains 20 holes, the size of the stack file will be:

         20 * 4  =  80 bytes

     If the table contains no holes then this file will have zero length.

Therefore to calculate the storage requirements for a table, use one of the
following formulae:

Tables with a primary key:

         table_storage_requirements
             =  expected_max_rows *
                 (
                     number_of_fields + 1 + total_chars +
                     (4 * total_ints) + (8 * total_reals) +
                     (size_of_key + 1) +
                     (4 * expected_deletion_ratio)
                 ) +
             (total_fields * 64)

Tables without a primary key:

         table_storage_requirements
             =  expected_max_rows *
                 (
                     number_of_fields + 1 + total_chars +
                     (4 * total_ints) + (8 * total_reals) +
                     (4 * expected_deletion_ratio)
                 ) +
             (total_fields * 64)

Using the keyed table above, if we expect it to contain a maximum of 10000 rows
and we expect to have a 10 percent deletion ratio (that is at any one time we
expect that 10 percent of our rows have been deleted but not replaced by new
rows), we should allow for:

    10000 *
        (
            7 + 1 + 13 +
            (4 * 2) + (8 * 4) +
            (13 + 1) +
            (4 * 0.10)
        ) +
    (7 * 64)

        =  10000 * ( 21 + 8 + 32 + 14 + 0.4) + 448
        =  754448 bytes

plus a handful of bytes to store file names in directories.

Note that this is the maximum storage allocation. Unlike some other database
systems, mSQL only uses disk space when it has data to add to a table - it does
NOT allocate a large block of empty disk space and then proceed to fill it. If
our example only had 1000 rows the storage requirements would only be 75848
bytes.

-------------------------------------------------------------------------------

Does msqld allocate more RAM to itself as new databases are added?

On Fri, 11 Aug 1995, Andrew Waegel wrote:

    does msqld allocate more ram to itself as new db's are added? i.e.
    is any part of the database held in ram or does it just access the
    database files directly from disc? I need to do some planning, and
    want to know if I need to plan to get more simms...

David Hughes replies:

    If your OS supports mmap() (e.g. Solaris, SunOS, *BSD, BSDI, Linux
    1.3.x, HP-UX >9.x) then the more memory you throw at it the
    better things will get if you are using big databases. The data is
    left on disk but is accessed via the virtual memory subsystem so it
    will be in memory some of the time.

    If you are not using mmap() then data is just read from disk as it
    is needed. There's a small buffer in the read code to make things
    faster but that's about it. It doesn't matter how many databases
    you have defined it only uses 1 buffer.

-------------------------------------------------------------------------------

Does performance degrade as the number of databases increase?

On Fri, 11 Aug 1995, Andrew Waegel wrote:

    Does performance degrade at all as the number of databases
    increases? That is, say a query from database A took n seconds when
    database A was the only one served by msqld. After adding databases
    B, C, D and E, should the database A query take any longer? It
    seems like 'no' from my experience, but...

David Hughes replies:

    No. It will degrade if people are hitting the new databases at the
    same time as they are hitting database A though. msqld only handles
    1 query at a time so if 2 queries come in they are queued and
    processed in order.

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

                             Installation Problems

Under Irix (SGI) I get problems relating to my username

Colin Surprenant reports:

    The solution to the problem with using the socket and then nsl
    libraries with NIS in Irix 5.2 is:

    1. Do not link them if they are not needed :) This is the case for
       mSQL.

    or

    2. link libc BEFORE the socket and the nsl libraries.

    For those who didn't know, the problem is that if you use NIS and
    link socket or nsl, the getpwuid() function doesn't work.

-------------------------------------------------------------------------------

On OSF/1 or HP-UX I have trouble starting msqld at boot time

David-Michael Lincke reports:

    Looks like the same thing that happens under HP-UX with background
    processes in rc scripts. They are killed off on exit of the ksh
    functions.

    Create yourself a wrapper for msqld. In there you do a fork and
    exit the parent process in the child process you do a call to
    setsid() to get rid of the controlling terminal followed by a call
    to execl() to launch msqld.  You might also want to close all open
    file descriptors before calling exec.

-------------------------------------------------------------------------------

Should I use cc or gcc when building mSQL on my Dec Alpha running OSF/1?

Rick Beebe writes:

    DEC Alphas running OSF/1 (Digital Unix): The original mSQL docs
    recommended using cc rather than gcc on this platform. In my
    experience this is still good advice. If you have gcc on the
    machine, however, autoconf will find it and default to it. After
    running 'setup' edit site.mm and change 'gcc' to 'cc' and
    'bison -y' to yacc.

-------------------------------------------------------------------------------

Does mSQL work with Linux mmap?

Version 1.3 or greater of the Linux operating system has full mmap support. If
you're using such a version of Linux, mSQL will work perfectly well using mmap.

If you have an earlier version of Linux you can either upgrade or ensure that
mmap support in mSQL is disabled by running the 'setup' program and then
editing

    ./targets/your-architecture/site.mm

and ensuring the mmap directive reads:

    MMAP=

and then recompile the mSQL package.

-------------------------------------------------------------------------------

Does mSQL work with Solaris 2.3 mmap?

There were problems reported with mSQL and Solaris 2.3 mmap. Version 1.0.10
appears to behave correctly.

Unless reports are made to the contrary, this question will be removed in
future versions of the FAQ.

-------------------------------------------------------------------------------

I'm having trouble compiling MsqlPerl-1.03 with mSQL under HP-UX

This problem has to do with the way HP-UX deals with shared libraries.

Ensure the EXTRA_CFLAGS option in the file:

    ./targets/your-architecture/site.mm

reads:

    EXTRA_CFLAGS= -Ae +O2 +Z

and recompile mSQL.

The +Z option ensures that "position independent code" is used when creating
object files. For more information see the manual pages on your compiler.

-------------------------------------------------------------------------------

How can I install mSQL on a SCO Unix system?

NOTE: This fix has had some conflicting results. Please let me
() know if it works OR fails. If it fails, let me
know exactly where (if possible) and how you fixed it (if you did).

The following is a summary of the efforts required by Andrew Cash
 to install mSQL version 1.0.8 on a SCO Unix system. It
should work perfectly well for version 1.0.9 as well.

   * Unpack the source code and create the site dependent files as follows:

         gunzip -c msql-1.0.9.tar.gz | tar xvf -
         cd msql-1.0.9
         make target
         cd targets/your-architecture
         ./setup
             answer questions

   * In the file:

         ./common/config.h

     ensure the lines referring to the sys/select.h include file are commented
     out as follows:

         /* Defined if you have sys/select.h  */
         /* #define HAVE_SYS_SELECT_H 1 */

   * SCO Unix doesn't have an alloca() library function so you'll have to use
     malloc(). You need to generate the file:

         ./msql/msql_yacc.c

     so run:

         make

     until it completes (or fails). Ensure that the msql_yacc.c file has been
     created. If it has, apply the following patch to the file. (Use the "-l"
     option of patch to avoid any problems with mismatched whitespace).

         *** msql/msql_yacc.c        Wed Nov  8 11:40:08 1995
         --- msql/msql_yacc.c.new    Wed Nov  8 11:47:14 1995
         ***************
         *** 329,362 ****
              Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.  */

         - #ifndef alloca
         - #ifdef __GNUC__
         - #define alloca __builtin_alloca
         - #else /* not GNU C.  */
         - #if (!defined (__STDC__) && defined (sparc)) || defined (__sparc__) || defined (__sparc) || defined (__sgi)
         - #include 
         - #else /* not sparc */
         - #if defined (MSDOS) && !defined (__TURBOC__)
         - #include 
         - #else /* not MSDOS, or __TURBOC__ */
         - #if defined(_AIX)
           #include 
         -  #pragma alloca
         - #else /* not MSDOS, __TURBOC__, or _AIX */
         - #ifdef __hpux
         - #ifdef __cplusplus
         - extern "C" {
         - void *alloca (unsigned int);
         - };
         - #else /* not __cplusplus */
         - void *alloca ();
         - #endif /* not __cplusplus */
         - #endif /* __hpux */
         - #endif /* not _AIX */
         - #endif /* not MSDOS, or __TURBOC__ */
         - #endif /* not sparc.  */
         - #endif /* not GNU C.  */
         - #endif /* alloca not defined.  */

           /* This is the parser code that is written into each bison parser
             when the %semantic_parser declaration is not specified in the grammar.
         --- 329,335 ----
         ***************
         *** 607,618 ****
                 yystacksize *= 2;
                 if (yystacksize > YYMAXDEPTH)
                 yystacksize = YYMAXDEPTH;
         !       yyss = (short *) alloca (yystacksize * sizeof (*yyssp));
                 __yy_bcopy ((char *)yyss1, (char *)yyss, size * sizeof (*yyssp));
         !       yyvs = (YYSTYPE *) alloca (yystacksize * sizeof (*yyvsp));
                 __yy_bcopy ((char *)yyvs1, (char *)yyvs, size * sizeof (*yyvsp));
           #ifdef YYLSP_NEEDED
         !       yyls = (YYLTYPE *) alloca (yystacksize * sizeof (*yylsp));
                 __yy_bcopy ((char *)yyls1, (char *)yyls, size * sizeof (*yylsp));
           #endif
           #endif /* no yyoverflow */
         --- 580,591 ----
                 yystacksize *= 2;
                 if (yystacksize > YYMAXDEPTH)
                 yystacksize = YYMAXDEPTH;
         !       yyss = (short *) malloc (yystacksize * sizeof (*yyssp));
                 __yy_bcopy ((char *)yyss1, (char *)yyss, size * sizeof (*yyssp));
         !       yyvs = (YYSTYPE *) malloc (yystacksize * sizeof (*yyvsp));
                 __yy_bcopy ((char *)yyvs1, (char *)yyvs, size * sizeof (*yyvsp));
           #ifdef YYLSP_NEEDED
         !       yyls = (YYLTYPE *) malloc (yystacksize * sizeof (*yylsp));
                 __yy_bcopy ((char *)yyls1, (char *)yyls, size * sizeof (*yylsp));
           #endif
           #endif /* no yyoverflow */

     That is, ensure that  is explicitly included and that all
     references to alloca() are changed to malloc().

   * Rebuild msqld with the modified msql_yacc.c file by running:

         make

   * If you are NOT going to be running mSQL as root, then you'll need to
     ensure that msqld can be installed correctly. Edit the makefile:

         ./msql/Makefile.full

     and comment out the mode change line for msqld as follows:

         # chmod 4750 $(INST_DIR)/bin/msqld

     Failure to make this change will result in an error during the install
     phase.

   * Install mSQL by running:

         make install

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

                                Runtime Problems

msqladmin will not let me create a database

This one is straight from the manual section on msqladmin. It states that the
only person who is allowed to create a database is the person nominated as the
"root user" during installation. If you indicated that the database would be
running as root then you must be root to create a database.

If you indicated that it would not be running as root, you would have then
nominated a username for the "root user". In that case, you must be logged in
as the user you nominated before you can perform admin functions like creation
of databases.

The manual also states that you can only perform the admin functions of
msqladmin (i.e. any function other than 'version') from the local host. For
security reasons you cannot perform administrative functions in a client/server
manner of a network.

-------------------------------------------------------------------------------

When I start msqld it complains about an ACL file

The ACL file is the file that contains Access Control Lists for mSQL. It is
located in the installation directory and is called msql.acl (e.g.
/usr/local/Minerva/msql.acl). The warning indicates that msqld couldn't locate
an ACL file. This doesn't stop mSQL from operating, it just implies that
everyone on every machine on your network has read/write access to your
databases.

A sample ACL file is installed in the installation directory. You could copy
this file to msql.acl and edit it to reflect the access you want to offer to
your databases.

-------------------------------------------------------------------------------

When I start msqld it complains about a PID file

The PID file is just a file in which msqld writes its process ID. If it can't
write its PID it will still work correctly.

During the installation, the setup program tried to determine the correct place
to store the PID file. If you are seeing this error then it's configured
incorrectly (either the directory it's trying to use doesn't exist or you don't
have write access to it). You can fix this by editing the site.mm file and
redefining the PID_FILE variable to another are that you have access to.

-------------------------------------------------------------------------------

I've just installed the latest version of mSQL and now my own applications
won't work!

Whenever you install a new version of mSQL you MUST remember to recompile any
third party applications - including those you developed yourself - to ensure
that they are linked with the new version of libmsql.a.

Failure to do this will almost certainly guarantee that your applications will
fail at some stage while talking to the new mSQL database server. You may also
miss out on some new feature provided by the new mSQL API.

-------------------------------------------------------------------------------

Access control doesn't work with my setuid applications

Consider the following scenario:

   * User peters is the ONLY user given read and write access to the database
     test by the appropriate additions to the msql.acl file.

   * An application is created to manipulate the test database and is saved
     such that it is a setuid peters application:

         -rwsr-xr-x   1 peters   db         24576 Nov 13  1995 db_app

   * User markp wishes to use the test database using the db_app application.
     Instead of the setuid nature of the application allowing markp access to
     the database, all that happens is the error message:

         Access to database denied

This occurs because of the way in which mSQL passes user information to the
database server. It uses the getuid() system call which returns the real user
id of the calling process rather than the effective user id.

mSQL version 2 will have radically different security mechanisms.

-------------------------------------------------------------------------------

Why do I see an "Address already in use" error message when I attempt to start
msqld?

If you're running Linux or SCO Unix, this message can be seen if you killed the
msqld process WITHOUT executing:

    msqladmin shutdown

The TCP/IP port will remain bound for about 90 seconds or so. After this time
the port should be available and msqld can be started successfully.

Another possibility to consider is that something is already using the TCP/IP
port that msqld is trying to use. For a default installation these port numbers
are 1112 for a root user or 4333 for an ordinary user. In this case user means
the name of the user you entered when answering the setup question(s):

    Will this installation be running as root ?
    What username will it run under ?

There are a number of ways you can check for something using the TCP/IP port:

   * msql

     If the mSQL monitor program msql can connect to the mSQL database server
     msqld then you KNOW that the database server is already running.

   * Telnet

     Telnet to the database server and specify the mSQL TCP/IP port number
     using one of the following commands:

         telnet dbhost 1112

     or

         telnet dbhost 4333

     You'll see the following types of messages:

        o Nothing is using that port or something is using the port
          exclusively:

              Trying 127.0.0.1 ...
              telnet: connect: Connection refused
              telnet>

        o msqld version 1.0.10 running protocol version 6 is using the port:

              Trying 127.0.0.1 ...
              Connected to localhost.
              Escape character is '^]'.

              0:6:1.0.10

        o Something else is using the port (or a client process is still
          communicating with a running msqld process:

              Trying 127.0.0.1 ...
              Connected to localhost.
              Escape character is '^]'.

   * netstat

     If your operating system has the netstat command, you can use it to
     display the contents of various network related data structures in various
     formats, depending on the options you select. Some of the options that may
     be helpful are:

        o To determine if something is listening on port 4333, use:

              netstat -a | grep 4333

          If you see output similar to:

                *.4333    *.*     0       0       0       0       LISTEN

          then something is using that port.

        o To see if msqld is using the port, use:

              netstat -f unix

          Output similar to the following will indicate that msqld is already
          running:

              Active UNIX domain sockets
              Address  Type          Vnode     Conn Addr
              fcf8bca8 stream-ord      231        0 /tmp/msql.sock

          This may not work for all operating systems. - the above examples
          were taken from a Solaris 2.4 system. Variations on this command
          include:

              netstat -f inet

          or

              netstat -f local

If you found something using the TCP/IP port that msqld would like to use,
examine the output of your operating system's ps command to check if you
already have a running msqld process. If you have then shut it down it by
executing:

    msqladmin shutdown

If you don't have a running msqld process then something else may be using the
port that msqld is trying to use. Examine /etc/inetd.conf /etc/services (or the
services NIS map if you're running NIS) to see if anything else is using the
port. The output from one of the netstat commands listed above may be helpful.

If you find such a program you have two options:

  1. Change the port number the other program is using so that it doesn't
     conflict with mSQL

  2. Ensure that mSQL uses a different port number. This can be done by either:

        o starting msqld with the environment variable MSQL_TCP_PORT set to
          another port number. You'll also have to ensure that client
          applications use the new port number too.

        o modifying the ./src/common/site.h file and changing the section

              /*
              ** TCP port for the MSQL daemon
              */

              #ifdef ROOT_EXEC
              #define MSQL_PORT       1112
              #else
              #define MSQL_PORT       4333
              #endif

          to suit your needs. Then recompile and reinstall mSQL.

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

                                 How do I ...?

How do I embed single quotes in a field?

To handle a single quote, escape it with a backslash character. So the string

    'Bambi's'

would be entered as

    'Bambi\'s'.

Note: This applies when using msql - the database monitor program. If you're
developing your own application you may have to escape other characters that
are special to the language you're using - for example perl, C or tcl.

-------------------------------------------------------------------------------

What other characters need special treatment?

When specifying table or field names or when inserting values into fields, the
only other character that requires special handling is the mSQL escape
character backslash. To handle a backslash, escape it with another backslash.
So the string

    c:\windows\system\

would be entered as

    'c:\\windows\\system\\'

When using regular expressions in queries of the form:

    SELECT table.column FROM table WHERE table.column LIKE 'regexp'

the following rules apply:

                To search for  Use this  To search for  Use this
               this character  string   this character   string

                     $          \\\\$          [           \[

                     %           \\%           \           \\

                     '           \'            ^         \\\\^

                     (          \\\\(          _          \\_

                     )          \\\\)          |         \\\\|

                     ?          \\\\?

Note: This applies when using msql - the database monitor program. If you're
developing your own application you may have to escape other characters that
are special to the language you're using - for example perl, C or tcl.

-------------------------------------------------------------------------------

How do I handle null fields?

For the NULL values, just use the NULL keyword.

For example

    insert into foo values ( NULL, 1, 2, 'some text' )

-------------------------------------------------------------------------------

How do I perform case insensitive matches?

mSQL uses Henry Spencer's regular expression library with a few modifications.
To perform case insensitive searches, your query should take the form

    SELECT * FROM my_table WHERE my_field LIKE '[Ss][Oo][Mm][Ee]
    [Vv][Aa][Ll][Uu][Ee]'

Sol Katz's  Object Oriented HTML API includes a C routine that
converts a string into its case insensitive form. You may wish to use this in
any C code that you write. See the section below on "What other third party
applications exist for use with mSQL?"

Alternatively, you can create an additional field in each table that will hold
a single case version of the information you are likely to be searching for.

For perl users, Michael Cowden  has contributed
this code example:

    The following statement turns mSQL into [mM][sS][qQ][lL]

    $string = "mSQL";
    $string =~ s/(.)/\[\L$1\E\U$1\E\]/g;

Vivek Khera  suggests a simpler method for perl users:

    Personally, I use this in Perl, as there is no need to complicate
    the regular expression with non-alpha characters.

       $string =~ s/([A-Za-z])/\[\L$1\U$1\]/gi;

Version 2 of mSQL will support functions similar to upper() and lower() which
will obviate the need for the above.

-------------------------------------------------------------------------------

How do I add a column to an existing table?

You can't! Once a table is created it must stay the way it is.

One possible solution is to use msqldump to create an ASCII dump of the entire
database. Then edit this dump file by hand and add the extra field to the
CREATE clause. You'll also need to edit each INSERT clause to ensure that the
new field is referenced. Once you've modified the dump file, drop and recreate
the database using msqladmin and repopulate the new database using the dump
file and msql.

This procedure could be automated by a shell or perl script.

As an example consider this output from msqldump

    #
    # mSQL Dump  (requires mSQL-1.0.6 or better)
    #
    # Host: localhost    Database: test
    #--------------------------------------------------------

    #
    # Table structure for table 'test'
    #
    CREATE TABLE test (
      name CHAR(40),
      num INT
    ) \g

    #
    # Dumping data for table 'test'
    #

    INSERT INTO test VALUES ('item 999',999)\g
    ...
    INSERT INTO test VALUES ('item 0',0)\g

If you wish to insert a field, say "discount", then you will need to modify the
dump file as follows:

    #
    # mSQL Dump  (requires mSQL-1.0.6 or better)
    #
    # Host: localhost    Database: test
    #--------------------------------------------------------

    #
    # Table structure for table 'test'
    #
    CREATE TABLE test (
      name CHAR(40),
      num INT,
      discount REAL
    ) \g

    #
    # Dumping data for table 'test'
    #

    INSERT INTO test VALUES ('item 999',999,0.0)\g
    ...
    INSERT INTO test VALUES ('item 0',0,0.0)\g

Notice that every insert clause MUST be changed as well as the table
definition.

-------------------------------------------------------------------------------

When should I call msqlConnect() in a parent/child situation?

If both the parent and child processes want to talk to the mSQL server then you
must call msqlConnect() AFTER you fork. They mustn't share the same socket.

-------------------------------------------------------------------------------

Can I use mSQL reserved words as field or table names?

No. The mSQL parser gets very confused if you attempt to use reserved words as
the name of a table or field. The full list of reserved words (as obtained from
msql_lex.c) is:

        <   >=   by      distinct   integer  not      real      update

        <=  all  char    drop       into     null     select    values

        <>  and  create  from       key      or       set       where

        =   as   delete  insert     like     order    smallint

        >   asc  desc    int        limit    primary  table

Remember that mSQL reserved words are case insensitive so UPPER case or MiXeD
cAsE reserved words are also forbidden in table or field names.

-------------------------------------------------------------------------------

How do I find the maximum or minimum value in a table?

To obtain the maximum value use:

    SELECT number FROM table ORDER BY number DESC LIMIT 1

To obtain the minimum value use:

    SELECT number FROM table ORDER BY number LIMIT 1

This will only work with mSQL 1.0.9 and above unless you have applied the
unofficial LIMIT patch to earlier versions. See the mSQL mailing list archives
for details on this patch. (Before searching for this unofficial patch, you
should seriously consider upgrading to the latest version of mSQL).

Note: Rasmus Lerdorf writes:

    The LIMIT statement limits the number of records actually
    transferred from the server to the client. It doesn't limit the
    scope of the search at all in any way. That means that if you are
    looking for the maximum value in a table with 30,000 entries, the
    query will first build the entire sorted result in memory in the
    server, but when it comes time to transferring the result to the
    client, it only sends the first item.

    In many cases, especially when you have a lot of fields, or long
    fields, the time it takes to transfer the data from the server to
    the client is actually many times that of the actual search. And
    the msqld daemon is tied up and not available to other clients
    while it is wasting time sending result records that will never be
    used. So, if you do queries and you know you will only be looking
    at the first couple of them, you should use the limit clause and
    cut down on the amount of useless records being sent across the
    socket.

-------------------------------------------------------------------------------

How can I determine the structure of a database?

Use the relshow application that comes bundled with the mSQL software
distribution.

   * To discover which databases are available use:

         relshow

     or

         relshow -h host

     This will return output similar to:

             +-----------------+
             |    Databases    |
             +-----------------+
             | test            |
             | photos          |
             | patches         |
             +-----------------+

   * To discover which tables are contained within a database use:

         relshow dbname

     or

         relshow -h host dbname

     This will return output similar to:

         Database = test

             +---------------------+
             |       Table         |
             +---------------------+
             | test_table          |
             | addresses           |
             | telephone           |
             +---------------------+

   * To discover the structure of a particular table use:

         relshow dbname tablename

     or

         relshow -h host dbname tablename

     This will return output similar to:

         Database = test

         Table    = test_table

             +-----------------+----------+--------+----------+-----+
             |     Field       |   Type   | Length | Not Null | Key |
             +-----------------+----------+--------+----------+-----+
             | name            | char     | 40     | N        | N   |
             | num             | int      | 4      | N        | N   |
             +-----------------+----------+--------+----------+-----+

-------------------------------------------------------------------------------

What happens when the mSQL server goes down between requests?

If the mSQL database server process, msqld, dies and is subsequently restarted,
or the host on which it was running is rebooted, any processes that were
connected to the mSQL database server MUST be reconnected. This is not unique
to mSQL, the Oracle database server behaves in a similar manner.

Programs that were connected to the mSQL database server should be either
restarted or have some internal mechanism whereby they notice the server has
died and attempt a reconnection.

One possible method for checking the status of the database server would be to
examine the return status of the msqlSelectDB() call.

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

                 Contributed Code and Third Party Applications

A number of people have contributed additional software that works with mSQL.
The contributed software falls into two categories - that developed by David
Hughes (mSQL's author) and that developed by others.

Note: Please consult the documentation that comes with each of these
applications to determine what licensing obligations may be involved.

                         Third Party mSQL Applications

         ESL                         Onyx

         w3-msql                     Perl

         DBI                         Python

         Digger                      REXX

         Emacs                       SQLBase

         Flatfile Importer           Tcl

         Home Page Construction Kit  Time and date utilities

         Java                        Unique sequence number generator

         MS Windows                  WDB

         NeXTSTEP EOF                Websql

         Object Oriented HTML API    Z Classes for C++

         ODBC

ESL
     David developed mSQL as the database component of a larger network
     management project called Minerva (Minerva was the Roman goddess of
     knowledge and information). Another component of Minerva is an Extensible
     Scripting Language called ESL. This has a C like syntax and provides
     support for the complete mSQL API as well as full SNMP support. It is
     available via anonymous ftp from:

         ftp://bond.edu.au/pub/Minerva/esl/esl-0.3.tar.gz
         (407046 bytes)

w3-msql
     David has also developed w3-msql which is an interface between the
     World-Wide Web and mSQL. It is a mechanism that can be used to greatly
     simplify the use of an 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. It is available via anonymous ftp from:

         ftp://bond.edu.au/pub/Minerva/msql/w3-msql/w3-msql-1.0.tar.gz
         (30203 bytes)

     At the time of compiling this FAQ, version 1.1 was being prepared.

DBI
     Tim Bunce  is working on a generic database driver
     (DBI) which allows perl to interface to any database in a standardised
     way. Details of Tim's work are available from:

         http://www.hermetica.com

     Alligator Decartes  has added an mSQL driver to
     Tim's work and his contributions can be obtained via anonymous ftp from:

         ftp://ftp.mcqueen.com/pub/dbperl

     It is also available from any CPAN (Comprehensive Perl Archive Network)
     site in the "modules" directory. For more information about CPAN see:

         ftp://ftp.funet.fi/pub/languages/perl/CPAN/CPAN

     The latest blurb describing Alligator's work can be obtained from:

         http://www.hermetica.com/technologia/DBI

Digger
     The folks at Bunyip Information Services (the current maintainers of the
     mSQL mailing list) have used mSQL as the database component of their
     Digger system.

     Digger is a Distributed Directory Service for the Internet based on
     Whois++ technology. For more information about digger send mail to
      or have a look at Bunyip's web pages:

         http://www.bunyip.com/products/digger

Emacs
     Igor Romanenko  has contributed some lisp code to
     provide emacs with hooks into mSQL. It is available via anonymous ftp
     from:

         ftp://bond.edu.au/pub/Minerva/msql/Contrib/sql-mode.tar.gz
         (6883 bytes)

Flat File Importer
     Pascal Forget  has contributed a program that will import
     flat file databases directly into mSQL databases. It is available via
     anonymous ftp from:

         ftp://bond.edu.au/pub/Minerva/msql/Contrib/msql-import-0.0.6.tar.gz
         (7516 bytes)

Home Page Construction Kit
     Rasmus Lerdorf  has developed a package that allows users
     to create WWW home pages with mSQL databases.

     Rasmus writes:

         For anybody using my FI form interpreter or David's w3-msql
         package, this PHP package should be of interest to you. It is an
         html embedded script language cgi wrapper with built-in web page
         access logging, access restriction, as well as support for ndbm,
         gdbm and mSQL databases through a powerful C-like scripting
         language. It is based on the original FI concept to which the
         functionality of the earlier PHP package has been added to form a
         single tool.

         The mSQL support is just a small set of functions supported by the
         package. A full set of string manipulation, regular expression,
         directory and file routines complement the script language.

     The source distribution as well as more information is available at:

         http://www.io.org/~rasmus.

Java
     Darryl Collins  has developed a version of the
     mSQL API for the Java programming language. It is available via anonymous
     ftp from:

         ftp://bond.edu.au/pub/Minerva/msql/Contrib/MsqlJava.tar.Z
         (12969 bytes)

     For more details see:

         http://www.minmet.uq.oz.au/msqljava

MS Windows
     Dean Fuqua  has contributed an mSQL API for MS
     Windows platforms. To use this software you need some Winsock compliant
     stack. (i.e. Trumpet Winsock, MS TCP/IP-32, Chameleon, etc.) It is
     available via anonymous ftp from:

         ftp://bond.edu.au/pub/Minerva/msql/Contrib/winapi.zip
         (87211 bytes)

     Not included in the winapi.zip is an msql.ini file. Its contents should
     resemble

         [Server]
         IP=your.server.host.name
         Port=1112
         Username=YourUsername

     There also appears to be a later version of Dean's work which includes
     compiled executables in

         ftp://bond.edu.au/pub/Minerva/msql/Contrib/winmsql7.zip
         (306827 bytes)

NeXTSTEP EOF
     Mark Onyschuk  has developed an NeXTSTEP EOF adaptor
     for mSQL.

     An Enterprise Object Framework (EOF) is an object framework that allows
     object oriented access to relational databases, where each row is
     considered an object. Besides a few limitations, it basically makes a
     relational database look like an OO database to the developer. By means of
     an adaptor, EOF can be used with virtually any database. The adaptor is
     responsible to transform the generic OO messages in database specific
     queries by subclassing a generic adaptor and modifying its behaviour.

Object Oriented HTML API
     Sol Katz  has developed an object oriented HTML API for
     mSQL.

     It is available via anonymous ftp from:

         ftp://ftp.blm.gov/pub/gis/msql_api.tar.gz
         (10317) bytes

     An example can be found at

         http://www.blm.gov/gis/msql/dbs6.html

ODBC
     Dean Fuqua  has developed a collection of ODBC files
     which are available via anonymous ftp from:

         ftp://ftp.comed.com/pub/odbc

     For more information on Dean's work see:

         http://alfred.niehs.nih.gov

Onyx
     Michael Koehne  has developed a rapid prototyping
     tool for database applications called Onyx.

     Onyx consists of a transaction manager, a shell like 4GL and a Simple
     Database Transaction Protocol engine.

     "Onyx is designed by the Model-View-Controller paradigm, so tables are the
     model, masks are the views and transactions are the controllers which can
     be bound to an input field, a menu, function keys or the change of the
     current record in a cursor."

     It is available via anonymous ftp from:

         ftp://ftp.uni-bremen.de/pub/unix/database/Onyx/Onyx.2.45.src.tar.gz
         (195872) bytes

Perl
     Andreas Koenig  has contributed a Perl 5 module which
     allows perl to interface to mSQL databases. It is available via anonymous
     ftp from:

         ftp://bond.edu.au/pub/Minerva/msql/Contrib/MsqlPerl-1.03.tar.gz
         (15037 bytes)

     It is also available from any CPAN (Comprehensive Perl Archive Network)
     site in the "modules" directory. For more information about CPAN see:

         ftp://ftp.funet.fi/pub/languages/perl/CPAN/CPAN

Python
     Anthony Baxter  has provided an mSQL extension
     to the Python language. It is available via anonymous ftp from:

         ftp://bond.edu.au/pub/Minerva/msql/Contrib/PymSQL.tar.gz
         (7581 bytes)

REXX
     Mark Hessling  has provided an mSQL extension for
     REXX. It is available via anonymous ftp from:

         ftp://ftp.qut.edu.au/src/REXXSQL/

SQLBase
     Klaus Thiele  has provided a wrapper to allow SQLBase
     users to interface to mSQL databases. It is available via anonymous ftp
     from:

         ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQLBase-1.00.tgz

     which is a symbolic link to

         ftp://bond.edu.au/pub/Minerva/msql/Contrib/msqlb-1.00.tgz
         (38136 bytes)

Tcl
     Hakan Soderstrom  has provided an mSQL extension to the
     Tcl language. It has been tested with Tcl 7.3, Tk 3.6p1, TclX 7.3a-p2; Tcl
     7.4b2, Tk 4.0b2, TclX 7.4a-b2, mSQL 1.0.6 under SunOS 4.1.3. Successful
     ports to several other platforms have been reported. It is available via
     anonymous ftp from:

         ftp://bond.edu.au/pub/Minerva/msql/Contrib/msqltcl-1.50.tar.gz
         (58929 bytes)

     Brad Pepers  has also provided an mSQL extension to
     Tcl. According to the documentation it supports tcl7.3 and tk3.6. It is
     available via anonymous ftp from:

         ftp://bond.edu.au/pub/Minerva/msql/Contrib/tcl_msql.tar.gz
         (7998 bytes)

Time and date utilities
     Pascal Forget  has contributed a library of time and date
     conversion utilities. It is available via anonymous ftp from:

         ftp://bond.edu.au/pub/Minerva/msql/Contrib/time_library.tar.gz
         (7989 bytes)

Unique sequence number generator
     Pascal Forget  has contributed a unique sequence number
     generator that can be used by mSQL applications (and others) to provide
     unique identifiers. It is available via anonymous ftp from:

         ftp://bond.edu.au/pub/Minerva/msql/Contrib/sgs1.0.0.tar.gz
         (24216 bytes)

WDB
     Bo Frese Rasmussen  has developed a Web database
     interface called WDB.

     WDB is a software tool set that tremendously simplifies the integration of
     SQL based databases into the World Wide Web. WDB lets you provide WWW
     access to the contents of databases without writing a single line of code!

     At the moment WDB supports Sybase, Informx and mSQL. However it is
     relatively easy to port it to other SQL based databases.

     For more details on WDB see:

         http://arch-http.hq.eso.org/bfrasmus/wdb

Websql
     Henry Minsky  has developed an mSQL Table WWW browser
     interface.

     "This is a C web CGI script to examine and modify rows in tables of an
     mSQL database. You should use Netscape or another browser which supports
     HTML 3.0 tables."

     More details and sample output are available from:

         http://www.ua.com/websql

     Source code is available from:

         http://www.ua.com/websql/websql.tar.gz
         (24225 bytes)

Z Classes for C++
     Dean Fuqua  has also contributed a set of C++ classes
     to provide access to both Oracle and mSQL databases. It is available via
     anonymous ftp from:

         ftp://bond.edu.au/pub/Minerva/msql/Contrib/zmsql-2.1.tar
         (40960 bytes)

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
--------------------------------------------------------------------------
To remove yourself from the Mini SQL mailing list send a message containing
"unsubscribe" to msql-list-request@bunyip.com.  Send a message containing
"info msql-list" to majordomo@bunyip.com for info on monthly archives of
the list. For more help, mail owner-msql-list@bunyip.com NOT the msql-list!