Running MSSQL Stored Procedures via ODBC (And getting the results back) with PHP

Question

My work is in the process of moving all of our PHP web applications off of our Windows production web server to a new Linux server environment specifically for PHP apps. The sticky part is, several of the PHP applications we're running are using MSSQL databases, and therefore all the DB connectivity has to be re-written to use unixODBC/FreeTDS since PHP on Linux doesn't support mssql_connect() and it's related functions.

I've gotten the connectivity down and basic queries execute fine. The problem I'm having is with running stored procedures, and replicating the mssql_init()/mssql_bind()/mssql_execute() functionality via ODBC.

The problem area I'm working on is the following:

$sp = mssql_init('sp_Search', $this->_link);
mssql_bind($sp, '@search', $this->_searchString, SQLVARCHAR);
$results = mssql_execute($sp);

I've read a bunch about the various ODBC functions and MSSQL functions. I've tried using the odbc_prepare()/odbc_execute() functions, to no avail (I always receive HY000 error codes from the SQL server). The closest I've come is this:

$results = odbc_exec($this->_link, "EXEC sp_Search @search='@this->_searchString'");

Running that query ("EXEC sp_Search @search='blah'") via the MSSQL Management Console works perfectly, but via PHP it seems to work, but if I try to fetch the rows from the result set, I get "No tuples available at this result index" errors. And odbc_next_result() returns false no matter what.

I also tried setting the cursor on the connection to SQL_CUR_USE_ODBC but that didn't help either. Does anybody have any experience doing this? Is it even possible, or is that functionality simply not available without the native MSSQL driver?

Edit:

Just to clarify, I have tried using odbc_prepare() and odbc_execute() as follows:

$results = odbc_prepare($this->_link, "{CALL sp_Search(?)}");
$params = array($this->_searchString);
odbc_execute($results, $params);

This results in the following error:

Warning: odbc_execute(): SQL error: Failed to fetch error message, SQL state HY000 in SQLExecute

Answer 1

try these:

Making a PHP Site on Linux Work with a Microsoft SQL Server Database
Accessing Microsoft SQL Server (mssql) from PHP under Apache on Unix or Linux

Answer 2

I got it working. Rather than calling the procedure like this:

$results = odbc_prepare($_link, "{CALL sp_Search(?)}");
odbc_execute($results, array($searchString));

I had to do it like this:

$results = odbc_prepare($_link, "{CALL sp_Search('$searchString')}");
odbc_execute($results, array());

I think this must be because the PHP ODBC driver doesn't have any equivalent to the mssql_bind() method, so using the ? method and then passing the parameters wasn't working, as the search string was never actually bound to the wildcard character. Doing it this way seems to be working fine, however!

I made a test stored procedure which didn't accept any parameters and verified that the PHP script was in fact getting the data back for that one. So I knew the problem was something with passing the parameters. From there, it was just a lot of trial and error, since there seems to be very little documentation on the subject (and none that recommends this method).