CODEWITHSUNDEEP
phpsqlsql-serverstored-procedures
norgematos
norgematos

Reputation: 620

I can't get records from a stored procedure

I can't get records from a stored procedure. If I run from SQL Server, I get records. If I call it from .NET, I get records... but if I call it from PHP, I don't get records.

I am using PHP 5.3.3 on CentOS 6. I am using mssql. All others SP are ok.

I tried

$provider = 1010;
$array = array();
$stmt2 = mssql_init("[dbo].[PORA_sp_GET_LetterGenerationRecords]");
mssql_bind($stmt2, "@ProviderID", $provider, SQLINT4);
$letters = mssql_execute($stmt2);
while($row = mssql_fetch_assoc($letters)){
    $array[] = $row;
}
mssql_free_statement($stmt2);
echo '<pre>'; print_r($array); echo '</pre>';

and I have 

Array
(
)

I tried too

$array = array();
$letters = mssql_query('EXEC [dbo].[PORA_sp_GET_LetterGenerationRecords] @ProviderID = 1010');
while($row = mssql_fetch_assoc($letters)){
    $array[] = $row;
}
echo '<pre>'; print_r($array); echo '</pre>';

and I got 

Array
(
)

I included

echo 'MSSQL error: '. mssql_get_last_message();

and the answer:

MSSQL error:

I am very curious. Right now I am running the SP step by step from PHP. It's a shameful.

I tried:

    $provider = 1010;
    $array = array();
//    $letters = mssql_query('EXEC [dbo].[PORA_sp_GET_LetterGenerationRecords] @ProviderID = 1010');
    $stmt2 = mssql_init("[dbo].[PORA_sp_GET_LetterGenerationRecords]");
    mssql_bind($stmt2, "@ProviderID", $provider, SQLINT4);
    $letters = mssql_execute($stmt2);
    if($letters){
        echo mssql_num_rows($letters);
        while($row = mssql_fetch_assoc($letters)){
            $array[] = $row;
        }
        mssql_free_statement($stmt2);
        echo '<pre>'; print_r($array); echo '</pre>';
    }
    else{
        echo 'MSSQL error: '. mssql_get_last_message();

        echo error_get_last();
    }

and same answer.

Upvotes: 2

Views: 1806

Answers (2)

Esselans
Esselans

Reputation: 1546

<?php
   $query = "EXEC [dbo].[PORA_sp_GET_LetterGenerationRecords] @ProviderID = 1010";
   $result = mssql_query($query);
   if (!$result) {
       die('Invalid query: ' . mssql_error());
   }

   while ($row = @mssql_fetch_assoc($result)) {
        echo $row['col1']
   }
?>

Upvotes: 0

meda
meda

Reputation: 45500

Your code looks fine but for some reason the sql statement is being terminated.

So you need to debug this,I would suggest first try to get it to work with the parameter hard coded:

try {
    $array = array();
    $result = mssql_query('EXEC [dbo].[PORA_sp_GET_LetterGenerationRecords] @ProviderID = 1010');
    while ($row = mssql_fetch_assoc($result))
    {
        $array[] = $row;
    }
    var_dump($array);
} catch (Exception $e) {
    echo 'Caught exception: ',  $e->getMessage(), "\n";
    error_log($e->getMessage());// or log it to a txt file
    echo 'MSSQL error: '. mssql_get_last_message();
    echo 'Last error: '.error_get_last();

}

You should also run SQL profiler and filter on the exception to have a better idea.

Upvotes: 3

Related Questions