CREATE PROCEDURE dbo.http_SFDCPost
(
@vchFirstName varchar(30),
@vchLastName varchar(30),
@vchEmailAddress varchar(255),
@vchPhoneNumber varchar(20),
@vchCompanyName varchar(255),
@chCountryCode varchar(4),
@chRegionCode varchar(4),
@CustomerID varchar(10),
@campaignid varchar(10),
@vchproduct varchar(50),
@vchdescription varchar(255),
@sfga varchar(50),
@lead_source varchar(50),
@retURL varchar(50),
@oid varchar(50)
)
AS
BEGIN
/*************************************
process to post data to the SalesForce.com servers
Runs when it is called, no schedule
***************************************/
SET NOCOUNT ON
DECLARE @vchURLToPostTo varchar(1000),
@vchHTTPVerbToUse varchar(10),
@vchAuthorization varchar(100),
@encoding varchar(50)
DECLARE @post varchar(4000)
declare @ret int
set @encoding="UTF-8"
set @sfga = "0"
BEGIN
-- now, depending on what kind of notification protocol the SN calls for, we setup the post data
-- to accomodate it.
BEGIN
SELECT @post = 'encoding=' + @encoding + '&first_name=' + @vchFirstName +'&last_name=' + @vchLastName + '&email=' + @vchEmailAddress + '&phone=' + @vchPhoneNumber + '&company=' + @vchCompanyName + '&country=' + @chCountryCode + '&state=' + @chRegionCode + '&oid=' + @oid + '&retURL=' + @retURL + '&lead_source=' + @lead_source + '&00N200000019CLw=' + @vchProduct + '&sfga=' + @sfga + '&00N200000019DkC=' + @campaignid + '&description=' + @vchDescription + '&00N200000019De9=' + @CustomerID,
@vchURLToPostTo = '[URL unfurl="true"]https://www.salesforce.com/servlet/servlet.WebToLead',[/URL]
@vchAuthorization=NULL,
@vchHTTPVerbToUse = 'POST'
END
Print @post
-- URL encode the post data!
-- this is a lame implementation of a URL ENCODE. basically it assumes that the data portion of
-- a varialble will never contain '&' or '=' which just isn't always true
--
-- we do the ENCODE in this lame way, because it's easier than actually working and writing a full Encoder.
declare @i int
declare @ch nchar
declare @hex varchar(6)
SET @i = 1
WHILE @i <= datalength(@post)
BEGIN
SET @ch = substring(@post, @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 = stuff(@post, @i, 1, @hex)
Select @i = @i + datalength(@hex) - 1
END
set @i = @i + 1
END
-- ok, now, do the post!
-- it is likely that the post data if done as a post will need to have dummy variables prepended and appended to the body parameters
-- something in the interaction with TSQL and the WinHTTP.5.1 object puts a space before and after the body, which causes
-- some level of parameter mis-naming and variable data corruption. putting dummy parameters at the beginning and end of the post
-- data ensures that the Parameter Mis-naming and data corruption is limited to the dummy parameters.
exec @ret = http_post_noauth @vchURLToPostTo,
@post OUTPUT
END
SET NOCOUNT OFF
-- all done!
return 0
END
GO