PHP DBLIB + Multiple Result Sets (SQL Server)?

Question

Yii

There is a task in which you need to connect to SQL Server 2005 from under php with the Yii framework and execute a procedure that returns several result sets at once (2 selects in the procedure).
The first part of the task was successfully solved, the FreeTDS + dblib bundle was used to connect.
We managed to receive and write data, albeit with certain difficulties (encoding).
There were problems with the second task. To get multiple datasets in php, it's not enough just to execute a query and loop through it. In this case, it will return only the first set of data.
It is necessary after reading each set to go to the next with: But the problem is that dblib does not have such functionality, and when this function is executed, an Exception is thrown:
$command->query()->nextResult(); //yii method<br>

Driver does not support this function: driver does not support multiple rowsets

Here is what the author says about it:

Support for multiple result sets will be added as soon as I (or someone
else) get the time to do so.

This post is from 2007. This functionality has not yet been supported.
I started digging towards other drivers for MSSQL, there is a solution with ODBC, but unfortunately it does not work either, because. Yii doesn't have it's support in AR:

CDbConnection does not support reading schema for odbc database.

In general, I have another option, this is to look towards the Windows machine, with the Sqlsrv driver from Microsoft, but these are quite drastic measures.
I can't change the procedure itself. This is the core of someone else's system, to which I only have read-only access.
Has anyone encountered a similar problem and knows how to solve it? It may be possible to somehow discard the sets and get only one, or immediately place them in variables, etc.

Answer 1

In general, the problem is not solved, but moved a little further.
Perhaps this information will be useful to someone:
To teach Yii to work through ODBC with mssql, it is enough to specify the following in the database connection configuration:

#protected/config/main.php
'db'=>array(
                        'class'=>'CDbConnection', 
      'connectionString' => 'odbc:your dsn', 
                        'charset'=>'utf8',
                        'username'=>'username',
                        'password'=>pass',
                        'driverMap' => array(
                            'odbc' => 'CMssqlSchema'
                        )
    ),

Thus, we will tell Yii that the database is hidden behind ODBC, to which you need to connect using the CMssqlSchema driver.
But for some reason it doesn’t work with several result sets, the PDOStatement::nextRowset ( ) function (or its Yii analogue CDbDataReader::nextResult()) returns false (i.e. there are no next results)
At the same time, the sqlsrv driver. dll (which only works under Windows) handles this just fine.

Answer 2

I will continue to present my findings:
While browsing the Internet, I came across a very interesting thread:
forums.famillecollet.com/viewtopic.php?id=434
In particular, it discusses the lack of support for nextRowset() in the PDO_DBLIB driver. The result of the topic is that in php 5.4 all the same, this function was added.
“Oh thank the Gods,” I thought, and began to test (just in the morning we updated the php version to 5.4)
But it wasn’t there ... The procedure we need returns 3 result sets, in the first two there is a scalar, in the third table and exactly she is what I need.
In general, it returned the first 2 results (scalar), and instead of the third result (table), it output an empty array.
Actually the question is, what the hell, is it mocking me or what?

Answer 3

If someone follows in my footsteps, then I’ll add the following:
I just didn’t succeed in using the nextRowset () function from PDO_DBLIB, the database returned the answer:

SQLSTATE[HY000]: General error: 7405 General SQL Server error: Check messages from the SQL Server [7405] (severity 16)

The solution to this problem is to run the following command:

 Yii::app()->db->createCommand('SET QUOTED_IDENTIFIER ON; SET ANSI_WARNINGS ON')->execute();

But this does not actually solve the main problem, the rest of the result sets do not come into the program.

Answer 4

In general, together with our admin, we solved the problem. The FreeTDS + ODBC bundle turned out to be working.
To do this, I had to update freetds to the latest version 0.91 (rebuilding from source).
Having collected, freetds sat down not in the standard paths, and therefore I had to correct the ODBC config:

#/etc/odbcinst.ini
[FreeTDS]
Driver          = /usr/local/lib/libtdsodbc.so
Setup           = /usr/local/lib/libtdsodbc.so
CPTimeout               =
CPReusage               =
FileUsage               = 1
TDS Version             = 8.0
UsageCount              = 2

Well, just for reference, the config for connecting to MS SQL Server via ODBC:

#/etc/odbc.ini
[MS]
Driver = FreeTDS
Description = production_db
Trace = No
server = 
Port = 1433
Database = 
User = 
Password = 
TDS_Version = 8.0

In Yii itself, then we connect to ODBC like this:

#protected/config/main.php
    'db'=>array(
      'connectionString' => 'odbc:MS', //MS это идентификатор конфигурации который мы задали в /etc/odbc.ini
                        'username'=>'логин',
                        'password'=>'пасс',
                        'driverMap' => array(
                            'odbc' => 'CMssqlSchema'
                        )
    ),

However, now we have caught another problem: encoding.
At the very beginning, there were also problems with it, but they were solved by iconv (). Now everything that comes from the base in Cyrillic is displayed in gibberish. Moreover, it is impossible to understand what is wrong with the encoding, the output is something like this:

'???????\0xР№yв„–\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0'

Answer 5

Interesting writing.
Isn't FreeTDS single threaded? In the sense that it cannot perform multiple requests at the same time.
You can work with it directly, bypassing the ODBC layer.