MAPI Client

MAPI Client mk Sun, 03/28/2010 - 23:14

The mclient program is the universal command-line tool that implements the MAPI protocol for client-server interaction with MonetDB.

On a Windows platform it can be started using start->MonetDB->MonetDB SQL Client. Alternatively, you can use the command window to start mclient.exe. Be aware that your environment variables are properly set to find the libraries of interest.

On a Linux platform it provides readline functionality, which greatly improves user interaction. A history can be maintained to ease interaction over multiple sessions.

The default setting is geared at establishing a guest connection to an SQL database at a default server running on the localhost. The -h hostname specifies on which machine the MonetDB server is running. If you communicate with a MonetDB server on the same machine, it can be omitted.

     Usage: mclient [ options ]

     Options are:
      -h hostname | --host=hostname    host or UNIX domain socket to connect to
      -p portnr   | --port=portnr      port to connect to
      -u user     | --user=user        user id
      -d database | --database=database  database to connect to
      -e          | --echo             echo the query
      -E charset  | --encoding=charset specify encoding (character set) of the terminal
      -f kind     | --format=kind      specify output format {csv,tab,raw,sql,xml}
      -H          | --history          load/save cmdline history (default off)
      -i          | --interactive      read stdin after command line args
      -l language | --language=lang    {sql,mal}
      -L logfile  | --log=logfile      save client/server interaction
      -s stmt     | --statement=stmt   run single statement
      -X          | --Xdebug           trace mapi network interaction
      -| cmd      | --pager=cmd        for pagination
      -?          | --help             show this usage message

     SQL specific opions 
      -n nullstr  | --null=nullstr     change NULL representation for sql, csv and tab output modes
      -r nr       | --rows=nr          for pagination
      -w nr       | --width=nr         for pagination
      -D          | --dump             create an SQL dump
      -N          | --inserts          use INSERT INTO statements when dumping

The default mapi_port TCP port used is 50000. If this port happens to be in use on the server machine (which generally is only the case if you run two MonetDB servers on it), you will have to use the -p port do define the port to which the mserver is listening. Otherwise, it may also be omitted. If there is more than one mserver running, you must also specify the database name -d database. In this case, if your port is set to the wrong database, the connection will always be redirected to the correct one. Note that the default port (and other default options) can be set in the server configuration file.

Within the context of each query language there are more options. They can be shown using the command \? or using the commandline.

For SQL there are several knobs to tune for a better rendering of result tables (\w).

     shell> mclient -d database
     Welcome to mclient, the MonetDB/SQL interactive terminal (Apr2011-SP2)
     Database: MonetDB v11.5.2, 'database'
     Type \q to quit, \? for a list of available commands
     auto commit mode: on
     sql>\?
     \?      - show this message
     \<file  - read input from file
     \>file  - save response in file, or stdout if no file is given
     \|cmd   - pipe result to process, or stop when no command is given
     \h      - show the readline history
     \t      - toggle timer
     \D table- dumps the table, or the complete database if none given.
     \d[Stvsfn]+ [obj] - list database objects, or describe if obj given
     \A      - enable auto commit
     \a      - disable auto commit
     \e      - echo the query in sql formatting mode
     \f      - format using a built-in renderer {csv,tab,raw,sql,xml}
     \w#     - set maximal page width (-1=unlimited, 0=terminal width, >0=limit to num)
     \r#     - set maximum rows per page (-1=raw)
     \L file - save client/server interaction
     \X      - trace mclient code
     \q      - terminate session