Idle Connection Resiliency doesn't work with Connection Pooling

[yukiwongky]

Driver version or file name

5.2.0-rc

SQL Server version

SQL Server 2016

Client operating system

Windows 10

PHP version

7.2.1

Microsoft ODBC Driver version

ODBC17 preview

Table schema

(c1 INT, c2 VARCHAR(40))

Problem description

With connection pooling enabled, connection is not resumed after it is broken.

Expected behavior and actual behavior

Connection should resume after it is broken.

Repro code

function BreakConnection( $conn, $conn_break )
{
    $stmt1 = sqlsrv_query( $conn, "SELECT @@SPID" );
    if ( sqlsrv_fetch( $stmt1 ) )
    {
        $spid=sqlsrv_get_field( $stmt1, 0 );
    }

    $stmt2 = sqlsrv_prepare( $conn_break, "KILL ".$spid );
    sqlsrv_execute( $stmt2 );
    sleep(1);
}

// create a connection for create the table and breaking other connections
$conn_break = sqlsrv_connect( $server, array( "Database"=>$dbName, "UID"=>$uid, "PWD"=>$pwd) );

$tableName = "test_connres";
$sql = "CREATE TABLE $tableName ( c1 INT, c2 VARCHAR(40) )";
$stmt = sqlsrv_query( $conn_break, $sql );

$sql = "INSERT INTO $tableName VALUES ( ?, ? )";
    for( $t = 200; $t < 209; $t++ ) 
    {
        $ts = substr( sha1( $t ),0,5 );
        $params = array( $t,$ts );
        $stmt = sqlsrv_prepare( $conn_break, $sql, $params );
        sqlsrv_execute( $stmt );
    }

// first connection
$connectionInfo = array( "Database"=>$dbName, "UID"=>$uid, "PWD"=>$pwd, "ConnectionPooling"=>true,
                         "ConnectRetryCount"=>10, "ConnectRetryInterval"=>10 );
                         
$conn = sqlsrv_connect( $server, $connectionInfo );

BreakConnection( $conn, $conn_break );

$stmt = sqlsrv_query( $conn, "SELECT * FROM $tableName", array(), array( "Scrollable"=>"buffered" ) );
if( $stmt === false )
{
    echo "Error in connection 1.\n";
    print_r( sqlsrv_errors() );
}
else
{
    echo "Connection 1 resumes successfully.\n";
    $rowcount = sqlsrv_num_rows( $stmt );
    echo $rowcount." rows in result set.\n";
}

sqlsrv_close( $conn );

// second connection
$conn = sqlsrv_connect( $server, $connectionInfo );

BreakConnection( $conn, $conn_break );

// connection fails to resume here if connection pooling is enabled 
$stmt2 = sqlsrv_query( $conn, "SELECT * FROM $tableName" );
if( $stmt2 === false )
{
    echo "Error in connection 2.\n";
    print_r( sqlsrv_errors() );
}
else
{
    echo "Connection 2 resumes successfully.\n";
    $rowcount = sqlsrv_num_rows( $stmt2 );
    echo $rowcount." rows in result set.\n";
}

sqlsrv_close( $conn );

[david-puglielli]

Fixed as of ODBC driver 17.1. Closing the issue.