PHP PDO (MSSQL) cannot get OUPUT parameters

Question

I am trying to get OUTPUT using bindParam (PHP PDO). PHP PDO library is a FreeTDS driver for MS SQL. No matter what I do, I cannot get "OUTPUT" in the associated parameters, as suggested on php.net. I checked that I can call EXEC and return the result set (using select), but the OUTPUT parameters never change.

PHP code. $ this-> db - PDO object

$stmt = $this->db->prepare("EXEC ".$this->db_schema."[".$this->procedure."] :error_num, :error_msg"); $error_num = 0; $error_msg = ''; $stmt->bindParam(':error_num', $error_num, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT); $stmt->bindParam(':error_msg', $error_msg, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 2000); $stmt->execute(); var_dump($stmt); echo "\nerror_num: $error_num"; echo "\nerror_msg: $error_msg\n\n"; 

Stored Procedure for OUTPUT Test

 USE [NGCustom] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [web].[addTest] ( @error_num int OUTPUT, @error_msg VARCHAR(MAX) OUTPUT ) AS BEGIN SET @error_num = 99 SET @error_msg = 'Error! Oh my gosh!' END GO 

Exiting PHP:

 object(PDOStatement)#77 (1) { ["queryString"]=> string(54) "EXEC [NGCustom].[web].[addTest] :error_num, :error_msg" } error_num: 0 error_msg: 

Answer 1

AFAIK, the FreeTDS driver does not support OUTPUT parameters. I remember this when my team made its assessment.

This is why this does not work: http://www.freetds.org/faq.html#ms.output.parameters

EDIT: Here's the link from pyodbc (which also works on FreeTDS), so the same goes for: https://code.google.com/p/pyodbc/wiki/StoredProcedures

Quote: “Results: since we cannot use the output parameters at this point, you will need to return the results in the result set. Usually this means that you end your stored procedure with the SELECT statement.”

Instead, you need to use SELECT.

Answer 2

Your stored procedure assigns parameters but does not return them.

 BEGIN SET @error_num = 99 SET @error_msg = 'Error! Oh my gosh!' SELECT @error_num, @error_msg; --add this line END GO