CODEWITHSUNDEEP
phpsql-serverstored-procedurespdo
Jason 'Jaypoc' Bauman
Jason 'Jaypoc' Bauman

Reputation: 536

MSSQL procedure does not complete from PHP

PHP: 5.4.20 Windows
Server: Microsoft SQL Server 2012

I am running into an issue where the following stored procedure does not run in its entirety when called from a PHP script (but it does run, with no errors returned):

CREATE PROCEDURE main_procedure (@uid int)
AS  
BEGIN
  DECLARE @NOW DATETIME
  SET @NOW = getDate()

  DELETE FROM metrics WHERE uid = @uid AND timestamp < @NOW
  EXEC update_metric_a @uid, @NOW
  EXEC update_metric_b @uid, @NOW
  EXEC update_metric_c @uid, @NOW
  EXEC update_metric_d @uid, @NOW
  EXEC update_metric_e @uid, @NOW
  EXEC update_metric_f @uid, @NOW
  EXEC update_metric_g @uid, @NOW
  EXEC update_metric_h @uid, @NOW
  EXEC update_metric_i @uid, @NOW
END

The function is called via: "EXEC main_procedure 1234" where 1234 is a user ID to pass to the individual update_metric functions. Each individual update_metric_n procedure adds a record for each metric, then parses through the past 12 months to populate the row with a month-over-month breakdown.

When called from Microsoft SQL Management Studio, the procedure executes all steps correctly.

When called from PHP, it runs all but the last procedure.

The others all run correctly, but for some reason the last one doesn't run. Re-ordering the procedures confirms that the issue is not an error in the last procedure as it runs fine when moved to the top of the list.

Here's my PHP that executes the procedure:

$conn = new PDO("sqlsrv:Server=######; Database=#####;", $user, $pass);
$conn->prepare("EXEC main_procedure ?");
$conn->execute(array("1234"));

Any thoughts on why this would stop after the eighth procedure, but only when executed via PHP?

Upvotes: 0

Views: 1522

Answers (1)

meda
meda

Reputation: 45490

Try this approach, not significantly different but worth a try.

Not sure if this is an typo but execute() method is available only on the statement objects, which is returned by prepare().

try {
    $conn= new PDO("sqlsrv:Server=######; Database=#####;", $user, $pass);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("EXEC dbo.main_procedure @uid = ?");
    $stmt->execute(array("1234"));
} catch (PDOException $e) {
    echo 'PDO failed: ' . $e->getMessage();
}

Upvotes: 1

Related Questions