msphpsql: HY010 [unixODBC][Driver Manager]Function sequence error when updating/deleting 0 rows

Driver version or file name

About pecl.php.net/pdo_sqlsrv-4.3.0
===================================
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 2008 R2 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 <meetb@microsoft.com> (lead)
                      Jay Kint <jaykint@microsoft.com> (lead,
                      inactive)
                      Marie Barwin <v-mabarw@microsoft.com> (lead)
                      Hadis Kakanejadi Fard <v-hakaka@microsoft.com>
                      (lead)
Release Date          2017-07-05 14:25:43
Release Version       4.3.0 (stable)
API Version           4.3.0 (stable)
License               The MIT License (MIT)
                      (https://opensource.org/licenses/mit)
Release Notes         [Added]
                      - Added Unicode Column name support (issue
                      #138).
                      - Support for Always On Availability groups via
                      Transparent Network IP Resolution (TNIR)
                      - Added support for sql_variant data type with
                      limitation (issue #51 and issue #127)
                      - Support drivers on Debian Jessie (tested on
                      Debian 8.7)
                      - Connection Resiliency support in Windows
                      - Connection pooling support for Linux and macOS

                      - Support for Mac(El Capitan and above)
                      - Azure Active Directory Authentication with
                      ActiveDirectoryPassword and SqlPassword
                      [Fixed]
                      - Fixed PECL installation errors when PHP was
                      installed from source (issue #213).
                      - Fixed segmentation fault with
                      PDOStatement::getColumnMeta() when the supplied
                      column index is out of range (issue #224).
                      - Fixed the assertion error (Linux) when
                      fetching data from a binary column using the
                      binary encoding (issue #226).
                      - Fixed issue output parameters bound to empty
                      string (issue #182).
                      - Fixed issue with
                      SQLSRV_ATTR_FETCHES_NUMERIC_TYPE when column
                      return type is set on statement (issue #173).
                      - Fixed a memory leak in closing connection
                      resources.
                      - Fixed load ordering issue in MacOS (issue
                      #417)
                      - Added a workaround for a bug in unixODBC 2.3.4
                      when connection pooling is enabled.
                      - Fixed the issue with driver loading order in
                      macOS
                      - Fixed null returned when an empty string is
                      set to an output parameter (issue #308).
                      - Fixed incorrectly binding of unicode parameter
                      when emulate prepare is on and the encoding is
                      set at the statement level (issue #92).
                      - Fixed binary column binding when emulate
                      prepare is on (issue #140).
                      - Improved performance by implementing a cache
                      to store column SQL types and display sizes
                      (issue #189).
                      - Fixed issue with the unsupported attribute
                      PDO::ATTR_PERSISTENT in connection (issue #65).
                      - Fixed the issue with executing DELETE
                      operation on a non-existent value (issue #336).
                      - Fixed incorrectly binding of unicode parameter
                      when emulate prepare is on and the encoding is
                      set at the statement level (issue #92).
                      - Fixed wrong value returned when fetching
                      varbinary value on Linux (issue #270).
                      - Fixed binary data not returned when the column
                      is bound by name (issue #35).
                      - Fixed exception thrown on closeCursor() when
                      the statement has not been executed (issue
                      #267).
                      [Limitation]
                      - Limited support for inout / output params when
                      using sql_variant type
                      [Known Issues]
                      - When pooling is enabled in Linux or MAC
                        - unixODBC 2.3.4 or prior (Linux and MAC)
                      might not return proper diagnostics information,
                      such as error messages, warnings and informative
                      messages
                        - due to this unixODBC bug, fetch large data
                      (such as xml, binary) as streams as a
                      workaround. See the examples
                      (https://github.com/Microsoft/msphpsql/wiki/Connection-Pooling-on-Linux-and-Mac)
Required Dependencies PHP version 7.0.0
                      PEAR installer version 1.4.0b1 or newer
                      OS/Arch matching pattern '//'
package.xml version   2.0
Last Modified         2018-03-03 14:08
Previous Installed    - None -
Version

SQL Server version

SQL Server 10.50.6220 (2008r2)

Client operating system

Debian 8¨

PHP version

7.0.27-1~dotdeb+8.1

Problem description

It seems, that issue #336 has not been fixed, because I can reproduce the same behavior in the 4.3.0 version. When executing UPDATE or DELETE through PDO with WHERE condition filtering out 0 rows (thus not updating anything), HY010 [unixODBC][Driver Manager]Function sequence error is raised.

Expected behavior and actual behavior

Assume this table with only one row:

Table test1
+----+------+
| id | name |
+----+------+
|  1 | test |
+----+------+

When running UPDATE test1 SET name = ‘updated’ WHERE id = 5;

HY010 [unixODBC][Driver Manager]Function sequence error is raised. No error should be raised. Same error is raised, when trying to delete non existent row.

Repro code

SQL

CREATE TABLE [dbo].[test1](
	[id] [int] NOT NULL,
	[name] [nvarchar](50) NOT NULL
);

PHP:

$pdo = new \PDO('sqlsrv:server=<server addr>;database=<db name>', '<username>', '<password>);
$result = $pdo->exec("UPDATE test1 SET name = 'updated' WHERE id = 5");
var_dump($result); // bool(false)
var_dump($pdo->errorCode()); // string(5) "HY010"
var_dump($pdo->errorInfo()); /*
array(3) {
  | [0]=>
  | string(5) "HY010"
  | [1]=>
  | int(0)
  | [2]=>
  | string(49) "[unixODBC][Driver Manager]Function sequence error"
  | }
*/

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 18 (9 by maintainers)

Most upvoted comments

So great !!! Thank you, it works like a charm so far. The issue can be closed.

younessadmi on Feb 27, 2019

@younessadmi I did not spend time trying different unixODBC versions. However, fyi unixODBC 2.3.7 is available with ODBC 17.3.1.1.

yitam on Feb 26, 2019

@yitam , thanks for your answer.

I actually find the real issue. Indeed, when I use the following code, it works like a charm :

$pdo = new \PDO('sqlsrv:Server=xx.xx.xx.xx;Database=xxxxx', 'xxxxx', 'xxxxx');
$attr = $pdo->getAttribute(constant('PDO::ATTR_CLIENT_VERSION'));
$version = substr($attr['DriverVer'], 0, 2);
var_dump($version);
var_dump($pdo->getAttribute( \PDO::ATTR_CLIENT_VERSION ));
 $sql = '
            DELETE FROM AddLinkLogInsert
            WHERE Key = 9999999999
 ';
$pdo->query($sql);

But my problem comes when I use Zend Framework. It’s actually the case when I try the following code :

$sm = $this->getServiceLocator();
/** @var Zend\Db\Adapter\Adapter $adapter */
$adapter = $sm->get('xxxxx');
$sql = '
            DELETE FROM AddLinkLogInsert
            WHERE ContenuKey = 9999999999
 ';
$adapter->query($sql);

I’ill try to resolve the issue then come back to you.

younessadmi on Feb 20, 2019