用于SQL Server的ODBC驱动程序STAR POINTS INSERTED PHP应用程序中的常规警告

Question

I used MSSQL databse (2008R2) to insert values via PHP(v5.28).PHP insert function and MSSQL Stored Procedure are shown below.

This function already work fine and data inserted into SQL Database.

But I got message like this.

transaction[1]
0{insertedid: -1, error: "Oops! An error occurred.... INSERTED
        )

)
"}
insertedid-1
error"Oops! An error occurred. Array
(
    [0] => Array
        (
            [0] => 01000
            [SQLSTATE] => 01000
            [1] => 0
            [code] => 0
            [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]STAR POINTS INSERTED
            [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]STAR POINTS INSERTED
        )

)
"

PHP Function

 public function InsertMainTrn($totAmt, $starpointAmt, $companyKey, $customerKey,$merchantKey,$uid,$type) {
        $tmp = array();
        $response["transaction"] = array();
        $query = "{call [dbo].[InsertTransDt](?,?,?,?,?,?,?)}";
        $params = array(
            array($totAmt, SQLSRV_PARAM_IN),
            array($starpointAmt, SQLSRV_PARAM_IN),
            array($companyKey, SQLSRV_PARAM_IN),
            array($customerKey, SQLSRV_PARAM_IN),
            array($merchantKey, SQLSRV_PARAM_IN),           
            array($uid, SQLSRV_PARAM_IN),
            array($type, SQLSRV_PARAM_IN)
        );
        $conn = $this->getDatabase();
        $stmt = sqlsrv_query($conn, $query, $params);       
        if ($stmt) {
            $tmp["insertedid"] = 1;  
            $tmp["error"]="";
        } else {
            $tmp["insertedid"] = -1;            
            $tmp["error"]= "Oops! An error occurred. " . print_r(sqlsrv_errors(), true);
        }
        array_push($response["transaction"], $tmp);
        header('Content-Type: application/json');
        echo json_encode($response);
        $this->closeDatabase($conn);
    }

SQL Stored Procedure :

USE [testdb]
GO
/****** Object:  StoredProcedure [dbo].[InsertTransDt]    Script Date: 02/02/2016 09:34:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[InsertTransDt]
    @totAmt decimal(18,2),
    @starpointAmt decimal(12,2),
    @companyKey int,
    @customerKey int,
    @merchantKey int,
    @uid varchar(36),
    @type varchar(4)
AS
BEGIN

    SET NOCOUNT OFF;
    DECLARE @Cardkey INT;

    IF @type = 'uid'
        BEGIN
            SELECT @Cardkey =  Cards.cardKey from Cards where Cards.UID = @uid and Cards.fActive = 1
        END
    ELSE    
            SELECT @Cardkey =  Cards.cardKey from Cards where Cards.cardCode = @uid and Cards.fActive = 1


   INSERT INTO [Smartfriends].[dbo].[Transaction]
           ([totalAmt]
           ,[starPointAmt]
           ,[companyKey]
           ,[customerKey]
           ,[merchantKey]
           ,[fDelete]
           ,[tranDate]
           ,[deletedDate]
           ,[trnType]
           ,cardKey)
     VALUES
           (@totAmt
           ,@starpointAmt
           ,@companyKey
           ,@customerKey
           ,@merchantKey
           ,0
           ,GETDATE()
           ,NULL
           ,'ADD'
           ,@Cardkey)


     SELECT SCOPE_IDENTITY() as insertedid
END

Any suggestions for this matter ?

Answer 1

Do you get the same results when you check for an explicit FALSE return value for the sqlsrv_query, like so:

$stmt = sqlsrv_query($conn, $query, $params);
        if ($stmt === false) {
            $tmp["insertedid"] = -1;            
            $tmp["error"]= "Oops! An error occurred. " . print_r(sqlsrv_errors(), true);
        } else {
            $tmp["insertedid"] = 1;  
            $tmp["error"]="";
        }

the difference being that your current code checks for an implicit "true" being returned for $stmt, which may not be equivalent to sqlsrv_query being successful.