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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Items_countDownload]
@Id INT
AS
BEGIN
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;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @MaxCount < @DownloadCount
SET @MaxCount = @DownloadCount;
FETCH NEXT FROM itemCursor
INTO @Id, @DownloadCount;
END
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