PHP: LOAD DATA INFILE syntax errors

Question

I'm trying to import csv data into mysql using PDO and LOAD DATA INFILE (I've also tried LOAD DATA LOCAL INFILE) but I keep getting a syntax error and I have no idea why. The filepath and table names seem to be correct.

here is the import() function I am using -

function import() {

    $this->db = mydb::getConnection();


    // set the column names for the selected journal table
    if ($this->table = "bsp_journals") {
        $columns = "category, discipline, subject, sourcetype, issn, publicationname, indabstart, indabstop, fulltextstart, fulltextstop, fulltextdelay, peerreviewed";
    }



    try {

        $sql = "LOAD DATA LOCAL INFILE '$this->file'
            INTO TABLE '$this->table'
            FIELDS TERMINATED BY ','
            LINES TERMINATED BY '\\n'
            OPTIONALLY ENCLOSED BY '\"'
            ($columns)";

        $statement = $this->db->prepare($sql);

        $statement->execute();

        $this->return = $this->files['filename']['tmp_name'];

    } catch (PDOException $ex) {

        //throw $ex;
        $this->return = $ex->getMessage() . "<br /></br />" . $sql . "<br /></br />File Name = " . $this->file;

    }



    return $this->return;
}

below is the message I get including the mysql error message and the SQL query

- import result : SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''bsp_journals' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ' at line 2

LOAD DATA LOCAL INFILE 'files/buh-journals.csv' INTO TABLE 'bsp_journals' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' OPTIONALLY ENCLOSED BY '"' (category, discipline, subject, sourcetype, issn, publicationname, indabstart, indabstop, fulltextstart, fulltextstop, fulltextdelay, peerreviewed)

File Name = files/buh-journals.csv

Bill Karwin · Accepted Answer · 2013-07-08 21:47:16Z

7

INTO TABLE '$this->table'

Don't use single-quotes to quote a table name. Single-quotes are for string literals or date literals.
Either use no quotes, or else back-ticks for delimited identifiers.

INTO TABLE `$this->table`

Re your comment:

You apparently removed quotes from both the filename and the tablename. This is not what I meant. Just remove the string-quotes from the tablename. You do need them for the filename.

Example:

$sql = "LOAD DATA LOCAL INFILE '$this->file'
    INTO TABLE `$this->table`
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\\n'
    OPTIONALLY ENCLOSED BY '\"'
    ($columns)";

Review the syntax documented at http://dev.mysql.com/doc/refman/5.6/en/load-data.html

Notice the presence or absence of quotes around INFILE 'file_name' and INTO TABLE tbl_name:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name

You don't actually need the back-ticks around the table name, unless your table name contains special characters, whitespace, or reserved words.

You got this error:

General error: 2030 This command is not supported in the prepared statement protocol yet

Right, not all SQL commands are compatible with prepare(). I didn't check that for your case, because we were resolving the syntax error first. You can find a list of the commands that can be prepare()d under the heading SQL Syntax Allowed in Prepared Statements on this page: http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html

I linked to the MySQL 5.6 docs, but you should visit the docs for the version of MySQL you use. Because the list of compatible commands changes from release to release.

So you can't use prepare() -- you'll have to use exec() or query() instead for a LOAD DATA INFILE command.

Alternatively, if you're using PDO, you can set the attribute PDO::ATTR_EMULATE_PREPARES to true. That will make PDO fake out MySQL, so prepare() is a no-op and the query is actually sent during execute().

edited Jul 8, 2013 at 21:47

answered Jul 8, 2013 at 18:07

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

7 Comments

Shane Over a year ago

Thanks Bill. I've tried $this->table (with back-ticks around it) and $this->table without any quotes. I'm still getting the same error, just with back-ticks and with no quotes.

Shane Over a year ago

: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'files/buh-journals.csv INTO TABLE bsp_journals FIELDS TERMINATED BY ',' at line 1 LOAD DATA INFILE files/buh-journals.csv INTO TABLE bsp_journals FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' OPTIONALLY ENCLOSED BY '"' (category, discipline, subject, sourcetype, issn, publicationname, indabstart, indabstop, fulltextstart, fulltextstop, fulltextdelay, peerreviewed)

Shane Over a year ago

I don't seem to be able to format that in a comment, sorry for the mess

Shane Over a year ago

Thanks again Bill. Got nowhere with the back-ticks and decided to try entering the filename and tablename directly instead of with variables. I got an error saying "This command is not supported in the prepared statement protocol yet", which is at least more informative than a syntax error.

Shane Over a year ago

SQLSTATE[HY000]: General error: 2030 This command is not supported in the prepared statement protocol yet

|

Puya Sarmidani · Accepted Answer · 2015-04-02 14:23:58Z

0

There is a bug in PDO. I recommend to use mysqli for now.

answered Apr 2, 2015 at 14:23

Puya Sarmidani

3292 silver badges7 bronze badges

Collectives™ on Stack Overflow

PHP: LOAD DATA INFILE syntax errors

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related