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 ESL (Unix) ODBC (Win 3.x, Win95, WinNT) ODBC (OS/2) 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 - 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
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.
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 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.
The available types (col_type) are:
Introduction and History
Mini SQL Specification
The CREATE TABLE Clause
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
)
char(len) | String of characters (or other 8 bit data) |
int | signed integer values |
real | Decimal or Scientific Notation real values |
DROP is used to remove a table definition from the database:
DROP TABLE table_name
for example:
DROP TABLE emp_details
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 syntax for mSQL's DELETE clause is
for example:
The SELECT offered by mSQL lacks some of the features provided by the SQL
spec:
It does however support:
So, the formal syntax for mSQL's SELECT is:
A simple SELECT may be
To sort the returned data in ascending order by last_name
and descending order by first_name the query would look like this
And to remove any duplicate rows, the DESTINCT operator could
be used:
To limit the number of rows returned to a maximum of 3 rows,
use the LIMIT operator:
The regular expression syntax supported by LIKE clauses is
that of standard SQL:
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:
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:
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
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 mSQL UPDATE clause cannot use a column name as a value. Only literal
values may be used as an update value
Note: for example:
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.
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:
The DELETE FROM Clause
DELETE FROM table_name
WHERE column OPERATOR value
[ AND | OR column OPERATOR value ]**
OPERATOR can be <, >, =, <=, >=, <>, or 'like'
DELETE FROM emp_details WHERE emp_id = 12345
The SELECT Clause
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
SELECT first_name, last_name FROM exmp_details
WHERE dept = 'finance'
SELECT first_name, last_name FROM emp_details
WHERE dept = 'finance'
ORDER BY last_name, first_name DESC
SELECT DISTINCT first_name, last_name FROM emp_details
WHERE dept = 'finance'
ORDER BY last_name, first_name DESC
SELECT DISTINCT first_name, last_name FROM emp_details
WHERE dept = 'finance'
ORDER BY last_name, first_name DESC
LIMIT 3
SELECT first_name, last_name FROM emp_details
WHERE dept = 'finance'
AND last_name like '_ughes'
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
SELECT t1.parent, t2.child FROM parent_data=t1, parent_data=t2
WHERE t1.child = t2.parent
The UPDATE Clause
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'.
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.
UPDATE emp_details SET salary=30000 WHERE emp_id=1234
UPDATE log_table SET date=sysdate WHERE date like '1996/%'
The Database Engine (Server)
Server Debugging
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: 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:
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
\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:
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.
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).
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.
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: 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.
The MSQL_HOST environment variable is used at the monitor program
msql.exe to specify an alternative
default mSQL server machine.
Note: 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).
Not used under OS/2, since OS/2 does not support UNIX domain
sockets.
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.
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.
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:
The following global variables are defined in
msql.h (only available with static linking):
The following definitions are defined in msql.h:
The following data types are defined in msql.h:
Like the mSQL engine, the API supports debugging via the
MINERVA_DEBUG environment
variable. Three debugging modules are currently supported by the API:
Runtime Configuration
Environment Variable EDITOR
Environment Variable MINERVA_DEBUG
API debugging includes debugging of the client software, because
the clients make use of the API.
Environment Variable MSQL_HOME
Environment Variable MSQL_HOST
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
Environment Variable MSQL_UNIX_PORT
Environment Variable USER
Environment Variable VISUAL
C Programming API
API Debugging
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: 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: 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.
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 is prefered for both static and dynamic linkage.
msqlConnect
int msqlConnect( char *host );
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.
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.
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.
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.
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.
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.
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.
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: 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:
Possible values for the type field (see also
CREATE TABLE) are defined in
msql.h as
The individual attribute flag entry can be accessed using
the following macros:
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.
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.
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().
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().
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().
The connection to the mSQL engine can be closed using
msqlClose(). The function must be called with the connection
socket returned by
msqlConnect() when the initial
connection was made.
msqlSelectDB
int msqlSelectDB( int sock,
char *dbName
);
msqlQuery
int msqlQuery( int sock,
char *query
);
msqlStoreResult
m_result *msqlStoreResult();
msqlFreeResult
void msqlFreeResult( m_result *result );
msqlFetchRow
m_row msqlFetchRow( m_result *result );
msqlDataSeek
void msqlDataSeek( m_result *result,
int pos
);
msqlNumRows
int msqlNumRows( m_result *result );
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 );
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;
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
);
msqlNumFields
int msqlNumFields( m_result *result );
msqlListDBs
m_result *msqlListDBs( int sock );
msqlListTables
m_result *msqlListTables( int sock );
msqlListFields
m_result *msqlListFields( int sock,
char *tableName
);
msqlClose
int msqlClose( int sock );
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
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.
contains a zero-terminated error message, if an error occurs.
Note: 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):
msqlErrMsg
extern char msqlErrMsg[];
This variable is only accessible with static linkage. On dynamic linkage the
function msqlGetErrMsg() is
prefered to use.
Clients delivered within this package
msql.exe | msql | Terminal Monitor |
msqladm.exe | msqladmin | Database Administration |
relshow.exe | relshow | Schema Viewer |
msqldump.exe | msqldump | Database Dumper |
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:
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: 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:
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 ]
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: 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: 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: Note: Note: 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.
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:
Note: The iODBC for OS/2 package provides an ODBC driver manager and
ODBC drivers for both mSQL 1.x and 2.0. See
http://www.fh-albsig.de/~ohme/ for the latest version. Since
version 1.0.3 of iODBC for OS/2 a JDBC-to-ODBC bridge is included for the Java
(TM) language.
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.
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: 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.
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 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.
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: 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:
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).
Mini SQL was written by:
Mini SQL has been ported to OS/2 by:
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:
The latest version of the OS/2 port can be found at :
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!
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 ]
'relshow.exe' is linked dynamically against mSQL.DLL
msqldump.exe - Database Dumper
msqldump[.exe] [ -h host ] [ -v ] database [ table ]
msqldump.exe started life as a user contributed program
called msqlsave written by Igor Romanenko
(igor@frog.kiev.us). Thanks Igor.
'msqldump.exe' is linked dynamically against mSQL.DLL
mSQL Access from (Script) Languages
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.
ESL (Unix)
ODBC (Win 3.x, Win95, WinNT)
In order to use the Windows ODBC driver you must have a Windows mSQL client.
ODBC (OS/2)
Perl5 (Unix)
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)
Version 1.3 has been adapted for use with mSQL for OS/2.
Tcl (Unix)
Frequently asked questions (FAQ)
common FAQ
FAQ - OS/2
This should be fixed with version 1.0.11 and above.
Author's Details
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
Dirk Ohme
Programmer at transtec AG
Tübingen, Germany
E-mail: Dirk.Ohme@transtec.de
Fidonet: 2:246/2001.9@fidonet
Archive Location
http://Hughes.com.au/
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