Cursor problem

[wbodger]

OK, I am pulling a recordset at a set interval and looping thru the recordset to 1) post to salesforce and 2) to update the record to mark it as processed.

I do this a few other times for other processes using a cursor, so I just changed a few values and off it went. Minus the update. Now, when I added in the For Update I get the error that I cannot use For Update on a Read-Only cursor and here is my code.

DECLARE cSalesForce CURSOR 
	LOCAL SCROLL KEYSET
	FOR 
		SELECT vchFirstName, vchLastName, vchEmailAddress, vchCompanyName, vchPhoneNumber, chCountryCode, chRegionCode, CustomerID, campaignid, vchproduct, vchdescription, sfga, leadsource, retURL, oid, vchSerialNumber
		FROM SalesForceTempHolding WHERE posted='0'
	FOR UPDATE OF posted, dtposted

OPEN cSalesForce

FETCH NEXT FROM cSalesForce INTO 
...

and then

WHILE @@FETCH_STATUS = 0

BEGIN

...

UPDATE SalesForceTempHolding 
	set	posted = '1',
	    	dtposted = getdate()
WHERE CURRENT OF cSalesForce

Which would beg 2 questions from me. First, could this be done more efficiently w/o a cursor (I inherited the code and hadn't thought about changing it until now) and second, why does it think it is a read-only cursor? Thanks!

WB

 

[ousoonerjoe]

It would appear to me that this entire UPDATE could be done as a SET rather than looping through it. No need to make it more difficult than is needed. It should run faster, too.

"...and did we give up when the Germans bombed Pearl Harbor? NO!"
"Don't stop him. He's roll'n.

 

[wbodger]

So, what would that look like, using a set rather than looping thru it?

 

[jbenson001]

What is the criteria for updating the row(s)?

 

[wbodger]

OK, from one of it's sister sprocs

	exec @ret = http_post @vchURLToPostTo,
			@vchHTTPVerbToUse,
			DEFAULT,
			@vchAuthorization,
			@post_data OUTPUT

	-- determine if the result was successfull
	SET @bRenewalServiceUpdateSuccessful = ~CAST(ISNULL(NULLIF(@ret, 200),0) as bit)


	-- UPDATE the subscription record.. This will flag the record as sent if we have a success code
	UPDATE tMasterSerialNumbers_Subscriptions 
		set	vchRenewalServiceResultCode = @ret,
		    	dtRenewalSentToService = getdate(),
			bRenewalServiceUpdateSuccessful = @bRenewalServiceUpdateSuccessful 
	WHERE CURRENT OF cRenewals

So, when the post process was successful (returns a 200 code), the update needs to happen.

 

[jbenson001]

I am not sure what you are showing me. What you need is:

UPDATE SalesForceTempHolding 
       set    posted = '1',
             dtposted = getdate()
WHERE [red][WHAT IS THE CRITERA HERE ???][/red]

Are you doing this within the cursor you are showing me?

 

[wbodger]

Oh, I see your question. In my initial post (a slightly different sproc) there was no criteria other than being the current record in the cursor. Ideally, I insert the value of the called sproc(0 or 1) which is returned in this code

SET @bRenewalServiceUpdateSuccessful = ~CAST(ISNULL(NULLIF(@ret, 200),0) as bit)

So, the WHERE is truly just update the current record.

Sorry about that.

 

[jbenson001]

So, you want to run the update from inside a Cursor? You will have to post the whole SP and specify where you want the update code. If you are using a cursor within the SP, I suggest you get rid of that one as well.

 

[ousoonerjoe]

Just based on what you have shown here, it looks like all you are doing is updating all records where posted = '0'. There doesn't seem to be any other criteria the UPDATE is based on.

In that case:

UPDATE SalesForceTempHolding
SET posted = '1',
    dtposted = GETDATE()
WHERE posted = '0'

"...and did we give up when the Germans bombed Pearl Harbor? NO!"
"Don't stop him. He's roll'n.

 

[wbodger]

True, as it stands I am missing a piece. I need to change it to include the post result. Here is the code for a complete procedure that works.

CREATE  PROCEDURE dbo.http_OnyxLLERenewals AS
BEGIN
/*************************************
	process to post data to the server

	This script is run nightly via a Scheduled DTS package.
	this is necessary because we don't necessarily want to include this process in the 
	actual allocation of SN's via the sp_assign_Vendor_Serialnumbers  sproc.
	we could.. but I really want something a little more robust in dealing with 
	intermitteent failures.. 
	also, the allocation process needs to be very fast.  This routine has the possibility
	of being quite slow.


***************************************/
SET NOCOUNT ON

DECLARE @vchURLToPostTo varchar(1000),
	@vchHTTPVerbToUse varchar(10),
	@iEntityID int,
	@vchSerialNumber varchar(255),
	@dtAssigned datetime,
	@vchSubscriptionPeriodType varchar(5),
	@iSubscriptionPeriod int,
	@vchSubscriptionNotifyProtocol varchar(255),
	@vchSubscriptionNotifyData varchar(255),
	@dtExpires datetime,
	@dtRenewalSentToService datetime,
	@vchRenewalServiceResultCode varchar(10),
	@bRenewalServiceUpdateSuccessful bit,
	@vchAuthorization varchar(100)


DECLARE @post_data varchar(4000)
declare @ret int

--
-- First off, pull a cursor full of new/un-posted subscriptions for processing.
-- If this cursor comes up without any records, the script will finish very quickly.
-- 
DECLARE cRenewals CURSOR
	LOCAL SCROLL KEYSET
	FOR 

		SELECT tms2.iEntityID,
			ms.vchSerialNumber,
			tms2.dtAssigned,
			pme.vchSubscriptionPeriodType,
			pme.iSubscriptionPeriod,
			(CASE
				WHEN pme.vchSubscriptionPeriodType='m' THEN (dateadd(mm, pme.iSubscriptionPeriod,tms2.dtAssigned)+5)
				WHEN pme.vchSubscriptionPeriodType='d' THEN (dateadd(dd, pme.iSubscriptionPeriod,tms2.dtAssigned)+5)
			END) as dtExpires,
			tms2.dtRenewalSentToService,
			tms2.vchRenewalServiceResultCode,
			pme.vchSubscrptionNotifyProtocol,
			pme.vchSubscrptionNotifyData,
			tms2.bRenewalServiceUpdateSuccessful
		FROM tMasterSerialNumbers_Subscriptions tms join tmasterserialnumbers_subscriptions tms2
			ON tms.iproductid=tms2.iproductid join productmaster_extended pme
			ON tms2.vchProductNumber=pme.chProductNumber join tMasterSerialNumbers ms
			ON tms2.iProductID=ms.iProductID
		WHERE tms2.iParentID<>0
			and tms2.iParentID=tms.iEntityID
			and tms2.bIsRenewed=0
			and tms.bIsRenewed=1
			and tms2.bIsCancelled=0
			and tms.bRenewalServiceUpdateSuccessful = 0
		        and pme.vchSubscrptionNotifyProtocol IN ('LLE')
			and ms.iOwnerID IS NOT NULL
	FOR UPDATE OF tms2.dtRenewalSentToService, tms2.vchRenewalServiceResultCode, tms2.bRenewalServiceUpdateSuccessful


OPEN cRenewals




FETCH NEXT FROM cRenewals INTO 
		@iEntityID,
		@vchSerialNumber,
		@dtAssigned,
		@vchSubscriptionPeriodType,
		@iSubscriptionPeriod,
		@dtExpires,
		@dtRenewalSentToService,
		@vchRenewalServiceResultCode,
		@vchSubscriptionNotifyProtocol,
		@vchSubscriptionNotifyData,
		@bRenewalServiceUpdateSuccessful

WHILE @@FETCH_STATUS = 0
BEGIN
	-- now, depending on what kind of notification protocol the SN calls for, we setup the post data
	-- to accomodate it.
	if @vchSubscriptionNotifyProtocol = 'LLE'
	BEGIN
		-- not implemented yet.
		SELECT @post_data = 'buildstring',
			@vchURLToPostTo  = 'SSL URL',
			@vchAuthorization = 'Basic authcode',
			@vchHTTPVerbToUse = 'POST'
	END
	
	-- URL encode the post data!

	declare @i int
	declare @ch nchar
	declare @hex varchar(6)
	SET @i = 1
	WHILE @i <= datalength(@post_data)
	BEGIN
		SET @ch = substring(@post_data, @i, 1)
		IF @ch not in ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','0','1','2','3','4','5','6','7','8','9','-','_','.','!','~','*',''',(',')','&','=') 
		BEGIN
			if @ch = ' '
			BEGIN
				-- use alternate RFC1738 encoding -- Spaces are encoded as a "+" not "%20"
				set @hex = '+'
			END
			ELSE
			BEGIN
				SELECT @hex = ASCII(@ch)
				-- converts the number from base10 to base16 (dec to hex)
				exec base_convert @hex OUTPUT, 10, 16
				SET @hex = lower('%'+@hex)
			end

			SELECT @post_data = stuff(@post_data, @i, 1, @hex)
			Select @i = @i + datalength(@hex) - 1
		END 
		set @i = @i + 1
	END

	-- ok, now, do the post!
	exec @ret = http_post @vchURLToPostTo,
			@vchHTTPVerbToUse,
			DEFAULT,
			@vchAuthorization,
			@post_data OUTPUT

	-- determine if the result was successfull
	SET @bRenewalServiceUpdateSuccessful = ~CAST(ISNULL(NULLIF(@ret, 200),0) as bit)


	-- UPDATE the subscription record.. This will flag the record as sent if we have a success code
	UPDATE tMasterSerialNumbers_Subscriptions 
		set	vchRenewalServiceResultCode = @ret,
		    	dtRenewalSentToService = getdate(),
			bRenewalServiceUpdateSuccessful = @bRenewalServiceUpdateSuccessful 
	WHERE CURRENT OF cRenewals
		



	FETCH NEXT FROM cRenewals INTO 
			@iEntityID,
			@vchSerialNumber,
			@dtAssigned,
			@vchSubscriptionPeriodType,
			@iSubscriptionPeriod,
			@dtExpires,
			@dtRenewalSentToService,
			@vchRenewalServiceResultCode,
			@vchSubscriptionNotifyProtocol,
			@vchSubscriptionNotifyData,
			@bRenewalServiceUpdateSuccessful
END

-- shut down our cursor
CLOSE cRenewals
DEALLOCATE cRenewals

SET NOCOUNT OFF

-- all done!
return	0

END
GO

So, it grabs the data, traverses thru it and updates one record at a time, posting each set of data to an external server one record at a time.

Thanks for your help!

wb

 

[jbenson001]

WOW. You guys/gals love cursors. This whole process could be done without cursors. I suggest rewriting it. The only place you need to loop is where you actually are using a while loop. And you may be able to get rid of that using the Replace() function.

 

[wbodger]

That sounds good to me, but I'm still not sure what this would look like as a set function rather than looped. Any good examples of sets that you could point me at to get an idea of the direction I could go would be much appreciated.

wb

And, it was a guy that wrote it. He didn't necessarily subscribe to my notion that simpler is better...