PHP PDO (MSSQL) can not get OUPUT parameters

Question

I'm trying to get OUTPUT using bindParam (PHP PDO). The PHP PDO library is the FreeTDS for MS SQL driver. Whatever I do, I can't seem to get the "OUTPUT" in the the bound params as suggested on php.net. I've verified I can call the EXEC and return a result set (using a select), but the OUTPUT parameters never change.

PHP Code. $this->db is a 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 test OUTPUT

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

Output from PHP:

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

error_num: 0
error_msg:

FlipperPA · Accepted Answer · 2015-11-10 22:57:20Z

4

AFAIK, the FreeTDS driver does not support OUTPUT parameters. I remember this from when my team was doing our assessment.

Here's why it doesn't work: http://www.freetds.org/faq.html#ms.output.parameters

EDIT: Here's a reference from pyodbc (which also runs on FreeTDS), so the same applies: https://code.google.com/p/pyodbc/wiki/StoredProcedures

To quote: "Results: Since we can't use output parameters at this point, you'll need to return results in a result set. Usually this means just ending your stored procedure with a SELECT statement."

You'll need to use a SELECT instead.

edited Nov 10, 2015 at 22:57

answered Nov 10, 2015 at 22:51

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

devlin carnate · Accepted Answer · 2015-11-09 21:01:37Z

1

Your stored procedure is assigning the parameters, but not returning them.

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

answered Nov 9, 2015 at 21:01

devlin carnate

8,6607 gold badges55 silver badges106 bronze badges

1 Comment

jjwdesign Over a year ago

That select will produce a result set that I can fetch, but it does not seem to change the values of the bound parameters.

Collectives™ on Stack Overflow

PHP PDO (MSSQL) can not get OUPUT parameters

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related