SQLSTATE[IMSSP]: The active result for the query contains no fields

Question

I get the following error in my PHP script that uses PDO to execute some insert queries on SQL Server.

SQLSTATE[IMSSP]: The active result for the query contains no fields.

I don't use any stored procedures, and appending the query with

SET NOCOUNT ON

...doesn't help either.

The code seems to have inserted all the records as expected, but the error message bugs me.

Here is a simplified code, as requested...

<?php

    $pdo = new PDO('sqlsrv:Server=SVR;Database=app', 'app', 'pass', [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ]);

    try {
        $stmt = $pdo->prepare('SELECT id FROM nation');
        $stmt->execute();
        while ($result = $stmt->fetch(PDO::FETCH_COLUMN)) {
            $ch = curl_init();
            curl_setopt($ch, CURLOPT_URL, "http://somegame.com/api/nation/id=$result&key=myapikey");
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
            curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
            $nation = curl_exec($ch);

            $json = $nation;
            $nation = json_decode($nation, true);

            $stmt = $pdo->prepare("INSERT INTO nation_record(nation_id,as_on,json) VALUES (?,?,?,?)");
            $stmt->execute([ $result, date("Y-m-d"), $json ]);
        }
    } catch (PDOException $e) {
        api_log($pdo, $e->getMessage());
    }

    api_log($pdo, 'Completed successfully!');


    function api_log($pdo, $desc) {
        $stmt = $pdo->prepare("INSERT INTO api_log(calling_api, description) VALUES (?,?)");

        $stmt->execute([ 'myscript', $desc ]);
    }

Answer 1

Consider the following:

• The reason for your error is that you are using one variable $stmt for your SELECT and INSERT statements and after the first INSERT statement, while ($result = $stmt->fetch(PDO::FETCH_COLUMN)) ... generates the error. Use different variable for the INSERT statements.
• The INSERT statement has four parameter placeholders in prepare(), but only three values in execute().
• Use PDOStatement::fetchColumn to return one column in a row.

Code:

<?php

    ...
    while ($result = $stmt->fetchColumn(0)) {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, "http://somegame.com/api/nation/id=$result&key=myapikey");
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        $nation = curl_exec($ch);

        $json = $nation;
        $nation = json_decode($nation, true);
 
        $stmt2 = $pdo->prepare("INSERT INTO nation_record(nation_id,as_on,json) VALUES (?,?,?)");
        $stmt2->execute([$result, date("Y-m-d"), $json ]);
    }

...
?>