# Table of contents:

      Info
      Introduction and History
      Mini SQL Specification
        The CREATE TABLE Clause
        The DROP Clause
        The INSERT INTO Clause
        The DELETE FROM Clause
        The SELECT Clause
        The UPDATE Clause
      The Database Engine (Server)
        Server Debugging
        Configuration
        Access Control List (ACL)
      Runtime Configuration
        Environment Variable EDITOR
        Environment Variable MINERVA_DEBUG
        Environment Variable MSQL_HOME
        Environment Variable MSQL_HOST
        Environment Variable MSQL_TCP_PORT
        Environment Variable MSQL_UNIX_PORT
        Environment Variable USER
        Environment Variable VISUAL
      C Programming API
        API Debugging
        msqlGetErrMsg
        msqlConnect
        msqlUserConnect
        msqlCreateDB
        msqlDropDB
        msqlSelectDB
        msqlQuery
        msqlStoreResult
        msqlFreeResult
        msqlFetchRow
        msqlDataSeek
        msqlNumRows
        msqlFetchField
        msqlFieldSeek
        msqlNumFields
        msqlListDBs
        msqlListTables
        msqlListFields
        msqlClose
        msqlGetHostInfo
        msqlGetProtoInfo
        msqlGetServerInfo
        msqlErrMsg
      Clients delivered within this package
        msql.exe - Terminal Monitor
        msqladm.exe - Database Administration
        relshow.exe - Schema Viewer
        msqldump.exe - Database Dumper
      mSQL Access from (Script) Languages
        CLI (OS/2)
        ESL (Unix)
        ODBC (Win 3.x, Win95, WinNT)
        Perl5 (Unix)
        Python (Unix)
        Rexx (OS/2)
        Tcl (Unix)
      Frequently asked questions (FAQ)
        common FAQ
        FAQ - OS/2
      Author's Details
      Archive Location
      Mailing List

# Mini SQL 1.0.16 (OS/2 version)

# Info

    Mini SQL - A Lightweight Database Engine
    mSQL 1.0.16 for OS/2 2.x or Warp with TCP/IP

    All programs included in this package are provided "as is", without
    any warranty! Try them on your own risk.

This documentation is based on the postscript file mSQL-111.ps describing version 1.0.11 (Feb. 1996) and has been adapted to the OS/2 port.

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

Adaption to OS/2 made by Dirk Ohme

# Introduction and History

Mini SQL, or mSQL, is a lightweight database engine designed to provide fast access to stored data with low memory requirements. As its name implies, mSQL offers a subset of SQL as its query interface. Although it only supports a subset of SQL (no views, sub-queries, etc.), everything it supports is in accordance with the ANSI SQL specification. The mSQL package includes the database engine, a terminal "monitor" program, a database administration program, a schema viewer, and a C language API. The API and the database engine have been designed to work in a client/server environment over a TCP/IP network.

# Mini SQL Specification

The mSQL language offers a significant subset of the features provided by ANSI SQL. It allows a program or user to store, manipulate and retrieve data in table structures. It does not support relational capabilities such as table joins, views or nested queries. Although it does not support all the relational operations defined in the ANSI specification, it does provide the capability of "joins" between multiple tables.

Although the definitions and examples on the next pages depict mSQL key words in upper case, no such restriction is placed on the actual queries.

## The CREATE TABLE Clause

The create clause as supported by mSQL can onyl be used to create a table. It cannot be used to create other definitions such as views. It should also be noted that there can only be one primary key field defined for a table. Defining a field as a key generates and implicit not null attribute for the field.

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

for example:

      CREATE TABLE emp_details (
          first_name   char(15) not null,
          last_name    char(15) not null,
          dept         char(20),
          emp_id       int primary key,
          salary       int
      )

The available types (col_type) are:

char(len) | String of characters (or other 8 bit data)
int | signed integer values
real | Decimal or Scientific Notation real values

## The DROP Clause

DROP is used to remove a table definition from the database:

      DROP TABLE table_name

for example:

      DROP TABLE emp_details

## The INSERT INTO Clause

Unlike ANSI SQL, you cannot nest a SELECT within an INSERT (i.e. you cannot insert the data returned by a SELECT). If you don not specify the field names they will be used in the same order they were defined - you must specify a value for every field if you do this.

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

      value can be a either a integer or scientific number, a string
      or 'sysdate' or 'systime'.

Note:
SYSDATE and SYSTIME are only available in the OS/2 version of mSQL. SYSDATE returns the current date in a string of 10 characters 'yyyy/mm/dd', there 'yyyy' is the year, 'mm' is the month (01-12) and 'dd' is the day of month (01-31). SYSTIME returns the current local time in a string of 8 characters 'hH:MM.SS', with 'hH' is the hour (0-23), 'MM' is the minute (00-59) and 'SS' is the second (00-59). On "Feb 14, 1996 08:01.30" SYSDATE returns "1996/02/14" and SYSTIME returns " 8:01.30". The key-words SYSDATE and SYSTIME are not case-sensitive.

for example:

      INSERT INTO emp_details (first_name, last_name, dept, salary)
        VALUES ('David', 'Hughes', 'I.T.S.', 12345)

      INSERT INTO emp_details
        VALUES ('David', 'Hughes', 'I.T.S.', 12345)

      INSERT INTO log_table
        VALUES ('starting time', sysdate, systime)

The number of values supplied must match the number of columns.

## The DELETE FROM Clause

The syntax for mSQL's DELETE clause is

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

      OPERATOR can be <, >, =, <=, >=, <>, or 'like'

for example:

      DELETE FROM emp_details WHERE emp_id = 12345

## The SELECT Clause

The SELECT offered by mSQL lacks some of the features provided by the SQL spec:

- No nested selects
- No implicit functions (e.g. count(), avg() )

It does however support:

- Joins - including table aliases
- DISTINCT row selection
- ORDER BY clauses
- Regular expression matching
- Column to column comparisons in WHERE clauses

So, the formal syntax for mSQL's SELECT is:

      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 number_of_rows ]

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

A simple SELECT may be

      SELECT first_name, last_name FROM exmp_details
        WHERE dept = 'finance'

To sort the returned data in ascending order by last_name and descending order by first_name the query would look like this

      SELECT first_name, last_name FROM emp_details
        WHERE dept = 'finance'
        ORDER BY last_name, first_name DESC

And to remove any duplicate rows, the DESTINCT operator could be used:

      SELECT DISTINCT first_name, last_name FROM emp_details
        WHERE dept = 'finance'
        ORDER BY last_name, first_name DESC

To limit the number of rows returned to a maximum of `3` rows, use the LIMIT operator:

      SELECT DISTINCT first_name, last_name FROM emp_details
        WHERE dept = 'finance'
        ORDER BY last_name, first_name DESC
        LIMIT 3

The regular expression syntax supported by LIKE clauses is that of standard SQL:

- '_' matches any single character
- '%' matches 0 or more characters of any value
- '\' excapes special characters (e.g. '\%' matches '%' and '\\' matches '\')
- all other characters match themselves

So, to search for anyone in finance who's last name consits of a letter followed by 'ughes', such as Hughes, the query could look like this:

      SELECT first_name, last_name FROM emp_details
        WHERE dept = 'finance'
        AND last_name like '_ughes'

The power of a relational query language starts to become apparent when you start joining tables together during a select. Lets say you had two tables defined, one containing staff details and another listing the projects being worked on by each staff member, and each staff member has been assigned an employee number that is unique to that person. You could generate a sorted list of who was working on what project with a query like this:

      SELECT emp_details.first_name, emp_details.last_name,
             project_details.project
        FROM emp_details, project_details
        WHERE emp_details.emp_id = project_details.emp_id
        ORDER BY emp_details.last_name, emp_details.first_name

mSQL places no restriction on the number of tables "joined" during a query so if there were 15 tables all containing information related to an employee ID in some maner, data from each of those tables could be extracted, albeit slowly, by a single query. One key point to note regarding joins is that you must qualify all column names with a table name. mSQL does not support the concept of uniquely named columns spanning multiple tables so you are forced to qualify every column name as soon as you access more than one table in a single select.

mSQL-1.0.6 adds table aliases so that you can perform a join of a table onto itself. With this you could find out from a list of child/parent tuples any grandparents using something like

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

The table aliases t1 and t2 both point to the same table (parent_data in this case) and are treated as two different tables that just happen to contain exactly the same data.

## The UPDATE Clause

The mSQL UPDATE clause cannot use a column name as a value. Only literal values may be used as an update value

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

      OPERATOR can be <, >, =, <=, >=, <>, or 'like'
      value can be a either a integer or scientific number, a string
      or 'sysdate' or 'systime'.

Note:
SYSDATE and SYSTIME are only available in the OS/2 version of mSQL. SYSDATE returns the current date in a string of 10 characters 'yyyy/mm/dd', there 'yyyy' is the year, 'mm' is the month (01-12) and 'dd' is the day of month (01-31). SYSTIME returns the current local time in a string of 8 characters 'hH:MM.SS', with 'hH' is the hour (0-23), 'MM' is the minute (00-59) and 'SS' is the second (00-59). On "Feb 14, 1996 08:01.30" SYSDATE returns "1996/02/14" and SYSTIME returns " 8:01.30". The key-words SYSDATE and SYSTIME are not case-sensitive.

for example:

      UPDATE emp_details SET salary=30000 WHERE emp_id=1234

      UPDATE log_table SET date=sysdate WHERE date like '1996/%'

# The Database Engine (Server)

The mSQL daemon, msqld.exe, is a standalone application that listens for connections on a well known TCP socket. It is a single process engine that will accept multiple connections and serialise the queries received. It utilises memory mapped I/O and cache techniques to offer rapid access to the data stored in a database. It also utilises a stack based machanism that ensures that INSERT operations are performed at the same speed regardless of the size of the table being accessed. Preliminary testing performed by a regular user of mSQL has shown that for simple queries, the performance of mSQL is comparable to or better than other freely available database packages. For example, on a set of sample queries including simple INSERTs, UPDATEs and SELECTs, mSQL performs roughly 4 times faster than University Ingres and over 20 times faster than Postgres on an Intel 486 class machine running Linux.

The server may be accessed via a well known TCP socket (per default port 4333) - since OS/2 does not offer UNIX domain socket with the file system, `there is no UNIX domain socket supported`. The availability of the TCP socket allows client software to access data stored on machine over the network. (Sentences concerning UNIX domain socket supported ommited.) See the details on the programming API and also the command line options to standard programs for details on selecting the server machine.

## Server Debugging

The database engine includes debugging code so that its progress can be monitored. There are currently 8 debugging modules available in the engine. Debugging for any of the available modules can be enabled at runtime by setting the contents of the MINERVA_DEBUG environment variable to a colon separated list of debug module names. A list of available debug modules is given below:

access | Display details on client accesses
cache | Display the workings of the table cache
error | Display error message as well as sending them to the client
key | Display details of key based data look-ups
malloc | Display details on memory allocation
mmap | Display details of memory mapped regions
proctitle | Display procedure calls
query | Display each query before it is executed
trace | Display a function call trace as the program executes
general | Anything that didn't fit into a category above

For example, to make the server display the queries before they are processed and also show details of the memory allocation that takes place during the query execution, the following value would be set

      set MINERVA_DEBUG=query:malloc

Note:
Since OS/2 comes with the concept of separate command shells, you have to stop the server, change the environment variable and then to re-start the server again in order to see the desired debugging information.

## Configuration

By default, the software is installed into \public\mSQL\ and the server will use space within that directory for the storage of the databases and also temporary result tables during operations such as joins and ordering. The following list shows the assignment of directories below there:

\BIN\ | contains all executables, such as server, monitor, utilities, etc.
\DOC\ | contains all printable and online documentation
\INCLUDE\ | contains header files for the C programming API
\LIB\ | contains the programming and runtime libraries
\MSQLDB\ | contains the sub-directories with the database tables
\TMP\ | temporary stuff
\W3-mSQL\ | W3-mSQL gateway programming samples

Note:
The access to directories has been changed. Up to 1.0.13c all paths were set absolutely. Since 1.0.14a the server msqld.exe the path detection goes like this:

- first the pre-defined path set in site.mm (INSTPATH) will taken
- if a environment variable `MSQL_HOME` does exist its values is taken instead of the pre-defined path

if a sub-directory MSQLDB containing the database files could be found, no more path checking is done, else:

- the server gets the path to its binary file (msqld.exe)
- first, at that path is checked whether MSQLDB exists or not
- if not, the same check is done a directory hierarchie higher than the server executable is located (i.e. \aaa\bbb\msqld.exe, first \aaa\bbb\msqldb\, second \aaa\msqldb\)
- if this again fails the server terminates with an error message

## Access Control List (ACL)

Access control is managed by the msql.acl file in the installation directory (default: \public\mSQL\). This file is split into entries for each database to be controlled. If the file doesn't exist or details for a particular database aren't configured, access reverts to global read/write. An example ACL entry is included below:

      # Sample access control for mSQL
      database=test
      read=bambi,paulp
      write=root
      host=*.Bond.edu.au,-student.it.Bond.edu.au
      access=local,remote

Using this definition, database `test` can be accessed by both `local` and `remote` connections from any host in the `Bond.edu.au` domain except for the machine `student.it.Bond.edu.au`. Read access is only granted to `bambi` and `paulp`. Nobody else is allowed to perform SELECTs on the database. Write access is only available to `root`.

Control is matched on the first match found for a given item. So, a line such as "read=-*,bambi" would not do the desired thing (i.e. deny access to everyone other than bambi) because -* will also match bambi. In this case the line would have to be "`read=bambi,-*`" although the -* is superfluous as that is the default action.

Note that if an entry isn't found for a particular configuration line (such as read) it defaults to a global denial. For example, if there is no "read" line (i.e. there are no read tokens after the data is loaded) nobody will be granted read access. This is in contrast to the action taken if the entire database definition is missing in which case access to everything is granted.

Another thing to note is that a database's entry `must` be followed by a blank line to signify the end of the entry. There may also be multiple config lines in the one entry (such as "`read=bambi,paulp`" "`read=root`"). The data will be loaded as though it was concatenated onto the same "read" line (i.e. "`read=bambi,paulp,root`").

Wildcards can be used in any configuration entry. A wildcard by itself will match anything whereas a wildcard followed by some text will cause only a partial wildcard (e.g. *.Bond.edu.au matches anything that ends in .Bond.edu.au). A wildcard can also be set for the database name.

Note:
A good practice is to install an entry with `database=*` as the last entry in the ACL file so that if the database being accessed wasn't covered by any of the other rules a default site policy can be enforced.

The ACL information can be reloaded at runtime using "msqladm reload". This will parse the file before it sends the reload command to the engine. Only if the file is parsed cleanly is it reloaded. Like most msqladm commands, it will only be accepted if generated by the root user (e.g. under OS/2 the user set in the environment variable USER at server startup) on the local host running the server.

# Runtime Configuration

Both the mSQL server and API library support a series of environment variables that can dictate and modify the behaviour of the software. Using these variables it is possible to run multiple servers on the same host (one for testing for example). It must be stressed that at no time must more than one server be configured to access the same database directory. If multiple servers are configured to work on the same data, you will probably end up with corrupted database (remark on OS/2: won't be possible since OS/2 prohibits write access to already opened files).

## Environment Variable EDITOR

Used by the monitor program, msql.exe. The environment variable specifies the path and name of the default editor used for editing a stored query. This variable is used only if VISUAL is empty. If VISUAL and EDITOR both are empty, the default e (OS/2 system editor) is used.

## Environment Variable MINERVA_DEBUG

This environment variable is used on debugging. It has different sets of parameters for server and API debugging. Please see the appropriate chapters for details.

Note:
API debugging includes debugging of the client software, because the clients make use of the API.

## Environment Variable MSQL_HOME

The MSQL_HOME variable instructs the server to ignore the default installation directory (such as \public\mSQL\) and use the value of that variable instead. The directory in which mSQL maintains the databases is a subdirectory of the installation directory so this environment variable allows you to run the mSQL server on another set of databases if you wish.

## Environment Variable MSQL_HOST

The MSQL_HOST environment variable is used at the monitor program msql.exe to specify an alternative default mSQL server machine.

Note:
This environment variable is used nowhere else except msql.exe (on the OS/2 port of mSQL it is used in every client program coming with the archive).

## Environment Variable MSQL_TCP_PORT

By default, mSQL uses a pre-defined TCP/IP port (OS/2: 4333) for network communications. You can reconfigure mSQL to use another TCP port in 2 ways, either with the MSQL_TCP_PORT variable or by editing \MPTN\ETC\services (on OS/2 Warp Connect).

mSQL initially searches for an entry of type msql/tcp in the service database. If it finds such an entry, it uses the port number specified in that file. It then checks for the MSQL_TCP_PORT environment variable. If it finds such a variable, it uses the port number stored in that variable as the TCP port (overriding the services entry if any). If it finds neither an services entry nor the MSQL_TCP_PORT variable, it defaults to using the precompiled value for the TCP port (see \SRC\site.mm).

## Environment Variable MSQL_UNIX_PORT

`Not used under OS/2, since OS/2 does not support UNIX domain sockets.`

## Environment Variable USER

This environment variable is normally set within the TCP/IP environment on OS/2 and represent the default user name. The OS/2 version of the database engine uses USER to determine a root user. Root user is a user with a name identical to the contents of USER. On the other hand, the OS/2 versions of monitor, tools and the API use USER to determine the user name. Since OS/2 does not offer a multi-user environment the way, UNIX does, this may be a solution. To prevent database engine from detecting a 'root' set different USER environment variables in the shells you use for engine and clients.

## Environment Variable VISUAL

Used by the monitor program, msql.exe. The environment variable specifies the path and name of the default editor used for editing a stored query. If VISUAL and EDITOR both are empty, the default e (OS/2 system editor) is used.

# C Programming API

Included in the distribution is the mSQL API library, mSQL.lib for static and mSQL_dll.lib for dynamic linking against mSQL.DLL. The API allows any C program to communicate with the database engine. The API functions are accessed by including the msql.h header file into your program and by linking against the mSQL library (by adding mSQL.lib or mSQL_dll to the list of modules to be linked by the linker). The library and header files will be installed by default into \lib\ and \include\ respictively.

The following function and procedure prototypes are defined in msql.h:

- int msqlGetErrMsg( char *pszErrMsg );
- int msqlConnect( char *host );
- int msqlConnect( char *host, char *user );
- int msqlCreateDB( int sock, char *dbName );
- int msqlDropDB( int sock, char *dbName );
- int msqlSelectDB( int sock, char *dbName );
- int msqlQuery( int sock, char *query );
- m_result *msqlStoreResult();
- void msqlFreeResult( m_result *result );
- m_row msqlFetchRow( m_result *result );
- void msqlDataSeek( m_result *result, int pos );
- int msqlNumRows( m_result *result );
- m_field *msqlFetchField( m_result *result );
- void msqlFieldSeek( m_result *result, int pos );
- int msqlNumFields( m_result *result );
- m_result *msqlListDBs( int sock );
- m_result *msqlListTables( int sock );
- m_result *msqlListFields( int sock, char *tableName );
- int msqlClose( int sock );
- char *msqlGetHostInfo();
- int msqlGetProtoInfo();
- char *msqlGetServerInfo();

The following global variables are defined in msql.h (only available with static linking):

- extern char msqlErrMsg[];

The following definitions are defined in msql.h:

- CHAR_TYPE
- INT_TYPE
- IS_PRI_KEY( flags )
- IS_NOT_NULL( flags )
- REAL_TYPE

The following data types are defined in msql.h:

- m_field
- m_row

## API Debugging

Like the mSQL engine, the API supports debugging via the MINERVA_DEBUG environment variable. Three debugging modules are currently supported by the API:

msql_api | Internal informations, such as connection details, are displayed
msql_malloc | Details about the memory used by the API library is obtained. Information
msql_query | API prints the contents of queries as they are sent to the server such as the location and size of (m)allocated blocks and the adresses passed to free() will be generated.

Multiple debug modules can be enabled by setting MINERVA_DEBUG to a colon separated list of module names. For example:

      set MINERVA_DEBUG=api:query

Note:
Since OS/2 comes with the concept of separate command shells, you have to stop the server, change the environment variable and then to re-start the server again in order to see the desired debugging information.

## msqlGetErrMsg

      char *msqlGetErrMsg( char *pszErrMsg );

This function grants access to an internal variable in the database library, containing a text string with a detailed message after errors. The parameter pszErrMsg points to a buffer long enough to store this message. In this case the returned pointer is equal to pszErrMsg. If the parameter is set NULL the returned pointer can be used to address the internal library variable (read-only).

Note:
This function is prefered for both static and dynamic linkage.

## msqlConnect

      int msqlConnect( char *host );

msqlConnect() forms an interconnection with the mSQL engine. It takes as its only argument the name or IP address of the host running the mSQL server. If NULL is specified as the host argument, a connection is made to localhost. If any error occurs, a value of `-1` is returned and the external variable `msqlErrMsg` will contain an appropriate text message. This variable is defined in msql.h.

If the connection is made to the server, an integer identifier is returned to the calling function. This value is used as a handle for all other calls to the mSQL API. The value returned is in fact the socket descriptor for the connection. By calling msqlConnect() more than once and assigning the returned values to separate variables, connections to multiple database servers can be maintained similtaneously.

In previous versions of mSQL, the MSQL_HOST environment variable could be used to specify a target machine if the host parameter was NULL. `This is no longer the case`.

## msqlUserConnect

    only available within the OS/2 port

      int msqlUserConnect( char *host, char *user );

Like msqlConnect(), but this routine allows the programmer to specify a user for which the connection should be done. In order to distinquish between msqlUserConnect() and msqlConnect() this routine adds a fix prefix to the user parameter (`x_`). If you specify a user named `Carl`, the server will treat him as user `x_Carl`.

## msqlCreateDB

    undocumented within original documentation

      int msqlCreateDB( int sock, char *dbName );

This function ables one to create of a new database. Its parameters are sock, the communication socket, and dbName, the name of the database to be created. The function returns a value of `0` on success, any other value represents an error.

Note:
A database is the place there tables are stored. Under mSQL this is a sub-directory.

## msqlDropDB

    undocumented within original documentation

      int msqlDropDB( int sock, char *dbName );

A database can be removed with this function. There must be an open connection to the database server (with sock as socket descriptor). If the database dbName could be removed successfully, a return value of `0` is provided.

## msqlSelectDB

      int msqlSelectDB( int   sock,
                        char *dbName
                      );

Prior to submitting any queries, a database must be selected. msqlSelectDB() instructs the engine which database is to be accessed. msqlSelectDB() is called with the socket descriptor returned by msqlConnect() and the name of the desired database. A return value of `-1` indicates an error with `msqlErrMsg` set to a text string representing the error. msqlSelectDB() may be called multiple times during a program's execution. Each time it is called, the server will use the specified database for future access. By calling msqlSelectDB() multiple times, a program can switch between different databases during its execution.

## msqlQuery

      int msqlQuery( int   sock,
                     char *query
                   );

Queries are sent to the engine over the connection associated with sock as plain text strings using msqlQuery(). As usual, a returned value of `-1` indicates an error and `msqlErrMsg` will be updated. If the query generates output from the engine, such as a SELECT statement, the data is buffered in the API waiting for the application to retrieve it. If the application submits another query before it retrieves the data using msqlStoreResult(), the buffer will be overwritten by any data generated by the new query.

## msqlStoreResult

      m_result *msqlStoreResult();

Data returned by a SELECT query must be stored before another query is submitted or it will be removed from the internal API buffers. Data is stored using the msqlStoreResult() function which returns a result handle to the calling routines. The result handle is a pointer to a `m_result` structure and is passed to other API routines when access to the data is required. Once the result handle is allocated, other queries may be submitted. A program may have many result handles active simultaneously.

## msqlFreeResult

      void msqlFreeResult( m_result *result );

When a program no longer requires the data associated with a particular query result, the data must be freed using msqlFreeResult(). The result handle associated with the data, as returned by msqlStoreResult(), is passed to msqlFreeResult() to identify the data set to be freed.

## msqlFetchRow

      m_row msqlFetchRow( m_result *result );

The individual database rows returned by a SELECT are accessed via the msqlFetchRow() function. The data is returned in a variable of type `m_row` which contains a char pointer for each field in the row. For example, if a SELECT statement selected 3 fields from each row returned, the value of the 3 fields would be assigned to the elements [0], [1], and [2] of the variable returned by msqlFetchRow. A value of `NULL` is returned when the end of the data has been reached. See the example at the end of this section for further details. Note, a `null` value is represented as a `NULL pointer` in the row.

## msqlDataSeek

      void msqlDataSeek( m_result *result,
                         int       pos
                       );

The `m_result` structure contains a client side "cursor" that holds information about the next row of data to be returned to the calling program. msqlDataSeek() can be used to move the position of the data cursor. If it is called with a position of `0`, the next call to msqlFetchRow() will return the first row of data returned by the server. The value of `pos` can be anywhere form `0` (the first row) and the number of rows in the table. If a seek is made past the end of the table, the next call to msqlFetchRow() will return `NULL`.

## msqlNumRows

      int msqlNumRows( m_result *result );

The number of rows returned by a query can be found calling msqlNumRows() and passing it the result handle returned by msqlStoreResult(). The number of rows of data sent as a result of the query is returned as an integer value. If a SELECT query didn't match any data, msqlNumRows() will indicate that the result table has `0` rows.

Note:
earlier versions of mSQL returned a `NULL` result handle if no data was found. This has been simplified and made more intuitive by returning a result handle with `0` rows of result data.

## msqlFetchField

      m_field *msqlFetchField( m_result *result );

Along with the actual data rows, the server returns information about the data fields selected. This information is made available to the calling program via the msqlFetchField() function. Like msqlFetchRow(), this function returns one element of information at a time and returns `NULL` when no further information is available. The data is returned in a `m_field` structure which contains the following information:

      typedef struct { char  *name,    /* name of the field        */
                             *table;   /* name of the table        */
                       int    type,    /* data type of field       */
                              length,  /* length in bytes of field */
                              flags;   /* attribute flags          */
                     } m_field;

Possible values for the `type` field (see also CREATE TABLE) are defined in msql.h as

- `CHAR_TYPE` (character string or any other 8 bit data)
- `INT_TYPE` (integer value)
- `REAL_TYPE` (floating point value)

The individual attribute `flag` entry can be accessed using the following macros:

      IS_PRI_KEY( flags )   /* Field is the primary key           */
      IS_NOT_NULL( flags )  /* Field may not contain a null value */

## msqlFieldSeek

      void msqlFieldSeek( m_result *result,
                          int       pos
                        );

The `result` structure includes a "cursor" for the field data. It's position can be moved using the msqlFieldSeek() function. See msqlDataSeek() for further details.

## msqlNumFields

      int msqlNumFields( m_result *result );

The number of fields returned by a query can be ascertained by calling msqlNumFields() and passing it the `result` handle. The value returned by msqlNumFields() indicates the number of elements in the data vector returned by msqlFetchRow(). It is wise to check the number of fields returned before, as with all the arrays, accessing an element that is beyond the end of the data vector - which can result in a segmentation fault.

## msqlListDBs

      m_result *msqlListDBs( int sock );

A list of the databases known to the mSQL engine can be obtained via the msqlListDBs() function. A result handle is returned to the calling program that can be used to access the actual database names. The individual names are accessed by calling msqlFetchRow() passing it the `result` handle. The `m_row` data structure returned by each call will contain one field being the name of the available databases. As with all functions that return a `result` handle, the data associated with the result must be freed when it is no longer required using msqlFreeResult().

## msqlListTables

      m_result *msqlListTables( int sock );

Once a database has been selected using msqlInitDB(), a list of the tables defined in that database can be retrieved using msqlListTables(). As with msqlListDBs(), a `result` handle is returned to the calling program and the names of the tables are contained in data rows where element [0] of the row is the name of one table in the current database. The result handle must bee freed when it is no longer needed by calling msqlFreeResult().

## msqlListFields

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

Information about the fields in a particular table can be obtained using msqlListFields(). The function is called with the name (`tableName`) of a table in the current database as selected using msqlSelectDB() and a `result` handle is returned to the caller. Unlike msqlListDBs() and msqlListTables(), the field information is contained in field structures rather than data rows. It is accessed using msqlFetchField(). The result handle must bee freed when it is no longer needed by calling msqlFreeResult().

## msqlClose

      int msqlClose( int sock );

The connection to the mSQL engine can be closed using msqlClose(). The function must be called with the connection `sock`et returned by msqlConnect() when the initial connection was made.

## msqlGetHostInfo

    undocumented within original documentation

      char *msqlGetHostInfo();

Set by msqlConnect before trying to connect to database server. The string returned contains the host name and the kind of connection, i.e. for local connections

      Localhost via UNIX socket          (Unix)
      localhost via TCP/IP               (OS/2)

and for remote connections

      <server_name> via TCP/IP

## msqlGetProtoInfo

    undocumented within original documentation

      int msqlGetProtoInfo();

Set by msqlConnect at connection. This function returns the current mSQL protocol version used.

## msqlGetServerInfo

    undocumented within original documentation

      char *msqlGetServerInfo();

Set by msqlConnect at connection. The function returns the host name information provided by the database server.

## msqlErrMsg

      extern char msqlErrMsg[];

contains a zero-terminated error message, if an error occurs.

Note:
This variable is only accessible with static linkage. On dynamic linkage the function msqlGetErrMsg() is prefered to use.

# Clients delivered within this package

The OS/2 version of mSQL comes with the same client programs as the original distribution for Unix does. In fact, in order to be able to run mSQL an FAT based file systems, too, the names of the programs has been adapted to 8.3 notation of FAT.

The client programs are (OS/2 file name, Unix file name, description):

msql.exe | msql | Terminal Monitor
msqladm.exe | msqladmin | Database Administration
relshow.exe | relshow | Schema Viewer
msqldump.exe | msqldump | Database Dumper

## msql.exe - Terminal Monitor

      msql[.exe] [ -q ] [ -h host ] database

Like all database applications, mSQL provides a program that allows a user to interactively submit queries to the database engine. In the case of mSQL, it is a program simply called 'msql.exe'. If requires one command line argument, being the name of the `database` to access. Once started, there is no way to swap databases without restarting the program.

The monitor also accepts two command line flags as outlined below:

- '-h host' connect to the mSQL server on the given `host`
- '-q' process one query and quit returning an exit code

The monitor has been modelled after the original Ingres (and the subsequent Postgres) monitor program. Commands are distinguished from queries due to their being prefixed with a backslash. To obtain help from the monitor prompt, the `\h` command is used. To exit from the program, the `\q` command must be entered.

To send a query to the engine, the query is entered followed by the `\g` command. `\g` tells the monitor to "Go" and send the query to the engine. If you wish to edit your last query, `\e` will place you inside an editor so that you can modify your query.

Note:
In order to determine the editor for doing the job, the monitor first asks the environment variable VISUAL for a default editor. If the variable is not set, the monitor second request the environment variable EDITOR for a default editor setting. If this again fails, the OS/2 default system editor e is used.

When you have completed your editing, exiting the editor in the usual manner will return you to msql.exe with the edited query placed in the buffer. The query can then be submitted to the server by using the `\g` "Go" command as usual.

The query buffer is maintained between queries to not only enable query editing, but to also allow a query to be submitted multiple times. If `\g` is entered without entering a new query, the last query to be submitted wil be resumitted. The contents of the query buffer can also be displayed by using the `\p` "Print" command of the monitor.

To enable convenient access to database servers running on remote hosts, the mSQL terminal monitor supports the use of an environment variable to indicate the machine running the server (rather than having to specify "-h some.host.name" everytime you execute mSQL). To use this feature set the environment variable MSQL_HOST to the name or IP address of the desired machine.

Note:
This is a function provided by the mSQL terminal monitor `NOT` the mSQL API library and as such is not available for use with other programs.

## msqladm.exe - Database Administration

      msqladm[.exe] [ -h host ] [ create database |
                                  drop   database |
                                  shutdown        |
                                  reload          |
                                  version ]

mSQL databases are administrated using the msqladm.exe (Unix: msqladmin) tool. Several administrative tasks, such as creating new databases and forcing a server shutdown are performed using msqladm.exe. Like all mSQL programs, msqladm.exe accepts the '-h host' command line flag to specify the desired machine. The commands available via msqladm.exe are:

create database | Create a new database called 'database'
drop database | Delete the entire database called 'database' (with all tables)
shutdown | Tell the server to shut itself down
reload | Tell the server to relaod its access control information (ACL)
version | Display various version information from the server

If should be noted that the server will only accept create, drop, shutdown, and reload commands if they are sent by the root user and are sent from the machine running the server. An attempt to perform any of these commands from a remote client or as a non-root user will result in a "permission denied" error. The only command you can execute over the network or as a non-root user is version.

Note:
On OS/2 `root` user is defined as the user indicated in the environment variable USER at server startup.

## relshow.exe - Schema Viewer

      relshow[.exe] [ -h host ] [ database ] [ table ]

mSQL provides the relshow command for display the structure of a database. If executed with `no arguments`, relshow.exe will list the available databases. If it is executed with the name of a `database`, relshow.exe will list the tables that have been defined for that database. If given both a `database` and `table` name, the schema viewer will display the structure of the table including the field names, types, and sizes. Like all mSQL programs, relshow.exe honors the '-h host' command line flag to specify a remote machine as the database server.

Note:
'relshow.exe' is linked dynamically against mSQL.DLL

## msqldump.exe - Database Dumper

      msqldump[.exe] [ -h host ] [ -v ] database [ table ]

A program is provided that will dump the contents and structure of a `table` or entire `database` in an ASCII form. The program, msqldump.exe, produces output that is suitable to be read by the mSQL terminal monitor msql.exe as a script file. Using this tool, the contents of a database can be backed-up or moved to a new database. By virtue of the '-h host' option, the contents of a remote database may be sucked over the net. This can be used as a mechanism for mirroring the contents of a mSQL database onto multiple machines. A `v` mode can be selected for more information during the dump.

Note:
msqldump.exe started life as a user contributed program called msqlsave written by Igor Romanenko (igor@frog.kiev.us). Thanks Igor.

Note:
'msqldump.exe' is linked dynamically against mSQL.DLL

# mSQL Access from (Script) Languages

Note:
The following list describes the Unix and OS/2 versions of user-contributed extensions to the respective language for use with mSQL. These extensions are not shipped with the mSQL package and may be retrieved separately.

## CLI (OS/2)

The Call Level Interface is a project, to able programmers to write protable C code for database applications. It is a part of the OS/2 mSQL package. See the appropriate documentation in the mSQL folder.

## ESL (Unix)

Another development that has arisen from the development of Minerva has been the Extensible Scripting Language ESL (pronounced Easel). ESL is a C styled scripting language that offers automatic memory allocation, strict typing, associative arrays (both in-core and bound the ndbm files), full SNMP support and much, much more. ESL resembles C so closly that any C programmer will be able to code in ESL within a minute or two of scanning the manual. Because both ESL and mSQL have developed as part of the Minerva project (D. J. Hughes: "well, both were developed in the spare bedroom I call office as part of my Ph.D."). ESL provides full support for the mSQL API. This includes every aspect of the C API as well as the client server mode of operation.

## ODBC (Win 3.x, Win95, WinNT)

Kevin Gill (kgill@kindle.ie) has written an open database connection driver (ODBC level 1) and a Windows mSQL dynamic link library, so one could access mSQL via Visual Basic or other ODBC client software within the Windows environment. For more details mail the author and check `ftp://ftp.Bond.edu.au/pub/Minerva/msql/contrib/ODBC/` for the latest archives:

- gorta.faq (28kB) - FAQ for the ODBC driver
- gortarts.zip (17kB) - ODBC driver for use with ODBC manager
- gortasrc.zip (33kB) - ODBC driver source for use with ODBC-SDK
- installd.zip (225kB) - ODBC driver set, if you don't have an ODBC manager
- wmsqlrts.zip (12kB) - mSQL client for MS-Windows
- wmsqlsrc.zip (15kB) - mSQL client for MS-Windows, Source

Note:
In order to use the Windows ODBC driver you must have a Windows mSQL client.

## Perl5 (Unix)

Andreas König (k@franz.ww.TU-Berlin.de) has developed MsqlPerl, a Perl5 adapter for mSQL. It was written against the mSQL 0.2 Patch 1 API but should still work with the 1.0 release as the API hasn't changed (although a couple of semantice have). MsqlPerl is available via ftp from `ftp://ftp.Bond.edu.au/pub/Minerva/msql/Contrib/MsqlPerl-a1.tgz`.

## Python (Unix)

;p.Anthony Baxter (anthony.baxter@aaii.oz.au) has developed PymSQL, a Python module for mSQL. It was written using mSQL 0.2 Patch 2 API but should still work well. PymSQL can be found on `ftp://ftp.Bond.edu.au/pub/Minerva/msql/contrib/PymSQL.tar.gz`.

## Rexx (OS/2)

Mark Hessling (M.Hessling@qut.edu.au) is the programmer of REXX/SQL, a REXX interface for SQL databases including mSQL and Oracle. It is distributed under the GNU General Public License with source included. It can be found on `ftp://ftp.qut.edu.au/src/REXXSQL/rxsql*.zip` (ZIPped version) or `ftp://ftp.qut.edu.au/src/REXXSQL/rxsql*.tar.Z` (TARed and compressed version). The current version is 1.3 for the mSQL and 1.2 for the Oracle implementation (V 1.3 dated March 1996). The mSQL version can be found on `ftp://ftp.Bond.edu.au/pub/Minerva/msql/Contrib/`, too.

Note:
Version 1.3 has been adapted for use with mSQL for OS/2.

## Tcl (Unix)

Brad Pepers (pepersb@cuug.ab.ca) has developed tcl_msql, a Tcl interface to mSQL. It can't be recalled which version of the mSQL API Brad was using when he wrote tcl_msql (Brad's been hacking on mSQL since the early days). But it's nearly certain that it'll work against release 1.0 of mSQL.

# Frequently asked questions (FAQ)

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

- common FAQ - common problems
- OS/2 - problems especially with the OS/2 version of mSQL.

## common FAQ

Common problems and their solution, that is the intention of this section.

`Q: When I try to store a large number into a 'real' field, then the number is stored wrong.`

A: This is a problem of the parser - not a bug, but a problem in determination between integer and floating point numbers. If you want to store a number larger than 32767 into a 'real' field, writethe number with an terminating `.` or `.0`. That signalizes a floating point number to the parser who then handles the number in the correct manner.

## FAQ - OS/2

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

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

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

`Q: I can't create databases, msqladm returns 'access denied'`

A: First, check if you have a entry localhost within your host configuration file (normally \tcpip\etc\hosts). Further, check if both server and client are having equal values for the environment variable USER. To perform actions as mSQL root the entries for USER (on server and client) must contain the same string.

`Q: msqld.exe dies immediately with a 'hit by sig 2'`

A: Sorry, folks, just a bug in the older port of mSQL for OS/2. The abort is caused by an incorrect access to a non-existing environment variable. Check, if your CONFIG.SYS contains setting for the environment variable USER. If not, insert the missing line.

`Q: msqld.exe does not start after a break-down, a message 'server couldn't start' occurs`

A: Wait for a moment (maximum 2 minutes) and try it again. OS/2 has a timeout on broken TCP/IP connections, when the server goes away.

Note:
This should be fixed with version 1.0.11 and above.

`Q: Oups, I got warnings (LIB0004) during compilation`

A: Ignore that stuff. The OS/2 library tool for static libraries (lib.exe) displays warnings if a module replacement is specified but the library is fresh. There should be no warnings at a subsequent compilation. The warnings do only occur if \lib\mSQLbase.lib has been removed.

`Q: I'm using OS/2 Warp with the IAK and I can't get the server run`

A:Do the following steps to set-up an local host entry:

1. Set the two lines "SET USE_HOSTS_FIRST=1" and "SET HOSTNAME=localhost" into your CONFIG.SYS file.
2. Create or edit startup.cmd in your root directory of OS/2, containing the line "ifconfig lo 127.0.0.1".
3. Create or edit \tcpip\etc\hosts with the line "127.0.0.1 localhost" (end the line with a line-feed).
4. Reboot your computer and check it again.

`Q: My programs compiled with a Watcom C compiler don't work with the DLL`

A:Make sure you have a new DLL library from mSQL for OS/2 1.0.14 patch level B or higher/newer. You can't use the old ones since they used an other way of parameter processing (IBM C/C++ Set/2 proprietary format).

# Author's Details

Mini SQL was written by:

      David J. Hughes
      Managing Director
      Hughes Technologies Pty Ltd.
      Australia

      E-mail: bambi@Hughes.edu.au
              http://Hughes.com.au/
      Fax:    +61 7 3302 2199

Mini SQL has been ported to OS/2 by:

      Dirk Ohme
      Programmer at transtec AG
      Tübingen, Germany

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

# Archive Location

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

      http://Hughes.com.au/

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

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

# Mailing List

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

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

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