onclient.txt

COPY ... ON CLIENT ... support in the MonetDB JDBC driver and JdbcClient program.

MonetDB provides the non-standard COPY INTO statement to perform bulk inserts and
retrievals, see also:
https://www.monetdb.org/documentation/user-guide/sql-manual/data-loading/copy-from/
https://www.monetdb.org/documentation/user-guide/sql-manual/data-loading/export_data/

By default, COPY INTO accesses files on the server but it also has a mode to
access files on the client.  This is supported by the command line tool
mclient(1) and now also as an extension to the MonetDB JDBC driver.

This is how it works: The JDBC client automatically announces that it is capable
of file transfers.  If you execute, for example,

	COPY INTO mytable FROM 'data.csv' ON CLIENT;

the server will send a request for file 'data.csv' to the JDBC driver.
By default, the JDBC driver will refuse with an error message:
	'No file upload handler has been registered with the JDBC driver'

or in JdbcClient:
	'Error [42000] when opening data.csv: ON CLIENT: No file upload handler has been registered with the JDBC driver'

This is for security reasons.  However, you can register a callback to handle
these requests from the server:

	Connection conn = DriverManager.getConnection(dbUrl, userName, password);
	MyUploader handler = new MyUploadHandler();
	conn.unwrap(MonetConnection.class).setUploadHandler(handler);

or provide the JdbcClient startup argument: --csvdir "/path/to/csvfilesdir"

Here, MyUploadHandler is an implementation of the interface MonetConnection.UploadHandler,
which signature looks like this:

	public interface UploadHandler {
		/**
		 * Called if the server sends a request to read file data.
		 *
		 * Use the given handle to receive data or send errors to the server.
		 *
		 * @param handle Handle to communicate with the server
		 * @param name Name of the file the server would like to read. Make sure
		 *             to validate this before reading from the file system
		 * @param textMode Whether to open the file as text or binary data.
		 * @param linesToSkip In text mode, number of initial lines to skip.
		 *                    0 means upload everything, 1 means skip the first line, etc.
		 *                    Note: this is different from the OFFSET option of the COPY INTO,
		 *                    where both 0 and 1 mean 'upload everything'
		 * @throws IOException when I/O problem occurs
		 */
		void handleUpload(Upload handle, String name, boolean textMode, long linesToSkip) throws IOException;

		/**
		 * Called when the upload is cancelled halfway by the server.
		 *
		 * The default implementation does nothing.
		 */
		default void uploadCancelled() {}
	}

In your implementation of handleUpload(), you can use the 'handle' object to
communicate with the server, for example:

- PrintStream handle.getStream() to obtain a stream object to which you can write.
  This is useful if you want to generate the data on the fly.

- void uploadFrom(InputStream stream) to have the JDBC driver read data from the
  stream and send it to the server as-is.  For text mode uploads this means the
  text must be UTF-8 encoded.

- void handle.uploadFrom(Reader reader) to have the JDBC driver read text from the given
  Reader and upload it.

- void handle.uploadFrom(BufferedReader reader, long linesToSkip) to have the JDBC
  driver read from the given BufferedReader and upload the text, skipping the first
  'linesToSkip' lines. Typically you would use the value passed to handleUpload in
  parameter 'linesToSkip'.

- void handle.sendError(String errorMessage) to refuse the upload.

See also:
 https://www.monetdb.org/hg/monetdb-java/file/tip/src/main/java/org/monetdb/jdbc/MonetConnection.java#l3546

If you use sendError() to refuse the upload, the COPY INTO statement will fail but
the connection will remain usable. On the other hand, if your implementation of
handleUpload throws an IOException, the connection will be closed because there
is currently no way to signal errors to the server once the transfer has begun.


The interface for DownloadHandler is:

	public interface DownloadHandler {
		/**
		 * Called if the server sends a request to write a file.
		 *
		 * Use the given handle to send data or errors to the server.
		 *
		 * @param handle Handle to communicate with the server
		 * @param name Name of the file the server would like to write. Make sure
		 *             to validate this before writing to the file system
		 * @param textMode Whether this is text or binary data.
		 * @throws IOException when I/O problem occurs
		 */
		void handleDownload(Download handle, String name, boolean textMode) throws IOException;
	}


Class org.monetdb.util.FileTransferHandler provides a default implementation
of both MonetConnection.UploadHandler and MonetConnection.DownloadHandler for
reading from and writing to local csv files.
You pass the FileTransferHandler constructor a directory name and a Charset to
specify the encoding used for the data in the csv files.
FileTransferHandler class is intended for situations where you do not need to
generate or transform data while uploading or downloading to a file.
See also:
 https://www.monetdb.org/hg/monetdb-java/file/tip/src/main/java/org/monetdb/util/FileTransferHandler.java#l29