PDO: Got error when exec 'DELETE' on something doesn't exist

[Gounlaf]

Hi,

I got a weird behavior when I try to delete something that doesn't exist (table exist, but the row I want to deleted doesn't), with pdo driver.
On PHP 5.6, there were no problem.

Bellow a test script and results obtained on this system:

PHP 7.0.15-0ubuntu0.16.04.4 (cli) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies
    with Zend OPcache v7.0.15-0ubuntu0.16.04.4, Copyright (c) 1999-2017, by Zend Technologies

$ pecl info sqlsrv
About pecl.php.net/sqlsrv-4.0.8
===============================
Release Type          PECL-style PHP extension (source code)
Name                  sqlsrv
Channel               pecl.php.net
Summary               Microsoft Drivers for PHP for SQL Server
                      (SQLSRV)
Description           The Microsoft Drivers for PHP for SQL Server are
                      PHP extensions that allow for the reading and
                      writing of SQL Server data from within PHP
                      scripts. The SQLSRV extension provides a
                      procedural interface while the PDO_SQLSRV
                      extension implements PDO for accessing data in
                      all editions of SQL Server 2005 and later
                      (including Azure SQL DB). These drivers rely on
                      the Microsoft ODBC Driver for SQL Server to
                      handle the low-level communication with SQL
                      Server.
                      *This package contains only the SQLSRV driver.*
Maintainers           Meet Bhagdev <[email protected]> (lead)
                      Jay Kint <[email protected]> (lead,
                      inactive)
                      Marie Barwin <[email protected]> (lead)
                      Hadis Kakanejadi Fard <[email protected]>
                      (lead)
Release Date          2016-12-19 13:12:21
Release Version       4.0.8 (stable)
API Version           4.0.8 (stable)
License               The MIT License (MIT)
                      (https://opensource.org/licenses/mit)
Release Notes         [Added]
                      - Unicode Column name support.
                      [Fixed]
                      - Fixed issue with buffered cursor in PDO_SQLSRV
                      driver when CharacterSet is UTF-8.
                      - Fixed issue with empty output parameters on
                      stored procedure.
                      - Fixed memory leaks in buffered queries.
Required Dependencies PHP version 7.0.0
                      PEAR installer version 1.4.0b1 or newer
                       Operating System
                      OS/Arch matching pattern '//'
package.xml version   2.0
Last Modified         2017-03-16 13:58
Previous Installed    - None -
Version



$ pecl info pdo_sqlsrv

About pecl.php.net/pdo_sqlsrv-4.0.8
===================================
Release Type          PECL-style PHP extension (source code)
Name                  pdo_sqlsrv
Channel               pecl.php.net
Summary               Microsoft Drivers for PHP for SQL Server
                      (PDO_SQLSRV)
Description           The Microsoft Drivers for PHP for SQL Server are
                      PHP extensions that allow for the reading and
                      writing of SQL Server data from within PHP
                      scripts. The SQLSRV extension provides a
                      procedural interface while the PDO_SQLSRV
                      extension implements PDO for accessing data in
                      all editions of SQL Server 2005 and later
                      (including Azure SQL DB). These drivers rely on
                      the Microsoft ODBC Driver for SQL Server to
                      handle the low-level communication with SQL
                      Server.
                      *This package contains only the PDO_SQLSRV
                      driver.*
Maintainers           Meet Bhagdev <[email protected]> (lead)
                      Jay Kint <[email protected]> (lead,
                      inactive)
                      Marie Barwin <[email protected]> (lead)
                      Hadis Kakanejadi Fard <[email protected]>
                      (lead)
Release Date          2016-12-19 13:12:19
Release Version       4.0.8 (stable)
API Version           4.0.8 (stable)
License               The MIT License (MIT)
                      (https://opensource.org/licenses/mit)
Release Notes         [Added]
                      - SQLSRV_ATTR_FETCHES_NUMERIC_TYPE attribute
                      support.
                      - Unicode Column name support.
                      [Fixed]
                      - Fixed precision issues when double data type
                      returned as strings using buffered queries.
                      - Fixed issue with buffered cursor in PDO_SQLSRV
                      driver when CharacterSet is UTF-8.
                      - Fixed segmentation fault in error cases when
                      error message is returned with emulate prepare
                      attribute is set to true.
                      - Fixed issue with empty output parameters on
                      stored procedure.
                      - Fixed memory leaks in buffered queries.
Required Dependencies PHP version 7.0.0
                      PEAR installer version 1.4.0b1 or newer
                       Operating System
                      OS/Arch matching pattern '//'
package.xml version   2.0
Last Modified         2017-03-16 13:58
Previous Installed    - None -
Version

<?php
define("DB_HOST", "127.0.0.1");
define("DB_PORT", "12345");
define("DB_NAME", "dbName");
define("DB_USER", "dbUser");
define("DB_PASS", "dbPass");

$sql = "DELETE FROM foo_table WHERE id = 42";
$sqlWithParameter = "DELETE FROM foo_table WHERE id = :id";
$sqlParameter = 42;


$dsn = 'sqlsrv:Server=' . DB_HOST . ',' . DB_PORT . ';Database=' . DB_NAME;
$options = array();
$dbh = new PDO($dsn, DB_USER, DB_PASS, $options);

echo 'PDO:sqlsrv' . PHP_EOL;

echo 'prepare, not args' . PHP_EOL;
$stmt = $dbh->prepare($sql);
var_dump($stmt->execute());
echo "errorCode: " . $dbh->errorCode() . PHP_EOL;
echo "errorInfo: " . print_r($dbh->errorInfo(), true);

echo 'prepare, with args' . PHP_EOL;
$stmt = $dbh->prepare($sqlWithParameter);
var_dump($stmt->execute(array(':id' => $sqlParameter)));

echo "errorCode: " . $dbh->errorCode() . PHP_EOL;
echo "errorInfo: " . print_r($dbh->errorInfo(), true);

echo 'direct exec' . PHP_EOL;
var_dump($dbh->exec($sql));

echo "errorCode: " . $dbh->errorCode() . PHP_EOL;
echo "errorInfo: " . print_r($dbh->errorInfo(), true);

PDO:sqlsrv
prepare, not args
bool(true)
errorCode: 00000
errorInfo: Array
(
    [0] => 00000
    [1] => 
    [2] => 
)
prepare, with args
bool(true)
errorCode: 00000
errorInfo: Array
(
    [0] => 00000
    [1] => 
    [2] => 
)
direct exec
bool(false)
errorCode: HY010
errorInfo: Array
(
    [0] => HY010
    [1] => 0
    [2] => [unixODBC][Driver Manager]Function sequence error
)

If you need more informations / test...

Thanks

[yukiwongky]

We are able to reproduce the issue on Linux; PHP7 on Windows do not seem to have the error. We will investigate on this and get back to you.

[yukiwongky]

The fix should be in the latest release (4.1.8-preview). Please check to see if your problem still exist.