mysql LOAD DATA LOCAL INFILE syntax error when executed via JDBC

Question

I am trying to load contents from a csv file a local using this sample query:

SET GLOBAL local_infile=1; LOAD DATA LOCAL INFILE 'C:/afc_orders.csv' INTO TABLE `afc_report`.`afc_orders` FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (`type_o`,`n_order`,`n_return`);

Executing the query from MySQL works perfectly but, when I try to execute the same query from JDBC, I get a syntax Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOAD DATA LOCAL INFILE 'C:/afc_orders.csv' INTO TABLE `afc_report`.`afc_orders` ' at line 1

Here is my code:

    Connection conn = null;

    String url = "jdbc:mysql://" + mysqlHost + ":" + mysqlPort + "/" + mysqlDatabase + "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";

    try {
        conn = DriverManager.getConnection(url, mysqlUser, mysqlPassword);

        Statement stmt = conn.createStatement();


        String sql = "SET GLOBAL local_infile=1; LOAD DATA LOCAL INFILE 'C:/afc_orders.csv' INTO TABLE `afc_report`.`afc_orders` FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n' (`type_o`,`n_order`,`n_return`);";
        System.out.println(sql);
        stmt.execute(sql);

    } catch(Exception e) {
       System.out.println(e.getMessage());
    }

And here is the driver I am using:

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.20</version>
    </dependency>

What am I missing?

Daniela Mogini · Accepted Answer · 2020-05-22 06:56:48Z

3

For future reference, I also had to add:

&allowLoadLocalInfile=true

to the connection string to avoid error:

ERROR 3950 (42000): Loading local data is disabled; this must be enabled on both the client and server side

answered May 22, 2020 at 6:56

Daniela Mogini

5394 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Rotteveel · Accepted Answer · 2020-05-21 18:07:44Z

2

Your code is attempting to execute two statements at once (the SET statement and the LOAD DATA statement), but a Statement.execute(...) only accepts a single statement, not multiple statements.

You could try to execute these statements separately (I don't normally use MySQL, so I'm not sure if that works in this case), or the MySQL Connector/J driver can be configured to allow multiple statements by adding the connection property allowMultiQueries=true in the connection string.

answered May 21, 2020 at 18:07

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

Collectives™ on Stack Overflow

mysql LOAD DATA LOCAL INFILE syntax error when executed via JDBC

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related