Jump to content

PDO Running a Stored Procedure on MSSQL


Recommended Posts

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!

 

Link to post
Share on other sites

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

 

Link to post
Share on other sites

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 :( 

Link to post
Share on other sites

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

Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.