PHP ODBC Stored Procedure Invalid Parameter Number

4
votes

I am 100% brand new to this world of SQL/PHP/ODBC/FBI/TLA etc. so I apologize if what I am asking is incredibly basic.

I am using a stored procedure that uses a lat/long database of zip codes to take a central zipcode and a given mile radius as 2 input parameters, and then returns an array of zip codes that are within that given mile radius. It works perfectly when I run it in my SQL viewer, but when I try to use php to do the same, I only get invalid parameter errors.

$connstr = "Driver={SQL Server};Server=MyServer;Database=MyDatabase;";
$conn = odbc_connect($connstr, "Name", "PW");

$query_string = " CALL FindZipCodeWithinRadius(?,?)  ";

$sp = odbc_prepare($conn, $query_string);
$zipcodes = odbc_execute($sp,array(" 14602, 35"));

print_r($zipcodes);

When I run the code like this, I get the error "Not enough parameters (1 should be 2)"

I have tried different iterations of double quotes/single quotes around those input parameters, but they all either give me the above error, or this error:

"SQL error: [Microsoft][ODBC SQL Server Driver]Invalid parameter number, SQL state S1093"

A quick google search leads me to believe that the second error means that there are far too many parameters being read in to the proc, so how did I go from 1 to many while skipping the desired 2?

The database is on SQL 2000 if that makes a difference.

Any ideas? Thanks for any help you can provide.

2
phpstored-proceduresodbc
Please post your stored procedure. We could be here all day guessing what it requires. Did you try array("14602", 35)? - Ian Atkin

2 Answers

3
votes
$zipcodes = odbc_execute($sp,array(" 14602, 35"));

Should be

$zipcodes = odbc_execute($sp,array("14602", "35"));

In your execute you are passing 1 array value, " 14602, 35", and your prepared statement is looking for 2.

0
votes
Below is the Code to execute MS SQL Stored Procedure in PHP

$DBConnString="DRIVER={SQL Server};SERVER=localhost;DATABASE=ERPDev";
$DBUsername="sa";
$DBPswd="";
$DBConnect = odbc_connect($DBConnString, $DBUsername, $DBPswd);  

$Param1 = '2';
$query_string = "P_Test[$Param1]";
$ResultSet = odbc_prepare($DBConnect, $query_string);
odbc_execute($ResultSet);
// odbc_result_all($ResultSet);
while($obRows = odbc_fetch_object($ResultSet))
{
    print $obRows->UserId." - ";
    print $obRows->UserFirstName." - ";
    print $obRows->UserLastName."<br />";
}
odbc_free_result($ResultSet);
odbc_close($DBConnect);