Skip to content
Advertisement

SQL (sqlsrv) Cursor not finishing when execute from PHP

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);

}

?>```

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement