SQL Server stored procedure call using PHP PDO from Lumen for return value gives syntax error

Question

I am trying to get the return value from a SqlServer stored procedure. But this gives a syntax error on my Ubuntu server that uses FreeTDS.

SQLSTATE [HY000]: General error: 20018 Invalid syntax near '0'. [20018] (severity 15) [(null)]

Below is my code:

$stateId = 1; $testData = 0; $retVal = 0; $pdo = DB::connection(env('DBCONNECTION'))->getPdo(); $stmt = $pdo->prepare('EXEC ? = GetMyCities_sp @StateID = ?, @TestData = ?'); $stmt->bindParam(1, $retVal, \PDO::PARAM_INT,20); $stmt->bindParam(2, $stateId, \PDO::PARAM_INT); $stmt->bindParam(3, $testData, \PDO::PARAM_INT | \PDO::PARAM_INPUT_OUTPUT, 20); $result_status = $stmt->execute(); $resultSet = $stmt->fetchAll(\PDO::FETCH_OBJ); print_r($resultSet); echo "<br />"; $stmt->nextRowset(); echo "Return value is ".$retVal; 

The same thing works fine on my windows machine. Any idea what is wrong with the code?

Answer 1

IIRC related parameters should be parameters, not procedure names. Can you give this test by replacing the first ? in the name of the stored procedure?

 $stmt = $pdo->prepare('EXEC your-proc-name = GetMyCities_sp @StateID = ?, @TestData = ?'); $stmt->bindParam(1, $stateId, \PDO::PARAM_INT); $stmt->bindParam(2, $testData, \PDO::PARAM_INT | \PDO::PARAM_INPUT_OUTPUT, 20); 

It has been a long time since I played with PHP, but you can do this if the first test works:

 $stmt = $pdo->prepare('EXEC $retVal = GetMyCities_sp @StateID = ?, @TestData = ?');