Hi,

I post this to the Database forum as I suspect that it has something to do with MySQL or the SQL written?

I am using PDO as a driver and have been trying to troubleshoot why I canīt get "LOAD DATA INFILE" working.

I have paired it down into a very simple example and can even get that working. I try output errors using
PHP Code:
echo "\nPDOStatement::errorCode(): ";
print 
$this->o_Db->errorCode(); 
which generates a rather generic
PDOStatement::errorCode(): 42000
Error 42000 seems most often to be associated with invalid SQL but I have been very careful with the paths and the sql syntax, plus I escape (using ticks) all db field names.

It is setup like this:
PHP Code:
$csv_path_and_filename '/var/www/eprs/libs/page_queries/test_data.csv';
$temp_table_name $o_RandomString->getRandomString('__temp_'); // Generates a temporary table name like __temp_rjxrrhh

$sql=<<<EOL
         LOAD DATA INFILE "$csv_path_and_filename"
         REPLACE INTO TABLE 
$temp_table_name
         FIELDS TERMINATED BY ';'
         IGNORE LINES 1
         ( `date`, `name`)
EOL;
$result $this->o_Db->exec($sql);
echo 
"\nPDOStatement::errorCode(): ";
print 
$o_Db->errorCode(); 
You may notice that I escaped the database field names using ticks as 'date' is a reserved word in SQL. Also I did try the SQL using OPTIONALLY ENCLOSED BY "\"" LINES TERMINATED BY "\\r\\n" and ENCLOSED BY "\"" LINES TERMINATED BY "\\n" as well as the absence of these lines as shown above.

The database create function:
PHP Code:
function createTempTable($temp_table_name){ 
        
$sql "
            CREATE TABLE 
$temp_table_name (
              date DATE
              , name VARCHAR(150)
             ) TYPE=innodb;
      
$stmt = prepare($sql);
      
$results = $o_Db->exec(($sql);
      return 
$results;

My CSV contains two data rows plus a 'title' row
Code:
Date;Name
05/05/07;Bill
05/05/09;Mike
I have verified that the '/var/www/eprs/libs/page_queries/test_data.csv' file is located in that specified path on the server. I have also set the file and folder permissions to 0755 so that permissions should not be causing the problem

It is a MySQL 5.0.51a-24 version and you can see in the create statement that it is a INNODB table. The server is Centos Linux

Do you have any suggestions on what else to try?

I would appreciate your help on this.

Regards,
Steve