5

I am trying to get the return value from the SqlServer stored procedure. But it is giving syntax error in my Ubuntu Server which uses FreeTDS.

SQLSTATE[HY000]: General error: 20018 Incorrect 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 works fine in my windows machine. Any idea what is wrong in the code?

Beniston
  • 542
  • 1
  • 8
  • 17
  • Have you seen this? https://bugs.php.net/bug.php?id=58514 – jakub wrona Sep 23 '16 at 06:43
  • Yes, but I am not sure if these two are exactly the same.For me the exception is thrown, param is taken in the right place, but showing a syntax error while executing it. – Beniston Sep 23 '16 at 06:57
  • placeholders can only represent VALUES, not keywords/identifiers. – Marc B Sep 30 '16 at 20:51
  • @marcB Sorry I didn't get your point here. Here the place holders are being bound with the values only. Not keywords. – Beniston Oct 18 '16 at 08:07

1 Answers1

0

IIRC, bound parameters need to be parameters, not procedure names. Can you give this a test, replacing the first ? with the stored procedure name?

$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've played with PHP, but you might be able to do this if the first test works:

$stmt = $pdo->prepare('EXEC $retVal = GetMyCities_sp @StateID = ?, @TestData = ?');
FlipperPA
  • 13,607
  • 4
  • 39
  • 71