[SOLVED] Connect Microsoft Fabrics Warehouse from PHP (here using Doctrine DBAL) (solved)

Connect Microsoft Fabrics Warehouse from PHP (here using Doctrine DBAL) (solved)

My company is moving all our data to Microsoft Fabrics, which is great, however we have some PHP app who needs to access those datas.

All those apps are using Doctrine DBAL, and we can't find how to get the connection.

PHP 8.3.16 Doctrine DBAL : 4.2.2 ODBC DRIVER : 18 sqlsrv et pdo_sqlsrv drivers : 5.12

I'm using the following code to get the connection :

    $connectionParams = [
        'dbname' => 'My_DB_Name',
        'user' => 'xxxxx@xxxxxx.com',
        'password' => 'MyPassword',
        'host' => 'xxxxxxxxxxxxxx.datawarehouse.fabric.microsoft.com',
        'driver' => 'pdo_sqlsrv', //doesn't work with sqlsrv either
        'port' => 1433,
        'driverOptions' => [ // tried few additional parameters, but are not accepeted or working
            //'Authentification' => 'ActiveDirectoryPassword',
            //"Trustedconnection"=>"true"
            //'TrustServerCertificate' => true
        ]
    ];
    $this->conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams);

And this code to test

    try{
        $a = self::instance()->fetchAssociative('SELECT * FROM Production_Data');
        echo('OK<br>');
        Toolbox::var_dump($a,false,0);
    }catch(\Throwable $e){
        echo($e->getMessage());
    }

As a results, I have a blank page. No results at all, not even an error.... and 'OK
' doesn't show up.... We also tried a classic PDO connection, but doesn't work either. We an reach and query the warehouse correctly using SQL Server Management studio though.

Thank you for your help !

Solution

After several weeks of discussion with Microsoft, it appear that this is because Warehouse doesn't support Multiple Active Result Sets (MARS). Setting MultipleActiveResultSets=0 in option resolve the problem.

so, the final method for me was :

        $connectionParams = [
            'dbname' => 'my_DBname',
            'user' => xxx@xxx.com',
            'password' => 'mypassword',
            'host' => 'xxxxxxxxxxxxxx.datawarehouse.fabric.microsoft.com',
            'driver' => 'pdo_sqlsrv',
            'port' => 1433,
            'driverOptions' => [
                'Authentication' => 'ActiveDirectoryPassword',
                'MultipleActiveResultSets' => 0,
                'Encrypt' => 1,
                'TrustServerCertificate' => true
            ]
        ];
        $this->conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams);