The mSQL FAQ

$Revision: 1.21 $

$Date: 1996/07/08 08:29:03 $

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

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

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

Notification of the FAQ's status is posted to the mSQL mailing list once a month - on or around the 1st of the month.

Additional releases of the FAQ may occur from time to time, when necessary patches are announced or when a new release of mSQL is made available for example.

This FAQ is maintained by Peter Samuel <Peter.Samuel@uniq.com.au>.

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

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

• Help and Support
  1. (-) Are there any recommended patches to the latest version?
  2. (-) Are there any known bugs or problems with the current release of mSQL?
  3. Are there any other patches available?
  4. How do I apply patches to the mSQL source?
  5. Is online documentation available?
  6. Is there a mailing list for mSQL?
  7. (=) Is there a mailing list digest for mSQL?
  8. (-) Is there a news group for mSQL?
  9. How do I post a question to the mSQL mailing list? (PLEASE READ THIS!)
  10. I've found a bug. How can I tell if it's been fixed yet?
  11. How do I report a bug in mSQL?
  12. How do I contribute a patch to mSQL?
  13. How do I contribute code to the mSQL community?
  14. Where can I find examples of code that interface with mSQL?

• 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?
  23. Does mSQL support cursors?
  24. Does mSQL support different character sets?

• 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 HP-UX?
  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?
  8. Why does setup fail when building mSQL on a Linux system?
  9. How can I make mSQL run on an Amiga running NetBSD 1.1?
  10. Why won't my third party applications compile under Solaris 2.x?
  11. (=) Why can't I run setup under Linux?

• 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?
  7. How can I avoid running out of space when doing certain complex table joins?
  8. msqld is suddenly dumping core and complaining about bzero()
  9. Why does relshow drop the first two characters from its output?
  10. Why won't msqladmin work?
  11. Why does msqld crash when attempting to display real numbers on 64-bit SGI platforms?
  12. (=) Why won't mSQL work with the compressed file system under AIX?

• 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?
  11. Can I run more than one copy of msqld on the same CPU?
  12. How can I automatically ensure that each record receives a unique primary key?
  13. How can I avoid compiler redefinition errors when compiling my own mSQL applications?

• (-) Contributed Code and Third Party Applications

• Web sites using mSQL

What is SQL?

SQL is an acronym that stands for Structured Query Language. It is often pronounced "sequel". It was developed in the mid 1970s by IBM.

The American National Standards Institute (ANSI) and the International Standards Organisation (ISO) have adopted SQL as the standard language for relational database management systems.

SQL provides commands for a variety of tasks including:

• querying data
• inserting, updating and deleting rows in a table
• creating, replacing, altering and dropping objects
• controlling access to the database and its objects
• guaranteeing database consistency and integrity

While most relational database management systems - including mSQL - provide support for SQL, each vendor usually has their own unique extensions to the language that may hinder the portability of SQL procedures from one database platform to another.

There are countless books available on database design and SQL. Some good starting point are:

    C. J. Date
    "An Introduction to Database Systems"
    Vol I, Sixth Edition, 1995
    Addison Wesley
    ISBN 0-201-54329-X
 
    C. J. Date and Hugh Darwen
    "A Guide to Sql Standard"
    Third Edition, 1993
    Addison Wesley
    ISBN 0-201-55822-X
 
    Judith S. Bowman, Sandra L. Emerson and Marcy Darnovsky
    "The Practical SQL Handbook: Using Structured Query Language"
    Second Edition
    Addison Wesley
    ISBN 0-201-62623-3
 
    G. M. Nijssen and T. A. Halpin
    "Conceptual Schema and Relational Database Design"
    1989
    Prentice Hall
    ISBN 0-7248-0151-0

What is mSQL?

What platforms will it run on?

Is it free?

"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.16, the cost of mSQL is:

    Commercial Installation - AUD $225
 
    Private Installation - AUD $65

An online currency conversion system developed by Olsen & Associates is available at http://www.olsen.ch/cgi-bin/exmenu.

Where can I get it?

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

    ftp://ftp.ntua.gr/pub/databases/msql/

    ftp://sunsite.auc.dk/pub/databases/msql

What is the latest version?

It is available via anonymous ftp from:

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

When will the next version be released?

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

Just to whet your appetite, here is a "state of play" snapshot for the up and coming 2.x release of mSQL. The snapshot was current as of February 29 1996.

A preliminary 2.x performance teaser follows:

David Hughes writes:

    On the mSQL-2 front, it's coming along well. The new file format,
    varchar, index, and query-plan code is running and not dumping core
    any more (the most positive sign I've had in a while). I've been
    running a few tests on a 35,000 row table and it looks good.
 
    Joins using the new indices are great. I did a join of the above
    table onto itself and provided a condition like "where
    A.name='foo' and B.name='ZZZ'" (there were no matching rows
    for the second part of the condition). Using mSQL-1 it took 5
    minutes on the dot to do the join (i.e. 35,000 * 35,000
    reads, merges, matches etc) and produced a result table with 0 rows
    as expected. Using mSQL-2 it took 0.4 seconds! It realised that the
    index of the inner loop didn't produce anything so it never
    actually read an inner row or did any of the real work of the
    join.
 
    Anyway, I'm rambling (staring at code for too long me thinks), but
    it is starting to look good.

Why is David called Bambi?

How is this FAQ prepared?

(See http://www.khoral.com/staff/neilb/weblint.html for more details on weblint).

Are there any recommended patches to the latest version?

Are there any known bugs or problems with the current release of mSQL?

• Problems with table/field name lengths

  The definition of NAME_LEN changed from version 1.0.10 to 1.0.12. It now seems that 18 characters is the maximum value for a table or field name. This problem exists in mSQL versions 1.0.12 and above.

  David Hughes writes:

      The reason it changed in 1.0.12 is because I had a couple of
      field_name buffers that didn't have room for a trailing NULL. Now,
      trying to fix that without altering the size of the struct implies
      that you have to shorten the name of the field (to leave the
      required room). I didn't want to force everyone out there to drop
      and reload every database they have just because of a 1 byte buffer
      over-run.
   
      If this is a major problem for you then ....
   
  	o dump all your databases
  	o find the definition in question
  	o set it to a value you like
  	o rebuild everything
  	o reload everything
   
      If you can work out what to change to get the extra field name
      length then you know enough about what you are doing to do the rest
      of what's required. I haven't provided a step-by-step because if
      people can't find the value they have to change I'm sure they'd
      still end up asking the list about this stuff after breaking
      things.
  

  At present there is NO patch to fix this problem. Possible workarounds are David's method outlined above or to downgrade to version 1.0.10.

• The regular expression code in version 1.0.14 and above has been cleaned up with the unfortunate side affect of making it impossible to search for the special characters ^ and \.

  The cleanup also changes the way in which other special characters can be searched. The tables in the section "What other characters need special treatment?" outline the differences for versions up to and including version 1.0.13 and 1.0.14 and above.

Are there any other patches available?

Some of these patches are for older releases of mSQL. They may have been rolled into subsequent releases of mSQL or they may not work with later releases of mSQL or they may break the successful operation of later releases of mSQL. Use them at your own risk.

A brief and no doubt incomplete list of these patches follows:

• When you insert a NULL as a primary key integer field, mSQL chooses a unique sequence number. Contributed by Jens-Peter Haack <peter@netcs.com>

  Available from:

      ftp://ftp.uni-bremen.de/pub/unix/database/Minerva/patches/insert.msql-1.0.9
      (593 bytes)
  

• Mathematical updates. You can use statements like:

      update account set balance = balance + 100 where nr = 12345;
  

  Contributed by Michael Koehne <kraehe@bakunin.north.de>.

  Available from:

      ftp://ftp.uni-bremen.de/pub/unix/database/Minerva/patches/update.msql-1.0.9
      (12677 bytes)
  

• You can now use double quotes where quotes are allowed. Contributed by Michael Koehne <kraehe@bakunin.north.de>.

  Available from:

      ftp://ftp.uni-bremen.de/pub/unix/database/Minerva/patches/quote.msql-1.0.9
      (1225 bytes)
  

• Michael Koehne <kraehe@bakunin.north.de> has bundled his value added patches to mSQL, which include the two mentioned above as well as some others.

  Michael writes:

      This patch includes the following features :
   
      - Usage of double quotes for strings. I know this is not ISO
        but a lot of other databases also allow double quotes and
        some of my programs use double quotes.
      - Speedup of regular expression.
      - Simple expressions in update.
      - auto primary key insert.
      - Some bug fixes.
  

  They are available via anonymous ftp from:

      ftp://ftp.uni-bremen.de/pub/unix/database/Minerva/features-msql-1.0.14
  

• Case insensitive searching. You can use statements like:

      select name from table where name ilike 'Manu%'
  

  Contributed by Benjamin Jacquard <jacquard@lix.polytechnique.fr>.

  Available from the mSQL mailing list archives for the month of February 1996.

How do I apply patches to the mSQL source?

The easiest way to apply these patches is to use Larry Wall's patch program:

    Patch will take a patch file containing any of the four forms of
    difference listing produced by the diff program and apply those
    differences to an original file, producing a patched version. By
    default, the patched version is put in place of the original, with
    the original file backed up to the same name with the extension
    ".orig".

If you're having difficulty finding the latest version of patch, you can download version 2.1 via anonymous ftp from:

    ftp://bond.edu.au/pub/Minerva/msql/Misc/patch-2.1.tar.gz
    (74856 bytes)

To apply the patches listed in this FAQ, use this procedure:

1. Save the patch to a file - say /tmp/msql-patch1. Only save the patch information - the patch program can usually determine which parts of the file are patches and which parts are not but its easier for you to strip out any unnecessary information before running patch.

   You should be left with a file containing a number of sections similar to:

   *** ./src/msql/net.c.orig	Tue Oct 17 11:24:03 1995
   --- ./src/msql/net.c	Tue Dec 12 15:24:11 1995
   ***************
   *** 66,72 ****
    
    
    
   ! void writePkt(fd)
   	int	fd;
     {
   	u_char	*cp;
   --- 66,72 ----
    
    
    
   ! int writePkt(fd)
   	int	fd;
     {
   	u_char	*cp;
   

2. Change directories to your mSQL source repository. For example:

       cd /usr/local/src/db/Minerva/msql/msql-1.0.16
   

3. Apply the patch as follows:

       patch -l < /tmp/msql-patch1
   

   The "-l" option is used to tell patch to ignore any whitespace mismatches between lines in the patch file and lines in the mSQL source file. (That's an "el", NOT a "one" or an "eye").

   Patch will respond with output similar to:

       Hmm...  Looks like a new-style context diff to me...
       The text leading up to this was:
       --------------------------
       |*** ./src/msql/net.c.orig        Tue Oct 17 11:24:03 1995
       |--- ./src/msql/net.c     Tue Dec 12 15:24:11 1995
       --------------------------
       Patching file ./src/msql/net.c using Plan A...
       Hunk #1 succeeded at 66.
       Hunk #2 succeeded at 84.
       done
   

   If you have problems applying the patch, you may wish to consider using some or all of the following arguments to patch:
   • -b

     forces patch to create a backup of the original file. By default it adds a .orig suffix to the original filename.

   • -N

     forces patch to ignore patches that it thinks are reversed or already applied.

   • -p0 (That's a "zero", NOT an "oh").

     forces patch to honour the full pathname of the files listed in the patch file.

4. Recompile mSQL. If you have third party applications that use the mSQL library, you'll probably have to recompile them as well.

Is online documentation available?

    http://Hughes.com.au

    http://cscsun1.larc.nasa.gov/~beowulf/msql/msql.html

Is there a mailing list for mSQL?

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/

    msql-list.archive.YYMM

    msql-list.archive.9510

    index msql-list
    help
    END

Wojciech Tryc <wojtek@solaris.tryc.on.ca> has begun to archive the mailing list messages in HTML format. They are available from:

    http://www.tryc.on.ca

    owner-msql-list@bunyip.com

    listmaster@bunyip.com

Traffic on the list is fairly high. There were approximately 1100 postings between April 1 1996 and May 31 1996 which gives an average of around 550 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!)

Turn around times for postings can sometimes be a little slow. It is not unusual for messages sent from Australia to take a few hours to appear on the list. List subscribers from other countries have also reported similar turn around times. Please be patient.

Is there a mailing list digest for mSQL?

To unsubscribe from the list digest send the words "unsubscribe msql-list-digest" in the body of your message to majordomo@bunyip.com.

Is there a news group for mSQL?

This question comes up about every three months or so - usually from new subscribers who haven't seen the previous threads.

There seem to be two distinct groups of people involved in this discussion:

1. Those who prefer the mailing list.
   • News turn around times can be horrendously slow. It is not uncommon for messages to appear in Australia up to seven (7) days after they were originally posted. Considering that mSQL is developed and supported in Australia, a news group would pose an unacceptable delay in providing support for mSQL.

   • The mailing list is well established and even if a news group were created, the mailing list would still live on. Creation of a news group would mean that users would need to track both forums in order to keep abreast of current developments in mSQL.

   • Newsgroup messages expire - if you happen to be away for an extended period you run the risk of losing valuable information that may have expired while you were away.

   • News groups are often subject to "spamming" whereas mailing lists are less vulnerable.

   • Signal to noise ratios on news groups tend to be higher than mailing lists (even when instances of "spamming" are discounted.

   • Not everyone who subscribes to the mSQL mailing list has a news feed. Establishment of a news group would immediately isolate those mSQL users from any discussions carried out within the news group.

   • Not everyone has access to a full news feed. Some people have difficulty in obtaining permission to receive new news groups. Establishment of a news group would immediately isolate those mSQL users from any discussions carried out within the news group.
2. Those who would prefer a news group.
   • A news group allows mSQL related messages to be isolated from other messages. This can be resolved by using mail filtering or threaded mail readers. Examples of mail filtering include procmail and filter (which is distributed with the Elm mail reader). Release 2.x and above of the Netscape Navigator includes a threaded mail reader.

   • The volume of messages on the mSQL list is increasing, making it difficult to keep track of threads. This can be alleviated using the methods described above.

   • It is often more cost effective for some users to receive blocks of news rather than individual mail messages. Similar behaviour can be achieved by subscribing to the mSQL mailing list digest.

Nor does it preclude you from establishing a global news group. There are well defined channels established within the Usenet community for the creation of new news groups.

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

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

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.

5. The name and version details of any third party application you are using with mSQL.

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

    http://Hughes.com.au/product/msql/history.htm
    

How do I report a bug in mSQL?

If you want to report a bug, send a report to the mSQL bug reporting address at msql-bugs@hughes.com.au. You may also wish to copy your report to the mSQL mailing list at msql-list@bunyip.com.

When making your bug report, please include the following information:

• the operating system and version - for example Solaris 2.4 or HP-UX 9.05
• 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

How do I contribute a patch to mSQL?

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.orig ./src/msql/msqld.c
   

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

How do I contribute code to the mSQL community?

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

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

Where can I find examples of code that interface with mSQL?

• ./src/msql/msql.c
• ./src/msql/relshow.c
• ./src/msql/msqldump.c
• ./src/msql/msqladmin.c

What support programs are bundled with mSQL?

For more details see the documentation that comes with mSQL.

What datatypes are supported?

    char
    int		(4 bytes)	-2147483647 <= i <= 2147483648
    real	(8 bytes)	-9223372036854775807 <= x <= 9223372036854775808

For more details see the documentation that comes with mSQL.

mSQL version 2 will support more data types.

Note: The storage of real numbers is highly machine dependent. If you store the number 10.0 don't be surprised if it is actually stored as either 10 or 10.00000000001 or 9.99999999999.

When dealing with real numbers it might be advisable to either convert the real number to an integer if possible (for example if you are dealing with decimal currency, it may be preferable to work in units of cents rather than dollars) or to perform some sort of delta check when retrieving values. The following logic demonstrates one possible application of this concept:

    delta = 0.00001
    select a real number from a table
 
    if abs(number - expected value) < delta
    then
	proceed
    else
	fail

What SQL commands are supported?

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] ] [LIMIT n]
 
	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?

For more details see the documentation that comes with mSQL.

What functions does the mSQL API provide?

For more details see the documentation that comes with mSQL.

Note: The PostScript documentation that comes with mSQL lists the function msqlClose() as type int. This is incorrect. It is actually type void.

Are views or virtual tables supported?

This feature may be included in mSQL version 2.

Does mSQL support table aliasing?

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

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

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

Are column constraints supported?

Are stored procedures supported?

Are access privileges supported?

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?

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 <pascal@wsc.com>:

    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
	)

    What I prefer to do in databases where I have text fields containing
    near infinite amounts of text is to define several tables like:
 
	create table prog (
	  id int,
	  name char(40),
	  programmer char(40),
	  ....
	)\p\g
 
	create table descript (
	  id int,
	  line int,
	  descript char(100)
	)\p\g
 
    Then in a program I break up the text as necessary and put one line
    of text in each record of <descript>. When I want it back, by
    program, I [use]
 
	select line, descript from prog, descript
	    where prog.id = descript.id
	    and name = 'foobar' order by line\p\g

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

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

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

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	19		/* Field/table name length */
   

   to suit your needs. Ensure that you use a number that is one less than the maximum value you desire. For example, if you wish to have a name length of 36 you would change the line to read:

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

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?

    ./src/msql/msql_priv.h

    #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	19		/* Field/table name length */
    #define PKT_LEN	(32*1024)	/* Max size of client/server packet */
    #define CACHE_SIZE	8		/* Size of table cache */

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?

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

Consider the SQL query:

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

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

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

How does mSQL return values?

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

How does SELECT return rows?

    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?

    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?

    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?

    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?

    /usr/local/Minerva/

    /usr/local/Minerva/msqldb/

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.

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)

    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

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?

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

    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?

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

    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.

Does mSQL support cursors?

    > To browse the database, I want mSQL to return me the first row
    > in the database, and keep a pointer to it. Then sometime later
    > I can ask it for the second row, and so on.
 
    mSQL does not provide support for cursors.
 
    You'll have to issue a SELECT query each time you want the next
    row. mSQL has no provisions for modifying a result set once it has
    been created. I suggest you add a field containing a unique
    identifier for each row, then fetch the next row using:
 
	SELECT ... FROM mytable where unique_field > last_id LIMIT 1
 
    > How do I express this in sql?
 
    If you find a way to express it, it most certainly won't be in
    standard SQL, as the language has no support for cursors.
 
    > I see that I could add an explicit field that was an arbitrary
    > row number, and query for the current row number +/- 1, but over
    > time with insertions and deletions there would be gaps and the
    > query would break. How is this problem usually solved?
 
    The select statement I gave you won't break even if there are gaps
    in the unique identifiers. You can periodically "compact" the
    numbers if you want.

Does mSQL support different character sets?

    Yes, as long as the client that fetches the characters knows what
    character set you are using. I.e. there is no support in mSQL for
    keeping track of the character set name, but mSQL is 8bit clean so
    you can store 8bit characters (in whatever character set).
 
    In Digger, the Whois++ server, we store UNICODE characters by
    encoding them first into UTF-8 which is an 8bit encoding scheme
    described in UNICODE 1.1.

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

    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

    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.

    msql:3:respawn:/usr/local/Minerva/bin/msqld </dev/console >/dev/console 2>&1

The next version of the FAQ will attempt to address this issue in detail.

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

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

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

    MMAP=

Does mSQL work with HP-UX?

    I just built msql-1.0.10 on hpux 9.05. It appears that you have
    slain the mmap bug. Good job. The only mods I [made] are in site.mm
 
	CC= cc -Ae +O3 +Z
 
    Remove -g flags also or you'll get a lot of annoying messages about
    opt not compatible with debug. The test suites for msql and
    msqlperl ran flawlessly.

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 +O3 +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?

The following is a summary of the efforts required by Andrew Cash <cash_a@sls.co.uk> 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. That's an "el", NOT a "one" or an "eye").

  This patch ensures that <malloc.h> is explicitly included and that all references to alloca() are changed to malloc().

*** msql/msql_yacc.c.orig	Fri Jan  5 13:07:02 1996
--- msql/msql_yacc.c	Fri Jan  5 13:09:34 1996
***************
*** 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 <alloca.h>
- #else /* not sparc */
- #if defined (MSDOS) && !defined (__TURBOC__)
- #include <malloc.h>
- #else /* not MSDOS, or __TURBOC__ */
- #if defined(_AIX)
  #include <malloc.h>
-  #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 */

• 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
  

Why does setup fail when building mSQL on a Linux system?

If you installed Slackware and didn't get the K series disks then you will be missing a lot of C header files that you need to compile mSQL. Go back and grab the kernel sources disks and install them on your box.

How can I make mSQL run on an Amiga running NetBSD 1.1?

George Harvey writes:

    Portability problem with 64bit ints and mmap() (with solution).
 
    I don't expect many people will be using an Amiga but this might
    apply to some other environments as well...
 
    In order to be able to take some of my work home, I needed to
    install mSQL 1.0.10 on my home system, which happens to be an Amiga
    3000T running NetBSD 1.1. The setup and compiles ran without a
    hitch but when I tried to run the 'killer' test, the server died
    with a signal 11.
 
    I noticed that setup had selected the MMAP option so I tried
    re-compiling without MMAP and everything worked. Since as far as I
    know, NetBSD is supposed to have a working mmap, I then looked a
    little deeper to see why it was falling over. After some fiddling
    around adding 'printf()s', I found the reason. This version of
    NetBSD defines 'size_t' as 32 bits and 'off_t' as 64 bits. The size
    argument to mmap() should be of type size_t but the value it was
    being given was an off_t. I have added a couple of casts to lines
    1505 and 1525 in msqldb.c to fix it and all seems well. I will now
    be running some further tests to see if any other problems show
    up.
 
    Casting a 64bit value into a 32bit value seems to be a bad thing to
    have to do but given the definition of mmap(), I can't see any
    alternative.
 
    Hope this may be of some use to somebody, ...
 
    I've run the standard test programs and my own application and not
    found any other problems so here are the diffs, the only change is
    to add a couple of 'size_t' casts to the mmap() call.
 
    These diffs are for msql 1.0.10 running under NetBSD 1.1 on an
    Amiga 3000T.

*** ./src/msql/msqldb.c.orig	Mon Nov 13 14:37:24 1995
--- ./src/msql/msqldb.c	Wed Jan 24 11:19:37 1996
***************
*** 1502,1510 ****
  			if (cacheEntry->size)
  			{
  				cacheEntry->dataMap = (caddr_t)mmap(NULL,
! 					cacheEntry->size,
  					(PROT_READ | PROT_WRITE),
! 					MAP_SHARED, cacheEntry->dataFD, 0);
  				if (cacheEntry->dataMap == (caddr_t)-1)
  					return(-1);
  			}
--- 1502,1510 ----
  			if (cacheEntry->size)
  			{
  				cacheEntry->dataMap = (caddr_t)mmap(NULL,
! 					(size_t)cacheEntry->size,
  					(PROT_READ | PROT_WRITE),
! 					MAP_SHARED, cacheEntry->dataFD, (off_t)0);
  				if (cacheEntry->dataMap == (caddr_t)-1)
  					return(-1);
  			}
***************
*** 1522,1530 ****
  			if (cacheEntry->keySize)
  			{
  				cacheEntry->keyMap = (caddr_t) mmap(NULL,
! 					cacheEntry->keySize,
  					PROT_READ | PROT_WRITE, MAP_SHARED,
! 					cacheEntry->keyFD, 0);
  				if (cacheEntry->keyMap == (caddr_t)-1)
  					return(-1);
  			}
--- 1522,1530 ----
  			if (cacheEntry->keySize)
  			{
  				cacheEntry->keyMap = (caddr_t) mmap(NULL,
! 					(size_t)cacheEntry->keySize,
  					PROT_READ | PROT_WRITE, MAP_SHARED,
! 					cacheEntry->keyFD, (off_t)0);
  				if (cacheEntry->keyMap == (caddr_t)-1)
  					return(-1);
  			}

Why can't I run setup under Linux?

After unpacking the mSQL distribution and running the make target command, the next step in installing mSQL is to change directories to:

    targets/target

This can be avoided (for all flavours of Unix) by issuing the command as:

    ./setup

msqladmin will not let me 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

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

If you are seeing an error regarding the PID file, then one of the following could be the cause:

• The directory in which msqld is attempting to create the PID file does not exist.

• The directory in which msqld is attempting to create the PID file does not have sufficient permissions.

    Top of install tree ? [/usr/local/Minerva]
    Will this installation be running as root ? [y] n
    What username will it run under ?  peters
    Directory for pid file ? [/var/adm]

If you did not specify root as the mSQL administration user when you answered the questions:

    Will this installation be running as root ? [y] n
    What username will it run under ?  peters

Under Irix 5.3 the /var/adm directory can only be written to by the root user, so if your mSQL administration user is NOT root then you'll have to choose some other location such as /var/tmp or /var/share.

If you need to change the location of this directory, you can either rerun the setup program, or edit the file:

    ./targets/your_architecture/site.mm

    PID_DIR= /var/adm

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

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
  

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?

    msqladmin shutdown

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 ?

• 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:
  • Nothing is using that port or something is using the port exclusively:

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

  • 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
    

  • 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:

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

  • 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
    

    msqladmin shutdown

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:
   • 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.

   • 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 can I avoid running out of space when doing certain complex table joins?

Some mSQL operations involving table joins can consume large amounts of temporary disk space.

mSQL version 1.0.10 uses the /tmp directory to store its temporary files. As this directory is also used by many other applications, it may not have sufficient space for mSQL. You can change the location of this temporary storage area using the following procedure:

• Shutdown the mSQL database server.

      msqladmin shutdown
  

• Apply the following patch to ./src/msql/msqldb.c

*** ./src/msql/msqldb.c.orig	Mon Nov 13 14:37:24 1995
--- ./src/msql/msqldb.c	Wed Jan 31 14:39:52 1996
***************
*** 1009,1015 ****
  		tmpfile = cp+1;
  	}
  	(void)sprintf(path,"%s/msqldb/.tmp/%s.dat",msqlHomeDir,tmpfile);
- 	(void)sprintf(path,"/tmp/%s.dat",tmpfile);
 
 
  	/*
--- 1009,1014 ----
***************
*** 1172,1178 ****
 
          msqlTrace(TRACE_IN,"freeTmpTable()");
  	(void)sprintf(path,"%s/msqldb/.tmp/%s.dat",msqlHomeDir,entry->table);
- 	(void)sprintf(path,"/tmp/%s.dat",entry->table);
  	freeTableDef(entry->def);
  	entry->def = NULL;
  	*(entry->DB) = 0;
--- 1171,1176 ----

• Recompile and install mSQL. As this change only affects the database server there is NO need to recompile any third party applications you may be using.

• Remove the msqldb/.tmp directory from your mSQL installation directory. For example, if you installed mSQL in /usr/local/Minerva run the following command:

      rm -rf /usr/local/Minerva/msqldb/.tmp
  

• Create a symbolic link from a directory with lots of space to the msqldb/.tmp directory:

      ln -s /lots/of/space /usr/local/Minerva/msqldb/.tmp
  

• Restart the mSQL database server.

      msqld &
  

• Remove the msqldb/.tmp directory from your mSQL installation directory. For example, if you installed mSQL in /usr/local/Minerva run the following command:

      rm -rf /usr/local/Minerva/msqldb/.tmp
  

• Create a symbolic link from a directory with lots of space to the msqldb/.tmp directory:

      ln -s /lots/of/space /usr/local/Minerva/msqldb/.tmp
  

• Restart the mSQL database server.

      msqld &
  

msqld is suddenly dumping core and complaining about bzero()

    This looks to me like you have compiled your msqld binary on a
    machine with bzero() in your libc but you are running it on a
    machine that does not have the bzero() function in its libc. Could
    it be that you compiled on a Solaris 2.5 box, but you are running
    it on a Solaris 2.4 machine? Sun made the brilliant decision to add
    bzero, bcopy and rindex to the Solaris 2.5 libc which means that
    Solaris 2.5 and 2.4 are not completely binary compatible anymore.
    When you compile msql on Solaris 2.5 it will see that bzero and
    bcopy are available and thus try to use them.
 
    The fix is to either link your msqld statically with the Solaris
    2.5 libc, or perhaps more preferable, recompile msql to not use the
    silly bzero, bcopy, bcmp and rindex functions.
 
    Edit common/config.h and make sure you do not have HAVE_BCOPY and
    HAVE_RINDEX defined. If you do, comment out these definitions and
    recompile. The resulting binary should then run on both target
    machines.

    peters@wheel[710] ./msqld
 
    mSQL Server 1.0.10 starting ...
 
    ld.so.1: ./msqld: fatal: relocation error: symbol not found: bzero:
    referenced in ./msqld
    Killed

Why does relshow drop the first two characters from its output?

    Wonder if anyone encountered this weird display while using relshow?
 
    relshow bookmarks
 
    Database = bookmarks
 
    +---------------------+
    |       Table         |
    +---------------------+
    | okmarks             |
    | st                  |
    +---------------------+
 
    notice that the first two letters of the table names are missing.
    Please help. Thanks in advance!

    I've seen this on Solaris if you link against the oh so broken BSD
    compatibility library (libbsd.a). Please make sure that libbsd.a
    isn't mentioned in your site.mm file.

Why won't msqladmin work?

    mSQL command failed!
    Server error = Permission denied

1. it can only be run by the msql root user. That is the user you specified when you compiled msql. This is a security feature.

2. it can only be run on the machine on which msqld is running - it will NOT work via tcp/ip connections. This is a security feature.

    msqladmin version

Why won't mSQL work with the compressed file system under AIX?

The corruption can be avoided by ensuring that the database files are not stored in a compressed file system.

How do I embed single quotes in a field?

    'Bambi's'

    'Bambi\'s'.

What other characters need special treatment?

    c:\windows\system\

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

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

Use this table for mSQL version 1.0.14 and above.

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 example

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

How do I perform case insensitive matches?

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

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 <cowden@leithaus.leitess.com> 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;

    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?

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

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

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

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

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?

    SELECT number FROM table ORDER BY number DESC LIMIT 1

    SELECT number FROM table ORDER BY number LIMIT 1

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?

• 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?

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.

Can I run more than one copy of msqld on the same CPU?

    I'm looking for a way to provide the full 25 connections to each of
    many mSQL databases running on a single box. Here's an idea, will
    it work? or is there a better way?
 
    (korn shell example)
    $ export MSQL_TCP_PORT=3000; msqld
    $ export MSQL_TCP_PORT=3001; msqld
    $ export MSQL_TCP_PORT=3002; msqld
 
    Then connect to the database as follows:
    $ export MSQL_TCP_PORT=3000; msql db_a
 
    While the above runs, another user connects:
    $ export MSQL_TCP_PORT=3001; msql db_b

    Well, sort of. By running 'msql db_a' you are using the local UNIX
    socket not the TCP socket so you'd have to use MSQL_UNIX_PORT not
    MSQL_TCP_PORT.
 
    The other thing is that you should run 3 MSQL_HOME areas
    (/usr/local/Minerva for example). If two of these servers __ever__
    access the same database at the same time then you are stuffed.
 
    So, something like
 
    export MSQL_UNIX_PORT=/dev/msql_1; export MSQL_HOME=/Minerva1; msqld&
    export MSQL_UNIX_PORT=/dev/msql_2; export MSQL_HOME=/Minerva2; msqld&
 
    and
 
    export MSQL_UNIX_PORT=/dev/msql_1; msql db_a
    export MSQL_UNIX_PORT=/dev/msql_2; msql db_b
 
    would do the job.

How can I automatically ensure that each record receives a unique primary key?

• Use Pascal Forget's unique sequence number generator.

• Build the unique key assignment and management into your application.

  One suggestion on how to implement this comes from Vivek Khera <khera@kci.kciLink.com>.

  Vivek writes:

      What I do is take some of the fields in the record, tack on a salt
      like the current time and generate a hash (either SHA or MD5) of
      it. I use part of the the hash string value as the key.
  

  Another solution is provided by Rasmus Lerdorf <rasmus@vex.net>.

  Rasmus writes:

      The issue here is not so much how to generate a key, but how to
      ensure it is unique. The way I have done it in the past is to
      associate a lock file with each table. Each table has a counter
      record. With the table locked, I pull out the current counter
      value, increment it, and put it back. Then I unlock the table. This
      is not the most efficient way to do it, but it does work nicely for
      systems that do not get pounded with queries.
  

  Others have suggested using a timestamp with milli second granularity. This approach has its pitfalls.
  • Not all client side operating systems have access to the gettimeofday() calls (or its equivalent).
  • Not all systems can determine the system time to the milli second.
  • Because the timestamps are being determined by the clients, it is possible for two clients to arrive at the same timestamp. This problem is amplified if you have a number of clients running on different machines which are not synchronised. This can be avoided by recalculating the timestamp if an insert operation fails due to a duplicate key error.
  • Clocks may drift backwards causing old timestamps to be reused. Sites that use time synchronisation methods such as rdate or xntp may be prone to this.

• Use a patch provided Jochem Wiedmann <wiedmann@wiedmann.neckar-alb.de>. This patch allows you to declare a key as:

      AUTO PRIMARY KEY
  

How can I avoid compiler redefinition errors when compiling my own mSQL applications?

    /usr/local/Minerva/include/msql.h:30: redefinition of `m_row'
    /usr/local/Minerva/include/msql.h:32: redefinition of `struct field_s'

To avoid this, apply the following patch to msql.h contributed by Vesa Tuomi <vesa@cardinal.fi>

*** ./src/msql/msql.h.orig Wed Mar  6 09:27:20 1996
--- ./src/msql/msql.h      Thu Mar  7 10:29:46 1996
***************
*** 16,21 ****
--- 16,23 ----
  **
  */
 
+ #ifndef       __MSQL_H__
+ #define       __MSQL_H__
 
  #if defined(__STDC__) || defined(__cplusplus)
  #  define __ANSI_PROTO(x)     x
***************
*** 109,111 ****
--- 111,115 ----
  #ifdef __cplusplus
        }
  #endif
+
+ #endif        /* __MSQL_H__ */

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

Note: There is often a delay of a day or so for files to be moved from the Incoming directory to the Contrib directory on the ftp server bond.edu.au. If you can't find the software in the Contrib directory, try the Incoming directory.

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.

Apache

Dirk van Gulik <Dirk.vanGulik@jrc.it> has developed some code for use with HTML and HTTP servers with specific reference to the Apache HTTP server. It is available via anonymous ftp from:

    ftp://ftp.ceo.org/pub/ewse-mSQL-apache-demos/apache-msql-demo.1.0.1.tar.gz
    (12723 bytes)

If you require a module that allows the Apache httpd daemon to perform authentication via an mSQL database, grab the file:

    http://www.apache.org/dist/contrib/modules/mod_auth_msql.c
    (9269 bytes)

Bind

Chris Seawood <mgrcls@nextwork.rose-hulman.edu> has extended the DNS naming service - BIND - to support mSQL databases. It is available in the contrib/msql directory of the current release of BIND which can be obtained via anonymous ftp from:

    ftp://ftp.vix.com/pub/bind/release/bind-4.9.3-REL.tar.gz
    (1682741 bytes)

Command Line Tool

Kai Mysliwiec <kvm@camelot.de> has developed an mSQL tool that allows you to send SQL queries from the command line. It is available from:

    http://www.camelot.de/~kvm/progs/sql.tar.gz
    (14083 bytes)

Dbadmin

James Harrell <gt4960a@prism.gatech.edu> has developed a CGI application that allows database administration using a web based form interface. A demonstration can be seen at:

    http://bauhaus.skiles.gatech.edu/~jharrell

Source code is available via anonymous ftp from:

    ftp://bond.edu.au/pub/Minerva/msql/Contrib/dbadmin_v1.0.tar.gz
    (20639 bytes)

DBASE

Maarten Boekhold <boekhold@cindy.et.tudelft.nl> has written a dbase to mSQL conversion utility. It handles the dbf data types string, num, real and date. It does not yet handle memo-fields because mSQL 1.x lacks support for variable length char fields. Maarten is aware that this may be insufficient for some users' needs but he is releasing the utility as 'do-whatever-you-want' software. It is available via anonymous ftp from:

    ftp://gopher.library.tudelft.nl/pub/misc/dbf2msql-1.02b.tar.gz
    (12219 bytes)

DBI/DBD

Tim Bunce <Tim.Bunce@ig.co.uk> 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 <descarte@hermetica.com> 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

Dbview

Gian Paolo Ciceri <gp.ciceri@it.net> has written a utility similar to relshow.

It shows the structure of an mSQL database as well as indicating the number of records in the tables.

It is available via anonymous ftp from:

    ftp://bond.edu.au/pub/Minerva/msql/Contrib/dbview-0.7.tar.gz
    (28557 bytes)

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 <digger-info@bunyip.com> or have a look at Bunyip's web pages:

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

Emacs

Igor Romanenko <igor@frog.kiev.ua> has contributed some lisp code to provide emacs with hooks into mSQL. It allows the msql monitor to run in an emacs window, so you can use emacs for editing and command recall. 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 <pascal@wsc.com> 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.9.tar.gz
    (9629 bytes)

Home Page Construction Kit - PHP

Rasmus Lerdorf <rasmus@vex.net> has developed a package that allows users to create WWW sites with mSQL databases.

Rasmus writes:

    PHP/FI is a server-side html-embedded scripting language with
    built-in access logging, access restriction, as well as support for
    ndbm, gdbm and mSQL databases. It also implements the RFC-1867
    standard for form-based file uploads.
 
    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.vex.net/php

HTML Interface to mSQL

Sol Katz <skatz@blm.gov> has written some C code that demonstrates an HTML interface to mSQL. Unlike other applications, this program does not need to be modified when new mSQL tables or databases are created. All required information is contained in the html. It is available via anonymous ftp from:

    ftp://bond.edu.au/pub/Minerva/msql/Contrib/msqlc2.zip
    (29002 bytes)

and also from:

    ftp://ftp.blm.gov/pub/gis/msqlc2.zip
    (29002 bytes)

It can be seen in operation at:

    http://www.blm.gov/gis/msql/vertical/test2.html

ICI

Yiorgos Adamopoulos <Y.Adamopoulos@noc.ntua.gr> has written an mSQL extension for the ICI programming language. It is available via anonymous ftp from:

    ftp://ftp.ntua.gr/pub/lang/ici/iciMsql.tar.gz
    (249077 bytes)

Yiorgos has set up a mailing list for iciMsql. Questions can be sent to:

    iciMsql@noc.ntua.gr

Java

Darryl Collins <darryl@minmet.uq.oz.au> 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-1.1.0.tar.Z
    (15611 bytes)

or

    ftp://bond.edu.au/pub/Minerva/msql/Contrib/MsqlJava-1.1.0.zip
    (20346 bytes)

For more details see:

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

George Reese <borg@imaginary.com> has developed a Java Database Connection (JDBC) class library using MsqlJava. It is available via anonymous ftp from:

    ftp://bond.edu.au/pub/Minerva/msql/Contrib/mSQL-JDBC.0.9.3.tar.gz
    (33365 bytes)

MS Access & dBase III

Brian Bartholemew <bb@wizard.pn.com> has written some scripts that translate dBase III tables saved from MS Access into tab-separated formats.

Brian writes:

    Here are three scripts, the first two are clean ones that break out
    tables saved from access in dBaseIII format into a tab-separated
    format that a set of database-operator scripts called rdb can use.
    The first breaks out .dbf files and the second breaks out .dbt
    files which contain the text of variable-length-text memo fields;
    the .dbf and .dbt can be joined by the block offset number given in
    the text field. These scripts have only been tested on the field
    types my database happens to have. Since the first one breaks out
    the table definitions it's the obvious candidate to generate table
    definitions for msql. The third script is a hack to get data into
    msql so I can play with msql, but it's a starting point. The first
    two scripts were written to be free of data-dependent bugs, the
    third is somehow confused about null fields in Pascal's msql-import
    program which I've hacked around to enter the complaining fields as
    the text "NULL"; debugging is welcomed. The third script
    needs rdb, available from rand.org:/pub/RDB-hobbs. I do MS Access
    -> rdb -> msql because I use rdb as a prototyping tool, the
    format is trivial to generate and modify, I have a forms-based
    emacs front-end to rdb, and the tables compress nicely in rcs.
    However, someone may wish to modify these scripts, (a) so that they
    generate msql dump files instead of rdb files, thereby bypassing
    the msql-import bug and the rdb dependency and probably some data
    dependencies with maximum portability, or (b) so that they talk to
    the database directly. If so please post diffs. Trigger the new
    behaviour by a command-line option so the non-msql-perl behaviour
    continues to run under vanilla perl. These programs are gpl'ed.

They are available from the mSQL mailing list archives for the month of February 1996.

mSQLexpire

Scott Burkett <scottb@dcicorp.com> has developed an mSQL utility that provides a mechanism for automatic expiration of records based on age. Further details can be seen at:

    http://www.dcicorp.com/~scottb/projects/msqlexpire

mSQL-Export

Kerry Garrison <garrison@delta.net> has developed a Perl script that will export an mSQL table to a delimited text file. Further details can be seen at:

    http://www.delta-design.com/msqltools

MS Windows

Dean Fuqua <fuqua@niehs.nih.gov> 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 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 <ask-oa@oa.guild.org> 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 <skatz@blm.gov> 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

OCX

Shayne Hughes <shayne@vgl.ucdavis.edu> has created a 32-bit OCX control that exposes all of the mSQL API (based on 1.0.12) with only slight modifications to make it work with pointer impaired languages like Visual Basic. It is available via anonymous ftp from:

    ftp://vgl.ucdavis.edu/pub/mSQL/ocxmsql-0.90.zip
    (1685189 bytes)

Chin-Jin Phua <jojo@ttsh.gov.sg> has also developed a mSQL 32-bit OCX for Windows95 and WinNT. He has only tested it for Visual Basic.

    ftp://Bond.edu.au/pub/Minerva/msql/Contrib/msqlocx.zip
    (17409 bytes)

ODBC

Dean Fuqua <fuqua@niehs.nih.gov> has developed a collection of ODBC files which are available via anonymous ftp from:

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

For more information on Dean's work see:

    http://alfred.niehs.nih.gov

Kevin Gill <kgill@kindle.ie> has extended Dean's work and developed ODBC software for connecting Visual Basic 3.0 to an mSQL server. His work can be obtained via anonymous ftp from:

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

and

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

The following file gives some out of date general information about the gorta software:

    ftp://bond.edu.au/pub/Minerva/msql/Contrib/ODBC/gorta.faq
    (27450 bytes)

The gorta software operates as either a 32 bit or a 16 bit ODBC driver. Different libraries/programs are required for each.

For the 16 bit version (Windows 3.1 and 3.11) install:

    ftp://bond.edu.au/pub/Minerva/msql/Contrib/ODBC/wmsqlrts.zip
    (11693 bytes)	- Mini-SQL API DLL (Must install First)
    ftp://bond.edu.au/pub/Minerva/msql/Contrib/gorta-rts.2.10.0.6.zip
    (17014 bytes)	- Gorta Driver

For the 32 bit version (Windows 95) install:

    ftp://bond.edu.au/pub/Minerva/msql/Contrib/ODBC/wmsqlr32-rts.zip
    (43193 bytes)	- Mini-SQL API 32 bit DLL (Must Install First)
    ftp://bond.edu.au/pub/Minerva/msql/Contrib/gorta32-rts.2.10.0.6.zip
    (22260 bytes)	- Gorta 32 bit driver

The following test tools are available. (These are 16 bit but should work with the 32 bit drivers).

    ftp://bond.edu.au/pub/Minerva/msql/Contrib/wmslqrs.zip
    (20257 bytes)	- A DOS/Windows version of the relshow program
    ftp://bond.edu.au/pub/Minerva/msql/Contrib/ODBC/gortars.1.0.zip
    (23756 bytes)	- An ODBC version of the relshow program

To install the ODBC driver for the first time requires the use of an install disk. This installs version 2.10.0.3 of the gorta drivers. Install from this and then overwrite the gorta dll (in the windows/system directory) with the one in the gorta 2.10.0.6 zip file. The install disk installs both the 16 and 32 bit versions of ODBC.

    ftp://bond.edu.au/pub/Minerva/msql/Contrib/ODBC/installd.zip
    (225019 bytes)

The install disk tends to replace dlls with older versions. This is a problem if you have ODBC already running. Check the DLLs on the disk, and make a copy of your dlls before starting.

    ftp://bond.edu.au/pub/Minerva/msql/Contrib/ODBC/gortars.1.0.zip
    (23756 bytes)
    ftp://bond.edu.au/pub/Minerva/msql/Contrib/ODBC/gortarts.2.10.0.3.zip
    (17480 bytes)
    ftp://bond.edu.au/pub/Minerva/msql/Contrib/ODBC/gortasrc.2.10.0.3.zip
    (51770 bytes)
    ftp://bond.edu.au/pub/Minerva/msql/Contrib/ODBC/installd.zip
    (225019 bytes)
    ftp://bond.edu.au/pub/Minerva/msql/Contrib/ODBC/msql-1.12-dump.tar.gz
    (158282 bytes)
    ftp://bond.edu.au/pub/Minerva/msql/Contrib/ODBC/wmsqlrs.zip
    (20257 bytes)
    ftp://bond.edu.au/pub/Minerva/msql/Contrib/ODBC/wmsqlrts.zip
    (11693 bytes)
    ftp://bond.edu.au/pub/Minerva/msql/Contrib/ODBC/wmsqlsrc.zip
    (15089 bytes)

Onyx

Michael Koehne <kraehe@bakunin.north.de> 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)

OS/2

Dirk Ohme <dohme@transtec.de> has ported mSQL 1.0.13 to OS/2. It is available via anonymous ftp from:

    ftp://bond.edu.au/pub/Minerva/msql/Contrib/msql113a-os2.lsm
    (1682 bytes)
 
    ftp://bond.edu.au/pub/Minerva/msql/Contrib/msql113a-os2.zip
    (675280 bytes)

Perl

Andreas Koenig <a.koenig@mind.de> has contributed a Perl 5 module which allows perl to interface to mSQL databases. It is available via anonymous ftp from any of the CPAN archives, for example:

    ftp://ftp.funet.fi/pub/languages/perl/CPAN/modules/by-category/07_Database_Interfaces/Msql/MsqlPerl-1.07.tar.gz
    (21201 bytes)

For more information about CPAN see:

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

Python

Anthony Baxter <anthony.baxter@aaii.oz.au> 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 <m.hessling@qut.edu.au> has provided an mSQL extension to REXX under Un*x and OS/2. It is available via anonymous ftp from the following sites:

    ftp://bond.edu.au/pub/Minerva/msql/Contrib
    ftp://ftp.qut.edu.au/src/REXXSQL
    ftp://ftp.xylogics.com/pub/misc/REXXSQL

You'll need to download the following files:

    rxsqldoc13.lsm (1014 bytes)
    rxsqldoc13.zip (39170 bytes)
    rxsqlmin13.lsm (1001 bytes)
    rxsqlmin13.zip (156525 bytes)
    rxsqlsam13.lsm (1014 bytes)
    rxsqlsam13.zip (25299 bytes)

Simple SQL

Brian Jepson <bjepson@janus.saturn.net> has developed a fairly full-featured database management system toolkit using PHP and mSQL. It is available from:

    http://www.saturn.net/~bjepson/simple.html

SQLBase

Klaus Thiele <kth@oblib.teuto.de> 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 <hs@soderstrom.se> 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 <pepersb@cuug.ab.ca> 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 <pascal@wsc.com> 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)

tkmSQL

Alligator Decartes <descarte@hermetica.com> is developing an mSQL interface that uses Perl 5, Tk, and DBD/DBD-mSQL. It is currently in alpha release for developers only but Alligator would appreciate feedback.

tkmSQL requires:

• perl 5.001m or above
• Tk-b8

  Note: This is a perl Tk module and is NOT to be confused with Tk itself. It can be obtained via anonymous ftp from:

      ftp://ftp.wpi.edu/perl5
  

  There is also a FAQ available from:

      http://w4.lns.cornell.edu/~pvhp/ptk/ptkFAQ.html
  

• DBI-0.65
• DBD::mSQL-0.60pl9

You may obtain tkmSQL via anonymous ftp from:

    ftp://ftp.mcqueen.com/pub/databases/dbatools/tkmSQL

Unique sequence number generator

Pascal Forget <pascal@wsc.com> 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/sgs-1.0.0.tar.gz
    (24216 bytes)

Visual Basic

Jim Gerace <kasi@dreamscape.com> has implemented a Visual Basic client API for MS Windows and mSQL. It is available via anonymous ftp from:

    ftp://bond.edu.au/pub/Minerva/msql/Contrib/msqlcvb.zip
    (42192 bytes)

WDB

Bo Frese Rasmussen <bfrasmus@eso.org> 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/wdb/html/wdb.html

Websql

Henry Minsky <hqm@ua.com> 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)

XfSQL

Mark Loveland <mark@zeus.mysticgrp.com> has developed an X interface to mSQL using the Xforms package. Mark's work is available via anonymous ftp from:

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

Xforms is available for a number of different platforms via anonymous ftp from either of the following locations:

    ftp://laue.phys.uwm.edu/pub/xforms/test
    ftp://ftp.cs.ruu.nl/pub/XFORMS/test

Z Classes for C++

Dean Fuqua <fuqua@niehs.nih.gov> 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)

3D Planet

Mark Mazur <mark@opencad.com> uses mSQL and The Personal Home Page Construction Kit to enable users to find the nearest 3D Planet store. This page can be seen at:

    http://www.3dplanet.com/d5.html

Bishop Museum, Honolulu

Jeffrey Sue <jysue@aloha.net> has used mSQL and Rasmus Lerdorf's PHP package to create the first of several web based biology related databases. The first database is the arthropod checklist, a list of all known arthropods found in the Hawaiian Islands. The next databases will be additional checklists, starting with flowering plants. It can be seen at:

    http://www.bishop.hawaii.org/bishop/HBS/arthrosearch.html

Dave 'Gizmo' Gymer

Dave Gymer <dgymer@gdcarc.co.uk> has used mSQL and Dean Fuqua's zclass C++ classes to provide a web based database system for his collection of compact discs. The CD collection and the source code can be seen at:

    http://www.mal.com/~dgymer/gizmo/music.html

European Wide Service Exchange

The European Wide Service Exchange uses mSQL extensively. Apart from a user registration and customization service, complex relational tables allow context sensitive searching, both by geographic area as well as by 'free text'. Further functionality relying on mSQL includes urn->urc resolution services, calendars and an automatic 'what is new page'. What makes this site unique however is the ability for the user to add, modify or remove data entries from the databases through a web interface.

Nerosworld

David Perry <deperry@nerosworld.com> uses mSQL extensively to drive his web server. Examples of his work can be seen at:

    http://www.nerosworld.com/realestate/
    http://www.nerosworld.com/business/
    http://www.nerosworld.com/tradingpost/
    http://www.nerosworld.com/fstop/
    http://www.nerosworld.com/nero/zipcode.htm
    http://www.nerosworld.com/romancing_the_web/
 

Physics Pilot

Kenneth Holmlund <Kenneth.Holmlund@TP.UmU.SE> from the Department of Theoretical Physics at Umee University in Sweden has used mSQL and The Personal Home Page Construction Kit to create the "The Internet Pilot to Physics". It can be seen at:

    http://www.tp.umu.se/TIPTOP

QMS

James Hill <james_hill@iscclink.is.qms.com> has used mSQL and The Personal Home Page Construction Kit to create a searchable web based FAQ for QMS printers. It can be seen at:

    http://www.qms.com/www/faq

James has made the source code available via anonymous ftp. It can be downloaded from:

    ftp://ftp.qms.com/pub/mktg/outgoing/SupportBase.tar.gz
    (5509 bytes)

The Railway Exchange

Lester Hightower <hightowe@scri.fsu.edu> uses mSQL to power his web pages on model railway equipment. They can be seen at:

    http://www.railwayex.com/

Used gear price list

Neil Bradley <neil@synthcom.com> has used mSQL to provide a price list for second hand music equipment. A demonstration of this system is available at

    http://www.synthcom.com/cgi-bin/gear

and the source code can be obtained via anonymous ftp from:

    ftp://ftp.synthcom.com/pub/stuff