I have acquire an existing project which has had to move servers. The new server was installed with a later version of PHP and as such this version does not support php_mssql so I have had to use php_sqlsvr.
I’ve converted most of the code as in most cases it was just a matter of changing the prefix in commands e.g. mssql_connect became sqlsvr_connect etc.
There is one bit of code that involves a stored procedure with a parameter that I’m struggling with as the is no equivalent to mssql_init and mssql_bind is as far as I can see now sqlsvr_prepare.
Here is my original code:
[php]//ORIGINAL CODE
$product = “X7RTUX3”;
$dbServerName = mssql_connect(“localhost”,“user”,“pwd”);
$db = @mssql_select_db(“DatabaseName”, $dbServerName);
$spname=“USP_Product”;
$sp = mssql_init($spname, $dbServerName);
mssql_bind($sp, “@product”, $product, SQLVARCHAR, false, false, 4000);
$data_values = mssql_execute($sp);
if($num_rows = mssql_num_rows($data_values)){
while ($data_row = mssql_fetch_array($data_values,MSSQL_ASSOC)){
$data[] = $data_row;
}
}
mssql_close($dbServerName);[/php]
And this is my attempt at converting…
[php]//CONVERTED CODE
$product = “X7RTUX3”;
//Define connection
$dbServerName = “localhost,1433”;
$connectionOptions = array(“Database”=>“DatabaseName”, “Uid”=>“user”, “PWD”=>“pwd”, “CharacterSet”=>“UTF-8”);
//Establishes the connection
$conn = sqlsrv_connect($dbServerName, $connectionOptions);
//Error handling
if($conn === false){
echo(“Could not connect to Database \n”);
die(print_r(sqlsrv_errors(), true));
}
//SP for Data
//this has required params so we need to define this as a transact-sql ‘call’ sp query and substiture (?) for any params
$call_spname="{call USP_Product(?)}";
//define parameter array for above sp
$params = array(array($product, SQLSRV_PARAM_IN));
//Executes the query
$sp = sqlsrv_prepare($conn, $call_spname, $params, array(“Scrollable”=>‘buffered’));
//Error handling
if ($sp === false) {
//die(FormatErrors(sqlsrv_errors()));
echo(“Query Statement could not be prepared \n”);
die(print_r(FormatErrors(sqlsrv_errors()), true));
}
$data_values = sqlsrv_execute($sp);
//Error handling
if ($data_values === false) {
echo("Query Execution failed: <br />");
die(print_r(sqlsrv_errors(), true));
}
//Check we have results
if($num_rows = sqlsrv_num_rows($data_values)){
while ($data_row = sqlsrv_fetch_array($data_values,SQLSRV_FETCH_ASSOC)){
$data[] = $data_row;
var_dump($data);
}
} else {
echo("We have no data
");
die(print_r(sqlsrv_errors(), true));
}
//close dbconnection
sqlsrv_close($conn);[/php]
When testing my converted code fails at the [php]if($num_rows = sqlsrv_num_rows($data_values))[/php] line saying that an Invalid parameter was passed to sqlsvr_num_rows
Any help appreciated
