Pervasive SQL error: Invalid cursor state

I'm trying to INSERT a record into the Pervasive SQL using PHP PDO with ODBC, but keep getting this error:

GSS Query failed: SQLSTATE[07005]: Prepared statement not a cursor-specification: 0 [PSQL][ODBC Client Interface]Invalid cursor state. (SQLFetchScroll[0] at /home/abuild/rpmbuild/BUILD/php-7.4.30/ext/pdo_odbc/odbc_stmt.c:562)

I Googled for days and tried every possible solution including closeCursor() and SQL_CUR_USE_ODBC, but none of them worked.

$dsn_gss = 'odbc:Driver={Pervasive ODBC Interface};ServerName=192.168.175.254;dbq=GSS'; $dsn_gss_username = '*******'; $dsn_gss_password = '*******';

try {
    $dbh_gss = new PDO($dsn_gss, $dsn_gss_username, $dsn_gss_password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
} catch (PDOException $ex) {
    echo $ex->getMessage();
    exit;
}

try {
    $query_gss = "INSERT INTO ORDER_TEST (ORDER_NO_EXTERNAL, CUSTOMERNO, CUSTOMERNO, PARTNUMBER, QTY, PRICE) VALUES (?, ?, ?, ?, ?, ?)";
    $stmt_gss = $dbh_gss->prepare($query_gss);
    $stmt_gss->execute(array('SAMPLE_ORDER_1', '001000', '001000', '0025', 100, 2.3));
    $result_gss = $stmt_gss->fetchAll(PDO::FETCH_ASSOC);
    print_r($result_gss);
} catch(PDOException $ex) {
    print_r($ex->getMessage());
}

But when I remove PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, the INSERT works, however this is not acceptable, because I won't be able to catch any errors (even the successful insert doesn't produce any messages when PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION is removed).

Here is my /etc/odbcinst.ini:

[Pervasive ODBC Interface]
Driver=/usr/local/psql/lib/libodbcci.so
Driver64=/usr/local/psql/lib64/libodbcci.so

Please help. Thanks.

Solution

Looking at your code, I see that you've got a duplicate in the insert value list (CUSTOMERNO). In my testing, it's not the insert that is failing but the $stmt_gss->fetchAll(PDO:FETCH_ASSOC). You can't fetch right after an insert. Fetch is done after a SELECT, not an INSERT. Even the PDO documentation for the fetchAll shows an INSERT then a SELECT before the fetchAll. If I remove the following, the page works correctly in my testing:

$result_gss = $stmt_gss->fetchAll(PDO::FETCH_ASSOC);
print_r($result_gss);

I removed the print_r line since the $result_gss doesn't exist.