UR: Encode SQL Server Post variable

[wbodger]

OK, I have a process that pulls data from a holding table and posts it into SalesForceDotCom. It works great unless the data has European names including special characters (like grave, umlaut etc.). The post data looks like this

@post = 'crapvariable=togetridofspace&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 + '&mf=' + @migrationforecast +  '&pn=' + @vchProduct + '&sfga=' + @sfga + '&cid=' + @campaignid + '&cust=' + @CustomerID + '&sn=' + @vchSerialNumber + '&description=' + @vchDescription  + '&crapvariable=togetridofspace',

And somebody well before my time wrote a pseudo encode function

	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

which does not seem to work (for the special characters) as the records with the special characters are not making it into SalesForceDotCom. So, I went looking around and found a number of options that people said were their encoding functions, but they did not look like they would actually work with the form of my @post variable, so I am coming to this great forum looking for help in doing this and understanding exactly what is happening.

Thank you for your time and help!

WB

 

[jbenson001]

If I understand you correctly, you have a table of values (strings) that you want to insert into another table after they have been encoded? Do these strings consist of HTML or URLs?

Honestly, I have no idea how to do that with SQL nor would I. This is not a process that should be done with SQL. Coming from a .NET background, I would simply pull the table into a .NET datatable, loop through each row and encode what you want, and then insert it into the your other table.

If you have access to Visual Studio, or have a coworker that knows .NET, I would go that route. It is as simple as:

In  your loop ...
var encodedString = Server.HtmlEncode(col from the datatable)
or Server.UrlEncode(col from the datatable)

 

[wbodger]

Actually, I have the data in a temp holding table in SQL Server. Every fifteen minutes I have a DTS package that runs, pulls that data into a cursor and then posts it to SalesForceDotCom using WinHTTP5.1. I guess it would helpful to know that at this point the box is running SQL Server 2000, SP4.

 

[gmmastros]

Do you have a stored procedure in your database called base_convert? If so, can you post the code for it?

[!]exec base_convert @hex OUTPUT, 10, 16[/!]



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Ahh yes

CREATE   PROCEDURE dbo.base_convert(
		@numstring varchar(255) OUTPUT,
		@basefrom bigint,
		@baseto bigint,
		@baselookup char(36) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
) AS
BEGIN

	/*********************************************
		Takes a string representation of a number in any base 2-36 
		and converts it to any other base 2-36
		
		There is currently no error checking or bounds checking!
	*************************************************/

DECLARE @decVal bigint
DECLARE @chr char(1)
DECLARE @iChr bigint
DECLARE @pos bigint
DECLARE @len bigint
DECLARE @pow bigint

	SET @decVal = 0
	
	SET @len = len(@numstring) 
	WHILE @len > 0
	BEGIN
		SET @chr = substring(@numstring, 1, 1)
	   	SET @pos = charindex(@chr, @baselookup)-1;
	   	if (@pos > 0 )
	   	BEGIN
	       		SET @decVal = @decVal + @pos * power(@baseFrom, @len-1)
	   	END
	   	SET @numstring = substring(@numstring, 2, len(@numstring));
		set @len = @len - 1
	END	

	-- figure out the final base rep of the number
	set @numstring = ''

	-- figure out how many positions in this base we need
	set @pos = 1
	while (power(@baseto, @pos)-1) < @decVal
		SET @pos = @pos + 1

	SET @numstring = REPLICATE(left(@baselookup,1),@pos)	
	
	while @pos > 0
	BEGIN
		set @pow = (power(@baseto, @pos-1))
		SET @iChr = floor(@decVal / @pow)
		set @decVal = @decVal - (@iChr * @pow)
		set @numstring = STUFF(@numstring, len(@numstring)-@pos+1, 1, SUBSTRING(@baselookup, @ichr+1, 1))
		set @pos = @pos - 1		
	END

	if @numstring = ''
	BEGIN
		SET @numstring = LEFT(@baselookup,1);
	END
END





GO