Laravel PDO::exec() expects parameter 1 to be string, object given

Question

I have this function and getting error PDO::exec():-

private function pdo()
{
    $path = File::get($_SERVER['DOCUMENT_ROOT'] . '\storage\uploadedReports\report.csv');
    try {
        $pdo = DB::connection()->getPdo();

        $loadCsv = $pdo->prepare("LOAD DATA LOCAL INFILE '" . $path . "'
                    INTO TABLE records 
                    FIELDS TERMINATED BY ',' enclosed by '\"' lines terminated by '\n' 
                    (percentage_artist_name, provider_name, sub_provider_name, @date, tune_code, tune_name,
                    category, sub_category, sub_type, downloads, total_revenue, @created_at, @updated_at)
                    SET `date` = date_format(@`date`,'%Y-%c-%d'), created_at = NOW(), updated_at = NOW()");

        $pdo->exec($loadCsv);
    } catch (\Exception $e) {
        dd($e->getMessage());
    }
}

How can I fix it?

Thanks in advance.

Welcome to StackOverflow. Can you please add more details to your post, since this is not a question? If you are not sure how to ask, see [Asking a good question](https://stackoverflow.com/help/how-to-ask) for more information. This will help us to understand you and your question/problem better. — Virginia, Dec 08 '18 at 15:53
I have a csv file in laravel project and the path "public\storage\uploadedReports\report.csv" and i want to load data infile instead of inserting records one by one so i use this code that is listed above and i got this error message via "catch dd" ==> "PDO::exec() expects parameter 1 to be string, object given" — Ahmad, Dec 08 '18 at 16:01
I presume you mean `$loadCsv->exec();` instead of `$pdo->exec($loadCsv);` but it doesn't make sense to *prepare* a statement that doesn't have any parameter. — Álvaro González, Dec 08 '18 at 16:24
if i change my code as you mention above: i got this error: "PDO::exec(): Error reading result set's header" — Ahmad, Dec 08 '18 at 16:32

dh117 · Answer 1 · 2018-12-08T21:38:28.367

1

The method $pdo->prepare returns a statement object. With this object you can call the execute method. In the execute method you can pass an array as a parameter, if query has parameter markups (? or :parameter_name).

Try this:

private function pdo()
{
    $path = File::get($_SERVER['DOCUMENT_ROOT'] . '\storage\uploadedReports\report.csv');
    try {
        $pdo = DB::connection()->getPdo();

        $loadCsv = $pdo->prepare("LOAD DATA LOCAL INFILE ?
                    INTO TABLE records 
                    FIELDS TERMINATED BY ',' enclosed by '\"' lines terminated by '\n' 
                    (percentage_artist_name, provider_name, sub_provider_name, @var1, tune_code, tune_name,
                    category, sub_category, sub_type, downloads, total_revenue, @created_at, @updated_at)
                    SET `date` = date_format(@`date`,'%Y-%c-%d'), created_at = NOW(), updated_at = NOW()");

        $loadCsv->execute([$path]);
    } catch (\Exception $e) {
        dd($e->getMessage());
    }
}

Hope this helps.

edited Dec 08 '18 at 21:38

answered Dec 08 '18 at 21:24

dh117

179
7

Thanks for your concern. No errors occurred, and it loads the page normally but it doesn't insert data into database table – Ahmad Dec 08 '18 at 21:59
Did you test the query in the MySQL console or tool? – dh117 Dec 09 '18 at 13:57
Does the `File::get()` method actually return the full path of the file? Only the `$_SERVER['DOCUMENT_ROOT'] . '/storage/uploadedReports/report.csv'` snippet would not be enough? – dh117 Dec 09 '18 at 14:07
From Mysql workbench side: using same query inserts data with date format error => inserting 0000-00-00 From Interface side: neither inserting records into database nor displaying any error – Ahmad Dec 09 '18 at 14:14
Make sure your MYSQL user configured on the PDO connection has LOAD DATA privileges. Also check the file permissions. Take a look at this [answer](https://stackoverflow.com/a/32354533/6548530) and the related question. – dh117 Dec 09 '18 at 17:56
Actually in laravel projects, it requires to add 'options' => [PDO::MYSQL_ATTR_LOCAL_INFILE=>true], in config/database.php and mysql is compatible with loading data infile – Ahmad Dec 09 '18 at 20:15

score 0 · Accepted Answer · answered Dec 09 '18 at 20:59

private function pdo()
{
    try {
        $path = base_path('public\storage\uploadedReports\report.csv');
        $path = str_replace('\\', '/', $path);

        $pdo = DB::connection()->getPdo();
        $pdo->exec("LOAD DATA LOCAL INFILE '" . $path . "' INTO TABLE records FIELDS TERMINATED BY ',' enclosed by '\"' lines terminated by '\\n' (percentage_artist_name, provider_name, sub_provider_name, @`date`, tune_code, tune_name, category, sub_category, sub_type, downloads, total_revenue, @created_at, @updated_at) SET `date` = NOW(), created_at = NOW(), updated_at = NOW()");
    } catch (\Exception  $e) {
        dd($e->getMessage());
    }
}

Laravel PDO::exec() expects parameter 1 to be string, object given

2 Answers2