sqlsrv_execute Errors With PHP Errors and Stored Procedures

Question

I am working on migrating our data from using inline SQL to using Stored Procedures. I am hitting a snag when running my code and getting the errors shown below:

Warning: sqlsrv_execute() expects parameter 1 to be resource, bool given in ajax_functions1.live.php on line 285

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, bool given in ajax_functions1.live.php on line 296

Below is an excerpt of the code:

function db_add_user($email, $provider, $subuid,$data){
   include 'config.php';
   $email = $data['preferred_username'];
 
    $params = array(
        array(&$myparams['email'], SQLSRV_PARAM_IN),
        array(&$myparams['provider'], SQLSRV_PARAM_IN)
    );

    $sql = "{EXEC [dbo].[getSubUniqueID_HPCreate] @email = ?, @provider = ?}";
    $stmt = sqlsrv_prepare($conn, $sql, $params);
    sqlsrv_execute($stmt);

 $uid=null;

if($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC) > 0) {
    $uid = $row[0]['sub_unique_id'];
}else{
            $params = array(
                array(&$myparams['email'], SQLSRV_PARAM_IN),
                array(&$myparams['provider'], SQLSRV_PARAM_IN),
                array(&$myparams['subuid'], SQLSRV_PARAM_IN)
            );
                    
            $sql = "{EXEC [dbo].[insertSubUniqueID_HPCreate] @email = ?, @provider = ?, @subuid = ?}";
            $stmt = sqlsrv_prepare($conn, $sql, $params);
            sqlsrv_execute($stmt);

        }
return $uid;

}

Here is the SP dbo.getSubUniqueID_HPCreate

USE [homepages_config]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getSubUniqueID_HPCreate]
    @email nvarchar(256),
    @provider nvarchar(64)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT sub_unique_id FROM homepages_config.dbo.users_providers
    WHERE email = @email AND provider_id = @provider;

END

Here is the SP dbo.insertSubUniqueID_HPCreate

USE [homepages_config]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insertSubUniqueID_HPCreate]
    @email nvarchar(256),
    @provider nvarchar(256),
    @subuid nvarchar(1024)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO homepages_config.dbo.users_providers ([email], [provider_id], [sub_unique_id])
    VALUES (LOWER(@email), @provider, @subuid)

END

Any help would be greatly appreciated.

Your `$stmt = sqlsrv_prepare($conn, $sql, $params);` obviously failed. Possibly because you not providing `$conn`. Check the `$conn` exist and than use `sqlsrv_errors()` to see what exact error was happen — Yaroslav, Mar 25 '20 at 03:57
Questions: 1) Where is `$conn` defined and initialized, 2) What is the connection between `$email`, `$provider`, `$subuid`, `$data` and `$myparams`? Thanks. — Zhorov, Mar 25 '20 at 09:19
$conn is defined in the config.php file that I include in the top of the function. $email, $provider, $subuid and $data get passed in from earlier in the code. $myparams I got from following this code here: https://stackoverflow.com/questions/31575135/how-to-execute-a-stored-procedure-in-php-using-sqlsrv-and-style-parameters - if there is another way I should do it, I'm open. I was just following what was already out there.. blindly. — MyLifeAsBK, Mar 25 '20 at 12:22

Zhorov · Accepted Answer · 2020-03-26T13:29:13.277

First, the stored procedures are correct. One issue is the different declaration for the @provider parameter (@provider nvarchar(64) and @provider nvarchar(256)), but it's probably a typing error.

So, I think that you should make some changes in your script:

Execute the stored procedures with {call sp_name (?, ...)} syntax. This is a possible reason for the Warning: sqlsrv_execute() expects parameter 1 to be resource, bool given in ajax_functions1.live.php on line ... error, because function sqlsrv_prepare() is not executed correctly and you need to check the result from this execution. Note, that you may use sqlsrv_query(), because this function does both statement preparation and statement execution, and can be used to execute parameterized queries.
Change the parameters declaration for the db_add_user() function.
Always check for errors.
The result from sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC) is an associative array, so $row[0]['sub_unique_id'] is an error.

The following script (based on your code) is a possible solution to your problem:

<?php
function db_add_user($email, $provider, $subuid){
    include 'config.php';

    $params = array(
        array($email, SQLSRV_PARAM_IN),
        array($provider, SQLSRV_PARAM_IN)
    );

    $uid = null;

    $sql = "{call [dbo].[getSubUniqueID_HPCreate]( ?, ? )}"
    $stmt = sqlsrv_query($conn, $sql, $params);
    if ($stmt === false) {
        echo print_r(sqlsrv_errors(), true);
        return null;
    }   

    if ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        $uid = $row['sub_unique_id'];
    } else {
        $params = array(
            array($email, SQLSRV_PARAM_IN),
            array($provider, SQLSRV_PARAM_IN),
            array($subid SQLSRV_PARAM_IN)
        );
        $sql = "{call [dbo].[insertSubUniqueID_HPCreate]( ?, ?, ? )}"
        $stmt = sqlsrv_query($conn, $sql, $params);
        if ($stmt === false) {
            echo print_r(sqlsrv_errors(), true);
            return null;
        }   
    }

    return $uid;
}
?>

sqlsrv_execute Errors With PHP Errors and Stored Procedures

1 Answers1