SQLSRV & Stored Procedures

[stereoworld]

Ahoy,

I'm having a bit of trouble executing a stored procedure using PHP's SQLSRV package thing. It's pretty much just a simple select query, with the parameters being passed through a php (it's from a property search).

SqlSrv is a tough nut to crack as docuemtnation is either non existent, or there are a various different methods on how to execute a Stored Procedure.

Here's the one I've gone with:

` $bedrooms = '3';
$pageno = '1';
$branchID = '1844';
$postcode = 'BL1';
$mint = '0';
$maxt = '1000000000';

            $params = array( 
                             array($bedrooms, SQLSRV_PARAM_IN),
                             array($mint, SQLSRV_PARAM_IN),
                             array($maxt, SQLSRV_PARAM_IN),
                             array($postcode, SQLSRV_PARAM_IN),
                             array($pageno, SQLSRV_PARAM_IN),
                             array($branchID, SQLSRV_PARAM_IN)

                           );

            /* Execute the query. */
            $stmt = sqlsrv_query($conn, '{CALL PROPERTY_SEARCH_S(?,?,?,?,?,?)}', $params);
            if( $stmt === false )
            {
                 echo "Error in executing statement 3.\n";
                 die( print_r( sqlsrv_errors(), true));
            }


            while($row = sqlsrv_fetch_array($stmt)){    `

I've temporarily created the parameters at the top and then, from what I gather, I'm supposed to plonk the params in an array with a constant to define what direction it's going in (I guess!, as setting it to OUT messes it up).

It then executes the search and then goes on to start a loop.

HOWEVER, I get the following error from it, which I don't quite understand:

Error in executing statement 3. Array ( [0] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server] SELECT TOP 25 p.PriceText, ph.PHOTOURL, p.PropertyID, p.EstateAgentID, p.StreetAddress1, p.StreetAddress2, p.Description1, p.Town, p.Postcode, p.PriceText, p.Bedrooms, p.Kitchens, p.Bathrooms, p.Garage, p.Gardens, PT.PROPERTY_TYPE,0 AS PAGE_COUNT FROM dbo.PROPERTY P LEFT JOIN dbo.PROPERTY_PHOTOS PH ON P.PROPERTYID = PH.PROPERTYID AND PH.CATEGORY = 'PRIMARY' LEFT JOIN PROPERTY_TYPE PT ON P.PROPERTYTYPEID = PT.PROPERTY_TYPE_ID WHERE P.POSTCODE LIKE [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server] SELECT TOP 25 p.PriceText, ph.PHOTOURL, p.PropertyID, p.EstateAgentID, p.StreetAddress1, p.StreetAddress2, p.Description1, p.Town, p.Postcode, p.PriceText, p.Bedrooms, p.Kitchens, p.Bathrooms, p.Garage, p.Gardens, PT.PROPERTY_TYPE,0 AS PAGE_COUNT FROM dbo.PROPERTY P LEFT JOIN dbo.PROPERTY_PHOTOS PH ON P.PROPERTYID = PH.PROPERTYID AND PH.CATEGORY = 'PRIMARY' LEFT JOIN PROPERTY_TYPE PT ON P.PROPERTYTYPEID = PT.PROPERTY_TYPE_ID WHERE P.POSTCODE LIKE ) )

I'm not 100% what its telling me here. My guess is that the params aren't being passed through, otherwise something would appear next to penultimate closing bracket. The "[message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]" might be a clue, although again I can't quite work it out!

The database guy I deal with tells me he's tested the stored procedure out on his end without error, so it must be the scripting side.

Your help would be much appreciated!

Thanks,
Tom

[pritaeas]

There appears to be nothing after the LIKE. Are you sure your parameters are of the right type and in the right order ?

Other thing, "PROPERTY_TYPE" is the only table without dbo prepended. Not sure if it matters though. What happens if you try to execute the query? Does that work?

SqlSrv docs

[stereoworld]

Hi Pritaeas, thanks for the reply :)

I'm certain the Parameters are the right type, if I change, for instance, the postcode param from SQLSRV_PARAM_IN to SQLSRV_PARAM_OUT I get an error saying "The formal parameter "@postcode" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output". It's definitely picking it up as postcode, but not passing it through.

My Database friend set me up a test stored procedure with 0 parameters and that seems to throw no errors, so everythings definitely hooked up correctly.

[pritaeas]

Are you sure all the parameters are IN parameters. If one is INOUT, or OUT, you'll need to specify that.

[stereoworld]

Cheers for the reply..

Yup, I tested all the parameters with an OUT and INOUT and they all give me "The formal parameter...." errors. I've also tried not declaring an IN/OUT and it just gives the error as before, so by the looks of it I don't even need to declare a direction.

[pritaeas]

IN is the default, so you could even omit the parameter. Have you tried executing the stored procedure with a different tool?

Have you tried with mssql_ functions?

[stereoworld]

Hi Pritaeas,

For unknown reasons with the php version on our windows server, we can only use SQLSRV.

Right, it's working in a different way now. Upon advice, I've been told to Prepare, Execute then Echo.

But now I get an error which looks like:

Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -22 [code] => -22 [2] => There are no more rows in the active result set. Since this result set is not scrollable, no more data may be retrieved. [message] => There are no more rows in the active result set. Since this result set is not scrollable, no more data may be retrieved. ) )

I've searched the error, but have absolutely no idea what it means. No-one does.

My code is now:

            $params = array( 
                             array(&$place),
                             array(&$min),
                             array(&$max),
                             array(&$pageno),
                             array(&$town1),
                             array(&$town2),
                             array(&$town3),
                             array(&$town4),
                             array(&$town1),
                             array(&$town2),
                             array(&$town3),
                             array(&$town4),
                             array(&$town1),
                             array(&$town2)

                           );
            echo '<pre>' . print_r($params) . '</pre>';
            /* Execute the query. */
            //$stmt3 = sqlsrv_query( $conn, $tsql_callSP, $params);
            $tsql = '{CALL PROPERTYSEARCH_SEARCH_S(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}';
            $stmt = sqlsrv_prepare($conn, $tsql, $params);
            if( $stmt === false )
            {
                 echo "Error in executing statement 3.\n";
                 die( print_r( sqlsrv_errors(), true));
            }
            $rp = "";
            sqlsrv_execute($stmt);
            while($row = sqlsrv_fetch_array($stmt)){
                echo 'ello';
                echo '<br />';
            }               
            if( sqlsrv_fetch_array( $stmt ) === false ) {
                  die( print_r( sqlsrv_errors(), true));
            }

Your advice would be much appreciated :)

Tom