|Summary:||COPY INTO ... ON CLIENT not working on Windows|
|Component:||all||Assignee:||SQL devs <bugs-sql>|
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 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 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 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 2019-12-11 19:28:59 CET
It is not a bug. Changed it to an enhancement request.