queryAll() failed if stored procedure returns nothing

Yii 1.1 General Discussion
1

I execute MSSQL stored procedure so:




$db = Yii::app()->db;

$command = $db->createCommand('exec :rc = get_names :id, :out');

$command->bindValue(':id', 7, PDO::PARAM_INT);

$command->bindParam(':out', $out, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 50);

$command->bindParam(':rc', $rc, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 1);

$rows = $command->queryAll();

This works well, but if stored procedure returns nothing I get exception:




CDbCommand failed to execute the SQL statement: SQLSTATE[IMSSP]: The active result for the query contains no fields.

How to fix this?

2

have you found solution for this?

3

check this, maybe it helps your problem,

http://www.yiiframework.com/forum/index.php?/topic/24147-trouble-reading-out-variable-from-mysql-sp/page__gopid__117684#entry117684

4

Hi All,

I found a solution. The problem is when the stored procedure does not use ‘SET NOCOUNT ON;’. All the operations in the stored procedure have results and maybe, the first one not have fields and it print something like ‘(1 row(s) affected)’.

For example, I wrote a stored procedure with many insert commands and at the end, it printed OK or an error code. From my model, I ran the sql statement using queryAll method. For each insert mssql return result saying ‘(1 row(s) affected)’ and no fields. I used ‘SET NOCOUNT ON;’ in the stored procedure and the only one result was OK or the error code.

Thank you all,

Diego

5

Thank you Diego, that has been driving me mad for the last 3 hours :) I’m awarding this post lots of stars :)

6

You are my hero!

thanks a lot!

7

Thanks Diego, very nice solution!

J.