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

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.

July 4th, 2013 5:49am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics