Bug 6787

Summary: COPY INTO ... ON CLIENT not working on Windows
Product: SQL Reporter: Fabio <palmaresk8>
Component: allAssignee: SQL devs <bugs-sql>
Status: NEW ---    
Severity: enhancement CC: martin.van.dinther
Priority: Normal    
Version: 11.33.11 (Apr2019-SP1)   
Hardware: Other   
OS: Windows   

Description Fabio 2019-11-07 12:37:22 CET
User-Agent:       Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.97 Safari/537.36
Build Identifier: 

The COPY ... ON CLIENT feature of the Apr2019 MonetDB release (11.33.3) is not working on Windows. Given the error message, it seens that the feature is not implemented. (Well, I'm guessing it's a Windows problem since I haven't tried it on Linux). The feature is not working even when the server is started at localhost.

Reproducible: Always

Steps to Reproduce:
1. Start MonetDB server (could be localhost or remote computer, it doesn't matter because the same apply to both situations).

2. Create some dummy table

CREATE TABLE xyz (name VARCHAR(255), age INTEGER);

3. Create some dummy file (myfile.txt)

name|age
Fabio|32
Pedro|14
Silva|80

4. Try to import data from file using COPY ... ON CLIENT

COPY 3 RECORDS OFFSET 2 INTO xyz 
FROM 'C:/Users/Public/Downloads/myfile.txt' ON CLIENT
USING DELIMITERS '|', '\n', '"' NULL AS '';


Actual Results:  
#client1:!ERROR:MALException:sql.copy_from:cannot transfer files from client

Expected Results:  
Table with values.
Comment 1 Sjoerd Mullender cwiconfidential 2019-11-07 12:55:06 CET
Which client are you using?
That is kind of important, since the feature requires support from both server and client program.
Comment 2 Fabio 2019-11-07 15:01:51 CET
Ok, this is a good question since I tried to replicate the issue using both DBeaver (using the JDBC Driver) and R (using the MonetDB.R driver) but not mclient. Surprisingly, when I tested the use of COPY ... ON CLIENT in mclient it worked fine both for localhost and for a remote server. Is this feature supposed to work only through mclient?

In DBeaver, when I'm at localhost and change the query from ON CLIENT to ON SERVER, the data is loaded into the table.

In R I tested this commands:

conn = dbConnect(
  drv = MonetDB.R::MonetDB.R(),
  host = "localhost",
  dbname = "ipea",
  port = 50000L,
  user = "monetdb",
  password = "monetdb",
  timeout = 86400L
)

dbSendQuery(conn, "CREATE TABLE xyz (name VARCHAR(255), age INTEGER);")

dbSendQuery(conn, 
  "COPY 3 RECORDS OFFSET 2 INTO xyz 
  FROM \'C:/Users/Public/Downloads/myfile.txt\' ON CLIENT
  USING DELIMITERS \'|\', \'\n\', \'\"\' NULL AS \'\';")

The same apply: if I change ON CLIENT to ON SERVER, the data is loaded into the table.
Comment 3 Sjoerd Mullender cwiconfidential 2019-11-07 15:08:52 CET
The feature has only been implemented in mclient.  It is possible to implement in other clients, but that hasn't happened as yet.
Comment 4 Martin van Dinther cwiconfidential 2019-11-07 18:38:44 CET
FYI: I have updated the documentation for COPY INTO commands on:
https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/CSV_bulk_loads
and
https://www.monetdb.org/Documentation/Manuals/SQLreference/SQLSyntaxOverview#COPY_INTO_FROM

It now includes information that the ON CLIENT option is currently only supported by mclient program and not supported in other programs or APIs (ODBC, JDBC, python DBI, etc.).
Comment 5 Martin van Dinther cwiconfidential 2019-12-11 19:28:59 CET
It is not a bug. Changed it to an enhancement request.