Converting from php_mssql.dll to php_sqlsvr.dll


#1

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


#2

A very very long time ago, I had a MS database that gave me a lot of trouble debugging errors. Back then, I was
moving from ASP to PHP and had similar problems. I do not remember everything I had to do, but, one thing is
that I found I needed to check everything at each step. Your code seems to do that, too. Except in one spot.
You are getting a row count before checking if there are rows. In PHP and MySQLi, or PDO, it will return zero for
empty row counts. But, in MsSQL, sometimes it will not do that. So, they created a checking function for it.
Here is a slight modification to your code to check for rows. Try it and let us know if it works for you:
[php]
//Check we have results
if(sqlsrv_has_rows( $data_values ) === true) {
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));
}
[/php]
Now, while researching that command which I had forgot, I came across another issue in the checks you use.
One big issue was variable types. It was mentioned that you should not use “===” to test if a function has
finished correctly. It seems that variable types can be cast incorrectly. The “===” says “throw ‘true’ if the
two are equal AND if they are the same type”… This can cause the test or compare error. In most places, it
seems they are saying to test it using this format: if (!$function_results) So, all of your checks should be
redone. But, not really sure which is best. I have seen both ways and my tests do not show much of a big
difference. If this really is a problem, then you would need to change line#3 to check this way:
[php]
if(sqlsrv_has_rows( $data_values )) {
[/php]


#3

Hi Ernie,
Thanks for your help… I’m using PHP 5.6.17 and when I try to use sqlsvr_has_rows I get an error PHP[6020] Fatal Error Call to undefined function sqlsvr_has_rows(). I tried both with and without the === true. My sqlsvr driver is php_sqlsvr_56_nts.dll :frowning:


#4

Well, my friend…

sqlsvr_has_rows() is NOT sqlsrv_has_rows()

LOL, you have flying fingers… (I do that myself a lot lately…)

if(sqlsrv_has_rows( $data_values ) === true) {


#5

Ernie

I did have if (sqlsvr_has_rows($data_values)… the problem I had was a missing ; not sure why it was giving an undefined function error. Anyways… I’ve corrected the code and now get to echo(“NO ROWS”) so my execution of the stored procedure is not returning any rows. It should return one row. The SP runs fine from SSMS. All I can think is the parameter is not being passed correctly.

The stored proc is called USP_Product and required a single parameter @product.


#6

Okay, the code is now working. This is why you got a result this time. So, now we just have to find out why
it is not returning the true results you are looking for… Looking over your stored procedure code I see a lot that
is not the same as samples I have researched.

Do you have a copy of the stored procedure to show us? Remove any passwords that might be built into it.
Then, we can see if your code is pulling the correct outputs from the database. You are getting close, I think!