The mSQL distribution includes the mSQL server, client programs, a C programming interface for client software, and several tools. User contributed software is available including interfaces to mSQL from Perl, Tcl, REXX, Java and Python, www interfaces, a Windows port of the client library and much more.
mSQL embodies a simple client-server architecture. The server maintains the databases, receives commands from client programs and sends replies. The standard client programs that you will need to use are:
% relshow
Error connecting to database : Can't connect to local MSQL server
If there is a server running on your machine, then executing the
relshow command will cause a list of the current databases to
be displayed, as in:
% relshow
+-----------------+
| Databases |
+-----------------+
| finin |
| bevans1 |
| ... |
+-----------------+
You can access a server running on another machines by giving any of
the client program a command line argument specifying which Internet
host the server is on. For example, to access the mSQL server on
cujo.cs.umbc.edu, you could do:
% relshow -h cujo.cs.umbc.edu
% relshow secret
Access to database denied
% msqladmin create company
% msqladmin drop company
If you (as the DBA) are creating a new database, you may want to add
an entry in the file msql.acl which defines access
type restrictions to databases. This file should be set up with a
"default" access specification for databases (e.g., anyone can read,
only root can write, access is permitted from any internet host,
etc.).
The DBA can also use the msqladmin command to shutdown the server or cause it to reload the access control file:
% msqladmin shutdown
% msqladmin reload
% relshow company
Database = company
+---------------------+
| Table |
+---------------------+
| project |
| dept_locations |
| employee |
| department |
| dependent |
| works_on |
+---------------------+
% relshow company project
Database = company
Table = project
+-----------------+----------+--------+----------+-----+
| Field | Type | Length | Not Null | Key |
+-----------------+----------+--------+----------+-----+
| pname | char | 15 | N | N |
| pnumber | char | 5 | Y | N |
| plocation | char | 10 | N | N |
| dnum | char | 1 | N | N |
+-----------------+----------+--------+----------+-----+
% msql company
Welcome to the miniSQL monitor. Type \h for help.
mSQL > \h
MiniSQL Help!
The following commands are available :-
\q Quit
\g Go (Send query to database)
\e Edit (Edit previous query)
\p Print (Print the query buffer)
mSQL > select lname, fname from employee
-> \g
Query OK.
8 rows matched.
+------------+------------+
| lname | fname |
+------------+------------+
| Smith | John |
| Wong | Frank |
| Zelaya | Alicia |
| Wallace | Jennifer |
| Narayan | Ramesh |
| English | Joyce |
| Jabbar | Ahmad |
| Borg | James |
+------------+------------+
mSQL >
Notice that the msql client takes input and buffers it
until you issue a command like \g or \q. Each
time you type a newline you will give you the prompt " ->
to let you know that more input is expected. You can enter any number
of commands before having them sent to the server by the \g.
However, it appears that the server stops executing the commands in a
series as soon as it encounters an error. Thus, it is a good idea to
put a \g after each command.
mSQL > CREATE TABLE project
-> (pname char(15),
-> pnumber char(5) primary key,
-> plocation char(10),
-> dnum char(1)) \g
Query OK.
mSQL > CREATE TABLE dept_locations
-> (dnumber char(1) not null,
-> dlocation char(10) not null ) \g
Query OK.
Since it is easy to make mistakes when you are typing things into the
computer, it's good idea to enter all of the mSQL commands to define
your database into a file, such as
company-schema.msql and then use file redirection to call
msql with this as input:
% msql company < company-schema.msql
In that way, if there are mistakes, you can edit the file and redo it.
Notice that in this file, we've included a DROP TABLE
command before each CREATE TABLE command to that the file
will work to initially define or to redefine the database.
DROP TABLE project \g
CREATE TABLE project
(pname char(15),
pnumber char(5) primary key,
plocation char(10),
dnum char(1)) \g
The first time this is executed (i.e., when there is no project table
defined) you will see an warning message
ERROR : Unknown table "foobar"
but it is not treated as a fatal error. However, recall that if a
mSQL command in a sequence of commands fails, then the rest of the
commands in that sequence are not executed. So be sure to include the
\g after the DROP TABLE command. In General, it is a good idea to
put a \g after every mSQL command.
mSQL > drop table test
mSQL > create table test (one int, two int)
mSQL > insert into test (one,two) values (11,21)
mSQL > insert into test values (12,22)
mSQL > insert into test (two,one) values (23,13)
mSQL > insert into test (one) values (14)
mSQL > select * from test\g
Query OK.
5 rows matched.
+----------+----------+
| one | two |
+----------+----------+
| 1 | 2 |
| 11 | 21 |
| 12 | 22 |
| 13 | 23 |
| 14 | NULL |
+----------+----------+
Again, you will probably find it convenient to enter a lot of data in
a batch mode by typing the mSQL input statements into a file such as
company-data.msql and using it as
input to the msql client:
% msql company < company-data.msql
% msql company < company-query.msql
% msqldump company > company-dump.msql
will write to the file company-dump.msql
a series of mSQL commands that will re-create the database if
read back into the msql client.
% makecompany
int msqlConnect (char * host); int msqlSelectDB (int sock, char *dbName); int msqlQuery (int sock, char *query); void msqlClose (int sock); void msqlDataSeek (m_result *result, int pos); void msqlFieldSeek (m_result* result, int pos); void msqlFreeResult (m_result * result); m_row msqlFetchRow (m_result * result); m_field *msqlFetchField (m_result * result); m_result *msqlListDBs (int sock); m_result *msqlListTables (int sock); m_result *msqlListFields (int, char*); m_result *msqlStoreResult();as well as an example programs in C that does something with the company database. I would also like to show the same example in a few other languages, such as Perl and Tcl. My ambitiousness may result in this section never being done, however.
http://www.your.site/cgi-bin/websql
WEBSQL Table Browser. Assuming your web server and msql server
are on the same machine, you can see what is in the Company database using
this url in your page:
http://www.your.site/cgi-bin/websql?db_name=company
WEBSQL Record Browser. A particular table, such as the
employee table, can be accessed, browsed and updated with this
url:
http://www.your.site/cgi-bin/websql?db_name=company&db_table_name=employee
Since the websql cgi script runs as uid 'www' (or whatever your web
server's effective uid is), your database will have to be accessible
by a client running with that uid.
create table t1 (a int, b int) create table t2 (b int, c int) insert into t1 values (1,2) insert into t2 values (2,3)Then these queries generate errors:
mSQL > select * from t1, t2 where t1.b=t2.b \g ERROR : Reference to un-selected table "" mSQL > select * from t1,t2 \g ERROR : Reference to un-selected table ""But explicitly specifying the fields to return works:
mSQL > select t1.a,t1.b,t2.b,t2.c from t1, t2 \g +----------+----------+----------+----------+ | a | b | b | c | +----------+----------+----------+----------+ | 1 | 2 | 2 | 3 | +----------+----------+----------+----------+ mSQL > select t1.a,t1.b,t2.c from t1, t2 where t1.b=t2.b \g +----------+----------+----------+ | a | b | c | +----------+----------+----------+ | 1 | 2 | 3 | +----------+----------+----------+