Installing and Using the mSQL Database Server

Installation

mSQL is a commercial Unix-dbase package created by Hughes, Australia. It's free (including source) for all-but commercial use; there are license agreements (reasonably priced) for commercial use, i.e., including it in a product.

mSQL supports a subset of SQL, comes with a tiny admin tool, an SQL interpreter, a script language, and a web-like scripting language for server-side includes in webpages. They claim strong performance with small data sets and reasonable performance with large data sets.

mSQL and mSQL documentation can be found at: http://www.hughes.com.au

You can either download mSQL from Hughes (above) or this gzipped tar file.

We will now install mSQL:

Copy over the file msql-2.0.9.tar.gz and un-tar

      % cp ~simha/java/ecom/msql-2.0.9.tar.gz ~/java/.
      % gunzip msql-2.0.9.tar.gz
      % tar -xvf msql-2.0.9.tar

This will create a directory called msql-2.0.9/

Next, compile mSQL:

Go to the main directory:

      % cd ~simha/java/ecom/msql-2.0.9/

Now, build the target directory by typing "make target"

      % make target

This script discovers your machine and environment and creates a configuration for compilation. The output to the screen is something like:

         Making target directory for Solaris-2.6-Sparc

         Building directory tree.
            Adding common
            Adding conf
            Adding lang-common
            Adding lite
            Adding makedepend
            Adding makegen
            Adding msql
            Adding regexp
            Adding tests
            Adding tests/rtest.src
            Adding w3-msql
            Adding w3-msql/tests

         Adding sym-links
         ............................................................
         ............................................................

         Build of target directory for Solaris-2.6-Sparc complete

Let's assume that your machine is running the Solaris OS.
cd to the /targets directory and then to the particular OS directory that was created:
```
       % cd targets/Solaris-2.6-Sparc/
  
```

Execute "setup":

       % setup

The output is something like:

        Starting configuration of mSQL 2.
        creating cache ./config.cache
        checking for gcc... gcc
        checking whether the C compiler (gcc  ) works... yes
        checking whether the C compiler (gcc  ) is a cross-compiler... no
   
        ... [stuff left out]

        Ready to build mSQL.
        You may wish to check "site.mm" although the defaults should be
        fine.  When you're ready, type  "make all" to build the software

Next, decide where you want the installation. Usually, this is a bin directory off of the main directory:
```
n
        msql-2.0.9/bin
  
```

Create the bin directory yourself:

       % cd ~/java/msql-2.0.9/
       % mkdir bin

Now edit the site.mm file to set the INST_DIR to this bin directory, e.g.,
```
        INST_DIR= /home/joe/java/msql-2.0.9/bin
  
```

Type make all to compile:

      % make all

This will take time and produce a lot of output, something like:

       Regenerating Makefile.
       ......................................................
       Done.

       Starting make for mSQL-2

       --> [common] directory  
 
       Regenerating Makefile.
       .....................................

       ...[stuff left out]

       Make of mSQL-2 complete.
       You should now install mSQL-2 using make install

Next, install the executables by typing make install

    % make install

This should produce output like:

       Starting install for mSQL-2

       ...[stuff left out]

       Installation of mSQL-2 complete.

Now the executable is ready and mSQL needs to be configured to run:

Go to the installation directory and edit the file msql.conf:
- Here, you set the username under which it runs, the admin username (joe) and port number for clients (and JDBC). The default is 1114, but that should be changed to your THIRD assigned port number (say, 8114) because to use 1114 you need to be root.
- Next, change the remote access part to True under the system parameters (Otherwise, JDBC can't connect).
Check the installation directory ~/java/msql-2.0.9/bin (that you specified) to look for the various executables such as msql2d, msql etc. If you see them, the installation is complete.
NOTE:
- msql2d is actually the dbase server (it'll run as daemon).
- msql is an interpreter that reads SQL-like commands.
- msqladmin is an admin program for mSQL.

Running mSQL:

Go to the ~/java/msql-2.0.9/bin directory containing the msql2d executable and fire up the daemon:
```
       % msql2d &
  
```
Create a "test" database using msqladmin: % msqladmin create testdb A dbase needs to be created before you can use it.

Test it:

       % msql testdb
       msql> create table testtable (age integer) \g

This will create a table that you can work with, e.g.,

       msql> select * from testtable \g

NOTE: every mSQL command must end with a "\g" (backslash followed by g).
The daemon will run continuously as long as the machine is up (you don't have to be logged in).

Shutting it down:
To shutdown the server, type:

     
  % msqladmin shutdown

Learning more mSQL administration:
- Simply typing msqladmin will list commands.

Using the msql interpreter:

You can type in SQL directly. To end an SQL statement use "\g". To quit type "\q."
There is a way to import data using the msqlimport tool.

A smarter way to create data:

Create a plain text file containing your various insert commands.
For example, download the file bank_schema.sql and examine its contents:
To create the bank database, first use msqladmin to create a new database (a new collection of tables, separate from other collections of tables):
```
       % msqladmin create bank
  
```
Now, simply feed the file to msql:
```
       % msql bank < bank_schema.sql
  
```
NOTE: do create this bank database, because we will use it below to test JDBC.

NOTE:

mSQL is single-user and therefore doesn't use passwords.
mSQL only supports a small subset of SQL (no nested SQL, for example).
mSQL does not support transaction processing, so you need to handle that yourself via threads in Java.

Installing and using a JDBC driver

There are two JDBC drivers for mSQL. One of these is mSQL-JDBC. It comes unrestricted with full source code. See http://www.imaginary.com for details of the source.

You can download the latest version from www.imaginary.com or download this local version.

Installing the JDBC driver:

Download the file mSQL-JDBC-1.tar.gz and save it in your /home/joe/java directory.

Extract:

      % gunzip mSQL-JDBC-1.tar.gz
      % tar -xvf mSQL-JDBC-1.tar

This will create a directory called mSQL-JDBC-1.0/

Now add the mSQL-JDBC-1.jar library to your CLASSPATH variable:

   setenv CLASSPATH /usr/local/jdk1.2/lib:$HOME/java/JSDK2.0/lib/jsdk.jar:$HOME/java/Jigsaw/Jigsaw/classes/jigsaw.zip:$HOME/java/mSQL-JDBC-1.0/msql-jdbc-1-0.jar:.

Log out and in, or source the .cshrc to update the CLASSPATH variable.

Run a test:

Create the following file TestJDBC.java:

       import java.sql.*;
       import java.net.*;
       import java.io.*;

       public class TestJDBC {

         public static void main(String args[]) {
           // The SQL query.
           String sql = "select * from emp";
           System.out.println("Executing: " + sql);

           // Create a driver instance.
           try {
	     Class.forName("com.imaginary.sql.msql.MsqlDriver").newInstance();

             // Make a connection.
  	     String url = "jdbc:msql://localhost:8114/test";
	     Connection con = DriverManager.getConnection(url, "simha", "");
   	     if (con == null) {
	       System.out.println ("Connection unsuccessful");
	       System.exit(0);
	     }
	     else {
	       System.out.println ("Connection successful. Continuing ...");
	     }

	     // Make a statement.
   	     Statement s = con.createStatement();   

	     if (s == null) {
	       System.out.println ("Statement creation unsuccessful");
	       System.exit(0);
	     }
	     else {
	       System.out.println ("Statement creation successful ... continuing");
	     }

	     // Execute the SQL statement.
	     if ( s.execute (sql) ) {
	       ResultSet r = s.getResultSet();
	       ResultSetMetaData meta = r.getMetaData();
	       int cols = meta.getColumnCount();
	       int rownum = 0;
	      
               // Iterate over the rows - get one row at a time.
	        while( r.next() ) {
		  rownum++;
		  System.out.println("Row: " + rownum);
		  for(int i=0; i<cols; i++) {
		      System.out.print(meta.getColumnLabel(i+1) + ": "
				       + r.getObject(i+1) +  ", ");
		  }
		  System.out.println("");
	        }
	     }
	     else {
	       System.out.println(s.getUpdateCount() + " rows affected.");
	     }
	     s.close();
	     con.close();
           }
           catch( Exception e ) {
	     e.printStackTrace();
           }
         }
       }

Make sure the mSQL daemon is running.
This file accesses the customer relation
Compile and run - it should work.

Running a Servlet that uses JDBC

Create the following test servlet:

       import javax.servlet.*;
       import javax.servlet.http.*;
       import java.sql.*;
       import java.net.*;
       import java.io.*;

       public class TestJDBCServlet extends HttpServlet {

         public void doGet (HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException
         {
          // Set the content type of the response.
          resp.setContentType ("text/html");

          // Create a PrintWriter to write the response.
          java.io.PrintWriter out = new PrintWriter (resp.getOutputStream());

          // The first part of the response.
          out.println ("");
          out.println (" Test ");
          out.println ("");

          // The SQL query.
          String sql = "select * from emp";
          System.out.println("Executing: " + sql);

          // Create a driver instance.
          try {
            Class.forName("com.imaginary.sql.msql.MsqlDriver").newInstance();

            // Make a connection.
  	    String url = "jdbc:msql://localhost:8114/test";
	    Connection con = DriverManager.getConnection(url, "simha", "");
   	    if (con == null) {
	      System.out.println ("Connection unsuccessful");
	      System.exit(0);
	    }
	    else {
	      System.out.println ("Connection successful. Continuing ...");
	    }

	    // Make a statement.
 	    Statement s = con.createStatement();   
	    if (s == null) {
	      System.out.println ("Statement creation unsuccessful");
	      System.exit(0);
	    }
	    else {
	      System.out.println ("Statement creation successful ... continuing");
	    }

	    // Execute the SQL statement.
	    if ( s.execute (sql) ) {
	      ResultSet r = s.getResultSet();
	      ResultSetMetaData meta = r.getMetaData();
	      int cols = meta.getColumnCount();
	      int rownum = 0;
	      
              // Iterate over the rows - get one row at a time.
	      while( r.next() ) {
		rownum++;
		System.out.println("Row: " + rownum);
		for(int i=0; i<cols; i++) {
		   out.print (meta.getColumnLabel(i+1) + ": "
			      + r.getObject(i+1) +  ", ");
		}
		out.println("");
	      }
	     }
	    else {
	      System.out.println(s.getUpdateCount() + " rows affected.");
	    }
	    s.close();
	    con.close();
          }
          catch( Exception e ) {
	    e.printStackTrace();
          }
          // Last part.
          out.println ("");
          out.println ("");
          out.close();

          // Screen I/O
          System.out.println ("Inside servlet ... servlet complete");
        }

        public void doPost (HttpServletRequest req, HttpServletResponse resp)
          throws ServletException, IOException
        {
          doGet (req, resp); 
        }

      }

Compile and copy the class file to the servlets directory.

Access the servlet using your assigned port number.