Saved procedure fails while updating

I am using the SQL Server driver for PHP to connect to SQL Server 2008 Express. Right now I am trying to replace all SELECT, UPDATE and INSERT statements with stored procedures. This works great for SPs that simply contain a SELECT statement. But now I tried to do this with the update, and I continue to receive the error message "Executing SQL directly, without cursor." I can cause a fine SP from Management Studio with the same parameter values.

Any ideas?

Greetings Alex

EDIT: here is one update procedure. The funny thing is that the procedure actually performs perfectly and updates the data as intended. But it still returns an error leading to an exception.

First, the PHP code crashes:

if (! $this->Result = sqlsrv_query($this->Conn, $strQuery, $arrParameters, array("Scrollable"=>SQLSRV_CURSOR_STATIC)))
{
    $this->sendErrorMail($strQuery, $arrParameters);
    throw new Exception(4001);
}

SQL

USE [testsite]  
GO  
/****** Object:  StoredProcedure [dbo].[Items_countDownload]    Script Date: 09/09/2010 18:03:28 ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
-- Author:      Alexis Hildebrandt  
-- Create date: 2010-09-09  
-- Description: Increases the download count by 1  
-- =============================================  
ALTER PROCEDURE [dbo].[Items_countDownload]  
    @Id INT  
AS  
BEGIN  
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements.  
    SET NOCOUNT ON;  

    DECLARE @DownloadCount INT = 0, @MaxCount INT = 0, @Id2 INT = 0  

    DECLARE itemCursor CURSOR SCROLL
    FOR
        SELECT Id, Downloads
        FROM Items
        WHERE Id = @Id
        OR SKU IN 
        (
            SELECT SKU FROM Items WHERE Id = @Id
        )
    FOR UPDATE OF Downloads

    OPEN itemCursor

    FETCH NEXT FROM itemCursor
    INTO @Id, @DownloadCount;

    -- Find the largest Download count across all versions of the item
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @MaxCount < @DownloadCount
            SET @MaxCount = @DownloadCount;
        FETCH NEXT FROM itemCursor
        INTO @Id, @DownloadCount;
    END

    -- Increase the download count by one for all versions
    FETCH FIRST FROM itemCursor
    INTO @Id, @DownloadCount;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE Items 
        SET Downloads = @MaxCount + 1
        WHERE CURRENT OF itemCursor
        FETCH NEXT FROM itemCursor
        INTO @Id, @DownloadCount;
    END

    CLOSE itemCursor;
    DEALLOCATE itemCursor;
END
+3
2

"Scrollable"=>SQLSRV_CURSOR_STATIC . , , PHP-.

0
+3

Source: https://habr.com/ru/post/1764014/


All Articles