SQL (sqlsrv) Cursor not finishing when execute from PHP-CodePudding

The SQL code below works great in MSSQL but when called via php it only outputs roughly half the results. I have tried placing this in a stored procedure and executing from php but that produces the same result as well.

It should be inserting 150 rows, but I am getting only 77, sometimes 78, but it is always one of those two numbers, which leads me to believe its stopping close to half way every time...


error_reporting(0);   //turn off error reporting

session_start();

if ($_SESSION['id']) {

require('../config.php');

$connectionInfo = array( "Database"=>$database, "UID"=>$user, "PWD"=>$password);
$conn = sqlsrv_connect( $host, $connectionInfo);

$sql = "INSERT INTO [RP].[dbo].[RP_UniqueOffers] (offertype)
Select distinct(offertype) from [RP].[dbo].[Offers] t2
where convert(date,[RedeemedDate]) >= convert(date,getdate()-30)
and not exists (Select distinct(offertype) from [RP].[dbo].[RP_UniqueOffers] t1 where t1.OfferType = t2.offertype )

truncate table [RP].[dbo].[RP_Last30days]

DECLARE @id INT
DECLARE @OfferType NVARCHAR(100)
DECLARE @getid CURSOR
Declare @counter int
Declare @nextDate as Date

SET @getid = CURSOR FOR
SELECT [RP].[dbo].[RP_UniqueOffers].ID,
       [RP].[dbo].[RP_UniqueOffers].OfferType
FROM   [RP].[dbo].[RP_UniqueOffers]


OPEN @getid
FETCH NEXT
FROM @getid INTO @id, @OfferType
WHILE @@FETCH_STATUS = 0
BEGIN
    --print @offertype


    Set @counter = 1
    WHILE (@counter < 31 ) 
    begin
    Set @nextDate = convert(varchar,getdate() -30   @counter,101)
    insert into [RP].[dbo].[Last30Days] ([Date],[OfferType])
    Select Convert(varchar,@nextDate,101), @OfferType
    Set @counter = @counter   1
    END


    FETCH NEXT
    FROM @getid INTO @id, @OfferType
END

CLOSE @getid
DEALLOCATE @getid";

$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt = sqlsrv_query( $conn, $sql , $params, $options );
if ($stmt === False) {
    echo "Faild to worked!";
} else {
   // echo "Should have worked... ";
}

sqlsrv_free_stmt($stmt);

}

?>```

CodePudding user response：

For your consideration: lose the cursor. This can be set-based.

$sql = "INSERT INTO [RP].[dbo].[RP_UniqueOffers] (offertype)
Select distinct(offertype) from [RP].[dbo].[Offers] t2
where convert(date,[RedeemedDate]) >= convert(date,getdate()-30)
and not exists (Select distinct(offertype) from [RP].[dbo].[RP_UniqueOffers] t1 where t1.OfferType = t2.offertype )

truncate table [RP].[dbo].[RP_Last30days];

WITH ThirtyDays
AS (
   SELECT top(30)
          DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY object_id)*-1,GETDATE()) as nextDate
     FROM sys.columns
)
INSERT [RP].[dbo].[Last30Days] ([Date],[OfferType])
SELECT CONVERT(char(10),ThirtyDays.nextDate,101)
     , RP_UniqueOffers.OfferType
  from [RP].[dbo].[RP_UniqueOffers]
CROSS JOIN ThirtyDays;
";

CodePudding user response：

Credit to @SalmanA

I needed to add SET NOCOUNT ON to the top of the script as well as using sqlsrv_prepare and sqlsrv_execute in place of sqlsrv_query.