How to run a stored procedure in an MS SQL DB from PHP using PDO on Windows Server

[bionic_25]

I am trying to call a stored procedure that accepts parameter inserts. The original query was being run from asp and looked like this:

EXEC [SearchForXML6b]
@sessionID = 973543,
@LineID = 892245,
@SortOrder = 'YearPublished DESC, SortTitle ASC',
@PageNumber = 1,
@RecordsPerPage = 20

I am redesigning the website in PHP, and am using the PDO method for querying the database. I am using a windows server, but have configured php to work on it, and can return simple selects from the db using pdo no problem.

From looking around for help for many hours, the accepted way to do what I need seems to be to do something like this:

$sql = "{:retval = CALL SearchForXML6b (@sessionID = 973543,
@LineID = 892245,
@SortOrder = 'YearPublished DESC, SortTitle ASC',
@PageNumber = 1,
@RecordsPerPage = 20)}";
$stmt = $this->c->prepare( $sql );
$stmt->bindParam('retval', $retval, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 4);

However this isn't working.

I have tried using EXEC instead of CALL but apparently PDO doesn't accept this.

I have heard talk of installing OBDC but it seems to be more of a LINUX focused driver. Does anyone know if it is possible to run stored procedures with my configuration?

Note: In SQL Server Management Studio, the SQL executes perfectly when using 'EXEC', but gives an error when trying to use 'CALL' as I don't think it's valid TSQL.

 

Thanks in advance 

[bionic_25]

Please can anyone help! 

[kicken]

$sql = "{:retval=CALL SearchForXML6b(73543,892245,'YearPublished DESC, SortTitle ASC',1,20)}";
$stmt = $this->c->prepare($sql);

$stmt->bindParam(':retval', $retval, PDO::PARAM_INT, 10);
$stmt->execute();

You don't need the @vars, just pass the values. You also don't want PARAM_INPUT_OUTPUT, giving a length parameter is enough to get the value back as output.

 

Are you using the PDO_SQLSRV driver? You should be if not.