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
echo "\
PDOStatement::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:
$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 "\
PDOStatement::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\
" and ENCLOSED BY “\”” LINES TERMINATED BY "\
" as well as the absence of these lines as shown above.
The database create function:
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
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