RELEASE NOTES
MonetDB JDBC driver version 3.2 (Liberica/MCL-1.21)
Release date: 2022-01-27

The Java Database Connectivity (JDBC) API provides universal data access from
the Java programming language.

The MonetDB JDBC driver is designed for use with MonetDB, an Open-Source column-store RDBMS.
For more information see https://www.monetdb.org/

The latest MonetDB JDBC driver can be downloaded from
https://www.monetdb.org/downloads/Java/

The sources for this JDBC driver and related Java programs can be found at:
https://dev.monetdb.org/hg/monetdb-java/file/tip


The MonetDB JDBC connection URL format to use is:
  jdbc:monetdb://<hostname>[:<portnr>]/<databasename>[?<property>=<value>[&<property>=<value>]]

Note: For a successful connection the database name part must be provided
 and be equal to the name of the database served by the mserver5 process running on
 the specified host and listening to the specified port number (default port is 50000).

Supported connection properties are:
	user=<login name>
	password=<secret value>
	so_timeout=<time in milliseconds>  default is: 0 (no timeout)
	treat_clob_as_varchar=false        default is: true
	treat_blob_as_binary=false         default is: true
	language=<sql or mal>              default is: sql
	fetchsize=<nr of rows>             default is: 250; -1 means fetch everything at once
	debug=true                         default is: false
	logfile=<name of logfile>
	hash=<SHA512, SHA384, SHA256 or SHA1>

The treat_clob_as_varchar property (when set to true) will let the method
ResultSetMetaData.getColumnType(int) to return Types.VARCHAR instead of Types.CLOB
for CLOB ResultSet columns. Generic JDBC applications such as SQuirreL SQL and DBeaver
will than use rs.getString() instead of rs.getClob() to fetch any CLOB column data.
Using rs.getString() avoids creating new objects and multiple copies of the clob
string data resulting in (much) faster response and better user experience.
This property is turned on by default as of release monetdb-jdbc-3.0.jre8.jar.
You can turn it off if you prefer the old behavior.

The treat_blob_as_binary property (when set to true) will let the method
ResultSetMetaData.getColumnType(int) to return Types.VARBINARY instead of Types.BLOB
for BLOB ResultSet columns. Generic JDBC applications such as SQuirreL SQL and DBeaver
will than use rs.getBytes() instead of rs.getBlob() to fetch any BLOB column data.
Using rs.getBytes() avoids creating new objects and multiple copies of the blob
string data resulting in (much) faster response and better user experience.
This property is turned on by default as of release monetdb-jdbc-3.0.jre8.jar.
You can turn it off if you prefer the old behavior.

We recommend to set following connection properties:
	so_timeout=20000

Multiple connection properties are separated by the & character.
For example:
  jdbc:monetdb://localhost:41000/mydb?user=monetdb&password=onlyiknow&so_timeout=20000

See also: https://www.monetdb.org/Documentation/SQLreference/Programming/JDBC


The MonetDB JDBC driver class name is: org.monetdb.jdbc.MonetDriver
This has been changed as of release 3.0 (monetdb-jdbc-3.0.jre8.jar).
The old driver class (nl.cwi.monetdb.jdbc.MonetDriver) has been deprecated.
It is still included in the jar file to ease the transition for existing deployments.
However it will be removed in a future release of the MonetDB JDBC driver.
Please use the new driver class name asap in your configuration files or Java code.


JDBC COMPLIANCE
The MonetDB JDBC driver is a type 4 driver (100% pure Java) and
complies to JDBC 4.2 definition, see
 http://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/index.html
and
 https://en.wikipedia.org/wiki/Java_Database_Connectivity

Within the current implementation not all functionalities of the JDBC
interface are available.  It is believed, however, that this
implementation is rich enough to be suitable for a majority of
application settings.

Below a list of (un)supported features can be found.
Please read this list if you intend to use this JDBC driver.

If you feel some features are missing or have encountered an issue/bug,
please let us know at our bugtracker:
  https://github.com/MonetDB/monetdb-java/issues

Currently implemented JDBC 4.2 interfaces include:
  * java.sql.Driver
    The next method is NOT useable/supported:
    - getParentLogger

  * java.sql.Connection
    The next features/methods are NOT useable/supported:
    - createArrayOf, createNClob, createStruct, createSQLXML
    - prepareStatement with array of column indices or column names
    - setHoldability (close/hold cursors over commit is not configurable)

    NOTE: be sure to check for warnings after setting concurrencies or
          isolation levels; MonetDB currently does not support anything
          else but "fully serializable" transactions.

  * java.sql.DatabaseMetaData

  * java.sql.Statement
    The next methods/options are NOT useable/supported:
    - cancel (query execution cannot be terminated, once started)
       see also: https://github.com/MonetDB/monetdb-java/issues/7
       or https://www.monetdb.org/bugzilla/show_bug.cgi?id=6222
    - execute with column indices or names
    - executeUpdate with column indices or names
    - setMaxFieldSize
    - setCursorName
    - setEscapeProcessing on

  * java.sql.PreparedStatement
    The next methods are NOT useable/supported:
    - setArray
    - setAsciiStream, setBinaryStream, setUnicodeStream
    - setBlob
    - setNClob
    - setRef, setRowId, setSQLXML

  * java.sql.ParameterMetaData

  * java.sql.CallableStatement
    The next methods are NOT useable/supported:
    - all getXyz(parameterIndex/parameterName, ...) methods because
      output parameters in stored procedures are not supported by MonetDB
    - all registerOutParameter(parameterIndex/parameterName, int sqlType, ...) methods
      because output parameters in stored procedures are not supported by MonetDB
    - wasNull() method because output parameters in stored procedures are
      not supported by MonetDB

  * java.sql.ResultSet
    The next methods are NOT useable/supported:
    - getArray
    - getAsciiStream, getUnicodeStream
    - getNClob
    - getRef, getRowId, getSQLXML
    - getObject(column, Class<T> type)
    - moveToCurrentRow, moveToInsertRow,
    - All methods related to updateable result sets such as:
      updateArray ... updateTimestamp, cancelRowUpdates,
      deleteRow, insertRow, refreshRow

  * java.sql.ResultSetMetaData

  * java.sql.SavePoint

  * java.sql.Wrapper

  * java.sql.Blob
    A simple implementation using a byte[] to store the whole BLOB.
    The next method is NOT useable/supported:
    - setBinaryStream

  * java.sql.Clob
    A simple implementation using a StringBuilder to store the whole CLOB.
    The next methods are NOT useable/supported:
    - setAsciiStream
    - setCharacterStream

  * java.sql.SQLData
    implemented by class: org.monetdb.jdbc.types.INET
            and by class: org.monetdb.jdbc.types.URL

  * javax.sql.DataSource (not tested)
    The next method is NOT useable/supported:
    - getParentLogger


The following java.sql.* interfaces are NOT implemented:
  * java.sql.Array
  * java.sql.DriverAction
  * java.sql.NClob
  * java.sql.Ref
  * java.sql.Rowid
  * java.sql.SQLInput
  * java.sql.SQLOutput
  * java.sql.SQLType
  * java.sql.SQLXML
  * java.sql.Struct


Since release 3.2 (monetdb-jdbc-3.2.jre8.jar), the MonetDB JDBC driver has
support for the ON CLIENT clause of the COPY statement. To make use of
this functionality you must register handlers for upload and download of data.
The MonetConnection class has been extended with 2 methods:
 public void setUploadHandler(UploadHandler uploadHandler)
 public void setDownloadHandler(DownloadHandler downloadHandler)
The API has been extended with interfaces and utility class:
 public interface org.monetdb.jdbc.MonetConnection.UploadHandler
 public interface org.monetdb.jdbc.MonetConnection.DownloadHandler
 public class org.monetdb.util.FileTransferHandler
  which implements MonetConnection.UploadHandler, MonetConnection.DownloadHandler
See onclient.txt for more information on how to use these from Java.

The JdbcClient application has also been extended to support COPY ...
ON CLIENT functionality. However for security reasons you must provide an
explicit new startup argument
  --csvdir "/path/to/csvdatafiles"
or on MS Windows
  --csvdir "C:\\path\\to\\csvdatafiles"
in order to allow the JdbcClient to down/up load data to/from local csv files.


Notes and Tips for Java Programmers using MonetDB JDBC driver:
- Close JDBC ResultSet, Statement, PreparedStatement, CallableStatement and
  Connection objects immediately (via close()) when they are no longer needed,
  in order to release resources and memory on the server and client side.
  Especially ResultSets can occupy large amounts of memory on the server and
  client side.

- By default the ResultSets created by methods in DatabaseMetaData
  which return a ResultSet (such as dbmd.getColumns(...)) are
  TYPE_SCROLL_INSENSITIVE, so they cache their ResultSet data to
  allow absolute, relative and random access to data rows and fields.
  To free heap memory and server resoucres, close those ResultSets
  immediately when no longer needed.

- By default the ResultSets created by stmt.executeQuery(...) or
  stmt.execute(...) are TYPE_FORWARD_ONLY, to reduce the potentially large
  amount of client memory needed to cache the whole ResultSet data.

- When you need to execute many SQL queries sequentially reuse the Statement
  object instead of creating a new Statement for each single SQL query.
  Alternatively you can execute the SQL queries as one script (each SQL query
  must be separated by a ; character) string via stmt.execute(script),
  stmt.getResultSet() and stmt.getMoreResults().
  Or you can use the batch execution functionality, see stmt.addBatch() and
  stmt.executeBatch() methods.

- The fastest way to retrieve data from a MonetDB ResultSet is via the
  getString(int columnIndex) method, because internally all data
  values (of all types) are stored as Strings, so no conversions are needed.

- Avoid using rs.getObject() as it will need to construct a new Object for
  each value, even for primitive types such as int, long, boolean.

- Avoid using rs.getClob(). Instead use getString() for all CLOB
  columns, which is much faster and uses much (3 times) less memory.

- Avoid using rs.getBlob(). Instead use getBytes() to get a byte array
  or use getString() to get a string containing hex pairs, for all BLOB
  columns. These methods are much faster and use much less memory.
  The getString() is the fastest way as no conversions are done at all.
  The getBytes() will need to convert the hex char string into a new bytes[].

- Try to avoid calling "rs.get...(String columnLabel)" methods inside the
   while(rs.next()) {...} loop. Instead resolve the columnLabels to column
  numbers before the loop via method "int findColumn(String columnLabel)"
  and use the int variables with the rs.get...(int columnIndex) methods.
  This eliminates the call to findColumn(String columnLabel) for
  each value of every column for every row in the ResultSet.
  See also the example Java JDBC program on:
  https://www.monetdb.org/Documentation/SQLreference/Programming/JDBC

Warning:
 The current implementation of the MonetDB JDBC driver is *not*
 multi-thread safe. If your program uses multiple threads concurrently on
 the same Connection (so one MapiSocket), this may lead to incorrect behavior
 and results (due to race conditions).
 You will need to serialize the processing of the threads in your Java program.
 Alternatively you can use a separate JDBC Connection for each thread.

Note: as of Febr 2021 (monetdb-jdbc-3.0.jre8.jar) we compile all
 the java sources to target: Java SE 8 (profile compact2), so
 you need a JRE/JDK JVM of version 8 or higher to use it.