PDO Running a Stored Procedure on MSSQL

[Chris87]

Hi All,

I'm hitting brick walls while trying to run a stored procedure query with PDO (linking to MSSQL)

If I run the query from SSMS (SQL Server Management Studio) I get a result every time (one single row is returned, as expected) - I'm placing the same query into PHP without any dynamic variables etc - its just a straight query... when it runs the query in PDO it fails every time with this error:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[IMSSP]: The active result for the query contains no fields.' in index.php:195
Stack trace:
#0 index.php(195): PDOStatement->fetch(2)
#1 {main}
  thrown in index.php on line 195

The code is as follows:

	$SQLSTMTA="exec pEmployeeGetData @EmployeeID='1',@Year='2020'";
	$STHA = $DBH2->prepare($SQLSTMTA);
	//$STH->bindParam(':EMPID', $EmployeeID);
	$STHA->execute();
		while($result = $STHA->fetchAll()) {              //(row 195)
		var_dump($result);
	}

I've also tried a few variations on the above such as ->Fetch (as opposed to FetchAll) without any luck.

Any ideas on how I can get around this?

Thanks in Advance!

 

[Barand]

I don't have SQL Server but the MySQL equivalent using PDO is

$result = $db->query("call average_result(1, 2015)");
$row = $result->fetch();
echo "{$row['name']}<br>{$row['avresult']}%<br>";           // Adam Simms
                                                            // 77.0%

procedure:

CREATE PROCEDURE `average_result`(IN pupil INT, IN year INT)
BEGIN
	SELECT concat(fname, ' ', lname) as name
		 , ROUND(AVG(pcent),1) as avresult
	FROM result r
		 JOIN
		 pupil p ON r.pupilid = p.pupilid
	WHERE r.pupilid = pupil AND r.schoolyear = year;
END

 

[Chris87]

Thanks for the reply - I've tried using call from SSMS and it doesnt like that, so I think it needs to remain as 'exec'... i've also tried to capture the result like you have - removing the loop and having a single record..:

 

		$STHA->execute();
		$result = $STHA->fetch();
		var_dump($result);

 

But still no luck - it seems to throw an error at $STHA->fetch(); and again it's reporting no data returned.... but there is most definitely data returned from the query!

 

Since it appears to run the query and just simply doesn't return a result, i'm going to try and update another table from within the SP, and then run a separate query to simply read a table - I have no idea if it will work but I'm running short of ideas  

[Barand]

Perhaps

$STHA = $DBH2->query("exec pEmployeeGetData '1','2020' ");
$row = $STHA->fetch();

 

[Chris87]

Just tried that - The active result for the query contains no fields.

 

I'm ready to throw my laptop - this is one of those!  Not having much luck inserting into another table either..! 

[Chris87]

Got it! - with the help of this article... https://stackoverflow.com/questions/16735898/the-active-result-contains-no-fields-using-pdo-with-ms-sql

In the query - nocount had to be enabled (e.g. $DBH2->query("SET NOCOUNT ON; EXEC pEmplo...............") --- thats a bit of a strange one, because I had already enabled nocount against the database handler like this... $DBH2->query("SET NOCOUNT ON"); - but regardless of that, it had to be in the query statement itself.

I now get data! 

There is still a minor annoyance; If i make the parameters dynamic and un-comment the bindparam line as you can see in my example, it resorts to failing again. the variables are not user inputs and are safe to hard code, so that's what i've gone with for now but i'm still not happy with accepting that.... my next challenge - if anyone has any thoughts please let me know

 

Thanks for your help

[Barand]

There are two ways to avoid sql injection by separating the values from the text of the sql query

1. prepared statements
2. stored procedures

Both accomplish the same task. To prepare a stored procedure and bind the parameters seems like a "belt and braces" approach to me.

[kicken]

You can run procedures using either EXEC

$sql = "exec exampleProcedure :data";
$stmt = $db->prepare($sql);
$stmt->bindValue(':data', $data);
$stmt->execute();

or  ODBC style CALL

$sql = "{call exampleProcedure(:data)}";
$stmt = $db->prepare($sql);
$stmt->bindValue(':data', $data);
$stmt->execute();

I've done them both ways without issue.  

The SQLSRV driver by default runs all queries as a prepared query (even when not using ->prepare) which has the effect of isolating state between queries.  For example, you can't do something like:

$db->query('CREATE TABLE #tmp (Id INT);');
$stmt=$db->prepare('INSERT INTO #tmp (Id) VALUES (?)');
foreach ($list as $id){
   $stmt->execute([$id]);
}

You'll get an error on the INSERT that table #tmp does not exist.  This is probably why your isolated SET NOCOUNT ON query did not work.  I personally always put the SET NOCOUNT ON line inside the stored procedure itself, usually first line of the procedure.  This way, it will always be in effect when the procedure is run.