Mapi Library

The Mapi Library

The easiest way to extend the functionality of MonetDB is to construct an independent application, which communicates with a running server using a database driver with a simple API and a textual protocol. The effectiveness of such an approach has been demonstrated by the wide use of database API implementations, such as Perl DBI, PHP, ODBC,...

Sample MAPI Application

The database driver implementation given in this document focuses on developing applications in C. The command collection has been chosen to align with common practice, i.e. queries follow a prepare, execute, and fetch_row paradigm. The output is considered a regular table. An example of a mini application below illustrates the main operations.

#include <mapi.h>
#include <stdio.h>
#include <stdlib.h>

void die(Mapi dbh, MapiHdl hdl) {
  if (hdl != NULL) {
    mapi_explain_query(hdl, stderr);
    do {
      if (mapi_result_error(hdl) != NULL)
        mapi_explain_result(hdl, stderr);
    } while (mapi_next_result(hdl) == 1);
    mapi_close_handle(hdl);
  }
  if (dbh != NULL) {
    mapi_explain(dbh, stderr);
    mapi_destroy(dbh);
  } else {
    fprintf(stderr, "command failed\n");
  }
  exit(-1);
}

MapiHdl query(Mapi dbh, char *q) {
  MapiHdl ret = NULL;

  if ((ret = mapi_query(dbh, q)) == NULL || mapi_error(dbh) != MOK)
    die(dbh, ret);

  return(ret);
}

void update(Mapi dbh, char *q) {
  MapiHdl ret = query(dbh, q);

  if (mapi_close_handle(ret) != MOK)
    die(dbh, ret);
}

int main(int argc, char *argv[]) {
  Mapi dbh;
  MapiHdl hdl = NULL;
  char *name;
  char *age;
  dbh = mapi_connect("localhost", 50000, "monetdb", "monetdb", "sql", "demo");

  if (mapi_error(dbh))
    die(dbh, hdl);

  update(dbh, "CREATE TABLE emp (name VARCHAR(20), age INT)");
  update(dbh, "INSERT INTO emp VALUES ('John', 23)");
  update(dbh, "INSERT INTO emp VALUES ('Mary', 22)");

  hdl = query(dbh, "SELECT * FROM emp");

  while (mapi_fetch_row(hdl)) {
    name = mapi_fetch_field(hdl, 0);
    age = mapi_fetch_field(hdl, 1);
    printf("%s is %s\n", name, age);
  }

  mapi_close_handle(hdl);
  mapi_destroy(dbh);
  return(0);
}

The mapi_connect() operation establishes a communication channel with a running server. The query language interface is either "sql" or "mal".

Errors on the interaction can be captured using mapi_error(), possibly followed by a request to dump a short error message explanation on a standard file location. It has been abstracted away in a function.

Provided we can establish a connection, the interaction proceeds as in many similar application development packages. Queries are shipped for execution using mapi_query() and an answer table can be consumed one row at a time. In many cases these functions suffice.

The Mapi interface provides caching of rows at the client side. mapi_query() will load tuples into the cache, after which they can be read repeatedly using mapi_fetch_row() or directly accessed (mapi_seek_row()). This facility is particularly handy when small, but stable query results are repeatedly used in the client program.

To ease communication between application code and the cache entries, the user can bind the C-variables both for input and output to the query parameters, and output columns, respectively. The query parameters are indicated by '?' and may appear anywhere in the query template.

The Mapi library expects complete lines from the server as answers to query actions. Incomplete lines leads to Mapi waiting forever on the server. Thus formatted printing is discouraged in favor of tabular printing as offered by the table.print() commands.

The following action is needed to get a working program. Compilation of the application relies on libtool and the pkg-config programs that should be shipped with your installation. The application above can be compiled and linked as follows:

The example assumes you have set the variable INSTALL_DIR to the prefix location given during configure of MonetDB. If you use a system installation, you can omit the 'env PKGCONFIG_PATH=.....' part, or set INSTALL_DIR to '/usr'.

The compilation on Windows is slightly more complicated. It requires more attention towards the location of the include files and libraries.

Command Summary

The quick reference guide to the Mapi library is given below. More details on their constraints and defaults are given in the next section.

Library Synopsis

The routines to build a MonetDB application are grouped in the library MonetDB Programming Interface, or shorthand Mapi.

The protocol information is stored in a Mapi interface descriptor (mid). This descriptor can be used to ship queries, which return a MapiHdl to represent the query answer. The application can set up several channels with the same or a different mserver. It is the programmer's responsibility not to mix the descriptors in retrieving the results.

The application may be multi-threaded as long as the user respects the individual connections represented by the database handlers.

The interface assumes a cautious user, who understands and has experience with the query or programming language model. It should also be clear that references returned by the API point directly into the administrative structures of Mapi. This means that they are valid only for a short period, mostly between successive mapi_fetch_row() commands. It also means that it the values are to retained, they have to be copied. A defensive programming style is advised.

Upon an error, the routines mapi_explain() and mapi_explain_query() give information about the context of the failed call, including the expression shipped and any response received. The side-effect is clearing the error status.

Error Message

Almost every call can fail since the connection with the database server can fail at any time. Functions that return a handle (either Mapi or MapiHdl) may return NULL on failure, or they may return the handle with the error flag set. If the function returns a non-NULL handle, always check for errors with mapi_error.

Functions that return MapiMsg indicate success and failure with the following codes.

When these functions return MERROR or MTIMEOUT, an explanation of the error can be had by calling one of the functions mapi_error_str(), mapi_explain(), or mapi_explain_query().

To check for error messages from the server, call mapi_result_error(). This function returns NULL if there was no error, or the error message if there was. A user-friendly message can be printed using map_explain_result(). Typical usage is:

Mapi Function Reference

Connecting and Disconnecting

• Mapi mapi_connect(const char *host, int port, const char *username, const char *password, const char *lang, const char *dbname)

  Setup a connection with a Mserver at a host:port and login with username and password. If host == NULL, the local host is accessed. If host starts with a '/' and the system supports it, host is actually the name of a UNIX domain socket, and port is ignored. If port == 0, a default port is used. If username == NULL, the username of the owner of the client application containing the Mapi code is used. If password == NULL, the password is omitted. The preferred query language is any of {sql,mil,mal,xquery }. On success, the function returns a pointer to a structure with administration about the connection.

• MapiMsg mapi_disconnect(Mapi mid)

  Terminate the session described by mid. The only possible uses of the handle after this call is mapi_destroy() and mapi_reconnect(). Other uses lead to failure.

• MapiMsg mapi_destroy(Mapi mid)

  Terminate the session described by mid if not already done so, and free all resources. The handle cannot be used anymore.

• MapiMsg mapi_reconnect(Mapi mid)

  Close the current channel (if still open) and re-establish a fresh connection. This will remove all global session variables.

• MapiMsg mapi_ping(Mapi mid)

  Test availability of the server. Returns zero upon success.

Sending Queries

• MapiHdl mapi_query(Mapi mid, const char *Command)

  Send the Command to the database server represented by mid. This function returns a query handle with which the results of the query can be retrieved. The handle should be closed with mapi_close_handle(). The command response is buffered for consumption, c.f. mapi_fetch_row().

• MapiMsg mapi_query_handle(MapiHdl hdl, const char *Command)

  Send the Command to the database server represented by hdl, reusing the handle from a previous query. If Command is zero it takes the last query string kept around. The command response is buffered for consumption, e.g. mapi_fetch_row().

• MapiHdl mapi_query_array(Mapi mid, const char *Command, char **argv)

  Send the Command to the database server replacing the placeholders (?) by the string arguments presented.

• MapiHdl mapi_quick_query(Mapi mid, const char *Command, FILE *fd)

  Similar to mapi_query(), except that the response of the server is copied immediately to the file indicated.

• MapiHdl mapi_quick_query_array(Mapi mid, const char *Command, char **argv, FILE *fd)

  Similar to mapi_query_array(), except that the response of the server is not analyzed, but shipped immediately to the file indicated.

• MapiHdl mapi_prepare(Mapi mid, const char *Command)

  Move the query to a newly allocated query handle (which is returned). Possibly interact with the back-end to prepare the query for execution.

• MapiMsg mapi_execute(MapiHdl hdl)

  Ship a previously prepared command to the backend for execution. A single answer is pre-fetched to detect any runtime error. MOK is returned upon success.

• MapiMsg mapi_execute_array(MapiHdl hdl, char **argv)

  Similar to mapi_execute but replacing the placeholders for the string values provided.

• MapiMsg mapi_finish(MapiHdl hdl)

  Terminate a query. This routine is used in the rare cases that consumption of the tuple stream produced should be prematurely terminated. It is automatically called when a new query using the same query handle is shipped to the database and when the query handle is closed with mapi_close_handle().

• MapiMsg mapi_virtual_result(MapiHdl hdl, int columns, const char **columnnames, const char **columntypes, const int *columnlengths, int tuplecount, const char ***tuples)

  Submit a table of results to the library that can then subsequently be accessed as if it came from the server. columns is the number of columns of the result set and must be greater than zero. columnnames is a list of pointers to strings giving the names of the individual columns. Each pointer may be NULL and columnnames may be NULL if there are no names. tuplecount is the length (number of rows) of the result set. If tuplecount is less than zero, the number of rows is determined by a NULL pointer in the list of tuples pointers. tuples is a list of pointers to row values. Each row value is a list of pointers to strings giving the individual results. If one of these pointers is NULL it indicates a NULL/nil value.

Getting Results

• int mapi_get_field_count(MapiHdl mid)

  Return the number of fields in the current row.

• mapi_int64 mapi_get_row_count(MapiHdl mid)

  If possible, return the number of rows in the last select call. A -1 is returned if this information is not available.

• mapi_int64 mapi_get_last_id(MapiHdl mid)

  If possible, return the last inserted id of auto_increment (or alike) column. A -1 is returned if this information is not available. We restrict this to single row inserts and one auto_increment column per table. If the restrictions do not hold, the result is unspecified.

• mapi_int64 mapi_rows_affected(MapiHdl hdl)

  Return the number of rows affected by a database update command such as SQL's INSERT/DELETE/UPDATE statements.

• int mapi_fetch_row(MapiHdl hdl)

  Retrieve a row from the server. The text retrieved is kept around in a buffer linked with the query handle from which selective fields can be extracted. It returns the number of fields recognized. A zero is returned upon encountering end of sequence or error. This can be analyzed in using mapi_error().

• mapi_int64 mapi_fetch_all_rows(MapiHdl hdl)

  All rows are cached at the client side first. Subsequent calls to mapi_fetch_row() will take the row from the cache. The number or rows cached is returned.

• int mapi_quick_response(MapiHdl hdl, FILE *fd)

  Read the answer to a query and pass the results verbatim to a stream. The result is not analyzed or cached.

• MapiMsg mapi_seek_row(MapiHdl hdl, mapi_int64 rownr, int whence)

  Reset the row pointer to the requested row number. If whence is MAPI_SEEK_SET, rownr is the absolute row number (0 being the first row); if whence is MAPI_SEEK_CUR, rownr is relative to the current row; if whence is MAPI_SEEK_END, rownr is relative to the last row.

• MapiMsg mapi_fetch_reset(MapiHdl hdl)

  Reset the row pointer to the first line in the cache. This need not be a tuple. This is mostly used in combination with fetching all tuples at once.

• char **mapi_fetch_field_array(MapiHdl hdl)

  Return an array of string pointers to the individual fields. A zero is returned upon encountering end of sequence or error. This can be analyzed in using mapi_error().

• char *mapi_fetch_field(MapiHdl hdl, int fnr)

  Return a pointer a C-string representation of the value returned. A zero is returned upon encountering an error or when the database value is NULL; this can be analyzed in using mapi_error().

• size_t mapi_fetch_fiels_len(MapiHdl hdl, int fnr)

  Return the length of the C-string representation excluding trailing NULL byte of the value. Zero is returned upon encountering an error, when the database value is NULL, of when the string is the empty string. This can be analyzed by using mapi_error() and mapi_fetch_field().

• MapiMsg mapi_next_result(MapiHdl hdl)

  Go to the next result set, discarding the rest of the output of the current result set.

Errors handling

• MapiMsg mapi_error(Mapi mid)

  Return the last error code or 0 if there is no error.

• const char *mapi_error_str(Mapi mid)

  Return a pointer to the last error message.

• const char *mapi_result_error(MapiHdl hdl)

  Return a pointer to the last error message from the server.

• const char *mapi_result_errorcode(MapiHdl hdl)

  Return a pointer to the SQLSTATE code of the last error from the server.

• void mapi_explain(Mapi mid, FILE *fd)

  Write the error message obtained from mserver to a file.

• void mapi_explain_query(MapiHdl hdl, FILE *fd)

  Write the error message obtained from mserver to a file.

• void mapi_explain_result(MapiHdl hdl, FILE *fd)

  Write the error message obtained from mserver to a file.

Parameters

• MapiMsg mapi_bind(MapiHdl hdl, int fldnr, char **val)

  Bind a string variable with a field in the return table. Upon a successful subsequent mapi_fetch_row() the indicated field is stored in the space pointed to by val. Returns an error if the field identified does not exist.

• MapiMsg mapi_bind_var(MapiHdl hdl, int fldnr, int type, void *val)

  Bind a variable to a field in the return table. Upon a successful subsequent mapi_fetch_row(), the indicated field is converted to the given type and stored in the space pointed to by val. The types recognized are ** MAPI_TINY, MAPI_UTINY, MAPI_SHORT, MAPI_USHORT, MAPI_INT, MAPI_UINT, MAPI_LONG, MAPI_ULONG, MAPI_LONGLONG, MAPI_ULONGLONG, MAPI_CHAR, MAPI_VARCHAR, MAPI_FLOAT, MAPI_DOUBLE, MAPI_DATE, MAPI_TIME, MAPI_DATETIME**. The binding operations should be performed after the mapi_execute command. Subsequently all rows being fetched also involve delivery of the field values in the C-variables using proper conversion. For variable length strings a pointer is set into the cache.

• MapiMsg mapi_bind_numeric(MapiHdl hdl, int fldnr, int scale, int precision, void *val)

  Bind to a numeric variable, internally represented by MAPI_INT Describe the location of a numeric parameter in a query template.

• MapiMsg mapi_clear_bindings(MapiHdl hdl)

  Clear all field bindings.

• MapiMsg mapi_param(MapiHdl hdl, int fldnr, char **val)

  Bind a string variable with the n-th placeholder in the query template. No conversion takes place.

• MapiMsg mapi_param_type(MapiHdl hdl, int fldnr, int ctype, int sqltype, void *val)

  Bind a variable whose type is described by ctype to a parameter whose type is described by sqltype.

• MapiMsg mapi_param_numeric(MapiHdl hdl, int fldnr, int scale, int precision, void *val)

  Bind to a numeric variable, internally represented by MAPI_INT.

• MapiMsg mapi_param_string(MapiHdl hdl, int fldnr, int sqltype, char *val, int *sizeptr)

  Bind a string variable, internally represented by MAPI_VARCHAR, to a parameter. The sizeptr parameter points to the length of the string pointed to by val. If sizeptr == NULL or *sizeptr == -1, the string is NULL-terminated.

• MapiMsg mapi_clear_params(MapiHdl hdl)

  Clear all parameter bindings.

Miscellaneous

• MapiMsg mapi_setAutocommit(Mapi mid, int autocommit)

  Set the autocommit flag (default is on). This only has effect when the language is SQL. In that case, the server commits after each statement sent to the server. information about autocommit and multi-statements transactions can be found here.

• MapiMsg mapi_setAlgebra(Mapi mid, int algebra)

  Tell the backend to use or stop using the algebra-based compiler.

• MapiMsg mapi_cache_limit(Mapi mid, int maxrows)

  A limited number of tuples are pre-fetched after each execute(). If maxrows is negative, all rows will be fetched before the application is permitted to continue. Once the cache is filled, a number of tuples are shuffled to make room for new ones, but taking into account non-read elements. Filling the cache quicker than reading leads to an error.

• MapiMsg mapi_cache_freeup(MapiHdl hdl, int percentage)

  Forcefully shuffle the cache making room for new rows. It ignores the read counter, so rows may be lost.

• char * mapi_quote(const char *str, int size)

  Escape special characters such as \n, \t in str with backslashes. The returned value is a newly allocated string which should be freed by the caller.

• char * mapi_unquote(const char *name)

  The reverse action of mapi_quote(), turning the database representation into a C-representation. The storage space is dynamically created and should be freed after use.

• MapiMsg mapi_output(Mapi mid, char *output)

  Set the output format for results send by the server.

• MapiMsg mapi_stream_into(Mapi mid, char *docname, char *colname, FILE *fp)

  Stream a document into the server. The name of the document is specified in docname, the collection is optionally specified in colname (if NULL, it defaults to docname), and the content of the document comes from fp.

• MapiMsg mapi_profile(Mapi mid, int flag)

  Set the profile flag to time commands send to the server.

• MapiMsg mapi_timeout(Mapi mid, unsigned int time)

  Set timeout in milliseconds for long-running queries.

• void mapi_trace(Mapi mid, int flag)

  Set the trace flag to monitor interaction of the client with the library. It is primarily used for debugging Mapi applications.

• int mapi_get_trace(Mapi mid

  Return the current value of the trace flag.

• MapiMsg mapi_log(Mapi mid, const char *fname)

  Log the interaction between the client and server for offline inspection. Beware that the log file overwrites any previous log. For detailed interaction trace with the Mapi library itself use mapi_trace().

The remaining operations are wrappers around the data structures maintained. Note that column properties are derived from the table output returned from the server.

• char *mapi_get_name(MapiHdl hdl, int fnr)
• char *mapi_get_type(MapiHdl hdl, int fnr)
• char *mapi_get_table(MapiHdl hdl, int fnr)
• int mapi_get_len(Mapi mid, int fnr)
• char *mapi_get_dbname(Mapi mid)
• char *mapi_get_host(Mapi mid)
• char *mapi_get_user(Mapi mid)
• char *mapi_get_lang(Mapi mid)
• char *mapi_get_motd(Mapi mid)

The mapi library source code can be found in MonetDB source tree at /clients/mapilib