Language bindings

Language bindings mk Sun, 03/28/2010 - 23:53

MonetDB comes with a JDBC, ODBC, PHP, Perl, Ruby and Python interface libraries. JDBC, PHP, Perl, Ruby, Python and Node.js interfaces are native implementations and do not require installation of the complete MonetDB code base. The Mapi library is the lowest level C-interface to interacting with the server.

We rely mostly on external documentation for the basics of the language bindings provided: PHP, Perl, Python, and the drivers JDBC and ODBC. Examples are used to illustrate their behavior in the context of MonetDB only. The resource locations identified below may at times proof valuable.

Python https://python.org
R http://www.r-project.org
JavaScript/Node.js https://nodejs.org
Ruby/Rails http://rubyonrails.org
Perl DBI https://www.perl.org
PHP5 https://php.net

The programming interface is based on a client-server architecture, where the client program connects to a server using a TCP/IP connection to exchange commands and receives answers. The underlying protocol uses plain UTF-8 data for ease of use and debugging. This leads to publicly visible information exchange over a network, which may be undesirable. Therefore, a private and secure channel can be set up with the Secure Socket Layer functionality.

In addition, a deeper integration of MonetDB with R is available on the website of the MonetDB.R connector. If you are looking for information about the embedded R support in MonetDB, follow check this blog post.

More information about using MonetDB on Node.js you can find here.

Mapi Library

Mapi Library mk Sun, 03/28/2010 - 23:55

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); 
    mapi_destroy(dbh); 
  } else 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:

% libtool --mode=compile --tag=CC gcc -c `env PKG_CONFIG_PATH=$INSTALL_DIR/lib/pkgconfig pkg-config --cflags monetdb-mapi` test.c % libtool --mode=link --tag=CC gcc -o test `env PKG_CONFIG_PATH=$INSTALL_DIR/lib/pkgconfig pkg-config --libs monetdb-mapi` test.o % ./test 

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.

mapi_bind() Bind string C-variable to a field
mapi_bind_numeric() Bind numeric C-variable to field
mapi_bind_var() Bind typed C-variable to a field
mapi_cache_freeup() Forcefully shuffle fraction for cache refreshment
mapi_cache_limit() Set the tuple cache limit
mapi_cache_shuffle() Set shuffle fraction for cache refreshment
mapi_clear_bindings() Clear all field bindings
mapi_clear_params() Clear all parameter bindings
mapi_close_handle() Close query handle and free resources
mapi_connect() Connect to a Mserver
mapi_destroy() Free handle resources
mapi_disconnect() Disconnect from server
mapi_error() Test for error occurrence
mapi_execute() Execute a query
mapi_execute_array() Execute a query using string arguments
mapi_explain() Display error message and context on stream
mapi_explain_query() Display error message and context on stream
mapi_fetch_all_rows() Fetch all answers from server into cache
mapi_fetch_field() Fetch a field from the current row
mapi_fetch_field_len() Fetch the length of a field from the current row
mapi_fetch_field_array() Fetch all fields from the current row
mapi_fetch_line() Retrieve the next line
mapi_fetch_reset() Set the cache reader to the beginning
mapi_fetch_row() Fetch row of values
mapi_finish() Terminate the current query
mapi_get_dbname() Database being served
mapi_get_field_count() Number of fields in current row
mapi_get_host() Host name of server
mapi_get_query() Query being executed
mapi_get_language() Query language name
mapi_get_mapi_version() Mapi version name
mapi_get_monet_versionId() MonetDB version identifier
mapi_get_monet_version() MonetDB version name
mapi_get_motd() Get server welcome message
mapi_get_row_count() Number of rows in cache or -1
mapi_get_last_id() last inserted id of an auto_increment (or alike) column
mapi_get_from() Get the stream 'from'
mapi_get_to() Get the stream 'to'
mapi_get_trace() Get trace flag
mapi_get_user() Current user name
mapi_log() Keep log of client/server interaction
mapi_next_result() Go to next result set
mapi_needmore() Return whether more data is needed
mapi_ping() Test server for accessibility
mapi_prepare() Prepare a query for execution
mapi_prepare_array() Prepare a query for execution using arguments
mapi_query() Send a query for execution
mapi_query_array() Send a query for execution with arguments
mapi_query_handle() Send a query for execution
mapi_quick_query_array() Send a query for execution with arguments
mapi_quick_query() Send a query for execution
mapi_quick_response() Quick pass response to stream
mapi_quote() Escape characters
mapi_reconnect() Reconnect with a clean session context
mapi_rows_affected() Obtain number of rows changed
mapi_seek_row() Move row reader to specific location in cache
mapi_setAutocommit() Set auto-commit flag
mapi_setAlgebra() Use algebra backend
mapi_stream_query() Send query and prepare for reading tuple stream
mapi_table() Get current table name
mapi_timeout() Set timeout for long-running queries[TODO]
mapi_output() Set output format
mapi_stream_into() Stream document into server
mapi_profile() Set profile flag
mapi_trace() Set trace flag
mapi_virtual_result() Submit a virtual result set
mapi_unquote()

remove escaped characters

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.

 

MOK No error
MERROR Mapi internal error.
MTIMEOUT Error communicating with the server.

 

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:

do {
    if ((error = mapi_result_error(hdl)) != NULL)
        mapi_explain_result(hdl, stderr);
    while ((line = mapi_fetch_line(hdl)) != NULL)
        /* use output */;
} while (mapi_next_result(hdl) == 1);

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_stream_query(Mapi mid, const char *Command, int windowsize)

    Send the request for processing and fetch a limited number of tuples (determined by the window size) to assess any erroneous situation. Thereafter, prepare for continual reading of tuples from the stream, until an error occurs. Each time a tuple arrives, the cache is shifted one.

  • 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.

  • char *mapi_error_str(Mapi mid)

    Return a pointer to the last error message.

  • char *mapi_result_error(MapiHdl hdl)

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

  • MapiMsg mapi_explain(Mapi mid, FILE *fd)

    Write the error message obtained from mserver to a file.

  • MapiMsg mapi_explain_query(MapiHdl hdl, FILE *fd)

    Write the error message obtained from mserver to a file.

  • MapiMsg 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.  More 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_shuffle(MapiHdl hdl, int percentage)

    Make room in the cache by shuffling percentage tuples out of the cache. It is sometimes handy to do so, for example, when your application is stream-based and you process each tuple as it arrives and still need a limited look-back. This percentage can be set between 0 to 100. Making shuffle= 100% (default) leads to paging behavior, while shuffle==1 leads to a sliding window over a tuple stream with 1% refreshing.

  • 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_trace(Mapi mid, int flag)

    Set the trace flag to monitor interaction of the client with the library. It is primarilly 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)

 

JDBC Driver

JDBC Driver mk Sun, 03/28/2010 - 23:58

MonetDB JDBC Driver

The most obvious way to connect to a data source using the Java programming language is by making use of the in Java defined JDBC framework. MonetDB has a 100% pure Java JDBC driver (type 4) which allows use of the MonetDB database in a Java alike way.

This document gives a short description how to use the MonetDB JDBC driver in Java applications. Familiarity with the Java JDBC API is required to fully understand this document. Please note that you can find the complete JDBC API on Oracle's web site http://docs.oracle.com/javase/7/docs/technotes/guides/jdbc/index.html.

The latest release of the MonetDB JDBC driver has implemented most of the essential JDBC API classes and methods (only CallableStatement is not yet implemented). If you make extensive use of JDBC API and semantics and rely on its features, please report any missing functionality on our bugzilla.

In order to use the MonetDB JDBC driver in Java applications you need (of course) a running MonetDB/SQL instance, preferably via monetdbd.

Getting the JDBC driver Jar

The easiest way to acquire the driver is to download it from our MonetDB Java Download Area. You will find a jar file called monetdb-jdbc-X.Y.jar where X and Y are major and minor version numbers. The other two listed jar files (jdbcclient.jar and monetdb-mcl-*.jar) are optional utility jars. jdbcclient.jar contains a java command line program similar to mclient, see below.

Compiling the driver (using ant, optional)

If you prefer to build the driver yourself, make sure you acquire the MonetDB Java repository, e.g. as part of the source downloads. The Java sources are built using Apache's Ant tool. Simply issuing the command ant distjdbc should be sufficient to build the driver jar-archive in the subdirectory jars. See the ant web site for more documentation on the ant build-tool: http://ant.apache.org/. The Java sources currently require at least a Java 7 compatible compiler.

Using the JDBC driver in your Java programs

To use the MonetDB JDBC driver, the monetdb-jdbc-X.Y.jar jar-archive has to be in the Java classpath. Make sure this is actually the case. Note: as of Jul2015 release (monetdb-jdbc-2.17.jar) the MonetDB JDBC Driver only works with Java 7 (or higher) JVMs.

Using the MonetDB JDBC driver in your Java program:

  // request a Connection to a MonetDB server running on 'localhost' (with default port 50000) for database demo for user and password monetdb
  Connection con = DriverManager.getConnection("jdbc:monetdb://localhost/demo", "monetdb", "monetdb");

The MonetDB JDBC Connection URL string passed to the "getConnection()"method is defined as

   jdbc:monetdb://<hostname>[:<portnr>]/<databasename>[?<property>=<value>[;<property>=<value>]]

where elements between "<" and ">" are required and elements between "[" and "]" are optional.

Following optional connection properties are allowed:

	so_timeout=<time in milliseconds>
	language=mal
	language=sql
	treat_blob_as_binary=true
	debug=true

A sample Java JDBC program

import java.sql.*;

/**
 * This example assumes there exist tables a and b filled with some data.
 * On these tables some queries are executed and the JDBC driver is tested
 * on it's accuracy and robustness against 'users'.
 *
 * @author Fabian Groffen
 */
public class MJDBCTest {
    public static void main(String[] args) throws Exception {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            // make a connection to the MonetDB server using JDBC URL starting with: jdbc:monetdb:// 
            con = DriverManager.getConnection("jdbc:monetdb://localhost:50000/mydb?so_timeout=10000", "monetdb", "monetdb");
            // make a statement object
            st = con.createStatement();
            // make a executeand SQL query which returns a ResultSet object
            rs = st.executeQuery("SELECT a.var1, COUNT(b.id) as total FROM a, b WHERE a.var1 = b.id AND a.var1 = 'andb' GROUP BY a.var1 ORDER BY a.var1, total;");
            // get meta data and print column names with their type
            ResultSetMetaData md = rs.getMetaData();
            for (int i = 1; i <= md.getColumnCount(); i++) {
                System.out.print(md.getColumnName(i) + ":" + md.getColumnTypeName(i) + "\t");
            }
            System.out.println("");
            // now print the data: only the first 5 rows, while there probably are
            // a lot more. This shouldn't cause any problems afterwards since the
            // result should get properly discarded when we close it
            for (int i = 0; rs.next() && i < 5; i++) {
                for (int j = 1; j <= md.getColumnCount(); j++) {
                    System.out.print(rs.getString(j) + "\t");
                }
                System.out.println("");
            }
            // close (server) resource as soon as we are done processing resultset data
            rs.close();
            rs = null;

            // tell the driver to only return 5 rows for the next execution
            // it can optimize on this value, and will not fetch any more than 5 rows.
            st.setMaxRows(5);
            // we ask the database for 22 rows, while we set the JDBC driver to
            // 5 rows, this shouldn't be a problem at all...
            rs = st.executeQuery("select * from a limit 22");
            // read till the driver says there are no rows left
            for (int i = 0; rs.next(); i++) {
                System.out.print("[" + rs.getString("var1") + "]");
                System.out.print("[" + rs.getString("var2") + "]");
                System.out.print("[" + rs.getInt("var3") + "]");
                System.out.println("[" + rs.getString("var4") + "]");
            }
            // close (server) resource as soon as we are done processing resultset data
            rs.close();
            rs = null;

            // unset the row limit; 0 means as much as the database sends us
            st.setMaxRows(0);
            // we only ask 10 rows
            rs = st.executeQuery("select * from b limit 10;");
            // and simply print them
            while (rs.next()) {
                System.out.print(rs.getInt("rowid") + ", ");
                System.out.print(rs.getString("id") + ", ");
                System.out.print(rs.getInt("var1") + ", ");
                System.out.print(rs.getInt("var2") + ", ");
                System.out.print(rs.getString("var3") + ", ");
                System.out.println(rs.getString("var4"));
            }
            // close (server) resource as soon as we are done processing resultset data
            rs.close();
            rs = null;

            // perform a ResultSet-less query (with no trailing ; since that should
            // be possible as well and is JDBC standard)
            int updCount = st.executeUpdate("delete from a where var1 = 'zzzz'");
            System.out.println("executeUpdate() returned: " + updCount);
        } catch (SQLException se) {
            System.out.println(se.getMessage());
        } finally {
            // when done, close all (server) resources
            if (rs != null) rs.close();
            if (st != null) st.close();
            if (con != null) con.close();
        }
    }
}

Note: it is no longer required (or recommended) to include code line:

  Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");

as the MonetDriver registers itself with the JDBC DriverManager automatically when the monetdb-jdbc-X.Y.jar file is loaded.

 

Using the JdbcClient utility program

We have created an example Java SQL command line program similar to (but not compatible with) mclient program. The jdbcclient.jar program can be downloaded from the MonetDB Java Download Area. It includes and uses the MonetDB JDBC driver internally. As it already includes the JDBC driver classes it is very easy to start it (assuming you have an MonetDB/SQL server running) via:

% java -jar jdbcclient.jar -p50000 -ddemo -umonetdb
password:

Welcome to the MonetDB interactive JDBC terminal!
Database Server: MonetDB v11.23.14
JDBC Driver: MonetDB Native Driver v2.23 (Liberica 20161117 based on MCL v1.13)
Current Schema: sys
Type \q to quit, \h for a list of available commands
auto commit mode: on
sql>

From this sql> prompt you can enter an SQL query (include an ; as end-of-statement) and execute it by using the enter-key.

If the connection fails, observe the error messages from JdbcClient (and/or the merovingian logs) for clues.

Use \q to quit the program.

To see all jdbcclient startup options just run:

% java -jar jdbcclient.jar --help

Tip: if you do not want to enter the password each time, use a .monetdb file (which contains the user and password settings) similar as for mclient

Python Library

Python Library mk Sun, 03/28/2010 - 23:58

The MonetDB MAPI and SQL client python API

Introduction

This is the native python client API. This API is cross-platform, and doesn't depend on any monetdb libraries. It has support for python 2.7 and 3.3+ and is Python DBAPI 2.0 compatible.

Installation

To install the MonetDB python API run the following command:

# pip install pymonetdb

That's all, now you are ready to start using the API. We recommend that you use virtual environments to avoid polluting your global python installation.

Documentation

The python code is well documented, so if you need to find documentation you should have a look at the source code. Below is an interactive example on how to use the monetdb SQL API which should get you started quite fast.

Examples

There are some examples in the 'examples' folder, but here are is a line by line example of the SQL API:

> # import the SQL module
> import pymonetdb
>
> # set up a connection. arguments below are the defaults
> connection = pymonetdb.connect(username="monetdb", password="monetdb", hostname="localhost", database="demo")
>
> # create a cursor
> cursor = connection.cursor()
>
> # increase the rows fetched to increase performance (optional)
> cursor.arraysize = 100
>
> # execute a query (return the number of rows to fetch)
> cursor.execute('SELECT * FROM tables')
26
>
> # fetch only one row
> cursor.fetchone()
(1062, 'schemas', 1061, None, 0, True, 0, 0)
>
> # fetch the remaining rows
> cursor.fetchall()
[(1067, 'types', 1061, None, 0, True, 0, 0),
 (1076, 'functions', 1061, None, 0, True, 0, 0),
 (1085, 'args', 1061, None, 0, True, 0, 0),
 (1093, 'sequences', 1061, None, 0, True, 0, 0),
 (1103, 'dependencies', 1061, None, 0, True, 0, 0),
 (1107, 'connections', 1061, None, 0, True, 0, 0),
 (1116, '_tables', 1061, None, 0, True, 0, 0),
 ...
 (4141, 'user_role', 1061, None, 0, True, 0, 0),
 (4144, 'auths', 1061, None, 0, True, 0, 0),
 (4148, 'privileges', 1061, None, 0, True, 0, 0)]
>
> # Show the table meta data
> cursor.description
[('id', 'int', 4, 4, None, None, None),
 ('name', 'varchar', 12, 12, None, None, None),
 ('schema_id', 'int', 4, 4, None, None, None),
 ('query', 'varchar', 168, 168, None, None, None),
 ('type', 'smallint', 1, 1, None, None, None),
 ('system', 'boolean', 5, 5, None, None, None),
 ('commit_action', 'smallint', 1, 1, None, None, None),
 ('temporary', 'tinyint', 1, 1, None, None, None)]

If you would like to communicate with the database at a lower level you can use the MAPI library:

> from pymonetdb import mapi
> mapi_connection = mapi.Connection()
> mapi_connection.connect(hostname="localhost", port=50000, username="monetdb", password="monetdb", database="demo", language="sql", unix_socket=None, connect_timeout=-1)
> mapi_connection.cmd("sSELECT * FROM tables;")
...

Perl Library

Perl Library mk Sun, 03/28/2010 - 23:56

MonetDB Perl Library

Perl is one of the more common scripting languages for which a 'standard' database application programming interface is defined. It is called DBI and it was designed to protect you from the API library details of multiple DBMS vendors. It has a very simple interface to execute SQL queries and for processing the results sent back. DBI doesn't know how to talk to any particular database, but it does know how to locate and load in DBD (`Database Driver') modules. The DBD modules encapsulate the interface library's intricacies and knows how to talk to the real databases.

MonetDB comes with its own DBD module which is included in both the source and binary distribution packages. The module is also available via CPAN.

Two sample Perl applications are included in the source distribution; a MIL session and a simple client to interact with a running server.

For further documentation we refer to the Perl community home page.

A Simple Perl Example

use strict;
use warnings;
use DBI();

print "\nStart a simple Monet MIL interaction\n\n";

# determine the data sources:
my @ds = DBI->data_sources('monetdb');
print "data sources: @ds\n";

# connect to the database:
my $dsn = 'dbi:monetdb:database=test;host=localhost;port=50000;language=mil';
my $dbh = DBI->connect( $dsn,
  undef, undef,  # no authentication in MIL
  { PrintError => 0, RaiseError => 1 }  # turn on exception handling
);
{
  # simple MIL statement:
  my $sth = $dbh->prepare('print(2);');
  $sth->execute;
  my @row = $sth->fetchrow_array;
  print "field[0]: $row[0], last index: $#row\n";
}
{
  my $sth = $dbh->prepare('print(3);');
  $sth->execute;
  my @row = $sth->fetchrow_array;
  print "field[0]: $row[0], last index: $#row\n";
}
{
  # deliberately executing a wrong MIL statement:
  my $sth = $dbh->prepare('( xyz 1);');
  eval { $sth->execute }; print "ERROR REPORTED: $@" if $@;
}
$dbh->do('var b:=new(int,str);');
$dbh->do('insert(b,3,"three");');
{
  # variable binding stuff:
  my $sth = $dbh->prepare('insert(b,?,?);');
  $sth->bind_param( 1,     7 , DBI::SQL_INTEGER() );
  $sth->bind_param( 2,'seven' );
  $sth->execute;
}
{
  my $sth = $dbh->prepare('print(b);');
  # get all rows one at a time:
  $sth->execute;
  while ( my $row = $sth->fetch ) {
    print "bun: $row->[0], $row->[1]\n";
  }
  # get all rows at once:
  $sth->execute;
  my $t = $sth->fetchall_arrayref;
  my $r = @$t;         # row count
  my $f = @{$t->[0]};  # field count
  print "rows: $r, fields: $f\n";
  for my $i ( 0 .. $r-1 ) {
    for my $j ( 0 .. $f-1 ) {
      print "field[$i,$j]: $t->[$i][$j]\n";
    }
  }
}
{
  # get values of the first column from each row:
  my $row = $dbh->selectcol_arrayref('print(b);');
  print "head[$_]: $row->[$_]\n" for 0 .. 1;
}
{
  my @row = $dbh->selectrow_array('print(b);');
  print "field[0]: $row[0]\n";
  print "field[1]: $row[1]\n";
}
{
  my $row = $dbh->selectrow_arrayref('print(b);');
  print "field[0]: $row->[0]\n";
  print "field[1]: $row->[1]\n";
}
$dbh->disconnect;
print "\nFinished\n";

ODBC Driver

ODBC Driver mk Sun, 03/28/2010 - 23:59

MonetDB ODBC Driver

Short for Open DataBase Connectivity, a standard database access method developed by the SQL Access group in 1992. The goal of ODBC is to make it possible to access any data from any application, regardless of which database management system (DBMS) is handling the data. ODBC manages this by inserting a middle layer, called a database driver, between an application and the DBMS. The purpose of this layer is to translate the application's data queries into commands that the DBMS understands. For this to work, both the application and the DBMS must be ODBC-compliant – that is, the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them.

The ODBC driver for MonetDB is included in the Windows installer and Linux RPMs. The source can be found in the SQL CVS tree.

To help you setup your system to use the ODBC driver with MonetDB, two how-tos are available, one for Windows users and one for Linux/UNIX users.

Microsoft Excel demo

A little demo showing how to import data from a MonetDB server into Microsoft Excel.

Using Excel with the MonetDB ODBC Driver

Start up the MonetDB SQL Server and Excel.

In Excel, select from the drop down menu, first Data, then Get External Data, and finally New Database Query...

excel1.png

If MonetDB was installed correctly, there should be an entry MonetDB in the dialog box that opens. Select it and click on OK.

excel2.png

In the wizard that opens, scroll down in the list on the left hand side and select voyages. Then click on the button labeled > and then on Next >.

excel3.png

In the next page of the wizard, click on Next >.

excel4.png

In the next page of the wizard, click on Next >.

excel5.png

In the final page of the wizard, click on Finish.

excel6.png

A new dialog window opens. Click on OK to insert the data into the current Excel worksheet.

excel7.png

That's all.

excel8.png

Installing the MonetDB ODBC Driver for unixODBC

Configuring the MonetDB Driver

As Superuser, start the unixODBC configuration program ODBCConfig and select the Drivers tab.

uodbc1.png

On this tab, click on the button labeled Add... and fill in the fields as follows.

uodbc2.png
Name
MonetDB
Description
ODBC Driver for MonetDB SQL Server
Driver
<path-to-MonetDB>/lib(64)/libMonetODBC.so
Setup
<path-to-MonetDB>/lib(64)/libMonetODBCs.so

Don't change the other fields. When done, click on the check mark in the top left corner of the window. The first window should now contain an entry for MonetDB. Click on OK

Configuring a Data Source

Now as normal user start ODBCConfig again.

uodbc3.png

On the User DSN tab click on the Add... button. A new window pops up in which you have to select the ODBC driver. Click on the entry for MonetDB and click on OK.

uodbc4.png

A new window pops up. Fill in the fields as follows.

uodbc5.png
Name
MonetDB
Description
Default MonetDB Data Source
Host
localhost
Port
50000
User
monetdb
Password
monetdb

Don't change the other fields. When done, click on the check mark in the top left corner of the window. The first window should now contain an entry for MonetDB. Click on OK